Rolling Forecast in TM1

[ExApplix]

I am trying to digest the concept of Rolling Forecast and implementing it within TM1.

Can anyone please share a sample TM1 database which is based on the concept of Rolling Forecasts?

Thanks in advance!!

[jydell]

Hi

I just implemented a rolling forecast. The basic premise was create a dimension to hold (Budget, Actual, re-estimate)
1: Establish a 'Budget' for the entire year
2: Ensure actual data gets posted to 'Actual'.
3: Copy 'Budget' to 'Re-estimate', to give users a starting point that they can later overwrite. (this was a business requirement to avoid duplication of effort)
4: Use a rule that overwrites every month as actual's arrive.
ie In February rolling forecast the rule says ['Re-estimate', 'January'] = ['Actual', 'January']. Note I have pointed this to a cube to drive this calculation I needed it to apply to about 50 cubes as detailed below. However the above illustrates the concept.
5: To keep a copy of the forecast I wrote a TI process to copy data from 're-estimate' to 're-estimate February' etc ensuring we always had a hardcoded copy of prior forecasts to refer to. Users were only ever entering revised rolling forecasts into the 'N' element 're-estimate'. (Business requirement to avoid confusion.)

By using this concept the user can continually update the outer months forecast, the starting point is simply what they said in the prior month + administrator has overwritten the latest month with actual data. All other forecast rules still applied for the outer months as they were written after the rule below (ie $ = price * quantity etc)

By doing this we achieved a simple easy to update rolling forecast.

Detail of my rule below, generic rule for all cubes to assess if the data should use 'Actual' or be hardcoded numbers by the user

Code: Select all

[]=  N: 
IF(DIMIX('month', !month) < dimix('month',DB('Reestimate parameters',!year,!Version, 'ChangeMonth')),
['Actual'],
continue);

[jydell]

Hi

Sorry I cant provide a sample cube. But you can create a simple one as per below

Create 3 new dimensions
Month: With N elements (January,February....December)
Version: with N elements (Budget, Re-estimate, Actual)
Ledger: Put a couple of example General ledger accounts (1234, 1235, 1236).

Create a cube with these three dimensions.

Enter sample data into Budget, copy this to re-estimate and enter sample data into actual

At this stage you will have a cube that holds Budget, Re-estimate and Actual.


Add the following rule in for the cube.
['Re-estimate', 'January'] = ['Actual', 'January'].

After this rule is added you will notice that the Re-estimate for January now has changed to show actual data.

Then add another rule for February

['Re-estimate', 'January'] = ['Actual', 'January'].
['Re-estimate', 'February'] = ['Actual', 'February'].

At this stage your re-estimate will include 2 months of actuals and you have achieved a "rolling forecast" where you have made a yearly forecast change based on actual results. This example can obviously be scaled up to meet your needs.

As for point 5 a TI process this is much more complicated and I recomend you copy and paste data within TM1 until you are confident with TI. My TI process exports an ASCII file based on a view using "asciioutput" then a second process imports it in the new version using "Cellput". (I had to use ascii output as other methods proved time consuming when copying large amounts of data.)

[ExApplix]

Do you have to use both TI process and the Rule to move the numbers from Actuals to the Budget Levels?

Also what is the frequecy of transfering the Actuals to Budget version?

My Period dimension has years like 2002, 2003....and then each year has months like 200201, 200102, 200203....

If I have 10years worth of data then do I have to write the Rule for each Month (that would be 12X10=120 rules)?? Can I write a more generic rule instead of hardcoding the Months?

[Marcus Scherer]

You can move actuals to budget by rules alone.
Start month/start year may be stored in a parameter cube and read out by the rule.
You don't have to write a rule for every month. Take a look at the DIMNM/DIMIX-example in the rules manual. Since you have only one time dimension it is easy to apply in your case.

[jydell]

The TI process is purely to save a hardcoded copy of the re-estimate before you change it the next month (nothing to do with Actuals or Budget), if you have no need to do this I suggest dont bother. I required it to ensire we had a copy of of re-estimates if we had to refer to them at a later date(as forecast data would have changed as users are requested to change outer months data as this is the forecast process)

In my organisation we run a new rolling forecast monthly so I do this exercise monthly once the 'Actual' data is finalised, however I have seen this work Daily and Weekly. (your periods are monthly so I suggest monthly for you) We run Budgets 1 time per year and I never change that data.

As suggested by Marcus I use tables to run this rule see my dimix example in earlier post (primarily as I needed to apply this to 50 cubes at once), below is one that may work for you without the added complexity of refering to another table.

Example that may work in your case. (then you just change the '201002' period to the new one when you need it) Note read up on how DIMIX function works and change the below as required. The below will direct the rule to use 'actual' data for all months before your choice of month example '201002'.

Code: Select all

[]=  N: 
IF(DIMIX('period', !period) < dimix('period', '201002')),
['Actual'],
continue);

[mce]

In our model we also have time time dimension like yyyy-mm.
I do not favour hardcoding switchover parameters in rule codes as I think they should not be touched for maintenance and operation of the applications.
Therefore I added an attribute to time dimension to identify if the month is either "actual" or "forecast", so that I put the rule to pull actual months from actual version to my forecast version.
When they want to roll new actual months over forecasted months in forecast version and re-work the future months, they only change the attribute in time dimension for the new actual months.