D

#### Daz70r

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

**(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

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

**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?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?