How to merge actual in planning cubes

Center of Excellence

1. Abstract

Forecasting is a prediction or projection about a future event, depending on the past and actual performance and trend. A forecasting process uses historical data to predict future numbers and allows organizations to project future results for budgets, expenses, and other financial and operational data based on their past results.

The forecast allows organizations to project future results based on a combination of actual YTD financial results and the original budget or updated revenue and expense forecasts for future periods. The future forecast period can extend to the end of the fiscal year, or can extend out four to six quarters into the future, but is extremely important Actual and Forecast can be merged and compared.

2. Context

Let’s imagine having a planning process that starts with an actual sales data load and goes on with a planning workflow where users oversee the budget sales for the next months.

At the end of the process, for reporting purposes, it is useful to have both pieces of information (actual and planning) in the same cube, instead of two separate ones.

3. Content

At the beginning of the process, it is suggested to set up a cube with a dimension to identify the Actual/Planning. This is to distinguish between actual and planning months.

Instead of managing the business based on a static budget that was created in the prior year, creating a forecast is used to revisit and update budgeting assumptions throughout the year. This process foresees a mixed overview between actual and planned figures.

First of all, we should be able to define the scope of our current Forecast, what portion of the year is actual, and which months need to be forecasted/planned. Even if this result could be achieved through a custom time relationship, it is suggested to use a “Setting Cube” where we can define different scopes for different combinations of Version/Scenario at the same time.

The actual and the planning data are collected in different cubes, and the two months’ settings cubes can be used at the end of the process to merge the data, with an entity selection “based on cube” in a dedicated procedure.

Actual figures are usually uploaded in Board through a Data Reader, and stored in a dedicated cube, while the forecasting process is usually based on a different cube because of the different granularity, a more aggregated level of detail, and data versioned by Version/Scenario.

To minimize data redundancy is suggested to introduce the Version/Scenario just at the bottom of the planning process, while the outcome while being used in comparison analysis intermediate steps result does not require to be kept.

At the bottom of the forecasting process, we end up with plan data, detailed by Version/Scenario and actual data. To achieve a full-year forecast it’s necessary to merge them.

Actual can be settled on a dedicated Version/Scenario combination or merged in every existing combination based on reporting requirements. By using the Setting Cube we can make sure to merge Actual figures just for the closed months.

In this way, the output is that only the data related to the actual months are shifted in the final cube in the actual months.