Understands Mix Variances Between Products


D

Daz70r

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
(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
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?
 
P

P-A

Have you tried a software called Controller's Comments? It
automatically creates specifications where you can see how much of the
variances are due to sales, margins or currency. It also gives you a
complete break-down of the formulas and calculations. You can read more
at www.obusoft.com
 
D

Daz70r

P-A said:
Have you tried a software called Controller's Comments? It
automatically creates specifications where you can see how much of the
variances are due to sales, margins or currency. It also gives you a
complete break-down of the formulas and calculations. You can read more
at www.obusoft.com

P-A,

Thanks for your post. I have looked at the website and this software (I
believe your own?) does not appear to address the specific mix variance
issues that I highlighted.
 
D

david m wicker

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


You're quite close, you just need to set up the appropriate
spreadsheet model for your requirements
Regards

David M Wicker
www.reflexology-gemma.co.uk
Guildford UK
 
Joined
Feb 5, 2019
Messages
1
Reaction score
0
Country
us
I agree with the mix calculation in Emanuel's spreadsheet which takes into consideration the individual product rates compared to the weighted average rate. However, the variance due to volume calculation is not completely accurate. Using Product C as an example for which actual volume is equal to budget, the variance due to volume calculation is giving us a -$15 value (cell R5). Instead, I would calculate the variance due to volume as [(Actual - Budget Volume)* weighted average Budget rate], which equals $0 in this case and seems more intuitive since there is no volume variance.

The shortfall to this approach is that the variance due to rate + volume + mix do not sum to the total profit variance for each product (although it always comes relatively close). It does sum perfectly at the total product level. Interested whether anyone else is familiar with this method and knows why the variance metrics do not sum to the total variance at the item level, but do in total (and if there is a better approach that will).

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top