With the consolidated view of the data now available in the AFC cubes, users have the flexibility to analyse and report as needed. Below is one of the interactive reports which were produced, showing the P&L and associated variances. The user can choose any two scenarios to compare, and the waterfall will update accordingly with the appropriate IBCS notation (Actual = Solid, Plan = Outline, Forecast = Hatched). The variance charts also understand that a decrease in costs is a benefit to the business and are hence depicted in green.
Any of the accounts contained in the Waterfall can be further explored in the by Country by Month integrated variance charts by simply clicking on the relevant bar.
Key to building this report quickly and easily are the ‘Business Rules’. These can be defined on the XLCubedBusinessRules sheet, or typically imported from a centralised source. It is a visual semantic layer which maps elements in the data to their business meaning. For example the following aspects can all be defined:
- Current Month / Period
- ‘Actual’, ’Plan’, ‘Forecast’ data
- Revenues & Expenses
With that in place, building the report is simple as each chart type understands how to depict the individual items in terms of colour, direction and sign, and it’s just a case of choosing the chart type and selecting the elements to display.
The P&L report above is built in five stages:
1) Add the two slicers for the selected scenarios
2) Build the waterfall chart
- Place Measures and Period on Filters
- Select the accounts to display on Categories
- Place Scenarios (Analysis) on Columns, and link to the two slicers
3) Build the Absolute Variance Chart (Δ PL)
- Copy the waterfall chart to the right (to avoid needing to re-select the accounts)
- Change the chart type to Absolute Variance, and set compare by to Scenario, and link to the two slicers
- Set a common scale – set the scaling of the variance chart to be shared with the Waterfall chart
- Use the Excel range picker to resize the chart to two or three columns
4) Build the Relative Variance Chart (Δ PL%)
- Copy the Absolute Variance Chart (Δ PL) to the right
- Change the Chart type to Relative Variance
- Remove the common scale setting
5) Add the Integrated Variance charts by Country and with Months on Columns (this part is an extension on the regular IBCS template)
- To create the link to the Waterfall chart, set the waterfall to output it’s Category selection to a cell, and filter the Chart By Account, linked to the same cell.
The end result allows users to quickly make a detail comparison across any two scenarios, and to further explore that on a monthly and by country basis.
The report can be shared interactively with Web and Mobile users by publishing to XLCubed Web.