Configure Rolling Forecast

Introduction

A Rolling Forecast is a financial model that dynamically updates over a period of time, to reflect the most up-to-date Forecast data, once the Actual data are available.

 

To configure a Rolling Forecast, we’ll need to design a Form that can be dynamically updated each month using application variables. Below is an example Form that's been configured as a Rolling Forecast. Values up to the current month are reported as actuals, while future values for the year are forecasted, with the totals aggregated at the end.

 

 

 

Before You Begin

The key to constructing this Form is configuring the column axis correctly. The column axis includes both the Time Dimension and Scenario Dimension. Rather than specifying a static set of members for the Time Dimension, we'll define standard variables to dynamically scope the forecast range. The scope will always be based on the current month.

 

 

Configure Variable Definitions

Step 1. First, let's create a standard variable called Current Month. This variable will represent the Time Dimension's MemberId for the current month. It will be used to split the months into Prior Months for actual data, and Forward Months for forecast data.

 

 

Deployment Script (optional)

This variable will need to be updated every month for the Form to display properly. We could use SQL Deployment Scripts to automatically update this value based on the current date. The following is an example of such a script, which will run every time the application is deployed.

 

 

UPDATE [dbo].[ApplicationVariables]
   SET [VariableValue] =  (SELECT CONVERT(varchar(12) , GETDATE(), 112 )) / 100 * 100
WHERE VariableName = N'Current Month'

 

Step 2. Next, we need to define two more standard variables to display the correct forecasting time-frame. We'll define these variables using MDX:

 

Rolling Prior Months

NULL:[Time].[Months].&[${Current Month}].Lag(1)

• The format NULL:[Member] indicates all members before, and including, the specified member.
• .Lag(1) indicates that we want the member one (1) before the specified member.
• Including the ${Current Month} variable we defined in the first step allows us to easily update the range each month.

 

Rolling Forward Months

[Time].[Months].&[${Current Month}]:NULL

•  [Member]:NULL creates the reverse behavior, indicating all members after, and including, the specified member.
• Including the ${Current Month} variable again makes this query more dynamic, and easier to update each month.

 

Note: It's also possible to define these variables using [Time].[Fiscal Calendar]. Both definitions would produce the same results, however you must match the hierarchy in the variable definition with the hierarchy used in the Form.

 

 

Define Form Structure

Now let's create a new Form, or update an existing Form, to configure our Rolling Forecast. In the example below, we've created a simple Form with just the Account, Scenario, and Time Dimensions.

 

Step 1. In the LAYOUT tab of the Form, since we defined the variables using the [Time].[Months] hierarchy, let's put the Months Hierarchy on the columns. Then let's add Scenario to both the columns and as a Filter Variable. And finally we'll include the Fiscal Year attribute as a filter.

 

 

Step 2. Then we need to define the columns. We can change which Dimension we're configuring using the drop-down on the left. Then we can use the Variable option to add both the application variables and filter variables.

 

We can configure the columns as in the image below. The first row tells the Form to display only Actual values for all months prior to the current month. The second row tells the Form to display the Scenario filter selection for all months after the current month.

 

 

Now let’s preview the Form. At this point, it should correctly show all months prior to the current month as actuals. If you change the Scenario filter selection, the forward months should continue to change as normal, but the prior months should remain static.

 

 

 

Calculate Aggregated Totals

Step 1. We'll need to create two Scenario Dimension Members to hold the Current FY Actual & Forecast total and the Rolling Year Forecast total. The first scenario will show the sum of both the actual and forecast values for the year, while the second scenario will show the sum of the forecast values for the next 12 months.

 

 

Step 2. Then let's update the column definition in our report. For the Months, as both of these scenarios are totals, let's select the All member.

 

 

 

Step 3. Since we want these columns to display customized totals, we'll need to define the calculations for them manually, in an MDX Rule. Let’s create a new MDX Rule in the Model called Dynamic. This rule should be created in the same Model that our Form belongs to.

 

 

Step 4. Copy and paste the following MDX Script into your rule. This defines the two new scenarios as the calculations we outlined above. Then save and deploy the application.

 

/*-----------------------
    Scenario Aggregation
*/-----------------------
 
//Current FY Actual & Forecast:
//Part 1: Using Actual values for months ranged between January and the month before current
SCOPE
(
    [Scenario].[Scenario].[Current FY Actual & Forecast]
    ,{[Time].[Fiscal Calendar].&[${Current Month}].Parent.Parent.FirstChild.FirstChild
      :[Time].[Fiscal Calendar].&[${Current Month}].Lag(1)}
);
THIS = [Scenario].[Scenario].[Actual];
END SCOPE;
 
//Part 2: Using Forecast values for months ranged between current month and December
SCOPE
(
    [Scenario].[Scenario].[Current FY Actual & Forecast]
    ,{[Time].[Fiscal Calendar].&[${Current Month}]
      :[Time].[Fiscal Calendar].&[${Current Month}].Parent.Parent.LastChild.LastChild}
);
THIS = [Scenario].[Scenario].[Forecast];
END SCOPE;
 
//Rolling Year Forecast: Using Forecast values for current months and next 11 months
SCOPE
(
    [Scenario].[Scenario].[Rolling Year Forecast]
    ,{[Time].[Fiscal Calendar].&[${Current Month}]
      :[Time].[Fiscal Calendar].&[${Current Month}].Lag(-12)}
);
THIS = [Scenario].[Scenario].[Forecast];
END SCOPE;

 

Note: Make sure the MemberLabels for your scenarios exactly match those in the script.

 

Tip: In this instance, we can use either [Time].[Fiscal Calendar] or [Time].[Months] to achieve the same result, regardless of which one we used in our application variables or Form layout.

 

You should now have a Form similar to the one below: