Business data is often better understood when viewed in the context of another scenario. We need to know if we are doing better than this time last year, or how close we are to meeting our budget. Variance charts are effective at displaying these differences between actual and comparative values. They can show absolute or relative variances and be coloured to indicate either a good or bad impact on the business.
Absolute variance is the difference between two values of the same measure. These variances are displayed as columns or bars.
Relative variance is the absolute variance as a percentage of the comparative value. To visually distinguish from absolute variances, these are displayed as lollipops.
Consistently displaying absolute and relative variances in this way helps make organisational reporting more intuitive, and individual reports faster to assimilate.
These chart types are available in XLCubed 10. Select the chart type from the task pane and define the base and comparison values (usually two scenarios or two years etc.).
Download the example workbook here:
Displaying basic and variance values together
Variance charts can be displayed alongside a primary chart as a way of increasing information density of a report. These are sometimes referred to as multi-tier charts. They are often used for displaying variances along with the basic values.
We’ll choose an offset comparison as the primary chart for plotting the monthly actual and plan values. We’ll then copy this chart, changing the chart type to an absolute variance. Move the chart above the original, using the range picker to precisely align the charts.
The category axis of both charts is identical, so we can reduce clutter by removing the labels on the variance chart. This can be done by clearing the font colour and reducing the axis height.
Both charts are plotting the same measure. Therefore, to maintain visual integrity, they should be plotted on the same scale. In the variance chart’s properties, set the value scale to ‘match to’ the first chart.
We can now create the chart again, this time using a relative variance chart type. Again, place it directly above the first two charts, and remove the category axis labels. This chart is not plotting the same measure so should not share the same scale as the first two charts.
Integrated variance charts
When there is too little space to show a multi-tier chart, an integrated variance chart is a good solution. This chart type shows the base value with the absolute variance displayed within the columns/bars.
Green or red?
By default, when the base value is higher than the comparative value, the variance is coloured green. If the base value is lower, it is coloured red. This makes sense for items that are revenues, e.g. if sales have decreased from last year, that is bad so is coloured red.
However, for categories that are expenses, an increase in these values has a negative impact to the business. They therefore need the reverse green/red formatting.
Members can be defined as either revenues or expenses using Business Rules. Once defined, variance charts will apply these rules to show the correct green or red fill per category.
Our recent blog on profit and loss waterfalls demonstrates the use of defining rules for expense accounts. For example, “Operating expenses” has decreased but, because it is an expense, this has a positive business impact so is coloured green. Other accounts such as “Consulting” and “Maintenance” have also decreased but, because they are revenues, their variance is coloured red.
Scenarios with solid, outlined and hatched formatting
Business rules can also be used to apply the solid, outlined and hatched formatting to distinguish different scenarios, as recommended by the IBCS standards.
For example, say we want to plot the current year in progress, showing actual values up to the current month, and then forecast values for the remaining months of the year. We want to compare this to the plan for each month. Variances of the actuals should have a solid fill, whereas the forecast variances should have a hatched fill.
First, we will create a custom calculation “AC/FC” that will show the actual scenario values where they exist, but otherwise will show the forecast value.
iif([Scenario].[Scenario].&[Actual] = 0, [Scenario].[Scenario].&[Forecast], [Scenario].[Scenario].&[Actual])
We can now use this calculation as the ‘base’ value in all our charts. The comparison value will be the Plan scenario.
Next, we need to define Business Rules to apply the hatched formatting for the forecast months. Create a “CurrentMonth” rule that applies to the last month with Actual data. The entry in the BusinessRulesSheet could be static, or you could use a calculated member or Excel formulae to determine the month to use.
Refresh the chart to apply the new rule.
We can now also create an offset comparison chart and a relative variance chart to display in a multi-tier view as before. They will automatically pick up the business rule to apply the correct actual and forecast formatting to give the final version report below.
.jpg)