Volume and Mix Analysis by Customer and Product

Hi All,

I need some guidance on a volume mix analysis that I'm working on. Both are comparing planned vs actuals.

In the attached excel there are 2 scenarios:

1. Volume and mix against each customers which to me is straight forward.
Volume variance is Actual - Planned * Planned Margin.
Mix not sure if I got this right - difference in Margin * Actual volume.

However, this is where things get tricky in Scenario 2.

If i were to now look at the products under customer 1 and break this volume and and mix at a product level, i cannot tie it to the customer vol and mix in scenario 1.
in Scenario 2, the total volume and margin for both plan and actual by product is the same as scenario 1.
But if you look at the total volume mix, i'm getting 1.28 for volume, vs .875 for mix.

Intuitively the customer vol variance makes sense and it's easier to explain since people doing mental math can relate. However, not being able to tie total customer vs product breakdown is causing an issue. Hoping someone here might be able to help.

Thanks!

If copy the same formula down to the total under Product, you get the same results as when getting the numbers by customer. I believe the issue is that you're adding the Vol and Mix for all products to get your total. I believe that's kind of like getting the average of averages. I think using same equation for the totals as you do for the individual products is the way to go.

Thanks, so seems like by product is correct? The biggest issue is rolling this up to a customer level - the volume variance will not be easy for others to understand if someone does a simple (act - plan vol)* plan margin since it's done at a SKU level.

This is a consolidation problem. In order to obtain correct answers at the consolidation level (by Customer) you MUST use "Rollup Variance".
Do an internet search for "Rollup Variance" (or, perhaps, "Hierarchical Mix Analysis Protocol") and you will learn all about it. It is a fascinating topic!

I will try to get some time to rework your spreadsheet; I can't make any promises as to "when".
Solving the consolidation problem (using "Rollup Variance") is an essential financial analysis tool.

Thanks Hotshot01! This is very fascinating indeed. First time hearing about it, googling price/mix was not working and now i understand why - Rollup variance was they keyword that I needed.
I'll try to re-create my sheet based my understanding, appreciate your help!

XF.xlsxXF.xlsx

Found a few minutes.
Hope this helps . . . .
I could only do Customer 1 because I have no data for Customers 2-4.

Thank you, this is extremely helpful.
Appreciate you taking the time to work on the excel file as well!