# Thread: inventory forward cover calculation Thanks: 0 Likes: 0

1. ## inventory forward cover calculation

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

2. ## Re: inventory forward cover calculation

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

3. ## Re: inventory forward cover calculation

maybe

Sheet7

 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

 Cell Formula D1 =MEDIAN(0,B2,C1)/B2+MEDIAN(0,B3,C1-B2)/B3+MEDIAN(0,B4,C1-B2-B3)/B4+MEDIAN(0,B5,C1-B2-B3-B4)/B5+MEDIAN(0,B6,C1-B2-B3-B4-B5)/B6+MEDIAN(0,B7,C1-B2-B3-B4-B5-B6)/B7+MEDIAN(0,B8,C1-B2-B3-B4-B5-B6-B7)/B8

 Cell Formula B10 =B9

Excel tables to the web >> Excel Jeanie HTML 4

4. ## Re: inventory forward cover calculation

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

GS

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•