How to calculate forecast percent based on latest 3 months (average) actuals?

Hi,

 

I have a source module without a version list/dimension. It contains only actuals.

 

What I would like to do is a new module that contains actuals and forecasts. There should be a version list, which contains Actuals and Forecasts. Forecast figures should be calculated from the latest 3 months average (from actual version). 

 

My question is what kind of setup is best and what kind line item formula I should implement to target module?

 

The attached is picture illustrates my need better.

 

Best Answer

  • Misbah
    Answer ✓

    antti.kerola@elisa.fi 

     

    I must have overlooked at your dimensionality. 

     

    1. Yes if you have percentages then you can tweak the mode of the formula  => MOVINGSUM(Actuals,-2,0,AVERAGE)

    2.  You can use IF Actuals = 0 THEN Previous(Forecast) + Actuals ELSE Actuals

    3. Write IF ELSE Statements in your target module if your target module has Versions and Source doesn't.

     

    Source Module has no version but two line items

    Misbah_0-1588861029630.png

    Below module has Versions as a dimension and one line item

    Misbah_1-1588861110299.png

     

Answers

  • antti.kerola@elisa.fi 

     

    Try this and see if it helps

     

    You can create a module without version as a dimension and insert two line items

     

    Actuals LI: Source Module.Line item[SELECT:Versions.Actual]

    Forecast LI: MOVINGSUM(Actuals LI,-2,0,SUM) 

     

  • Thanks for your reply.

     

    I think that there is a mistake Forecast LI: MOVINGSUM(Actuals LI,-2,0,SUM) -> SUM should be AVERAGE. Another issue is that line item brings only the correct value for first forecast period (Mar 20). March 20 value should be for all forecast periods.

     

    The third thing that SELECT: Versions.Actual doesn't work either because the source module doesn't include version.

     

  • Thank you it works! What's your opinion from performance point of view if I would like to put actual + forecast as one line item. This line item could use switchover or would it be better from performance point of view to use Version module?

  • antti.kerola@elisa.fi 

     

    With Switchover comes added benefits - Space Consumption is less because Anaplan doesn't take space for Switchover periods.

     

    However there are factors that you need to consider as well

    1. Changing Switchover monthly is an admin activity, can't be automated - so monthly maintenance

    2. You can't move Switchover date backwards, if done so there will be data loss -Hence you have yo be very careful