Rolling Actual and Forecast Reporting

It’s a very common requirement in financial reporting to show actual and forecast data by month across the entire year. Often, we are focusing on the expected year end position so we will want to use the actual data where it exists, and the forecast data where it doesn’t so that the year-end total becomes increasingly accurate through the year.

Excel is a flexible environment and people often build that type of report using a mixture of copy-paste and formulas. It achieves the goal but can be a cumbersome process needing quite a bit of manual amendment each month to update it.

The Challenge

When building this type of report the main challenge is normally that Actual and Forecast are two different measures, or members of a ‘scenario’ hierarchy. In most client reporting tools, reporting across multiple months will mean both actuals and forecast showing for every month where both exist, as shown below. In our case if completed actuals exist, that’s all we want to see, and then forecast for the remaining months.

XLCubed Grid in Excel showing Actual and Forecast Data for all months

We want our report to be dynamic so that we are not having to redesign it every time. In a previous blog from some years back, we showed you how to create the report using the “Keep – exclude from display” feature. While that approach still works, there is now a more intuitive way to achieve the same result using “Keep – Keep Members and Create Dynamic Set”

Method

(Note there are various ways to make the ‘Last Complete month’ a selectable member within the cube, and if that’s available in your cube environment you just need to select it using an XL3Member() formula, and so can skip step 1 here.)

1. XLCubed Slicer to pick the ‘Last Complete’ month

Insert an XLCubed slicer for the months required from your cube. Next, set the slicer to output the caption for the selected month (to $B$1 in this case). To do this, go to the slicer setup and under the ‘Behaviour’ Tab, tick ‘Update Range with Selection’ and choose the ‘Output Range’ as $B$1 and the ‘Value to Output’ as ‘Caption’.

Slicer Setup pop-up page

2. Excel range to determine the available months

Use XLCubed – Insert Members to list all the months for the year and assign each a 1-12 number. Assign a number for the month in $B$1 using a HLOOKUP formula as below.

HLOOKUP FORMULA

We now have to determine which months we want to assign for Actuals and which ones for the Forecast.  For this we will use an if statement so that if the month is before or equal to the last complete month, it will be reported as Actuals otherwise as Forecast. So, for Actuals, an example of the formula would be =if (B4<=$C$1, B3, “”) and for forecast,  = if(B4>$C$1, B3, “”)

If our last complete month is July 2019, we can see that the available months for Actuals are January 2019 – July 2019 and the forecasted months are August 2019 – December 2019.

Available months for Actual and Forecasr

3.   Making the Grid dynamic using ‘Keep Members and Create Dynamic Set’

Now that we have chosen the months we require for each measure, we need our Grid to use these ranges.

Highlight a particular column (for now it does not matter what month and Scenario has been chosen) and go to the Grid Ribbon > Keep > ‘Keep Members and Create Dynamic Set’. A pop-up dialog will ask you to insert a name for the named set.

'Keep All and Create Dynamic Range' Pop-up Page

The grid will now only display the results for January 2019.

To edit this, go to the grid designer and under columns, click on to ‘{…} 2019 – Actual and Forecast’. This opens the set designer as below:

Set designer pop-up page

The above shows us that we are currently only keeping January 2019 for both Actual and Forecast. We can now easily change the Calendar Date for each scenario. For Actual, we will set the range to keep the months from $B$6:$M$6 and for Budget we will set the range to $B$7:$M$7.

Set Designer for the 'Actual' Data
Set Designer for the 'Forecast' Data

Click OK and we now have the grid as required.

Changing the Last Complete Month slicer will update the ranges being used, which will in turn update the Grid to show Actuals and Forecast for the relevant months.

Formatting the Grid

We can also format the grid so that we know more clearly which months our actuals and which ones are the forecasts. For example, here I have chosen to make the forecast months italic and grey.

Rolling actual and forecast report in Excel that has been formatted

For more information about formatting grids, read this page.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.