Forum Discussion

iwjvanaerschot's avatar
iwjvanaerschot
Frequent Visitor
7 years ago

Forecast based on Actual

I am stuck with a running forecast calculation and hopefully someone can help me out!  On a weekly basis I receive a sales forecast and production forecast for several weeks ahead. I want to calcu...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi iwjvanaerschot,

 

Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

 

Regards,

Frank

  • iwjvanaerschot's avatar
    iwjvanaerschot
    Frequent Visitor
    7 years ago

    Hi Frank,

    Thanks for your response. Let me give you a very small data set example of my problem: 



    Normally the Forecasted Sales, Forecasted Production and Actual Stock are located in three different files and connected in the Data Model. But for the sake of explanation I just included them in 1 table now. 

    So I have a forecast plan for sales and production. The fourth column, is the impact on my stock: + Forecasted Production - Forecasted Sales. The calculation I am looking for is a running forecast in the stock column. We are now in week 6 so I want my actual stock of week 5 + forecast impact of week 6. Once we reach week 7, I want to automatically use the week 6 stock as input and calculate the forecasted stock for the coming x weeks based on that volume. Actual stock week 6 + Impact week 7 = Forecasted Stock week 7.  Forecasted stock week 7 + Impact week  8 = Forecasted stock week 8 and so forth.. 

    Hopefully you can help me out! 

    Ian