Forecast Archives | XLCubed Blog

Rolling Actual and Forecast Reporting

Ambika Aggarwal/ 27th August 201921st April 2020/ Reporting & Analytics

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”

Rolling Forecast reporting in Excel

XLCubed/ 27th March 20134th January 2019/ Reporting & Analytics

In monthly reporting for the current year the most pertinent numbers for closed periods are the actuals, and for open or future months the forecast. In most cubes actual and forecast (or budget) are implemented as either different measures, or as part of a scenario hierarchy. This can lead to challenges in reporting, where putting both the period and the actual/forecast on the same axis means you will get duplicated months which show the forecast for periods when the actuals are already in as in the PivotTable below.

We would want the report to be asymmetric (i.e. different months returned for Actual and Budget). Various tools can handle that, and it’s always been possible to achieve that in XLCubed through formulae or in a grid. What’s more difficult is to make the report fully dynamic without the need to redesign it each time. Ideally we’d want the user to be able to change a slicer to select the last completed month (or pick this up directly from the cube), and have the appropriate months reported under Actual and Budget. Here’s how to achieve it in one grid…..

The approach uses a combination of:

Excel Ranges to determine the available months, and which are for Actual and Budget
An XLCubed Slicer to pick the ‘Last Actuals’
One XLCubed grid with the ‘Exclude from display’ pointing to the relevant ranges above

Let’s work through this example which uses the Finance cube on the AdventureWorks DW 2008R2 database.

We’re going to use data from FY 2006 and report Actual and Budget figures across departments. We will use a slicer to select the last Actual month we want reported. For the remaining FY 2006 months we will report Budget values.

Entire year of Months in the report

There are several ways to achieve this – we added an XLCubed Excel driven slicer (XLCubed – Slicer – Excel) based on the range $AA$1:$AB$12 as shown below, containing all months in FY 2006. This lets the user pick the last month for which we want to report Actuals.

RP3

The slicer is set to output its selection into $B$1. In $C$1 the formula =VALUE(B1) converts the text output of the slicer into a number which we’ll use to calculate which months should report Actual and which Budget, see the screenshot below:

Date Table

This is an Excel range supporting the reporting logic. Row 2 contains every month in the reporting year. Row 3 assigns an incremental number to each month, 1-12.

We can then compare the value of the selected month from the slicer ($C$1) with the value for each month. Basically if the month is less than or equal to the selected last actuals month it should be reported as Actuals, otherwise as Budget. An example formula contained in B4 for Actuals would be: =IF(B3<=$C$1,B2,””) . For the Budget row the formula has the inverse logic.

At this stage we’ve used a little XLCubed, and the flexibility of Excel to match the appropriate months to Actual and Budget. We now need to add the report itself.

We’ll create our grid – initially we set it up to report Budget and Actual figures across the year, by basing the selection for Date.Fiscal on the range containing all months for the year ( $B$2:$M$2).

We then use XLCubed’s right-click menu (Keep – Exclude From Display) to exclude members. It doesn’t matter which slices are excluded at this point. To do this highlight ‘Actual’, followed by the first available month and then choose Exclude From Display. Repeat this for ‘Budget’ and the first available month. We will exclude July 2005 for Actuals and August 2005 for Budget. You will notice the red comment marker on the grid. Right-click on this cell and choose Axis – Edit, and go to the Excluded Slices tab.

The above screenshots show that we are currently excluding July 2005 from Actual as well as August 2005 from Budget.

We can then easily change the Date Fiscal selection for each scenario. For Budget we will exclude Budget values for months in cell locations $B$4:$M$4 (as we want to report Actuals for those entries) and Actuals values for the months in cell locations $B$5:$M$5 (as we want to report Budgets for those entries).

The grid report now looks like this with Actuals being reported up to November 2005 and Budgeted for the remainder of FY 2006:

We have also set Merge Repeating Cells on (in Grid Properties). You could do further formatting – for example by colour filling all Budget values and hiding the grid headers.

The end result is a one grid report giving the Actual:Budget month mix that we want with the monthly maintenance process a case of simply changing a combo box selection.

Asymmetric grid reporting

XLCubed/ 6th August 20124th January 2019/ Reporting & Analytics

A common scenario for Analysis Services reporting is to want to present different measures for different members, particularly in budgeting and planning. So I want a grid that shows actuals for previous month, budget for this month and forecasts for future months.

This could be achieved in the cube, by using a “Phasing” measure to switch to the different values but quite often our customers are not in control of the cube structure.

We will look at a way to achieve this from within XLCubed itself by working through an example.

So this is our initial grid – it is currently set to report both Revenue and Discount values across all quarters in 2002 – 2004.

Let’s create a couple of slicers – one for Revenue:

and a similar one for Discounts:

The settings tab for Discount slicer is:

You can see that this is a multi-select slicer allows which updates an Excel range with the slicer choices.

The entries in the Excel range are referred to by their ‘Unique Name’ eg for Quarter 2 2004 equates to April 2004.

The settings tab for Revenue is similar except it outputs to different cell locations:

In this example our Discount slicer choices are Quarters 2 & 3 in 2004 and our Revenue slicer choices are Quarter 4, 2004.

Now let’s set up the excluded data – remember that we when reporting Revenue rows we want to exclude the Discount slicer choices and vice versa.

Right-click on the Discount header row and then select Exclude from Display

You will now see a red triangle appearing in the corner for the first member of the hierarchy which has excluded data. If you hover over this cell it displays an additional message that the rows are being restricted by members and that you should right-click to edit axis (it’s the menu option just after grid charts).

In the Axis Designer window pick the Excluded Slicers tab and click in the lower-half of the window (highlighted) – this is where we are going to define the quarters that are to be excluded on the Time hierarchy.

Select the time hierarchy and then click the box to its right (highlighted in screenshot below) and click the box next to the drop-down so that you can pick the Excel range – in our example it is cells I23 through to I28 (the Revenue slicer choices that we do not want reported as Discounts). Clicking OK will refresh the report and only show Discounts rows for the quarters selected.

We now need to do the same for Revenue so right-click on the row containing the red triangle and set up the Revenue excluded slices in a similar way. Click the icon highlighted to add a new exclusion. This will be cells A23 through to A28 (the Discount slicer choices that we do not want reported as Revenue).

Click on the new exclusion row and then in lower half of screen build up the Revenue exclusion in the same way but remembering to point to the Excel range to cell locations A23 to A28.

You should end up with an Axis Designer window something like this – for Discounts exclude slices in cell locations I23 – I28; for Revenue exclude slices in cell locations A23 –A28.

OK to apply these changes and the report now looks like:As you can see the report shows Discounts for Quarters 2 & 3 in 2004 but only shows Revenue for Quarter 4 in 2004 and because everything has been linked to ranges driven by slicers, the user of the report can easily control the switch in measures.