inventory forward cover calculation

gsullivan

New Member
Joined
Sep 10, 2009
Messages
3
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,294
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

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
maybe

Excel Workbook
BCD
11505003.2
21505002.8
31506003
41508005
52508006.5
62007006.5
71506006
81005005
91005005
10100
11100
12100
13100
14100
15100
16100
17100
18100
19100
Sheet7
 

Watch MrExcel Video

Forum statistics

Threads
1,108,538
Messages
5,523,428
Members
409,522
Latest member
szophie

This Week's Hot Topics

Top