Accumulating Values for a Rolling Forecast

Mmmm.  Hard one.  How do I take a value and then use it for all periods from now on in a Rolling Forecast environment.  For example, I have a change I want to apply to a rate that is then to be used from now until the end of the rolling forecast.

Year20142015
MonthOctNovDecJanFebMar
Price            100            100            100            100            100            100
Price Change %10.0%10.0%10.0%
Accum Price Change %10.0%10.0%21.0%21.0%33.1%
Price Used            100            110            110            121            121            133
Volume               50               50               50               50               50               50
Revenue         5,000         5,500         5,500         6,050         6,050         6,655

So in the above example, we have two years and 3 months from each.  We have a price we are setting in Oct ($100) and then plan price increases in Nov, Jan and Mar (if only that was reality).  Obviously the price changes need to be cumulative.

So how do we write the rule to do the accumulation.  First of all we need to have both a “Prev Period” attribute in our Month dimension that we can interrogate via an ATTRS.

Then we insert a rule like the following:

 ‘Current Forecast’,’Accum Price Change %’ ,’Amount’] = N:
(
(1 + DB(‘Gross Profit’, !Year, !Month, !Scenario, ‘Price Change %’, !Cost Centre, !Planning Product, !Measures) ) *
(1 + DB(‘Gross Profit’, SUBST(ATTRS(‘Period’,!Month|’ ‘|!Year,’Prev Period’),5,4), SUBST(ATTRS(‘Period’,!Month|’ ‘|!Year,’Prev Period’),1,3), !Scenario, !GP Planning Account, !Cost Centre, !Planning Product, !Measures))
)-1;

So the maths behind this is a = (( 1 + b) * (1 + c)) – 1, where a is the Accumulated Price Change %, b is the Price Change % and c is last months Accumulated Price Change %.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.
This entry was posted in Rule Examples. Bookmark the permalink.