SolutionUK    


HOME









 


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