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,

	LY				TY
	VOLUME	PRICE	SALES		VOLUME	PRICE	SALES
Product A	10	1	10		8	1.05	8.4
PRODUCT B	15	1.1	16.5		13	1	13
PRODUCT C	20	0.9	18		27	1	27
PRODUCT D	15	0.5	7.5		0	0	0
PRODUCT E	0	0	0		25	0.75	18.75
	60	0.87	52		73	0.92	67.15

<tbody>
</tbody><colgroup><col><col><col><col span="2"><col><col><col></colgroup>

 

[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