Calculate the number of weeks the stock on hand will last 1

[cashback]

I need to calculate how many weeks my on hand stock will last based on the weekly demands going out 30 weeks (current limit of data for each item).
Column A has the item code
Column B has the On Hand stock (each row = 1 week)
Column C has the weekly Demand

Data example
Cell B2 has 245 units as the On Hand inventory. B3 is calculated (B2 - C2)and so on for each cell in Col B.
Cells C2 = 46
Cells C2 = 64
Cells C2 = 62
Cells C2 = 62
Cells C2 = 59
Cells C2 = 57
Cells C2 = 57

I need the formula in Cell D2 to look at the value in B2 and then work out how many rows of demand from column C it will take to consume the 245 units. (answer should be 4.2 rows)
245-46-64-62-62-(11/59)to 1 decimal = 4.2

Can anyone give me the correct function I need to do this..

 

[ETID]

Can you give me an example of how that would look?

 

[cashback]

Better Idea (if possible)
example:
Col A = Item No.
Col B = Inventory
Col C = Demand

Rows 2 through 41 have item 123
Rows 42 through 81 have item 456

If the inventory in row 36 = 600 and the demand in C36, C37, C38, C39, C40, C41 totals 500 the calc would not be able to determine the number of weeks correctly as there would 100 remaining. Hence the calc could switch to averaging the remaining rows of the item (100) and provide the answer as 6 weeks for that row. The next would do the same but only using the remaining 5 rows and perhaps end up with a different avg. Last row would basically divide the inventory by the demand in that row. (if demand was less than the inventory). At least this gives a cleaner way to get a reasonable value in the last few rows..What do you think..?
Logic = If Total demand for each item is greater than the inventory your calc works. If not switch to an average of the remaining demand to detemine the cover.

 

[cashback]

To ETID,

Any chance you could modify the code for the Function StockDepletedWeek to do what I have suggested.

I've tried to do it myself but It's beyond my understanding.

I'm willing to learn, if you can break the steps down for me. Happy to send you the sheet I'm using it in.

Thanks

Kevin

 

[ETID]

Ok..I thing an an example of your data will clear up some confusion on my end.

send to:

pms512@suscom.net