Configuring Rolling Forecast

By - Apr 24, 2013

To configure rolling forecast functionality in Kepion, we’ll need to design forms in a correct method that can be updatable from a single place.

Below is a form instance that can be used in rolling forecast. It is scoped from six months prior of actuals data to twelve months of forecast data. Based on user requirements, this scope can be extended further (i.e. 12, 15, or 18 months of rolling forecast)

We will use this scope as an example in this article with the current month as Apr FY13.

Notice the column axis. For the months before current month, the form is showing actual numbers. For months after current month, it is showing forecast numbers. The end of the form is a sum aggregation numbers.

Rolling Forecast-Graph 1 Target Table

To get to this form, we need to configure the column axis correctly. The column axis includes Time Dimension and Scenario Dimension. We’ll describe how to configure this step by step.

We use Application Variables to define global string variables that can be used in forms and rules. The variable value can be updated in a single place with the impact across the application. All application variables are stored in the [dbo].[ApplicationVariables] table in the database.

In this case, we will be defining some application time variables to specify the forecast scope. The scope is dynamically based on current month value. Therefore we need to define an application variable for current month first.

Step 1: Create Current Month Variable

Here we have created a variable called: Current Month.  This variable represents the Time MemberId for current month value.

Rolling Forecast-Graph 2 Current Month

This value can either be manually updated & deployed (generate OLAP), or we can use a SQL script to automatically update this value based on current date. This script will run every time the application is deployed.

Rolling Forecast-Graph 3 Update Current Month Variable

Step 2: Create Rolling Prior and Forward Months Variable

Then we can define the forecasting time frame. It contains two parts: prior months and forward months.

Rolling Prior Months – prior 6 months:

Rolling Forecast-Graph 4 Rolling Prior Months

Rolling Forward Months – current month and the following 11 months

Rolling Forecast-Graph 5 Rolling Forward Months

Step 3: Scenario Dimension – Calculation

For the last two columns in our rolling forecast form, we need to create two Scenario dimension members to represent Current FY Actual & Forecast summation and another for Rolling Year Forecast.

Rolling Forecast-Graph 6 Scenario Dimension

Step 4: MDX Calculation – Definition

Now that we have the calculated dimension members created, we need to define the calculations in MDX rule editor.  Let’s go ahead and create a MDX rule in the modeler called Dynamic.

Rolling Forecast-Graph 7 MDX Rule

Step 5: Form Design

In this case, we can create a Rolling Forecast P&L form based off the current P&L form on the CPG Sample Application (available through Kepion support downloads).

First create a form based off the P&L form, and lets call this new form Rolling Forecast.

Second, we’re going to modify the layout of the new form. In layout, we should the use Months hierarchy instead of Fiscal Calendar for column because the form time scope is no longer restricted to a fiscal year. For the same reason, we are no longer going to use FiscalYear as filter.

Rolling Forecast-Graph 8 Form Layout

Then we need to define column axis. We can combine time variables in Time dimension with corresponding scenarios in Scenario dimension to achieve the correct form structure, show as below.

Rolling Forecast-Graph 9 Form Column

Now let’s preview the form to check if it shows the months and scenario correctly.

Rolling Forecast-Graph 10 Form Preview

Conclusion

In practice, rolling forecast duration and rolling forecast comparison periods varies based on your business cycle and business needs. Kepion can be easily configured to meet your requirements. The only thing you need to modify is the application variables: give the right definition to Rolling prior months and Rolling forward months to scope your application’s time frames.

 


Facebooktwitterlinkedinmail