Free Excel templates

A free price volume mix analysis template in Excel, with a complete budget-to-actual bridge

Price and volume on revenue and gross profit, the cross term under both conventions, the group gross profit bridge, the margin split into mix and price, then operating expense, operating leverage and the commission plan: six linked sheets, one fictional quarter, no macros.

The file decomposes one quarter's budget-to-actual variance into volume, price and mix. Calderwood Instruments sells 1,380 instruments against a budget of 1,200, at an average price of 88,000 against 92,000. Instrument revenue is 11,040,000 favourable, yet instrument gross profit is 220,800 adverse. At group level, volume adds 1,919,200 of gross profit and the discount takes away 5,520,000: gross profit ends 3,600,800 below budget while revenue is 3.02% above it. The gross margin falls 3.00 points, of which 1.57 points is mix and 1.43 points is price.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Price_Volume_Mix_Analysis.xlsx (25 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from Financial Planning and Analysis. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon →

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Six sheets, in the order the calculation runs. Inputs are blue type and sit on one sheet; the yellow cells are the actual units and price, and the two assumptions the book flags as such. Amounts carry no currency, as in the book.

Inputs
Units, prices and the standard unit cost for instruments; revenue and margins for consumables and service; operating expense lines; the full-year budget; commission rate, representatives and the installed base.
Bridge
Instrument revenue and gross profit split into volume and price, the price-volume interaction under both conventions, the group gross profit bridge with no plug line, and the gross margin movement separated into mix and price.
Opex
Operating expense and EBIT, budget against actual, line by line, and the research underspend set against the platform revenue a delay would defer.
Leverage
Degree of operating leverage, the breakeven at the budget margin and at the quarter's margin, the margin of safety, the value of one point of gross margin, and the same with part of the cost base variable.
Commission
What a revenue-based commission plan pays for: commission against gross profit, what a representative and the company each give up per unit of discount, the same plan rebased on gross profit, and where a price floor would sit.
Checks
Every figure the book prints set against what the workbook computes, with a verdict on each of the 77 lines.

What the worked quarter returns

MeasureValue
Instrument revenue variance11,040,000 (volume 16,560,000, price -5,520,000)
Instrument gross profit variance-220,800 (volume 5,299,200, price -5,520,000)
Price-volume interaction-720,000
Group gross profit, budget to actual95,788,000 to 92,187,200 (-3.76%)
All volume effects, group1,919,200
Gross margin, budget to actual45.61% to 42.61% (-3.00 points)
Mix effect on the margin-1.57 points
Price effect on the margin-1.43 points
EBIT, budget to actual23,288,000 to 19,487,200 (-16.32%)
Degree of operating leverage4.11x

Read the bridge by cause, not by segment. Volume across all three segments is worth 1,919,200 of gross profit; one pricing decision in one segment is worth 5,520,000 the other way. Remove the discount and gross profit beats budget, but the margin is still 1.57 points down: the growth landed in instruments, the lowest-margin segment, and the shortfall in consumables, the highest. A pricing fix recovers the profit and leaves the mix effect where it is.

How to use it on your own case

  1. Open Inputs and overwrite the blue cells: units, prices and standard cost for the product sold per unit, revenue and margin for the other segments.
  2. Read the Bridge sheet first. The instrument and group bridges must sum to the variance with nothing left over.
  3. Decide your convention and keep it. The file computes price at actual volume, so the interaction sits in price; the cross-term block shows what the other convention gives.
  4. Read the margin split separately from the profit bridge. Mix and price have different owners and different remedies.
  5. Use Opex, Leverage and Commission to carry the gross profit variance down to EBIT, the breakeven and what the sales plan pays for.
  6. Keep the Checks sheet: once you change inputs it stops matching the book, which is expected. Use its structure for your own tie-outs.

Questions people ask about it

Is this price volume mix template really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Where does the price-volume cross term go?

Into the price effect: price is computed at actual volume. That is a stated convention, not a truth. Computed at budget volume, price is -4,800,000 and the -720,000 interaction has to be shown on its own line. On gross profit the wrong pairing even turns the variance favourable, at 499,200. The Bridge sheet prints all of it.

How is mix separated from price in the margin?

Sequentially. Actual revenue and gross profit are restated at budget prices, which gives a margin of 44.04%. Budget margin to that figure is mix, -1.57 points; that figure to the actual margin is price, -1.43 points. Reversing the order moves a little between the two lines.

Can I use it for a real company?

You can reuse the structure. Calderwood Instruments and every figure in the file are fictional, and nothing here is investment or accounting advice.

The rest of the files

This template is one of the companion files for Financial Planning and Analysis: the full set adds the five comparisons workbook, the segment contribution and allocation workbook and the forecast accuracy workbook. Free, like this one.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Financial Planning and Analysis. The book is on Amazon.

If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.