Thread: Formula to calculate forward coverage Thanks:  1 Post #4227696 (1) Likes: 0

1. Formula to calculate forward coverage

Hello,

I am wondering if it is possible to write a formula in excel (without VBA) to calculate forward coverage.

For example.

Beginning Inventory = 1000
Month 1 Forecast = 500
Month 2 Forecast = 200
Month 3 Forecast = 500

Coverage = 2.6 Months

The formula needs to work no matter how many months of coverage there are and be the same formula in every cell.

Here is an example of how my spreadsheet is laid out

January February March April May June
Beginning Inventory 1000 700 500 200 600 600
Forecast 500 200 500 600 200 200
Receipts 200 0 200 1000 200 100
Month's of Coverage ? ? ? ? ? ?

Any ideas on how this would work?  Reply With Quote

2. Re: Formula to calculate forward coverage

Welcome to MrExcel...

Given a set up like this:

Excel 2012
ABCDEFG
1JanuaryFebruaryMarchAprilMayJune
2Beginning Inventory1,000700500200600600
3Forecast500200500600200200
4Receipts20002001,000200100
5Month's of Coverage2.602.001.000.33999.00999.00

Sheet2

In B5, try:

Code:
=IF(B2 < SUM(B3:\$G3),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B3:\$G3,,,,COLUMN(B3:\$G3)-COLUMN(B3)+1)) < =B2))+LOOKUP(0,SUBTOTAL(9,OFFSET(B3:\$G3,,,,COLUMN(B3:\$G3)-COLUMN(B3)+1))-B3:\$G3-B2,(B2-(SUBTOTAL(9,OFFSET(B3:\$G3,,,,COLUMN(B3:\$G3)-COLUMN(B3)+1))-B3:\$G3))/B3:\$G3),IF(B2=SUM(B3:\$G3),COUNT(B3:\$G3),999))
And copy over to G5.

I have used a value of 999 where it is impossible to calculate the coverage due to the inventory being more than the forward forecast. This can be changed to something else if you wish.

Hope this helps.

Matty  Reply With Quote

3. Re: Formula to calculate forward coverage

Matty - thanks for the quick response. The formula works great. If inventory goes negative, is it possible to have it show the theoretical negative months coverage? If not, how would I put in 0 instead of getting and "N/A"?

Thanks  Reply With Quote

4. Re: Formula to calculate forward coverage

Try:

Code:
=IF(B2<0,0,IF(B2 < SUM(B3:\$G3),SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B3:\$G3,,,,COLUMN(B3:\$G3)-COLUMN(B3)+1)) < =B2))+LOOKUP(0,SUBTOTAL(9,OFFSET(B3:\$G3,,,,COLUMN(B3:\$G3)-COLUMN(B3)+1))-B3:\$G3-B2,(B2-(SUBTOTAL(9,OFFSET(B3:\$G3,,,,COLUMN(B3:\$G3)-COLUMN(B3)+1))-B3:\$G3))/B3:\$G3),IF(B2=SUM(B3:\$G3),COUNT(B3:\$G3),999)))
Matty  Reply With Quote

5. Re: Formula to calculate forward coverage

That works. Thanks again!  Reply With Quote

6. Re: Formula to calculate forward coverage

Hello, thanks for the solution, lets say we want see it in days rather than months of coverage, what is the change that we need to do?  Reply With Quote

7. Re: Formula to calculate forward coverage

Welcome to Mr Excel,

How is your data laid out? If in weekly buckets, you can just multiple the result of the formula by 7 to get days.

Cheers,

Matty  Reply With Quote

8. Re: Formula to calculate forward coverage

Hi, I need help to calculate the inventory months of coverage for rolling forecast. This is to determine how long will the current stock will last.
Is there a formula to calculate actual coverage for product A and B (by reducing the rolling month forecast) instead of applying average coverage base on average forecast?

 Forecast Product Code Stock On Hand Jul Aug Sep Oct Nov Dec Months of Cover A 200 100 30 30 30 20 20 ? B 150 10 10 5 5 5 5 ?  Reply With Quote

User Tag List

Tags for this Thread

formula, forward coverage, inventory  Posting Permissions

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