Home Forums Power Pivot Forecasting Opening and Closing Inventory

This topic contains 1 reply, has 1 voice, and was last updated by  gkaropo 4 months, 1 week ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #14382

    gkaropo
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hello all,

    new to the forum.  This site is great!

    I have an issue that I have not been able to solve.  I hope someone can help.
    I have:

    -current starting Inventory

    -monthly sales forecast

    -monthly incoming inventory

    DATA:
    MONTH CURRINV Sales Purchases
    1 80 250 400
    2 200
    3 200
    4 150 450
    5 175
    6 320
    7 200 150
    8 200
    9 250 950
    10 220
    11 100
    12 150

    Im using the following

    InvMovement:=[PURCHASES]-[SALES]

    OPENING INV:=calculate([InvMovement],FILTER(ALL(‘DATE'[DATE]),’DATE'[DATE]< MAX(‘DATE'[DATE])))+[CURRINV]

    ENDING INV:=calculate([InvMovement],FILTER(ALL(‘DATE'[DATE]),’DATE'[DATE]<= MAX(‘DATE'[DATE])))+[CURRINV]

    the problem is that there cases where my Sales Team is forecasting above the available inventory for a future period. When we are out of Stock we still want the forecast to remain, even though we cant fill the sale.

    The above OPENING INV and ENDING INV calculations work when inventory is always positive, but accumulates the negatives on Out of Stocks and results in incorrect (negative) opening inventory positions.

    Id like the ENDING INV results to be 0 if it calculates as negative and OPENING INV of the following period to also be zero.

     

    THANKS!!!

    #14383

    gkaropo
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    This is the file that shows the desired outcome.

    Attachments:
    You must be logged in to view attached files.
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.