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.InputSales quantitySales 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:AnalyticsFrom 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 conclusionVariance due to volume = change in volume * price 2017 = $ 100 in totalVariance due to price = change in price * volume 2018 = $ -60 in totalAs 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? Related Leave a Reply Cancel reply Enter your comment here... Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required) Website You are commenting using your WordPress.com account. ( Log Out / Change ) You are commenting using your Google account. ( Log Out / Change ) You are commenting using your Twitter account. ( Log Out / Change ) You are commenting using your Facebook account. ( Log Out / Change ) Cancel Connecting to %s Notify me of new comments via email. Notify me of new posts via email.