Earlier this week a friend of mine came over. He is a Key Account Manager for a large, international company and he is in the process of preparing an internal Key Account presentation. I showed him the wonders of Excel to help him save time.
One of the analysis I suggested that he include, is the price volume analysis (also called variance analysis). The price volume analysis is a very valuable tool. It will give you a breakdown of the variance from one year to another in a simple way.
Input
- Sales quantity
- Sales value
Per product for two time periods.
I will use 2017 and 2018 in this example, but you can use any two periods.
Total sale is up $ 40 from 2017 to 2018:
Analytics
From the simple table above, you calculate price per product (value / volume):
Then you calculate variance (2018 – 2017) for volume and price:
Now you have what you need to do the price volume analysis.
Calculations and conclusion
- Variance due to volume = change in volume * price 2017 = $ 100 in total
- Variance due to price = change in price * volume 2018 = $ -60 in total
As you can see, the sales increase of $ 40 is the result of an increase of $100 due to a higher volume, but additionally a decrease of $ 60 due to a lower price.
Full table and graphics
If foreign exchange is relevant there will be a third element in the analysis. There is a ‘How to’ guide in the Resource section that explaines this in detail.
What do you think? Useful? Complicated?
