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.