Price-Volume-Mix Analysis
Originally published: 25/06/2020 06:41
Last version published: 25/03/2021 16:41
Publication number: ELQ-25560-7
View all versions & Certificate
certified

Price-Volume-Mix Analysis

A comprehensive full-scope model to analyse revenue and profit changes

Description
Price-volume-mix analysis is vital in understanding the changes in sales and profits between two periods, between budget and actual or between model scenarios.

This comprehensive full-scope model allows to:

1. Compare total prices, volumes, revenues and gross profit by product between two periods.
2. Figure out which products experienced the highest price, volume, revenue and profit changes, both positive and negative
3. Visualize mix changes by products and by periods
4. Determine which products affected the change in revenues (profits) and to what extent
5. Build revenue and gross profit bridges for a particular product
6. Split the revenue and gross profit changes into three components: price effect, mix effect and volume effect
7. Calculate price-volume correlations

The model handles ten product and five annual periods (which can be amended to your requirements rather quickly). Every sheet allows selecting periods for the analysis independently from other sheets. You will most probably want to compare two consecutive periods but those can really be any two periods you choose.

The model uses professionally designed magazine-quality charts and mini-dashboards. Some of them are custom-built. For instance, waterfall charts are great to visualize the effect of every product on the change between two years’ revenues. Excel has a standard vertical waterfall chart which I find rather bulky. Instead, I use more compact and elegant horizontal waterfall charts. Unlike a vertical waterfall chart, my chart is compatible with all versions of Excel.

Another type of charts is a ‘broken line’ (or ‘stepped line’) chart which also looks smarter than a regular line chart.

To make sure you can update/create new such charts, I am providing detailed appendices with full instructions. Use them in your models and presentations to impress your clients, managers and colleagues!

Another feature of this model is the use of form controls (check boxes, list boxes, drop-down lists) to quickly select periods and products. Such controls add interactivity to your files and make them more professional and user-friendly. There is also an appendix to the file with instructions on those.

The model contains 18 worksheets and uses no macros. It is accompanied by a detailed text guide.

This Best Practice includes
1 Excel file, 1 PDF file

Acquire business license for $50.00

Add to cart

Add to bookmarks

Discuss

Further information

Calculate price, volume and mix effect; analyse total revenue and gross profit changes by product

Any business selling more than one product

n/a

Reviews

  • Rate this Downloadable Best Practice

    Write a review

  • Scott Donald(last updated: 02/02/2021 01:50)

keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Andrei Okhlopkov

See all
keyboard_arrow_leftkeyboard_arrow_right

Any questions on Price-Volume-Mix Analysis?

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


5.0 / 5 (1 votes)

please wait...