Volume Price Mix headache

[Drewtaylor1983]

All,

Hoping you can help, i'm working on a Volume / Price / Mix report, but can't quite get my head around the logic.

Ordinarily this would be fine to calculate but i'm in a situtation where there are non like for like products year on year. (think FMCG)

here is an example of a sample set of data that i need to work out the variances for, the price and mix part is the tricky bit, as the usual maths fails when you have 0 volume vs LY (or standard variance if you want to interpret last year as "budget" in a normal manufacturing environment)

Hoping someone has come accross this before and can help,

[TABLE="width: 520"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]LY[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]TY[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]VOLUME[/TD]
[TD="align: center"]PRICE[/TD]
[TD="align: center"]SALES[/TD]
[TD="align: center"][/TD]
[TD="align: center"]VOLUME[/TD]
[TD="align: center"]PRICE[/TD]
[TD="align: center"]SALES[/TD]
[/TR]
[TR]
[TD="align: center"]Product A[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]1.05[/TD]
[TD="align: center"]8.4[/TD]
[/TR]
[TR]
[TD="align: center"]PRODUCT B[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]1.1[/TD]
[TD="align: center"]16.5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]13[/TD]
[/TR]
[TR]
[TD="align: center"]PRODUCT C[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]0.9[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]27[/TD]
[/TR]
[TR]
[TD="align: center"]PRODUCT D[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]7.5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]PRODUCT E[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]0.75[/TD]
[TD="align: center"]18.75[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]0.87[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"][/TD]
[TD="align: center"]73[/TD]
[TD="align: center"]0.92[/TD]
[TD="align: center"]67.15[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col><col><col></colgroup>[/TABLE]

 

[mrmmickle1]

Hey, Welcome to the board!!!

If you can give us a little more information it may be easier to solve your issue.

What specific formula / calculation are you having issues with? What is the current result? What would you like the result to be? Can you put up a simple before and after example please?

 

[Drewtaylor1983]

So, using the example above,

Total sales variance = TY Sales less LY sales.

This then needs breaking down into Volume, price & Mix.

Volume = (TY vol - LY volume) * ( LY weighted average price (sales/volume))

Price & Mix, is then where I come unstuck. As you would normally have comparible products in both lists. To work out mix, you would work out Actual mix - standard mix, * LY price by line. Price = difference in price * LY Volume.

Where i am at currently is to work out price first only for the LFL products, so A, B & C in above example. with the balance of all variances then being Mix. (as products D & E don't appear in each period, there is no Price variance associated with these as the individual prices haven't moved, its just the volume - but he maths doesn't work as difference in volume is absolute)

The approach seems to work and solve my problem, but i can't currently work out the maths to prove that it is true....

Hope that made sense

 

[mrmmickle1]

You can use the ABS() Function to obtain Absolute values.

If you are getting an error message then use the IFERROR() function