**** 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!
Similar Products
Other customers were also interested in...
3 Statement Financial Modeling with DCF & Rel...
Financial Modeling Tutorial guides user via step by step approach on how to build financial models w... Read more
Due Diligence P&L – Exhaustive Revenue a...
Model for in depth understanding of high level profit and loss and revenue analysis. Big-4 like chec... Read more
Multiple rounds Cap Table including Safe, pro-rata...
Multiple rounds cap table including SAFE conversion, Management option pool and investment performan... Read more
Private Equity Fund Model (Investor Cashflows)
Private Equity Financial Model to analyze fund cashflows and returns available to Limited Partners (... Read more
All My Financial Models, Spreadsheets, Templates, ...
Lifetime access to all future templates as well! Here is a set of spreadsheets that have some of the... Read more
Advanced Financial Model with DCF & Valuation
New Version Updates:• Model is now dynamic. Users can select the desired forecast length for up to... Read more
Startup Company Financial Model – 5 Year Fin...
Highly-sophisticated and user-friendly financial model for Startup Companies providing a 5-Year adva... Read more
Top 16 Google Sheet Templates
This is a bundle of all the most useful and efficient google sheet templates I have built over the y... Read more
Small Business Playbook (Financial / Tracking Temp...
About the Template Bundle: https://youtu.be/FPj9x-Ahajs These templates were built with the ... Read more
Budgeting & Forecasting Model Template
Budgeting & Forecasting Template suitable for either a Trade or Service company in any type of i... Read more
You must log in to submit a review.