# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Sales price volume mix variance analysis - who can explain!

## Crawfinator1

I am having a friendly argument at work in relation to how to calculate a mix variance, we all agree on the volume and Price analysis calc's.

We have two formulas that come out with the same result on a macro scale, however on a product by product basis they differ completely.

The theory is killing my brain and i was hoping that there may be someone here that can help show which logic makes more sense!?

Appreciate any thoughts on which is correct...!

----------


## shyamhappy

(Act Vol *Act price) -(Bud Vol * Bud Price)

For the Mix which will give you an exact variance

________________________________________________________________________________________________
if something has helped please click * 

I have not failed. I've just found 10,000 ways that won't work.

Thomas A. Edison

----------


## Crawfinator1

That is just the plain variance - I am trying to determine the corrrect "Mix" variance for a particular product?

----------


## Aces11

I'm playing with this too.  I've done some work using the "level adjustment" method here.  http://www.solutionuk.com/consult/RE-EXAMINATION-2.htm

Still trying to wrap my brain around using different equations though if the Price is up & Volume up, vs. Price Up & Volume Down, etc.

----------


## PhrankXXX

Volume and mix analysis can be a difficult challenge.  The best course of action is to "Google" search "Volume and Mix Analysis".  There are a couple of sites that might well be worth investigating:

http://volume-and-mix-analysis.weebly.com/
http://towsonhigh55.com/links/MixVariance.htm

These 2 sites provide complete, concise, practical, easy to comprehend "Micro to Macro" level solutions to this complex problem; complete .pdfs and downloadable explanatory .xlsx  spreadsheets are provided.  These solution(s) will give you the exact mix variance for a particular product within any  product grouping and then show you how it "rolls up" the various levels of the product's budget/actual hierarchy.  I sincerely hope that this helps and ends your mind bending headaches!

----------


## PhrankXXX

The attached spreadsheet is a fully correct and complete analysis of the original query submitted by Crawfinator1.  It segregates the difference in $ Sales Volume between budget and actual into Price, Volume and Mix Variances.   The analysis provides a "Mix-Adjusted Volume Variance" for each product; the product totals (a macro view) are consistent with the total product group.  The analysis of $ Sales Volume is of some interest to management but it should be extended to include a complete analysis of the difference in $ Gross Profit between budget and actual into Volume and Mix Variances.  In order to do this, product costs must be included in the analysis.  See the following links for complete examples of $ Gross Profit Volume and Mix Variance Analysis:

http://www.volume-and-mix-analysis.com/
http://towsonhigh55.com/links/MixVariance.htm

Price Volume Mix Variance - Correct .xlsx

----------


## PhrankXXX

Try this for spreadsheet download:
Price Volume Mix Variance - Correct .xlsx

----------


## magnuskruber

This is very useful indeed PhrankXXX. I've been working on this analysis for some time but on the operating profit level including the cost component you mention. I think the link you posted describes how to do it, just wanted to check if you have actually modelled it out? I'm struggling.

----------


## Aces11

PhrankXXX - I like the formula as well but the results aren't intuitive.  An example on the spreadsheet is Product #3.  The Mix amount of $38,222 is greather than either the Actual Sales Volume or the Budget Sales Volume.  I've not been able to sell the rest of my FP&A group on the formula because of this anomoly with Mix.

----------


## protonLeah

_It has come to our attention you have violated Rule 4 of our Forum_ RULES. *Don't Private Message, Visitor message or email Excel questions to moderators or other members.* _(Or Access, Word, etc.)_

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

Post your question in a public thread and our many contributors will come to you to assist, especially if the title is accurate _(see Rule #1)_ and you include a sample desensitized workbook that makes it easy for others to try and help.

----------


## PhrankXXX

Price Volume Mix Variance .xlsx

The attached Excel spreadsheet differs from the 2 solutions in the spreadsheets furnished by Crawfinator1.  The user will have to determine for his or her self which of the 3 solutions is mathematically correct.  The problem I have with Crawfinator1's solution(s) is that I believe that the only "weighted average" which should EVER be used in the calculations is the "weighted average" (Budget Mix) of Sales Prices.  In the case of all 3 solution(s) . . . mine and Crawfinator1's, that figure is merely $162.00.  The "TOTAL Volume" ($105,300) and "TOTAL Mix" (-$5,300) variances are correct for all 3 solutions but the INDIVIDUAL Product figures for "Volume" and "Mix" are mathematically incorrect for both of the Crawfinator1 solutions.

----------


## PhrankXXX

This thread has the same Volume Variance / Mix Variance question:

Mix, Volume and Price impact on revenue

----------


## PhrankXXX

The Mix Variance amount for each individual product is determined by 3 amounts:
	. The difference between the price of the individual product and the Actual Group Budget Price.
	. The difference between the product's Actual Product Mix and its' Budget Product Mix.
	. The Actual Group Total amount.

In the case of, for example, Product #1:
	. $210 - $162 = $48
	. 0.173913 - 0.300000 = -0.126087
	. 1150

Thus:
	. Product #1 Mix Variance = $48 x -0.126087 x 1150 =  $-6960

Price Volume Mix Variance .xlsx

Intuitive results . . . no anomalies . . . no "gotchas" . . .

----------


## sehla78

great                                                                          .

----------


## magnuskruber

Extended this framework to include cost, any input or comments would be great.

----------


## TheKiniGroup

If you're looking for an easy solution to determining the effect of mix, The Kini Group has a business analytics solution with a Price Volume Mix Analysis module that can help you build margin bridges and identify specific margin drivers quickly and effectively. Here's more information on the module: https://thekinigroup.com/suites/pric...-mix-analysis/

If you're curious to see how it works, you can try it out for free by clicking here: http://info.thekinigroup.com/kinimetrix-free-trial 

I hope this helps!

----------


## cveile

Hello!

Realize this is an old thread but it has good info.  Thank you for the examples.

How do you handle examples where products had no sales one year or the other? if you remove units and prices in some of the examples the differences show as price changes when they should be more volume/mix related changes.  Can someone post an example with no sales in budget/target and another line with no sales/units in actual...

----------


## Hotshot01

Hi cveilo,
You might take a quick gander at my previous posts (PhrankXXX) before my change in UserName.  You are absolutely right . . . those situations you mention should never show up as price changes because they are obviously NOT price changes.  If you have Actual sales for which there was no Budget you simply make the Budget Profit Rate = Actual Profit Rate; Budget Mix = 0%.  If you have Budget sales but no Actual the answer is obvious . . . Actual Mix = 0%.  This is not only the accurate mathematical solution, it makes intuitive sense and is easy to explain to management.  This is a frequent question and has popped up many times over the years; you are certainly not the first person to raise the issue.  I would love to post a complete example in this forum but that is very difficult.  If my response is insufficient for your needs, however, let me know and I will do so.  Have a great day!

----------


## protonLeah

cveile,
*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------


## Andy5999

These examples are great. Thanks! I'm wondering how would one calculate channel (Online and In-store) mix. Calculating this and product mix separately - we'll have to ensure if product mix is not included while calculating channel mix and vice versa. Attached modified example.

----------


## alansidman

*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------

