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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
Using a standard colour scheme for the scenarios such as that recommended by IBCS also helps with immediate recognition.