Sales price volume mix variance analysis - who can explain!

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...!

(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

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

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.

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!

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

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

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.

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.

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.

great .

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

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!

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!

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.