USA Sales Volume Variance Analysis

[Jason Sharpe]

Hello, I've had this question for a while and have not been able to figure out the math by myself and could use some help. I often run variance analysis' for sales volume and need to figure out what metrics are driving the variance, and how much each metric is contributing to the overall favorability or unfavorability of sales compared to budget.

The issue I have is when I have multiple components, if I were to add up the sum of variance analysis for each component, I get different results than when I do a variance analysis when all components are combined. Basically the sum of the parts don't equal the whole.

I have attached a screenshot from excel of what I mean. It is broken into 3 components (A, B, and C) and there is a variance analysis performed in column E for each one. Starting on row 23, I am showing the 2 methods that I am trying to reconcile.

Method 1- adds up all the individual components from above; Method 2- run my own variance analysis using the total metrics.

The area highlighted in yellow should be equal, regardless of the method, but it clearly is not. I believe this is due to volume mix not being taken into account properly in Method 1, but I am unsure how to adjust for it.

Can someone please help reconcile the 2 methods? Thanks for your help!

 

[Jason Sharpe]

I made some adjustments and got both methods to equal out, but not sure if this is an accurate analysis. I made changes to the actual and budget plug-ins to help better exemplify a scenario where overall volume is relatively flat when you compare budget v. actual, BUT at the component level, the volume is coming from a much lower converting group (group C). I want to normalize for the shift in component mix (more volume coming into C, a lower converting group, than anticipated), so I adjusted the budget numbers for Vol., Eligible Volume, and Sales to account for the change in Actual Mix.

The Original Budget budget is under Column C, and the adjusted budget is under Column H. Column O is the actual mix that was used to help calculate the adjusted Volume, Elig. Volume, and Sales for the budget.

One of the things I realized by doing this though, is that the "eligible conversion rate" now reads over 100% under component A and B. I guess that's a by-product of the shift in volume? Basically due to that shift and given the adjusted volumes, it would be impossible for actuals to meet budget?