Mix, Volume and Price impact on revenue

Looking for some assistance...

I am trying to determine how to create a summary that will analyse the
differnce between Revenue from one quarter to another...

For Example

In Quarter 1

Part Unts Price Revenue
A 10 5 50
B 5 3 15

Total revenue for quarter is 65

In Q2
Part Unts Price Revenue
A 20 4 80
B 6 3 18

Total revenue for quarter is 98

So we had a 33 change in revenue from Q1 to Q2...so how much was related to
volume, how much to Price erosion and how much to Mix?

I know how to isolate the ASP impact and can get to a combined Mix/Volume
dirrerence....but I will be damed if I can remember how to split out voiume
from mix.....any help would be greatly apprecaited.

This comment has nothing to do with Excel. From the perspective of revenue
analysis, one approach is to take the +33 revenue change and split it up into
understandable parts. For example:

1. +3 is attributed to a 20% increase in sales of unit B
2. + 30 is attributed to 100% increase in sales of unit A resulting (?) from
a 20% price drop

Other factors may have affected the sales gains, but more data is required
to substantiate that.
--
Gary's Student


"Stumped in San Francisco" wrote:

> Looking for some assistance...
>
> I am trying to determine how to create a summary that will analyse the
> differnce between Revenue from one quarter to another...
>
> For Example
>
> In Quarter 1
>
> Part Unts Price Revenue
> A 10 5 50
> B 5 3 15
>
> Total revenue for quarter is 65
>
> In Q2
> Part Unts Price Revenue
> A 20 4 80
> B 6 3 18
>
> Total revenue for quarter is 98
>
> So we had a 33 change in revenue from Q1 to Q2...so how much was related to
> volume, how much to Price erosion and how much to Mix?
>
> I know how to isolate the ASP impact and can get to a combined Mix/Volume
> dirrerence....but I will be damed if I can remember how to split out voiume
> from mix.....any help would be greatly apprecaited.

Here's an Excel spreadsheet template that will show how you can split out Mix Variance and Volume Variance:

Quarterly Price Volume Mix Variance .xlsx

This site will give additional background regarding this topic:

Mix Matters