Price volume mix analysis: the formulas, a worked bridge, and how to build it in Excel

Price volume mix analysis splits the change in revenue between two periods into a price effect, a volume effect and a mix effect that add up exactly to the change. In the example below, revenue grew by $289,000: $39,000 from price, $270,000 from volume, and −$20,000 from mix, because the extra units went mostly to the cheapest line. You need units and revenue per product for both periods, and the bridge fits on one Excel sheet.

What price, volume and mix each mean

Price. The same products sold at different prices. A $1.00 increase on 36,000 units is $36,000 of price effect.

Volume. More or fewer units in total, valued as if the product blend and prices had not changed. It answers: if we had sold the extra units in last year's proportions at last year's prices, what would revenue have done?

Mix. A different blend of products. Selling relatively more of a low-priced line and less of a high-priced one lowers revenue even when total units and every price are unchanged.

US public companies already answer this question in words: Item 303 of Regulation S-K asks management's discussion and analysis to describe, where net sales or revenue changed materially, "the extent to which such changes are attributable to changes in prices or to changes in the volume or amount of goods or services being sold or to the introduction of new products or services." A price volume mix bridge is how commercial finance puts numbers on that sentence.

The rows you need

One row per product, or per product and customer, with four numbers:

No units, no honest price effect. If the only data is revenue, price and mix cannot be separated, and anything labeled "price" is a guess. Units must also be comparable across products: cases on one line and each on another make the mix effect meaningless.

The formulas

With Q for units, P for price, R for revenue, PY for prior year and CY for current year:

Price effect = Σ (P_CY − P_PY) × Q_CY

Volume effect = (Q_CY,total − Q_PY,total) × P_PY,average, where P_PY,average = R_PY / Q_PY,total

Mix effect = Σ (Q_CY − Q_CY,total × Q_PY / Q_PY,total) × P_PY

Check: Price + Volume + Mix = R_CY − R_PY

The order is what makes the residual disappear. Price is valued at current-year units, so it carries the interaction between price and volume. Volume and mix are both valued at prior-year prices, and together they equal Σ (Q_CY − Q_PY) × P_PY, the full unit change at old prices. Price plus that is exactly R_CY − R_PY.

Other methods exist. A common one values price at prior-year units and reports the leftover as a separate "price-volume interaction" line. Both are valid; state the method on the bridge and use the same one every period. For cost variances under a standard costing system, see variance analysis.

Worked example: three product lines

Prior year (PY) and current year (CY):

The prior-year average price is 2,160,000 / 80,000 = $27.00. Prior-year shares of units are 50% Standard, 20% Premium and 30% Value.

Price. (26.00 − 25.00) × 36,000 = +36,000; (51.00 − 50.00) × 20,000 = +20,000; (14.50 − 15.00) × 34,000 = −17,000. Total +39,000.

Volume. Total units rose by 10,000. At the prior-year average price: 10,000 × 27.00 = +270,000. By product, each line's share of that at its own price: Standard 10,000 × 50% × 25 = 125,000, Premium 10,000 × 20% × 50 = 100,000, Value 10,000 × 30% × 15 = 45,000.

Mix. At 90,000 units in last year's blend, the lines would have sold 45,000, 18,000 and 27,000. The actual differences, at prior-year prices: Standard (36,000 − 45,000) × 25 = −225,000; Premium (20,000 − 18,000) × 50 = +100,000; Value (34,000 − 27,000) × 15 = +105,000. Total −20,000.

Reading it: growth was mostly volume. Mix took $20,000 off because the blend moved from Standard toward Value, and the $0.50 price cut on Value cost $17,000 of the $56,000 gained on the other two lines.

The check that proves it

Two checks, both exact.

1. The effects sum to the change. 39,000 + 270,000 − 20,000 = 289,000, and 2,449,000 − 2,160,000 = 289,000. No residual, to the cent.
2. Each product's effects sum to that product's change. Standard: 36,000 + 125,000 − 225,000 = −64,000, which is 936,000 − 1,000,000. Premium and Value tie the same way, as the Total column shows.

A cross-check on mix: current-year units at prior-year prices are worth 36,000 × 25 + 20,000 × 50 + 34,000 × 15 = 2,410,000, an average of $26.78 against $27.00 last year. The same 90,000 units at last year's average would be 90,000 × 27 = 2,430,000. The difference, 2,410,000 − 2,430,000 = −20,000, is the blend alone: the same mix effect. Every line of a bridge must tie like this; how to read a forecast bridge covers the same discipline for forecasts, and the bridge glossary entry gives the definition.

Build it in Excel

Rows 2 to 4 hold the products and row 5 the totals. Columns: A product, B PY revenue, C PY units, D PY price (=B2/C2), E CY units, F CY price, G CY revenue (=E2*F2). Totals: =SUM(C2:C4) in C5 and =SUM(E2:E4) in E5.

H2  Price effect:   =(F2-D2)*E2
I2  Volume effect:  =($E$5-$C$5)*(C2/$C$5)*D2
J2  Mix effect:     =(E2-$E$5*C2/$C$5)*D2
K2  Total:          =H2+I2+J2
L2  Check:          =K2-(G2-B2)

Fill down to row 4 and sum H to K in row 5. L2 to L4 must be 0, and K5 must equal G5 − B5. The volume formula multiplies the total unit change by each product's prior-year share and price, which is why the per-product volume effects add to the 270,000.

For the chart, lay out five rows: PY revenue 2,160,000; Price 39,000; Volume 270,000; Mix −20,000; CY revenue 2,449,000. Select them and choose Insert > Waterfall, then mark the first and last bars with "Set as total" so they start at zero. Microsoft's waterfall guide lists it for Excel 2016 and later, including Microsoft 365.

Extending it: customers, new and lost, and margin

Customers. Run the same rows at product-and-customer level and the mix effect splits further: a customer buying more of the cheap line shows up as mix. The answer changes with the level, so state it. For the price side by customer, see price realization by customer.

New and lost. A product or customer with no prior-year sales has no prior price, so it cannot have a price effect. Show new lines and lost lines as their own bars, then run price, volume and mix on the lines sold in both periods.

Margin. Replace price with unit margin, price minus unit cost, in the same formulas. A margin bridge adds a cost effect beside price: −(unit cost CY − unit cost PY) × Q_CY, negative when unit cost rose. The revenue bridge and the margin bridge should sit on the same page and reconcile to the P&L.

Where it goes wrong

• No units, or units that do not compare. Cases against eaches, or revenue only, and price and mix cannot be separated honestly.
• A different order. Valuing price at prior-year units without an interaction line leaves an unexplained residual. State the order.
• New and discontinued lines inside price. A product with no prior price produces a meaningless price effect; treat it as new or lost.
• Currency. A dollar that moved against the euro shows up as price unless foreign sales are translated at constant rates first; see currency and multi-entity roll-ups.
• The wrong level. A total-level bridge can show no mix effect when the mix that matters is by customer or channel.

The bridge from your own ledger

This is the bridge Covirage's revenue driver tools compute from your sales file, by product, customer and region, with new and lost customers as their own lines and the total checked against the ledger; the external AI model explains it and never does the arithmetic. Ask where revenue growth came from and get the bridge that sums to the change: see revenue driver analysis. For the margin side, see gross profit margin; for where the bridge sits in the monthly pack, how to analyze a P&L and management reporting.