In the first post of this series we looked at some of the considerations for implementing Top down budgeting, you can find this post here.
Planning analytics leverages the existing Actuals reporting to make Top Down Budgeting a simple enhancement. Planning analytics data spreading functionality enables budget holders to input high level values to be automatically allocated into business units.
To highlight a top down budget, I have created a copy of the Profit and Loss cube which holds data by version, period, years, regions, chart of account line and measures:
In the Profit and Loss model we hold data by 10 separate regions, we will be setting the budget for the company as a whole and we will utilise data spreading to allocate the high level budget values into each of these regions across 12 periods of the year.
In short, I want to input values against “Gross Sales Revenue” for “Full Year” (12 periods) and “All Regions”
I can input values directly into the Cube Viewer or I can Slice my values to excel and input directly in excel.
However, for simplicity I have developed a simple application to help me with my Top Down Budgeting tasks:
This application is hosted on the TM1 Server and contains live links to the data held on TM1, any changes made in the application will reflect in the Cube Viewer and any other TM1 clients you care to use.
We can select the Versions, Years, Regions that we want to interact with and we can input Full Year Values for the base elements in our chart of accounts (we call them base level or N level elements as they are the lowest level in the hierarchy that is our Chart of Accounts).
Once I have input my Yearly values for All Regions then I select the “Input Top Down Budget” button which is linked to a Turbo Integrator process.
We can see in the cells on the right hand side that the values have been input into the respective accounts and that the hierarchy has consolidated these values to produce my Net Income figure for the year.
We could input a value into Net Income and utilise TM1 data spreading functionality to apportion across all Chart of Account lines if we wanted too but I wanted to hold control of each Account line.
If I select Gross Sales Revenue we see that the values have been spread evenly across Regions and Months. I know that the “North West England” region should achieve Gross Revenue Sales of 1,100,000 and that “South” will achieve 900,000 Gross Sales Revenue.
I can achieve this through the Override section of the application:
I update the Override Input and select the “Update Top Down Budget” button to amend the Gross Sales Revenue values for “North West England”. I do the same for “South” and we see the following results:
Top Down Budgeting in Planning Analytics is simple and straight forward and can quickly and easily be adapted to fit your needs.
The third part of our Top Down Budgeting analysis will investigate how the Turbo Integrator processes are built and further design considerations for our build.
