Home >
Excel
Solutions >
Use Excel to 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.
by Charley Kyd
January, 2005
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
timeconsuming. 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
Excelfriendly 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 Excelfriendly 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 wellcontrolled 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. Snowremoval 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 sawtooth
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
farright 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.
