How to create a price volume analysis

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:

Price Volume 1

Analytics

From the simple table above, you calculate price per product (value / volume): 

Price Volume 2

Then you calculate variance (2018 – 2017) for volume and price:

Price Volume 3

Now you have what you need to do the price volume analysis.

Calculations and conclusion

Price Volume 4

  • 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

Price Volume 5

 

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?

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close