Variance Analysis

Variance analysis is one of the simplest but most fundamental concepts in business reporting. It's essentially the investigation of deviations between what actually happened, and what was planned to happen, or between current period and previous period.

Almost all businesses spend significant time developing a plan of what they expect to happen during the next year, and will allocate resources to meet the plan. To improve future plans it's important to be able to re-assess the accuracy of prior versions, to identify significant deviations and to then be able to investigate these further to understand underlying causes.

XLCubed provides a range of interactive charts specifically designed for variance analysis to provide a clear and consistent understanding of where variances arise and which are of significance.

Variance and percentage variance chart alongside a data grid
Revenue variance between current and previous year by region

The charts provide complete formatting flexibility but default to align to IBCS best practice recommendations. For variance by category as above we depict absolute variance in either columns or bars, and relative (%) variance as lollipops, with red showing a shortfall and green exceeding target. Charts can easily be aligned to data tables to provide a detailed view of actuals and variance.

Alternatively, actuals and variance can be displayed in the same chart as an integrated variance: 

Overlay comparison chart
Actuals by month with variance overlays

In these overlay comparison charts, the black column or bar represents the actual, and the variance is depicted by integrated columns with green showing improvement and red deterioration. 

Another option for variance analysis, which places more emphasis on the underlying trend, are the line charts below which can be period by period or cumulative. Here the current period or actual is shown as a solid line, and the prior period or plan as a dashed line. Where the current period or actual is higher the deviation is shown in green, or when lower as red. This is an effective way to analyse deviation over a time period. 

Monthly variance line chart
Monthly variance
Cumulative monthly variance line chart
Cumulative monthly variance

Contribution Analysis

Waterfall charts are a staple of many management report packs. They are an intuitive way to visualize the cumulative effect of positive and negative contributions to a total, or between two totals. They are particularly prevalent in financial reporting, and many organisations spend significant time preparing complex waterfall charts in software not originally intended to create them.

XLCubed provides two main types of interactive Waterfall Chart, which are both flexible and intuitive. They connect directly to underlying data and can be built in seconds, or in more complex cases a few minutes.

The structural waterfall is used to display the contribution of structural elements, often to view sections of the account structure e.g. for profitability analysis.

Table of waterfall and variance charts
Account contribution to net income and previous year variance and % variance

The variance waterfall allows for a comparison between actual figures at two points in time, with the deviation between the actuals split by individual categories, such as product or region, or by the intervening months.

Quarterly contribution waterfall chart
Quarterly contributions waterfall
Trend waterfall chart by country
Quarterly trend broken down by region
Contribution to variance by product category waterfall chart
Contribution to variance by product

Variance waterfalls provide huge flexibility in how you choose to display the data. In some cases, when analysing variance there may be too many contributing elements, and the overall chart is cluttered and difficult to read, as in the example below on the left. Here it would be more useful to focus on the key elements which are contributing most of the variation and use a pareto-style approach. The outlier-grouping capability means we can choose to view only the top 5 (by default this is done on ‘absolute’ variance, i.e. positives and negatives), and group everything else under ‘others’ for a more useful view as shown on the right.

Waterfall chart by region
All regional contributions
Waterfall chart with others grouping
Top 5 regional contributions with others grouping

Time Series & Structural Analysis

Time series analysis is a core part of business reporting that examines change over time, and is often used to help predict what will occur in the future. It’s well understood that when looking at time based charts the time sequence moves from left to right, with the oldest period being furthest left. We can use this understanding, as recommended by IBCS,  to help standardise our reporting such that time is always displayed on the horizontal axis. When a chart is not comparing time, the categories are displayed on the vertical axis. Using this approach helps us immediately understand that a column chart is displaying an aspect of time, whereas a bar chart is displaying structural elements.

We can use both columns and lines to display time series data, and the choice will often depend on the primary use-case. Columns make the comparison of two values easier, whereas lines will show the overall trend more clearly.

Column time series chart
Time series line chart

For structural analysis, as mentioned we display the categories on the Y axis. Aside from the initial recognition being faster, this has the added benefit that non-time categories can often have longer names, which are more easily displayed on the vertical axis.

Bar chart showing product sales amount

Business Scenario Analysis

Alongside ‘Actuals’ - what actually happened - most businesses consider a number of other scenarios, including the annual Plan, Forecast and Budget. Variance analysis can specifically look at the deviation between these aspects, but alongside that XLCubed provides an indicator comparison chart focusing on attainment - how close to the ‘target’ was the actual.

Indicator charts
Actuals with variance indicators

For a comparison over time, the offset comparison chart below is particularly effective. The actuals are displayed as the primary focus, with the budget or plan visible as an offset column, and for future months the plan is fully visible.

Budget chart with data table
Gross Margin Actual : Plan

Using a standard colour scheme for the scenarios such as that recommended by IBCS also helps with immediate recognition.

Budget chart
Actual vs Budget
Forecast chart
Actual and Forecast
Line chart with actual and previous on series
Actual vs Previous Year
Subscribe to the XLCubed newsletter.
  • Latest features
  • Version releases
  • News and events
Privacy Policy