How do you take a value from one period and roll it to the next in TM1? This is a really simple task in TM1.
Why Would you Pull a Value Forward?
Often when we are doing financial modelling or operational planning in TM1, we need to be able to take closing balance and roll it to the opening balance of the next period.
For example, you might be calculating inventory and your formula might be Closing Balance = Opening Balance + Purchases – Cost of Sales. That’s easy, yeah? Well then the next month you want to use the same formula, but you want to populate the Opening Balance with the Closing Balance from the previous month. That’s easy too and we’ll show you how below.
How to Roll Forward Values in TM1
To do a roll forward in TM1 we need to do a few things:
- Set up a Prior and Next Period attributes in the time dimensions
- Add a rule to pull the values from Closing Balance into Opening Balance
- Add a feeder to push the Closing Balance to activate the Opening Balance cell
- Check results
Time needed: 30 minutes
How to calculate a value that rolls from one month to the next TM1?
- Create Prior and Next Period Attributes
First of all we need to have prior period attributes.
If you have Month and Year dimensions, then create a Prior Month and Next Month attributes in the Month dimension and a Prior Year and Next Year attributes in the Year dimension.
If you have a single, continuous, time dimension with months and years in it, then create a Prior Period and Next Period attributes.
These attributes can then be interrogated via an ATTRS to enable the roll forward fuel and the feeder.
- Write a Rule to Pull Forward the Closing Balance:
['Opening Balance'] = DB('Inventory Plan',IF(!Month@='Jan',ATTRS('Year',!Year,'Prior Year'),!Year),ATTRS('Month',!Month,'Prior Month'),!Version,!Product,'Closing Balance','Value'); - Create Feeders to Activate the Future Cells
['Closing Balance'] => DB('Inventory Plan',!Year,ATTRS('Month',!Month,'Next Month'),!Version,!Product,'Opening Balance','Value');
['Dec','Closing Balance'] => DB('Inventory Plan',ATTRS('Year',!Year,'Next Year'),'Jan',!Version,!Product,'Opening Balance','Value');
- Review Results
The final step in carting a roll forward in TM1 is to review the results in a view. Here is the result we have from the above.
You can see that we start with a zero balance in January and then add on Purchases and subtract Cost of Sales to calculate Closing Balance. This is all in the dimension hierarchy and the weight of Cost of Sales is set to -1 (so it is treated as a negative).
Then the rule pulls the value from January Closing Balance into February Opening Balance and so on.
Finally, if we were to change year, the Opening Balance fo rJanuary in 2021 would be the Closing Balance for December 2020.
