# inventory forward cover calculation

#### gsullivan

##### New Member
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

### 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
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"))

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

#### gsullivan

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

GS

Replies
1
Views
47
Replies
6
Views
58
Replies
1
Views
93
Replies
0
Views
43
Replies
5
Views
278

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