Automated Variance Analysis
Traditional Price, Volume, Exchange Variance calculations
are not particularly reliable; often resulting in the need for an Analyst to
spend significant time investigating major variances and Ledger differences
and then making adjustments, usually based on personal judgment, to correct
for calculation errors and data deficiencies. Root cause analysis has revealed
that a number of issues are usually involved:
a) There may be unresolved issues between Ledgers
and Management accounts. It is not uncommon to find that while Financial Ledger
entries are correct, the supporting quantity data (essential to Price/Volume
Variance) becomes corrupted - this particularly applies where corrections have
been made and process short cuts have been taken.
b) Data may actually be missing from the Management
Accounts
c) The Analysis, prepared for different Sales
Regions is found not to reconcile with analysis prepared at the
total business level; this problem is discussed in more detail in
the section headed "MIX" below. (see
also the mathematics of Mix)
Following a period of intense technical development
SolutionUK have developed a process, based on Fully Integrated Financial and
Management Accounting; which provides consistent and meaningful analysis across
multiple organizational levels; incorporating error traps, to ensure that results
are not distorted by spurious data points; as well as taking account of multiple
currencies and automated calculation of Exchange Rate Variances; all Variances
are held at the Customer / Product element level.
As a result of the "Virtually Error Free"
quality of data produced by this process, significant reductions in analysis
time can be achieved, without human intervention.
This development has opened the way to fully automated
data analysis; with the main variance drivers being reported in terms of Customers
and Products, enabling Management to take appropriate corrective action immediately.
Mix
Sales Mix is now recognised to be
computational error. The description below illustrates a way of
tracking this error through various levels of analysis. (see the mathematics of mix
for a full
explaination)
The narrative above highlights
an analysis problem common to most large and complex organizations; the fact
that standard variance analysis will produce a different results, when analysis
is prepared at different levels in the organization. The example below illustrates
the problem.
In this simple example we
have an organization with two Sales Regions - East Region and West Region
Price Variance = Price
Movement x Units This Year
Volume Variance = (Units
This Year - Units Last Year) x Price Last Year
Variance Analysis for
East Region
|
Units This Year |
Sales This Year |
Price This Year |
Units Last Year |
Sales Last Year |
Price Last Year |
Price Movement |
Price Variance |
Volume Variance |
Total Variance |
East |
2,500 |
2,400 |
0.96 |
3,000 |
2,100 |
0.7 |
0.26 |
650 |
-350 |
300 |
Variance Analysis for
West Region
|
Units This Year |
Sales This Year |
Price This Year |
Units Last Year |
Sales Last Year |
Price Last Year |
Price Movement |
Price Variance |
Volume Variance |
Total Variance |
West |
3,000 |
990 |
0.33 |
950 |
475 |
0.5 |
-0.17 |
-510 |
1,025 |
515 |
Sum of Variances for
East and West
|
Units This Year |
Sales This Year |
Price This Year |
Units Last Year |
Sales Last Year |
Price Last Year |
Price Movement |
Price Variance |
Volume Variance |
Total Variance |
East |
2,500 |
2,400 |
0.96 |
3,000 |
2,100 |
0.7 |
0.26 |
650 |
-350 |
300 |
West |
3,000 |
990 |
0.33 |
950 |
475 |
0.5 |
-0.17 |
-510 |
1,025 |
515 |
Total |
|
3,390 |
|
|
2,575 |
|
|
140 |
675 |
815 |
Variance Analysis carried
out at "Top" level
|
Units This Year |
Sales This Year |
Price This Year |
Units Last Year |
Sales Last Year |
Price Last Year |
Price Movement |
Price Variance |
Volume Variance |
Total Variance |
East |
2,500 |
2,400 |
|
3,000 |
2,100 |
|
|
|
|
|
West |
3,000 |
990 |
|
950 |
475 |
|
|
|
|
|
Total |
5,500 |
3,390 |
0.6164 |
3,950 |
2,575 |
0.6519 |
-0.0355 |
-195 |
1,010 |
815 |
Conclusion
From the above it can be
seen that the sum of the Price Variances for East and West is equal to 140
While the Price Variance
calculated at the "Top" level is equal to -195
Hence, it can be seen that
the results are different when calculated at different levels.
Traditional Explanation
The traditional explanation
for the difference between the two methods of calculation is that "Mix" has
not been taken into account. Although this difference can be explained in terms
of "Mix" in practice it is often found that "Mix" is not a useful measure when
analyzing Sales Variances.
Alternative Measures
Mathematically there are
many ways in which the difference between, High and Low level calculations,
can be allocated or explained. The problem has been to find a solution which
provides meaningful results.
Solutionuk have developed
an approach which is based on "Level Adjustment".
A comparison of the attributes
of "Level Adjustment" and "Mix" is shown below:
Level Adjustment |
Mix |
Variance is attached to data element which
has changed |
Variance is spread over all data elements |
Provides Customer by Product level analysis |
Does not provide meaningful Customer Product
Level analysis |
Variance explained in terms of more or less
units sold at above or below last years average unit price |
Variance explained in terms of changes in
the proportion of units sold between this year and last year at last years
unit price |
|