Home >  Excel Solutions > 

Excel Can Help You Find
The Variances That Matter

Managers and staff 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.

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

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 lost 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, even millions, of variances and list the ones that probably are significant.

The General Approach to Excel Variance Analysis

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

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. And a bad one.

There are at last three problems with this approach: It's very time-consuming. It limits the number of methods you can use to identify problems. And it's very prone to error.

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

1. Automatically, pull each data series into your spreadsheet, one series at a time. To analyze spending variances, for example, you pull recent spending and variance history for each spending account in each department, one account and department at a time.

2. Use formulas to determine whether the current data series has a variance with a likely problem.

3. Add each data series with likely problems 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.

The key to this method is that you must have a way for Excel formulas to retrieve data from a central database. If you use an Excel-friendly OLAP, this is easy to do. Otherwise, you will need to work with your IT department to add this functionality to Excel, or else acquire an Excel-friendly OLAP.

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.

1. Materiality

Your filter could list all variances beyond a certain dollar amount, or percentage of 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 deviations. You could use Excel's STDEV or LINEST functions to identify these outliers. For example, you could list all variances that are greater than three standard deviations above or below the average variance over the past 18 months or so.

If spending is growing quickly, by design, recent variances would naturally be larger than variances months ago. Therefore, you could test for percentage variations, rather than dollar variations.

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. Some accounts have unremarkable variances over several months, but each with the same sign; but these variances that could add up to a significant percentage variance for the months in question.

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

One way to do this is to 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

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.

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. The maximum value of 10 would mark an account with significant spending. A value of 1 would mark an account with minor spending. This figure charts a value of 8.

Similar charts could be used to analyze many types of variances: actual sales versus quota by sales person or product, sales by customer versus the prior year, purchases by vendor versus the prior year, and so on.

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 change. By creating improved variance reports, Excel users can specify those targets and thus improve business performance.