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?