Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
  • Price Volume Mix Analysis (PVM) excel template with Charts - Sales mix and Gross Profit by Product
Originally published: 16/12/2019 07:08
Last version published: 12/04/2020 16:54
Publication number: ELQ-20434-6
View all versions & Certificate
certified

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.

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

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 $49.00

Add to cart

Add to bookmarks

Discuss

Further information

Give a strong and automated tool to analyse revenue and gross profit change on a product by product level and efficiently understand the impact of the change in price, change in volume and change in mix on your top line and your gross profit.

A business with multiple products. Retail, marketplace, services companies.

If you are using a microsoft excel version older than 2016

Reviews


    • 4.9 / 5 (17 votes)

      • 88%
      • 12%
      • -
      • -
      • -
    • Rate this Downloadable Best Practice

      Write a review

  • 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


More Best Practices from Pierre-Alexandre HEURTEBIZE

See all
keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for Price Volume Mix Analysis (PVM) Excel Template With Charts - Sales Mix And Gross Profit By Product

The user community and author are here to help. Go ahead!

  • Darshika Govindram
    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
      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
      What is the best way to add additional products to this analysis?
      arrow_drop_uparrow_drop_downReply reply
      • Pierre-Alexandre HEURTEBIZE
        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!
        1arrow_drop_uparrow_drop_downReply reply


      4.9 / 5 (17 votes)

      please wait...