inventory forward cover calculation

[gsullivan]

Hi, I am looking for a one cell formula that can be copied down, that will give me my forward inventory cover. To explain, forward cover is the number of weeks or days that I have of inventory as cover for a specific level of inventory at a particular period of time, based on forecast sales. ie: ( I hope this little table makes sense!)

Week / Sales / Stock-value / Cover weeks
1 / 150 / 500 / 3.2
2 / 150 / 500 / 2.8
3 / 150 / 600 / 3.25
4 / 150 / 800 / 5
5 / 250 / 800 / 6.5
6 / 200 / 700 / 6.5
7 / 150 / 600 / 6
8 / 100 / 500 / 5
9 / 100 / 500 / 5

I have manually worked out the cover weeks in the above example to (ie for week 1 we have stock of 500 and this will cover us for 3.2 weeks at the projected sales), but it is this that I want to caluclate as a formula.

All help is greatly appreciated.

Regards,
GS

 

[Domenic]

For Week 3, where the stock value is 600, shouldn't it cover 3 weeks, not 3.25? As I understand it, the stock would cover the projected sales for Week 4 (150), Week 5 (250), and Week 6 (200). If this is correct, assuming that A1:C10 contains the source data, including the headers, try...

D2, copied down:

=IF(C2 < SUM($B3:$B$10),SUMPRODUCT(--(SUBTOTAL(9,OFFSET($B3:$B$10,,,ROW($B3:$B$10)-ROW($B3)+1)) < =C2))+LOOKUP(0,SUBTOTAL(9,OFFSET($B3:$B$10,,,ROW($B3:$B$10)-ROW($B3)+1))-$B3:$B$10-C2,(C2-(SUBTOTAL(9,OFFSET($B3:$B$10,,,ROW($B3:$B$10)-ROW($B3)+1))-$B3:$B$10))/$B3:$B$10),IF(C2=SUM($B3:$B$10),ROWS($B3:$B$10),"Inventory Exceeds Projected Sales"))

 

[wsjackman]

maybe

Excel Workbook
	B	C	D
1	150	500	3.2
2	150	500	2.8
3	150	600	3
4	150	800	5
5	250	800	6.5
6	200	700	6.5
7	150	600	6
8	100	500	5
9	100	500	5
10	100
11	100
12	100
13	100
14	100
15	100
16	100
17	100
18	100
19	100
Sheet7

 

[gsullivan]

Many thanks for your help guys, appreciate it. Will give them both a try.

GS

 

[BMil8]

For Week 3, where the stock value is 600, shouldn't it cover 3 weeks, not 3.25? As I understand it, the stock would cover the projected sales for Week 4 (150), Week 5 (250), and Week 6 (200). If this is correct, assuming that A1:C10 contains the source data, including the headers, try...

D2, copied down:

=IF(C2 < SUM($B3:$B$10),SUMPRODUCT(--(SUBTOTAL(9,OFFSET($B3:$B$10,,,ROW($B3:$B$10)-ROW($B3)+1)) < =C2))+LOOKUP(0,SUBTOTAL(9,OFFSET($B3:$B$10,,,ROW($B3:$B$10)-ROW($B3)+1))-$B3:$B$10-C2,(C2-(SUBTOTAL(9,OFFSET($B3:$B$10,,,ROW($B3:$B$10)-ROW($B3)+1))-$B3:$B$10))/$B3:$B$10),IF(C2=SUM($B3:$B$10),ROWS($B3:$B$10),"Inventory Exceeds Projected Sales"))

I realize this post is over 10 years old so you may not even still be here, but I came across it in my search for the same question. I feel like I'm going crazy because for the life of me, I can't get it to work if I change the data to columns going across instead of down by row. I changed the Row to Column and the cell references from B3:B10 to B3:I3 but I just get N/A. What am I missing?