I need to create a excel formula to find out how long the product in stock will last based in forecast per month. Like in the example image below, with 10 products stock, I’m able to last for 4.5 month following this sales forecast.
Tried to find some other examples, but can’t find the exactly what resolve my problem. I’m able to make this by create a column until get a negative value, sum this values that are not negative and divided but stock product, but should be something more simple.
2
Using Office 365 you could make use of SCAN:
=LET(s,SCAN(B1,A4:L4,LAMBDA(a,b,a-b)),
x,XMATCH(0,s,-1),
i,INDEX(s,,x),
j,INDEX(A4:L4,,x),
IF(i,x-1+(j+i)/j,x))
2
I would use two helper columns to determine the beg inventory and ending inventory. Then use a match formula to find the last month you can fulfill demand and then take a percentage of the remaining stock against the next month.
What the formula below does is the first component finds the non-exact match that’s greater than 0. Giving us the column of the array where we have our last remaining inventory. The next component then finds the value of that remaining inventory and divides it by the following month’s demand to determine how much as a percent you can fill. In this case only half, therefore it’s 4 + .5 = 4.5. If your demand varies you’ll see the .5 adjust to how much can be filled.
=MATCH(0,B6:M6,-1)+(INDEX(B6:M6,1,MATCH(0,B6:M6,-1))/INDEX(B4:M4,1,MATCH(0,B6:M6,-1)+1 ))