I'm not really sure if there is an answer to this question. Intuition says there must be, but inumerable hours scouring the net tells me there is not. If anyone could put me out of my misery one way or another I will be forever in awe/debt.
I require a method of variance analysis to better report mix variances between products. For example, take the following information...
Product Volume Volume Rate Rate Actual Budget Actual Budget A 700 400 3 4 B 100 300 2 1 C 300 300 1 2 Total 1100 1000 2.36 2.50
Conventional variance analysis defines
Volume Variance A = (700-400) * 4 = 1200 B = (100-300) * 1 = (200) C = (300-300) * 2 = 0
Rate Variance A = (3-4) * 700 = (700) B = (2-1) * 100 = 100 C = (1-2) * 300 = (300)
This does not address mix issues however, i.e. where the weighting of more or less profitable products may be different to budget mix.
I am also aware that the Volume Variance can be broken down into two components named the 'Sales-Mix' and 'Sales-Quantity' Variances. These are defined as follows:
Sales Mix Variance
(Act Vol - ((Bud Vol/Tot Bud Vol) * Tot Act Vol)) * Bud Rate
A = (700 - ((400/1000)*1100)*4 = 1040 B = (100 - ((300/1000)*1100)*1 = (230) C = (300 - ((300/1000)*1100)*2 = (60)
Sales Quantity Variance
(((Bud Vol/Tot Bud Vol) * Tot Act Vol) - Bud Vol) * Bud Rate
A = (((400/1000) * 1100) - 400)*4 = 160 B = (((300/1000) * 1100) - 300)*1 = 30 C = (((300/1000) * 1100) - 300)*2 = 60
I have managed to get this far but am unhappy with the results and believe that there must be a better way of measuring the mix effect. I am unhappy that the formula's are limited to looking at the mix variance using only Budget Rate. I think that it would be more appropriate to take into consideration when selling more (or less) of a product, how its budgeted margin compares to the weighted average budget margin.
Take product B for example, the analysis above is indicating an adverse mix variance of 230. However this product has a budgeted rate of only 1 which is well below the weighted average rate of 2.5. Hence there must exist some favourable variance given the fact that as a proportion of total sales product B is down from 30% in the budget to 9.1% in actuals.
This led me to try and deconstruct the Sales Mix Variance into the following
[A] (Act Vol - ((Bud Vol/Tot Bud Vol) * Tot Act Vol)) * (Bud Rate - Weighted Ave. Bud Rate) and [B] (Act Vol - ((Bud Vol/Tot Bud Vol) * Tot Act Vol)) * (Weighted Ave. Bud Rate)[A] above I believe sort of gives me what I'm wanting in that it returns a favourable variance of 345 for product B. The second element [B] however returns an adverse of 575 which of course takes me back to my original adverse 230.
OK now for my question.
I need formulas which report to me the variances (however many are necessary) the volume, rate and mix issues present - use the sample data to illustrate. I require that mix variances in particular take into account the weighted average budgeted rate. (It may need to take into account the weighted average Actual rate - but I don't think so). Of course the product variances need to add back to the grand total variance. I may be close to getting there or I may be well off track?