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.
Integrating Actuals and Forecasts: A Challenge for Excel Forecast Formulas
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.
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”
Step-by-Step with Excel: Merging Actuals and Forecasts
(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.)
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’.
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.
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.
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.
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:
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.
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.
For more information about formatting grids, read this page.
