For business users of Microsoft Excel Free guides and templates
Home >  Excel Reports > 

Management Reporting

Find the Variances That Matter with Excel

Your company can waste significant time and money trying to control insignificant, uncontrollable, and random variances. Instead, Excel users can filter variances to find those that matter.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Most variance reports are horrible, for at least three reasons.

Charts of actual and budgeted performance
The lines show actual spending and the areas show budgets—by account for the past 24 months. They show spending patterns hidden by normal variance reports.
1. Typical variance reports, which show every variance, force managers and staff to waste time investigating meaningless random variances.

2. Serious problems can easily be overlooked because they're hidden in a sea of triviality.

3. Many areas of business performance that should use variance analysis don't do so, because the state of the art for variance analysis is so primitive.

Excel users don't have to put up with these problems. We can create automated variance reports that search through thousands of variances to discover the ones that probably are significant.

The General Approach to Improved Variance Analysis in Excel

Suppose your company has many departments with many expense accounts, and you want to list the spending variances that probably are a correctable problem. You could use two general approaches, one good, one not so good.

Here's one approach: You could import the entire data file into Excel. You could create columns of formulas to indicate possible problems in the many rows of data. Then you could sort the problems to the top of your worksheet.

This is a common approach. But not a very good one...

  • It's time-consuming.
  • It limits the number of methods you can use to identify problems.
  • And it's prone to error.

The better approach has none of these problems. Here it is:

1. Starting with account for which you want to evaluate a variance, set up formulas to determine whether the current data series has a variance with a likely problem.

2, Using a short macro, pull each data series into your spreadsheet, one series at a time. For example, to analyze spending variances, you could pull recent spending and variance history for each spending account in each department, one account and department at a time.

3. When your calculations find a likely problem, have your macro add the account and department to a list of problems.

4. Research that list of suspicious variances.

I call this approach analytical filtering. Its advantage is that it's developed and automated by Excel users. Because it's automated, you can spend your time researching questionable variances, rather than searching for them. Because you use Excel to define each filter, you can add or change the analyses easily.

There are two keys to this method.

First, you must have a way for Excel formulas to retrieve data by month, account, derpartment, and so on from a central database. If you use an Excel-friendly OLAP database, this is easy to do. Otherwise, you will need to work with your IT department to add this functionality to Excel; or else you can acquire an Excel-friendly OLAP.

Second, you need to have a short macro available that can loop through a list of measures like account and department, write each to an Account or Department cell, recalculate, and then write data to a list if a cell named something like ProblemFound has a value of TRUE. But this is an easy macro for most VBA programmers to write.

Specific Methods of Analytical Filtering of Variances

No single decision rule will find all meaningful variances. Therefore, you should create a series of worksheets to analyze each variance from different points of view.

There are a variety of methods you might want to include. For example...

1. Materiality

Your filter could list all variances beyond a certain dollar amount, or percentage of the budget.

This is an easy filter to create, but it has many disadvantages...

  • It won't signal when a well-controlled account gets out of control, but is still below your threshold.
  • Certain accounts that vary significantly each period would appear on the list most of the time.
  • Small accounts that typically are ignored could vary significantly on a percentage basis, and thus appear on your list.
  • Etc.

2. Statistical Significance

For many accounts in many companies, variances tend to be normally distributed. Therefore, you can identify outliers by listing those accounts with variances beyond a specified number of standard errors. To do so, you could use Excel's STEYX function to identify these outliers.

For example, you could list all variances with more than two standard errors over the past 18 months or so.

3. Multiple Periods

Many variances occur because an invoice arrives a few days early or late, and is booked in a different month than it normally is.

Or, some accounts could have unremarkable variances over several months, but each variance could have the same sign. Therefore, over time, these variances that could add up to a significant variance.

For these and other reasons, it's usually a good idea to include screens that analyze total variances over several months of performance.

From another perspective, you could use Excel's TREND function to compare the trend in the budget in recent months with the trend in spending. If the difference between these trends is significant, no matter what the variance is, then you should investigate the account.

4. Consolidations

Often, spending problems can be found and controlled in subtotals, rather than at the detail level.

Most departmental budgets include accounts that managers typically have little control over, like payroll taxes or utilities.

Similarly, many accounts are difficult to control, no matter which manager tries to control them. Snow-removal and certain legal expenses are common examples. In either case, spending for these accounts can't be controlled by the managers, only predicted.

Therefore, it's often useful to screen and rank department managers for total spending variances in the the accounts that the managers can control. The other accounts, if large, typically require other techniques to bring their costs under control.

5. Charting Methods

Charts of actual and budgeted performance
The lines show actual spending and the areas show budgets—by account for the past 24 months. They show spending patterns hidden by normal variance reports.
Often, numbers can't reveal spending patterns that would be obvious in a chart. That's why figures like this can be useful.

This figure shows spending patterns for four GL Accounts during the past 24 months. The area chart shows budgets and the line chart shows actuals.

This report is for analysis, not for presentation, which is why it has such a high information density. A single printed page of an Excel worksheet could contain seven columns by ten  rows of these charts, or 70 charts in total.

As you can see, these charts have no axis labels. By eliminating the labels we can fit more charts on a page and we can concentrate our attention  on patterns, rather than numbers.

For example, spending for account 6245 maintains an obvious saw-tooth pattern that its budget ignores. Account 6248 has a variation of this pattern. The other two accounts, on the other hand, have similar budget and spending patterns.

Charts of actual spending, budgets, and relative significance.
The columns indicate the relative significance of the spending compared to other accounts. A tall column is much more significant than a short one.  
If it would help to prioritize your analysis, you could add a column at the far-right of the chart, as shown here. The column, which uses the chart's secondary axis, shows the relative magnitude of the dollars in the account—compared to other accounts.

The maximum value of 10 could mark an account with significant spending. A value of 1 could mark an account with minor spending. This figure's columns vary from 2 to 10.

Similar charts could be used to analyze many types of variances...

  • actual sales versus quota by sales person by product,
  • sales by customer versus the prior year,
  • purchases by vendor versus the prior year,
  • etc.

The Bottom Line

The purpose of most variance reports is to change behavior in a way that reduces costs or increases revenue. Variance reports can't succeed unless they target where behavior should and can change. By creating improved variance reports, Excel users can specify those targets and thus improve business performance.

 

Free Excel Dashboards

Click to see customer testimonials

Charley's SwipeFile charts