• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 

Send to a friend

• linkedin
• twitter
• facebook
• code

Originally published: 16/12/2019 07:08
Last version published: 08/02/2021 07:59
Publication number: ELQ-20434-9
View all versions & Certificate

Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product

Price volume mix analysis (PVM) excel template to better understand your sales and improve pricing strategy.

by Pierre-Alexandre HEURTEBIZE
Consultant - M&A, Modelling and Financial analyst - Investment Manager at HoriZen CapitalFollow 45

19 reviews9,432 views|8 comments|Bookmark Add to cart - $60.00

price volume mix analysissales mixvolume price mix analysisvolume price analysisrevenue bridgesales analysissales metricspricing modelsprice volume mix excel template

Description
**** Warning : Waterfall Charts require at least Excel 2016. ******

---- NOTE ON MODEL UPDATE OF 2021-01-17 -----
I have added a new tab that includes a bridge chart to see the impact of the new products on the Gross Profit Margin.
In short, by adding the revenue and gross profit of the products launched in the new year, the model calculates the marginal impact on your total Gross Profit margin of each of these new products and show these impact on a bridge chart.
------------

This Price Volume Mix analysis excel template is an unlocked fully automated PVM excel model (100% pure excel, no VBA included in the model) which allows you to simply input your Revenue, Volume sold and Direct Costs by product for several periods (Months. years... you choose!) and automatically have insightful charts showing you on a product by product basis where are the drivers of growth or decline.
For your convenience I also have automated all the chart titles so that you only have to input the name of the period and of the Var once (everything that needs your input is easily identifiable and indicated in a specific color).

This model starts with an overview of Annual volume sold and average price for a product that you can select using a dedicated drop down list, along with Annual revenue and GP margin % and an overview of the Product mix for each year.
The model then moves on to charts where all products are automatically sorted out from lower variance to larger variance on the basis of Revenue, Gross Profit, Volume and Price, so that you can easily spot which product experienced the most change.
The PVM model then moves on to a Price / Volume effect bridge between 5 periods at a product level. Products can be switched easily using the dedicated drop down list. Then a similar bridge is generated for Gross Profit / product & Volume effect.

The next tab offers a comprehensive Bridge at Company level detailing Price Effect by product, Volume effect by Product and Mix effect by product, with total Price, Volume, Mix effects )PVM) at company level being automatically calculated and updated on charts.

The following tab does the same bridges with Gross Profit by product effect, Volume effect and Mix effect
Finally the two last tabs gives you an insightful representation of how each of your products changed vs. last period in terms of volume and price by showing all of your product on a Matrix Change in Volume / Change in Price. These charts take their inspiration from strategy consulting and will allow you to easily categorise your products between Winners and Losers, or at the very least quickly identify the products which need to be more closely monitored and updated.

As a bonus, I have included a tab with links to interesting and exhaustive articles on the theory behind Price Volume Mix (PVM) analysis and on price volume mix analysis calculation. In particular, if you are a math Hero, you will particularly like the one from solutionuk.com which explains in details why the concept of Price Volume Mix effect is so hard to fully grasp and understand.

Overall, I've put extra hours of work to make this tool as flexible and easy to use as it can be. This best practice required hours of work to develop so that it only takes 1 min to use it. Also it has been built in a way that you will never find anything else than a direct link between tabs. No hard-to-track multiple-tab formulas here!

This Best Practice includes
1 Excel file with 12 tabs

Acquire business license for $60.00

Add to cart

Add to bookmarks

Discuss

Further information

Reviews

• • 
    4.9 / 5 (19 votes)

    • 89%
    • 11%
    • -
    • -
    • -
  • Rate this Downloadable Best Practice
    

    Write a review

• Grace Park43(last updated: 19/03/2021 13:19)
  
• Gabor Bakler-Kugler(last updated: 20/02/2021 20:38)
  
• Harry Marfatia(last updated: 24/08/2020 00:38)
  
• Cameron Alfier(last updated: 04/07/2020 19:25)
  
• Ruben Valdez(last updated: 24/06/2020 23:24)
  
• Curtis Knight(last updated: 18/06/2020 10:53)
  
• David Konteh(last updated: 13/05/2020 08:38)
  
• Rafael Brother(last updated: 05/05/2020 22:34)
  
• Brian Pham(last updated: 17/12/2019 21:11)
  
• Nick leaver(last updated: 17/12/2019 21:09)
  
• Anthony Wheeler(last updated: 17/12/2019 21:08)
  
• Jordan Colomb(last updated: 17/12/2019 08:35)
  
