TM1 is a completely customizable solution for budgeting and forecasting. It is extremely robust and flexible, but with that comes complexity. Two of the biggest challenges my customers face are the amount of time it takes to prepare their budgets and forecasts, especially if they are responsible for rolling monthly forecasts, and how difficult it may be to update the data, rules, feeders, etc. They likely spend as much time preparing the model as they do inputting, analyzing, and making informed business decisions because the preparation work is a heavily manual process. We often times use global parameter flags to simplify some of that complexity as well as streamline the preparation process.
We’re going to start out with a simple example to demonstrate this. In this example, we have a Version Flag cube with a picklist consisting of an element called ‘Actual’. You’ll notice the ‘Actual’ flag has been populated for ‘Forecast Q3’ for ‘201401’ through ‘201406’ and other intersections of the version, measure, and time dimensions where applicable. This indicates we have actual data for those periods loaded into the Revenue Actual and Expense Actual cubes, and we’d like it to move to the respective input cubes (Revenue and Expense) without having to adjust the rules and feeders. There are dynamic rules and feeders in place that are noted with each cube that will need to be set up initially.
*Something important to remember is the ‘Actual’ flag should never be removed. Doing so will result in actual data being removed from the Revenue and Expense cubes. If this happens in error, then simply selecting the ‘Actual’ flag again will move the data back to the intersection where it belongs.
In the Version Flag Rules Editor, the following feeders have been set up for the Revenue and Expense cubes:
SKIPCHECK;
FEEDSTRINGS;
FEEDERS;
#Feeders for Revenue
[‘Amount’] => DB(‘Revenue’, !Time, !Versions);
#Feeders for Expense
[‘Amount’] => DB(‘Expense’, !Time, !Versions, ‘Amount’);
[‘Hours’] => DB(‘Expense’, !Time, !Versions, ‘ Hours’);
We have a Revenue Actual cube that has been loaded with actual data for various projects and revenue accounts for ‘Forecast Q3’.
In the Revenue Actual Rules Editor, the following feeders have been set up for the Revenue cube:
SKIPCHECK;
FEEDERS;
#Feeders for Revenue
[] => DB(‘Revenue’, !Accounts Revenue, !Projects and Tasks, !Time, !Versions);
You’ll notice that all the intersections of ‘Forecast Q3’ for ‘201401’ through ‘201406’ are read only and populated with the Revenue Actual data for the respective projects, accounts, and time periods. The time periods to be forecasted appear in white so the end users can write to those intersections and easily identify what remains to be forecasted.
In the Revenue Rules Editor, the following calculation has been set up:
SKIPCHECK;
FEEDSTRINGS;
#Calculation for Actual Data
[] = N:IF(DB(‘Version Flag’, !Time, !Versions, ‘Amount’) @= ‘Actual’, DB(‘Revenue Actual’, !Accounts Revenue, !Projects and Tasks, !Time, !Versions), CONTINUE);
We have an Expense Actual cube that has been loaded with actual hours and amounts for various projects, resources, and a labor account for ‘Forecast Q3’.
In the Expense Actual Rules Editor, the following feeders have been set up for the Expense cube:
SKIPCHECK;
FEEDERS;
#Feeders for Expense
[‘Amount’] => DB(‘Expense’, !Accounts Expense, !Projects and Tasks, !Resources, !Time, !Versions, ‘Amount’);
[‘Hours’] => DB(‘Expense’, !Accounts Expense, !Projects and Tasks, !Resources, !Time, !Versions, ‘Hours’);
All the intersections of ‘Forecast Q3’ for ‘201401’ through ‘201406’ are read only and populated with the Expense Actual data for the respective projects, resources, account, measures, and time periods. Again, the time periods to be forecasted appear in white so the end users can write to those intersections and easily identify what remains to be forecasted.
In the Expense Rules Editor, the following calculations have been set up:
SKIPCHECK;
FEEDSTRINGS;
#Calculation for Actual Data
[‘Amount’] = N:IF(DB(‘Version Flag’, !Time, !Versions, ‘Amount’) @= ‘Actual’, DB(‘Expense Actual’, !Accounts Expense, !Projects and Tasks, !Resources, !Time, !Versions, ‘Amount’), CONTINUE);
[‘Hours’] = N:IF(DB(‘Version Flag’, !Time, !Versions, ‘Hours’) @= ‘Actual’, DB(‘Expense Actual’, !Accounts Expense, !Projects and Tasks, !Resources, !Time, !Versions, ‘Hours’), CONTINUE);
As you can see from this simple example, global parameter flags can be very powerful when used in TM1 applications. I highly suggest using flags to make things simpler and streamlined. Be sure to check our blog regularly, as I will cover some more intricate uses of global parameter flags in the future!
