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 50

19 reviews11,242 views|8 comments|Bookmark Add to cart - $60.00

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