Rollforward data with average growth rate from actuals

If you, for example, want to let costs grow with the same average rate as in past years, the following pattern is useful.

Suppose your last year of actual data is 2018, then calculate the Growth Rate for the future years, based on the past as:

Growth Rate (Future)

FILTER(ADDEACH(EXPAND(ROLLUP(FILTER(  'Base Data' / SHIFT('Base Data',"Year",-1),"Year","2018","lte"),[-- Insert Dimensions and Levels to keep here–] "AVG"),"Year"),-1),"Year","2018","gt")

Now, to calculate the future rollforwards, use the standard ROLLFORWARD Syntax.

How it works in detail

Formula Part Detail
'Base Data' / SHIFT('Base Data',"Year",-1) Calculate the Yearly growth rate. Note that this works along all dimensions that your data may have, e.g. products etc.
FILTER( .. , "Year","2018","lte") keep only our actuals, e.g. remove the 2019 0 growth rate that is calculated.
ROLLUP( ... , ,[-- Insert Dimensions and Levels to keep here–] "AVG") Create the average of the past years
EXPAND(..."Year") Copy the average value in all years.
ADDEACH(... ,-1) Subtract 1 to get the actual growth, not the ratio.
FILTER(...,"Year","2018","gt") The growth rates are only applicable to the future, and hence should only be changed in the simulation years.



Tip

We recommend to further extend the model above with a "Correlation to Past Growth Rate" node, which is 0 by default. Thereby you can enable and adjust the correlation in a simulation, and quantify the effects of the past growth rate, for example in bridges.


Was this helpful?

Yes | Somewhat | No