Results 1 to 8 of 8

Thread: Formula to calculate forward coverage

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  3. #3
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  5. #5
    New Member
    Join Date
    Jul 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to calculate forward coverage

    That works. Thanks again!

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  7. #7
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 ?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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