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

Highlighted

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.

 

5 REPLIES 5
Highlighted
Master Anaplanner/Community Boss

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

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) 

 

Highlighted

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

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.

 

Highlighted
Master Anaplanner/Community Boss

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

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

 

Highlighted

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

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?

Highlighted
Master Anaplanner/Community Boss

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

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