Rolling Forecast reporting in Excel

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.

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.

TwitterLinkedInEmailShare

Budget,Effective Management Reports,Forecast,Member Selection

Post navigation

Previous ArticleXLCubed en Français, dans Lausanne!

Next ArticleCurrent and Previous month reporting made easy

6 Replies to “Rolling Forecast reporting in Excel”

1. arno says:
2. 16th April 2013 at 3:04 pm
3. This is a nice workaround. It would be a lot nicer to make the “Exclude from Display” dialog programmable as all (!) other dialogs are that deal with member selections. I mean, allow sets {}, manual mdx… for excluding members, too. This should be possible, here I am assuming that November 2005 is coming from a slicer, the rest is just mdx, not very beautiful but demonstrating my point:
4. Select { } On Columns,
5. Except (
   ( {[Scenario].&[Actual],[Scenario].&[Budget]}
   * {Descendants([Time].[Year].&[2005-01-01T00:00:00],[Time].[Month],SELF)})
6. ,{([Scenario].&[Budget],[Time].[Year].&[2005-01-01T00:00:00].FirstChild.FirstChild:[Time].[Month].&[2005-11-01T00:00:00])
   ,([Scenario].&[Actual],[Time].[Month].&[2005-12-01T00:00:00]:[Time].[Year].&[2005-01-01T00:00:00].LastChild.LastChild)}
   ) On Rows
   From [Sales] Cell Properties VALUE
7. Result:
   Actual January 2005
   Actual February 2005
   Actual March 2005
   Actual April 2005
   Actual May 2005
   Actual June 2005
   Actual July 2005
   Actual August 2005
   Actual September 2005
   Actual October 2005
   Actual November 2005
   Budget December 2005
8. There is not much Excel required here, simply write the result eg. into A1 and use one textformula to create the “manual mdx” eg in B1 to exclude all Budgets from January to November:
   = “([Scenario].&[Budget],[Time].[Year].&[2005-01-01T00:00:00].FirstChild.FirstChild:”&A1&”)”
9. I have trouble with the exclude dialog that could be simply solved by accepting eg. “SomeMember.Children”.
10. regards
    arno

‍