**** Important Note: Waterfall Charts require at least Excel 2016. ******
This best practice is a fully automated 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 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 give 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 products on a Matrix Change in Volume / Change in Price. These charts take their inspiration from strategy consulting and will allow you to easily categorize 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. In particular, if you are a math Hero, you will particularly like the one from solutionuk.com which explains in detail why the concept of Price Volume Mix (PVM) 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!