How can I calculate Months on Hand of inventory with a forecast that varies each month (not using to-CodePudding

I have an excel spreadsheet and column E shows the projected monthly demand, starting in E4. column G shows the latest inventory, starting with opening inventory in cell G3 and then each cell below it subtracts the next month's demand and adds in the next month's supply to calculate the projected closing inventory (so G4=G3-E4 F4). In column H, we would like to calculate the projected months on hand (MOH) that the inventory at the close of the prior month can satisfy. The current setup simply divides the inventory by an average demand across the horizon. This falls apart when you have seasonality of product demand or an increasing or decreasing demand year over year.

Example (assumes no new supply added):

Product A (inventory 100) forecast is 20 20 20 20 20 20 20, so MOH would be 5.00
Product B (inventory 100) forecast is 10 10 20 20 50 10 10, so MOH would be 4.80
Product C (inventory 100) forecast is 0 0 50 0 0 50 0 , so MOH would be 6.00
Product D (inventory 100) forecast is 0 5 10 15 20 25 30, so MOH would be 6.83

Any advice?

CodePudding user response：

So, not built your model, but this is a model that works over 6 months. You can take this and edit to suit your situation.

The production capacity varies due to staff holidays etc. The closing stock is held between limits - the lower limit to give an emergency stock.

Costs or revenue calculations can be added as well as stock costs.

CodePudding user response：

Using BYCOL/LAMBDA(but this requires office 365):

=SUM(BYCOL(B1:H1,LAMBDA(z,IF(AND(z=0,SUM(B1:INDEX(1:1,COLUMN(z)))<A1),1,MAX(MIN(A1-IF(COLUMN(B1)<>COLUMN(z),SUM(B1:INDEX(1:1,COLUMN(z)-1)),0),z),0))/IF(z=0,1,z))))

for 2010 we will need to make it volatile with OFFSET:

=SUM(IF(IF(COLUMN(B1)<>COLUMN(B1:H1),SUMIF(OFFSET(B1,0,0,1,COLUMN(A1:INDEX(1:1,COLUMNS(B1:H1)))-1),"<>"),0)<A1,IF(SUMIF(OFFSET(B1,0,0,1,COLUMN(A1:INDEX(1:1,COLUMNS(B1:H1)))),"<>")<=A1,1,(A1-SUMIF(OFFSET(B1,0,0,1,COLUMN(A1:INDEX(1:1,COLUMNS(B1:H1)))-1),"<>"))/B1:H1),0))

This may require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode in 2010