Forward inventory months of coverage

[elysium]

Hi All,

Hoping to get some help here, I followed a solution provided in an old thread for forward coverage of projected inventory and it is working in some columns but not in the ones in yellow (should be 1 month of coverage each).

This is the formula I have in cell b4: =IFERROR(IF(B3<=0,0,IF(B3<SUM(C2:$K2),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(C2:$K2,,,,COLUMN(C2:$K2)-COLUMN(C2)+1))<B3))+LOOKUP(0,SUBTOTAL(9,OFFSET(C2:$K2,,,,COLUMN(C2:$K2)-COLUMN(C2)+1))-C2:$K2-B3,(B3-(SUBTOTAL(9,OFFSET(C2:$K2,,,,COLUMN(C2:$K2)-COLUMN(C2)+1))-C2:$K2))/C2:$K2),IF(B3=SUM(C2:$K2),COUNT(C2:$K2),999))),0)

Would appreciate if someone can guide me one what went wrong, thank you.

Key Figure	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22
Forecast	27	28	43	95	121	94	78	72	63	57
Projected Inventory	188	160	117	121	94	78	72	63	57	0
Fwd Coverage	3.2	2.2	1.2	0.0	0.0	0.0	0.0	0.0	1.0	0.0

 

[DRSteele]

I have no idea how this works, but I changed the big bold part to less than or equal to and thar she blows!

=IFERROR(IF(FALSE,0,IF(TRUE,SUMPRODUCT(--(SUBTOTAL(9,OFFSET(C2:$K2,,,,COLUMN(C2:$K2)-COLUMN(C2)+1))<=B3))+LOOKUP(0,SUBTOTAL(9,OFFSET(C2:$K2,,,,COLUMN(C2:$K2)-COLUMN(C2)+1))-C2:$K2-B3,(B3-(SUBTOTAL(9,OFFSET(C2:$K2,,,,COLUMN(C2:$K2)-COLUMN(C2)+1))-C2:$K2))/C2:$K2),IF(B3=SUM(C2:$K2),COUNT(C2:$K2),999))),0)

 

[HeadyFinance]

Hi, try this method:

Formula to find months inventory coverage

Hi, Need help to find out the formulation to use to calculate the months of inventory coverage base on the Stock on hand ( Column B ) verus the monthly demand ( Column D to O )

www.mrexcel.com