Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Jun 2008
    Location
    North Carolina
    Posts
    2,465
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: inventory forward cover calculation

    maybe

    Sheet7

    BCD
    11505003.2
    21505002.8
    31506003
    41508005
    52508006.5
    62007006.5
    71506006
    81005005
    91005005
    10100
    11100
    12100
    13100
    14100
    15100
    16100
    17100
    18100
    19100

    Spreadsheet Formulas
    CellFormula
    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



    Spreadsheet Formulas
    CellFormula
    B10=B9


    Excel tables to the web >> Excel Jeanie HTML 4
    May I deal with Honor
    May I act with Courage
    May I strive for Humility
    "Straight" By **** Francis

  4. #4
    New Member
    Join Date
    Sep 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: inventory forward cover calculation

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

    GS

Some videos you may like

User Tag List

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
  •