Originally published: 16/12/2019 07:08
Last version published: 08/02/2021 07:59
Publication number: ELQ-20434-9
View all versions & Certificate
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 HEURTEBIZEConsultant - M&A, Modelling and Financial analyst - Investment Manager at HoriZen CapitalFollow 42
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 (19 votes)- 89%
- 11%
- -
- -
- -
Write a review
- Nice toolReally helps us understand where our growth is and improve our pricing strategy! Highly recommend
- Very helpfulI 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.
- Real time saver!This tool became a staple in my daily workflow. A real time saver. Highly recommend it
- GreatAmazing analysis tool!
People using this Best Practice also downloaded
Price-Volume-Mix Analysis
A comprehensive full-scope model to analyse revenue and profit changes1,423Discussadd_shopping_cart$50.00
by Andrei Okhlopkov
Cost-Volume-Profit (Breakeven) Analysis
A template to perform cost-volume-profit (CVP, or breakeven) analysis586Discussadd_shopping_cartfree
by Andrei Okhlopkov
Sales Mix, Menu Mix and Menu Engineering Spreadsheet
A series of sales mix worksheets that allows you to analyze menus based on a series of factors.7,3114add_shopping_cart$10.00
by Chef David Buchanan
Black-Scholes Excel Pricing Model
A mini Black-Scholes Excel model, just hit and go!5,406Discussadd_shopping_cartfree
by Gia Hy Le Pham
Monadic Price Testing Excel Template
A tool designed to analyze peoples' purchase intention at different price points and for alternative products.1,482Discussadd_shopping_cart$3.00
by Alberto Scappini
Due Diligence Tools and Methods
A suite of best practices to perform financial and commercial due diligence621Discussadd_shopping_cart$150.00
by Andrei Okhlopkov
Maximized Margin & Revenue Pricing Analysis
Determines optimal sales price for highest profitability, based on comparison of sales (demand) at two price points.15Discussadd_shopping_cart$15.00
by Shane Shepherd
More Best Practices from Pierre-Alexandre HEURTEBIZE
Multiple investing rounds Cap Table
Cap table for capital raise / buy in - by out and investor IRR and investment multiple forecast2,225Discussadd_shopping_cart$10.00
by HEURTEBIZE
Multiple rounds Cap Table including Safe, pro-rata rights and Management Option Pool
Multiple rounds cap table including SAFE conversion, Management option pool and investment performance (IRR) 3 scenarios878Discussadd_shopping_cart$39.00
by HEURTEBIZE
Simple Startup and SME Forecast Business Plan
A projection Model for startup and SME to forecast a complete P&L, Net Working Capital and Cash flow.444Discussadd_shopping_cart$49.00
by HEURTEBIZE
P&L Due Diligence - Exhaustive Revenue Analysis model
Model for in depth understanding of high level P&L and Revenue Analysis. Big-4 like book of due diligence analyses.484Discussadd_shopping_cart$199.00
by HEURTEBIZE
DSO - DPO - DIO computation and graph - Countback method (Includes countforward method!)
DSO (Days Sales Outstanding)/ DPO (Days Payable Outstanding)/ DIO (Days Inventory Outstanding) charts countback method.1,199Discussadd_shopping_cart$25.00
by HEURTEBIZE
Due Diligence Excel Best Practice - Balance Sheet starter pack
Starter Pack Best Practice for Financial Due Diligence - Balance Sheet997Discussadd_shopping_cart$40.00
by HEURTEBIZE
Scorecard - Scoring and Valuation Model
Scorecard / scoring model to help valuation, target assessment and visually see strengths and weaknesses of a business236Discussadd_shopping_cart$5.00
by HEURTEBIZE
Inventory Management and Analysis best practice with dynamic charts
Inventory Management and Analysis best practice. Useful to the Management team as well as to external financial analyst.1,1491add_shopping_cart$30.00
by HEURTEBIZE
Discounted Cash Flow (DCF) and Comparables Valuation Model
Discounted Cash Flow analysis of your business and compare it to a valuation comparables.586Discussadd_shopping_cart$49.00
by HEURTEBIZE
Advanced multiple rounds Cap Table Model including SAFE an Management pool with 4 different structures
Multiple rounds cap table including SAFE conversion, Management option pool and performance (IRR) with 3 deal structures247Discussadd_shopping_cart$69.00
by HEURTEBIZE
Due Diligence P&L - Exhaustive Revenue and Costs analysis template
Due Diligence exhaustive P&L template for Revenue & Costs Analysis. Big-4 like playbook of due diligence analyses.187Discussadd_shopping_cart$249.00
by HEURTEBIZE
P&L (Profit & Loss) Constant Currency Analysis Excel Model
Best practice to assess the impact of FX on a P&L for a business trading in different currencies1,4672add_shopping_cart$20.00
by HEURTEBIZE
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!
- 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?- 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
- Hi Pierre,
1. is it possible you could add DELETED products to the bridge chart?
2. can this template be converted to Google Sheets?- 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
- 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?
- 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?”
- What is the best way to add additional products to this analysis?
- 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!