• Jesse F. Mouallek(last updated: 16/12/2019 15:25)
  
• Guillaume Guitton(last updated: 16/12/2019 15:21)
  
• Franck Lim(last updated: 16/12/2019 15:20)
  

  Nice tool

  Really helps us understand where our growth is and improve our pricing strategy! Highly recommend
• Yoomin Y(last updated: 16/12/2019 14:06)
  

  Very helpful

  I have been using it on a daily basis. Saves time and makes my life a lot easier. Very good value for money, will definitely recommend it.
• Loïc Grandman(last updated: 16/12/2019 13:39)
  
• Kyle Bourienne(last updated: 16/12/2019 10:19)
  

  Real time saver!

  This tool became a staple in my daily workflow. A real time saver. Highly recommend it
• Alexandre Allegret(last updated: 16/12/2019 09:27)
  

  Great

  Amazing analysis tool!

keyboard_arrow_downShow all

Post Comment

• 

  Rasmus Dahl Møllerhøj 10 months ago
  Eloquens Member

  Hi Pierre,
  How do you calculate the mix effects?
  And is it possible in your mix calculation to have mix on a the individual line level?
  And what do you do in terms of underlying lines & subtotals - do you recalculate your price, volume & mix effects on the higher level, or do you sum the underlying lines?

  arrow_drop_uparrow_drop_downReply reply

  • 

    Pierre-Alexandre HEURTEBIZE 10 months ago
    Authorstar

    Hello Rasmus.
    I calculate
    - Price effect as Var in price * last year volume,
    - Volume effect as Var in Volume * last year price
    - Mix Var in price * Var in Volume. Effectively that computation of the "mix" is here to make sure that the sum of Price Effect + Volume Effect + Mix effect = actual var in sales.
    If you'd like to better understand the calculation please look at the following paper who's very detailed
    http://solutionuk.com/consult/RE-EXAMINATION-2.htm
    
    For total effect, I do sum the underlying lines, which is the best practice that PwC applies (at least on all deals I've worked on).
    
    Hope this helps!
    
    Kind regards

    arrow_drop_uparrow_drop_downReply reply
• 

  Shane Shepherd 5 months ago
  Eloquens Member

  Hi Pierre,
  1. is it possible you could add DELETED products to the bridge chart?
  2. can this template be converted to Google Sheets?

  arrow_drop_uparrow_drop_downReply reply

  • 

    Pierre-Alexandre HEURTEBIZE 4 months ago
    Authorstar

    Hello Shane.
    
    1) If you want to show discontinued product, you can include them as part of your product basket (please see recording)
    https://www.loom.com/share/fd0927c666b5414a8192d8ed0b04feeb
    
    Also, the whole model is unlocked so you can modify everything if you want to add sections in your bridge chart, like showing movement of discontinued product as a separate effect.
    
    2) I do not think waterfall bridges are supported by Google Sheet, and overall the formatting is rarely compatible so I would not advise to download this model for use in Gsheet
    
    Hope this helps

    1arrow_drop_uparrow_drop_downReply reply
• 

  Darshika Govindram 1 year ago
  Eloquens Member

  Hi I was looking for simple solution for the company I work for. here they want constant GP but want to play around with the price and volume to decide the next step ( to increase the price or volume) with fixed GP. will this work on what you have explained?

  arrow_drop_uparrow_drop_downReply reply

  • 

    Pierre-Alexandre HEURTEBIZE 1 year ago
    Authorstar

    Hello Darshika,
    
    Happy to help if i can. Could you provide more context and be more specific about what you mean by “fixed GP”? Gross profit being revenue less cost, I struggle to understant how your gross profit can stay fixed if you increase the price? Do you mean GP by product? Total GP?
    Is the aim to have a model that would say “our costs are X per product. We want to achieve a total gross profit of Y. How many products do we need to sell if the product is priced at Z?”

    arrow_drop_uparrow_drop_downReply reply
• 

  David272 1 year ago
  User

  What is the best way to add additional products to this analysis?

  arrow_drop_uparrow_drop_downReply reply

  • 

    Pierre-Alexandre HEURTEBIZE 1 year ago
    Authorstar

    Hi David!
    Given how I've built the model mostly using only direct links, this should not prove too complex to add new product.
    First, I would insert the new rows below all the tables and chart sources throughout the tool.
    Once this is done, you will simply need to drag down the existing formula and to include the new products in the chart. For the latter, clicking on one chart should allow you to see the source data area colored and you can just expand that area by dragging the right bottom corner of the coloured area.
    Hope this helps!

    2arrow_drop_uparrow_drop_downReply reply