Price volume analysis is one of the most often used tools to find out whether the increase in sales/revenue is because of an increase in price or due to an increase in volume. However, it is often the most incorrectly calculated – with the person who is reviewing knowing no better than the person who has calculated.

Price Volume Analysis example:

Price Volume Analysis

In the above example if you were asked to segregate the change in sales because of a change in prices and change in volume – what will you answer?

If you are going to calculate :
Average price in FY19 = 160/100 = 1.6
Average price in FY20 = 230/110 = 2.09
Hence the price change = (2.1-1.6)*100 ~ 49.1 and 10*2.1 ~20.9 as volume change, giving the total as 70

That would be wrong! —– Whatt? Read Along.

This kind of analysis is ok if there is only 1 category, however, when there are multiple categories, then this is not sufficient because there is a movement among the categories apart from the total increase or decrease in the volume.

“But I don’t do it this way – I take the difference in price and multiply by volume and take the difference in volume and multiply by price.”

Well, this is still wrong! —– What? Read Along.

It was a total fluke that when I was creating this – the Net change {last column} actually came equal to 70 (equal to sales change), but when I changed any of the value, the sales change was different from Net change.

Changing the yellow cell, the red cells no longer tallied. This is when the sweat beads start to come out – so as to say what to do now? Then people start doing manual adjustments, taking p1 in some, v1 in others, and all sorts until the net change come equal to sales change and when it comes there is a self pat on the back and the analysis is hurried away for consumption by the top brass which also arrives at the future strategy basis these reports – well obviously no one questions it – otherwise they will have to do it!

So what is the correct way?

See price impact can’t be (p2-p1)*v2 or (p2-p1)*v1. This is because using v2 would be wrong for Cat A {price did not change for 30 units – it did for 20 units} and v1 would be wrong for Cat D {price change for 40 and not 50 units – original question}.

Hence, we have to work with Minimum volume for the Price change and once we have price change we get volume change by just subtracting it from the total change in the category.

But that is such an inelegant way of calculating the volume change –

“You cheated”

Agree. To be honest, the best way is to get both changes and then show that the addition is equal to sales change. Well, there is a way, however, the net change for each category will not come exactly equal to the sales change of that category. This is because we will have to make certain approximations.

Why? Because we know that 10 units increased in A and B each while 10 units decreased in D. So how do we say – where have units in A and B come from? Are they from D with an initial price 1 or are they new ones with an initial price 0? This can be accurate when only 1 category suffers loss or increase (there can be multiple categories on the contra-side) – then everything comes or goes from that category. However, here 2 categories are increasing (A and B) and 2 categories (D and a new category with initial price as 0)

Hence we have to calculate the initial average price of items that have moved. For this, we include 1 more category with 10 units and 0 price in period 1 so that volumes are equal in both periods and the pure movement formula can be replicated.

Here, the average price is 0.5 {1 for Cat D and 0 for Cat E}. We again get volume change of 50. However, as mentioned the individual categories will not be exactly this way since there is no way of knowing that from where is the item coming from. Having said that, it is good to know how to calculate like this, because in certain situations this can come in handy.

Some of the columns are there to arrive at this 0.5 number if there are many categories. Let me know in the comments if you found this useful. Also, subscribe to get the excel with formulae and to remain informed about the tips and tricks to improve your financial know-how.

Recommended Read: How to manage personal finances? 4 crucial steps