Formula to calculate forward coverage

twilson4

New Member
Joined
Jul 27, 2015
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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 CodeStock On HandJulAugSep OctNovDecMonths of Cover
A2001003030302020 ?
B15010105555 ?

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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
I tried co create VBA Code for custom function called =stockcoverW(B5,C3:G3) to calculate any coverage month/ week/ day in bucket. But seems not work. Please help.

Public Function StockCoverW(SOH As Range, Demand As Range, WeekMonth As Range) As Double
Application.Volatile
Dim cell As Range, SubTotal As Double, Counter As Integer
Dim i
i = 1
Counter = SOH.Value
For Each cell In Demand
If Counter >= cell.Value Then
Counter = Counter - cell.Value
SubTotal = SubTotal + WeekMonth.Cells(1, i)
Else
SubTotal = SubTotal + Counter / (Cells(cell.Row, cell.Column) / WeekMonth.Cells(1, i))
Exit For
End If
i = i + 1
Next cell
StockCoverW = SubTotal
End Function
 
Upvote 0
I tried co create VBA Code for custom function called =stockcoverW(B5,C3:G3) to calculate any coverage month/ week/ day in bucket. But seems not work. Please help.

Public Function StockCoverW(SOH As Range, Demand As Range, WeekMonth As Range) As Double
Application.Volatile
Dim cell As Range, SubTotal As Double, Counter As Integer
Dim i
i = 1
Counter = SOH.Value
For Each cell In Demand
If Counter >= cell.Value Then
Counter = Counter - cell.Value
SubTotal = SubTotal + WeekMonth.Cells(1, i)
Else
SubTotal = SubTotal + Counter / (Cells(cell.Row, cell.Column) / WeekMonth.Cells(1, i))
Exit For
End If
i = i + 1
Next cell
StockCoverW = SubTotal
End Function
You would be better off posting your question to a new thread of its own, instead of posting on an old inactive thread where most users have not been active on this board in quite some time.
Then it will show up as a new unanswered question in the "Unanswered threads" listing for all to see.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top