Projected Inventory Run Out Date

[dowdc]

Hi All, I need a formula that provides the date inventory will run out based on a quantity on hand and a demand forecast. I'm assuming an array function will be necessary.

I would like to assume that the demand per month is evenly spread per day. For example, Item 123A has 500 units on hand. Based on the forecast, I would run out of inventory sometime in July. I would like the run out date to assume a demand of 11.29 units per day in July (350/31) and give me a run out date of 7/17/19.

Appreciate your help!

Item # <tbody> </tbody>	Inventory On Hand <tbody> </tbody>	Run Out Date <tbody> </tbody>	5/1/2019 <tbody> </tbody>	6/1/2019 <tbody> </tbody>	7/1/2019 <tbody> </tbody>	8/1/2019 <tbody> </tbody>	9/1/2019 <tbody> </tbody>	10/1/2019 <tbody> </tbody>	11/1/2019 <tbody> </tbody>	12/1/2019 <tbody> </tbody>	1/1/2020 <tbody> </tbody>	2/1/2020 <tbody> </tbody>	3/1/2020 <tbody> </tbody>	4/1/2020 <tbody> </tbody>
123A <tbody> </tbody>	500 <tbody> </tbody>		100 <tbody> </tbody>	200 <tbody> </tbody>	350 <tbody> </tbody>	200 <tbody> </tbody>	325	370	500	200	100	350	400	100
678B <tbody> </tbody>	2000 <tbody> </tbody>		300 <tbody> </tbody>	800 <tbody> </tbody>	600 <tbody> </tbody>	200	100	700	600	400	300	500	300	300
843C <tbody> </tbody>	200 <tbody> </tbody>		5 <tbody> </tbody>	20 <tbody> </tbody>	50 <tbody> </tbody>	50	20	18	20	5	75	80	75	30

<tbody>
</tbody>

 

[dowdc]

bump

 

[dowdc]

ttt

 

[dowdc]

bump