Exploring TM1 - a Chartertech Company
Search
Close this search box.

How to Accumulate Values in TM1 for Rolling Forecast or Cashflow

How do I take a value and then use it for all periods from now on in a Rolling Forecast environment?  In other words, how do I accumulate (or calculate a cumulative) value over time – especially if that goes across years? Accumulation can be daunting to a novice, but it really is quite easy. Here’s how.

How to Accumulate over Multiple Years in TM1

Let’s say I want to calculate a new measure called Accumulated Revenue over multiple years. I have the following dimensions in a cube called “Gross Profit”:

  • Year
  • Month
  • CostCentre
  • Product
  • Scenario
  • Gross Profit Measures
Year2021  2022  
MonthOctNovDecJanFebMar
Revenue100012009509007501200

How to calculate accumulated values across years in TM1?

  1. Create Prior Period Attributes

    First of all we need to have both a ‘Previous Period’ attribute in the Month dimension and a ‘Previous Year’ attribute in the Year dimension. Both of these can then be interrogated via an ATTRS to enable the accumulation.

  2. Write a rule like the following:

    [‘Accumulated Revenue’ ] = N: [‘Revenue’] + DB ( ‘Gross Profit’ , IF ( ( !Month @= ‘Jan’ ) , ATTRS ( ‘Year’ , !Year , ‘Previous Year’ ) , !Year ) , ATTRS( ‘Month’ , !Month , ‘Previous Period’ ) , !CostCentre , !Product, !Scenario, ‘Accumulated Revenue’ );

In essence what this is saying is to do the cumulative calculation for Accumulated Revenue, take Revenue and add onto it the prior month’s value for Accumulated Revenue.  We use an ATTRS function to get the value from the Previous attributes. There is the complication that the example (deliberately) covers year end, so we need to initially test if the Current Month is ‘Jan’ and if so, look up the ‘Previous Year’ attribute from the Year dimension. If the Current Month is not ‘Jan’, then just use the value from !Year. Then we need to look up the ‘Previous Period’ from the Month dimension. The remainder of the rule just uses the current values from the remaining dimensions and specifies that we use the value already calculated from ‘Accumulated Revenue‘.

Calculating a Cumulative Rate

A more complicated example of how to accumulate values is this.  I have a change I want to apply to a ‘Price Change %’ rate that is then to be used from now until the end of the rolling forecast.

Year2021  2022  
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

In the above example of accumulation, we have two years and 3 months from each of the Year and Month dimension.  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.

Rule Required for Rate Accumulation

So how do we write the rule to do the accumulation?  We need to have 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 accumulation 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 %.

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

John Vaughan

John Vaughan

John is a CPA, MBA and has been a Performance Management consultant for over 25 years. He is the founder of ExploringTM1 and highly regarded for his experience combining financial management with corporate planning, reporting and analysis. He lives in Sydney with his wife, two of his three children, their cat, Freckles, a bunch of chooks and some fish. John is a sports nut, who played rugby until he was 40, started playing football at 54 and loves being outdoors.

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In