Inventory run out based on date available, not the forecast start date

[Loobyw]

Hi

Thank you for accepting me to these forums. I'm not new to excel, but my latest project is taxing my formula knowledge, I've done a search but can't find this exact problem mentioned and I'd appreciate some expert help please?

I've uploaded an example spreadsheet that shows what I'm trying to achieve. The cells/formulas I'm specifically seeking help with are highlighted in BLUE.

I have materials that could have 4 stock values assigned to them a) in stock b) WIP c) future stock d) alternative supplier stock

The sheet also includes the usage forecast for each material.

In stock is OK, as the offset formula assumes that it is available at the start of the forecast, so calculates the stock out date correctly.

WIP, future stock and alt supplier stock are what are causing me problems as the formula assumes that the stock is available when In Stock runs out and this is not the case.

I would like a formula that calculates the stock out date based on when the stock is available, but using the forecast to calculate it accurately.
ie In Stock runs out on 21/10 but WIP isn't available until 31/10 - currently it calculates a date based on 21/10 - how do I get it to look at 31/10 and the forecast from that date instead?

Apologies in advance if this isn't altogether clear.

TIA

[JeteMc]

Hello Loobyw and Welcome to Excel Forum.
It appears that the only blue cells that have formulas are P5:P6.
Neither of them display 21/10 and neither of the dates in J5:J6 are 31/10 which I feel makes it is hard to understand the issue.
Perhaps it would help if we could get a detailed explanation of why the values shown in P5:P6 (assuming those are the cells in in need of correction) are incorrect, then what the correct values should be and why.
Let us know if you have any questions.

[Loobyw]

Hi

Thank you for replying. I'll summarise the function of the sheet and what I'm trying to achieve the formulas, hopefully it will make sense - i've checked the file I uploaded and apologies, the dates don't match but the formulas are there in columns H,L&P (although P is different because a colleague tried to help). I'm looking for a formula solution for these columns.

This sheet tracks raw materials coming into stock, but the in stock date can be before or after the previous stock runs out - it doesn't currently use the instock date to start from, it goes from the start of the forecast and runs down the total.

The way I've tried it is to leave the formula as is, and add the number of days between stock run out and new stock arriving but can't get it to work within my abilities. ie if the stock runs out on 4th November and new stock arrives on 14th November add 10 days onto the run out date calculated by this formula.

In my spreadie example i'll use row 5 as the example:
Current stock runs out on 4th November
New (Orange)stock due in on 14th November so the run out should be >9th December (column L), but this stock in date is not being taken into account by the formula as it states 2nd December.
Further (yellow) stock is due in on 24th November - so the run out date of this shouldn't say 14th October it should say sometime after 16th December (column P).
The same premise for column U based on stock arriving in column T.

It would be amazing if there is a solution for this, thank you.

[JeteMc]

It seems to me that the formula in column H is yielding the correct results, so I am going to work on the formula for column L and when you confirm that one is yielding correctly attempt a formula for column P.
This array entered formula** yields a result of 9 December in cell L5:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.