Price Volume Mix Analysis in Excel: PVM Bridge Guide (2026)
Most price volume mix analysis in Excel doesn't reconcile. The analyst builds three columns, sums them, compares to the actual revenue variance, and finds a gap — so they plug the difference into "mix" and hope nobody asks. That plug is the single most common defect in FP&A variance work, and it's entirely avoidable: the gap is a cross-term with a known algebraic form, not a rounding artifact.
This guide gives you a PVM decomposition that ties out to zero every time, handles new and discontinued products explicitly, and drills down to the SKU level. Every number below is from one worked example you can rebuild in ten minutes.
What Is Price Volume Mix Analysis?
Price volume mix (PVM) analysis decomposes a revenue variance between two periods into three drivers: price (you charged more or less per unit), volume (you sold more or fewer units in total), and mix (the share of units shifted between high-price and low-price products). The three effects must sum to the total revenue variance exactly.
The reason PVM matters is that "revenue grew $280K" is not an answer. Revenue growing because you pushed 400 more units is a very different business than revenue growing because your customers drifted toward the $600 SKU. One is demand, the other is portfolio shift — and they call for opposite responses.
The Three Effects, Precisely Defined
- Volume effect — what revenue would have changed by if total units moved but the product mix and every price stayed frozen at the base period.
- Mix effect — what revenue changed by because the composition of those units shifted toward products priced above or below the base-period average.
- Price effect — what revenue changed by because realized per-unit prices moved, holding the current-period unit mix constant.
ℹ️ Note: Mix is not a rounding bucket. It is a specific quantity — the extent to which current-period units are weighted toward SKUs whose base price differs from the base-period average price. Treat it as a residual and you lose the ability to explain it.
Why Doesn't My PVM Analysis Reconcile?
Your PVM doesn't reconcile because you calculated price on base-period volume and volume on base-period price, which leaves the cross-term (Δquantity × Δprice) unassigned. In the example below that gap is exactly $4,000. Fix it by calculating the price effect on current-period quantity, which absorbs the cross-term into price by construction.
Here's the algebra. Revenue variance is:
ΔRevenue = Σ(q₁ × p₁) − Σ(q₀ × p₀)
The naive split most templates use is:
Price = Σ (p₁ − p₀) × q₀ ← base-period quantity
Volume = Σ (q₁ − q₀) × p₀
Expand both and you're left with Σ (q₁ − q₀) × (p₁ − p₀) unaccounted for. That's the cross-term. It's zero only by coincidence — when quantity and price moves happen to offset across the portfolio.
⚠️ Warning: If your PVM "reconciles" one month and breaks the next with no methodology change, you're almost certainly living on an accidental zero cross-term. It will break in front of the board.
The Convention That Always Ties Out
Use current-period quantity for price, and measure both volume and mix against the base-period average price:
Volumeᵢ = (q₁ᵢ − q₀ᵢ) × P̄₀
Mixᵢ = q₁ᵢ × (p₀ᵢ − P̄₀)
Priceᵢ = q₁ᵢ × (p₁ᵢ − p₀ᵢ)
where P̄₀ = total base-period revenue ÷ total base-period units, across the comparable set only.
Sum all three across every SKU and the terms telescope: the P̄₀ cancels, Σ q₁ᵢp₀ᵢ cancels, and you're left with Σq₁p₁ − Σq₀p₀. That's an identity, not an approximation. It holds to full floating-point precision regardless of how many SKUs you have or how wild the price dispersion is.
The Worked Example: Five SKUs, Two Periods
Here is the dataset. It deliberately includes one discontinued product and one launch, because real portfolios always do.
| SKU | Status | PY Qty | PY Revenue | CY Qty | CY Revenue | PY Price | CY Price |
|---|---|---|---|---|---|---|---|
| Basic | Comparable | 10,000 | $500,000 | 9,000 | $468,000 | $50 | $52 |
| Pro | Comparable | 4,000 | $480,000 | 5,200 | $650,000 | $120 | $125 |
| Enterprise | Comparable | 500 | $300,000 | 700 | $392,000 | $600 | $560 |
| Legacy | Discontinued | 2,000 | $70,000 | 0 | $0 | $35 | — |
| Starter | New | 0 | $0 | 1,500 | $120,000 | — | $80 |
| Total | 16,500 | $1,350,000 | 16,400 | $1,630,000 |
Total revenue variance: +$280,000. Note that total units fell by 100 while revenue rose 20.7% — that's a mix story waiting to be told.
Step 1: Isolate the Comparable Set
New and discontinued products have no counterpart period, so they cannot have a price or mix effect. Strip them out first and bridge them as their own bars.
- Comparable PY: 14,500 units, $1,280,000
- Comparable CY: 14,900 units, $1,510,000
- Comparable variance: +$230,000
- New products (Starter): +$120,000
- Discontinued (Legacy): −$70,000
Check: 230,000 + 120,000 − 70,000 = 280,000. ✓
💡 Pro Tip: Base-period average price must be computed on the comparable set only. Including the $35 Legacy SKU in
P̄₀drags the average down and inflates every mix number in the analysis. This is the most common way an otherwise-correct PVM produces nonsense.
Step 2: Compute the Base Average Price
P̄₀ = $1,280,000 ÷ 14,500 = $88.2759 per unit
Step 3: Run the Three Effects at SKU Level
| SKU | Volume Effect | Mix Effect | Price Effect | SKU Revenue Δ |
|---|---|---|---|---|
| Basic | −$88,276 | −$344,483 | +$18,000 | −$32,000 |
| Pro | +$105,931 | +$164,966 | +$26,000 | +$170,000 |
| Enterprise | +$17,655 | +$358,207 | −$28,000 | +$92,000 |
| Total | +$35,310 | +$178,690 | +$16,000 | +$230,000 |
$35,310 + $178,690 + $16,000 = $230,000. Ties exactly to the comparable variance.
⚠️ Warning: Look at the Basic row — its three effects sum to −$414,759, but its actual revenue only fell $32,000. That is correct and expected. Mix is inherently a cross-SKU concept: it measures a SKU's weight relative to the portfolio average, so it only reconciles at the total row. Never present row-level PVM as a per-SKU variance explanation.
Step 4: Assemble the Bridge
| Bridge Step | Amount | Running Total |
|---|---|---|
| PY Revenue | $1,350,000 | $1,350,000 |
| Discontinued products | −$70,000 | $1,280,000 |
| Volume | +$35,310 | $1,315,310 |
| Mix | +$178,690 | $1,494,000 |
| Price | +$16,000 | $1,510,000 |
| New products | +$120,000 | $1,630,000 |
| CY Revenue | $1,630,000 |
graph LR
A[PY Revenue<br/>$1,350K] --> B[Discontinued<br/>-$70K]
B --> C[Volume<br/>+$35K]
C --> D[Mix<br/>+$179K]
D --> E[Price<br/>+$16K]
E --> F[New Products<br/>+$120K]
F --> G[CY Revenue<br/>$1,630K]
The story writes itself: units were flat-to-down and pricing added almost nothing. Essentially all of the growth came from customers migrating to Pro and Enterprise, plus a successful Starter launch. If management believed this was a pricing win, the PVM just corrected them.
How Do You Build Price Volume Mix Analysis in Excel?
Lay the raw data out in eight columns, define the base average price as a named range calculated over the comparable set only, then write three formulas — one per effect — down the SKU rows and sum each column. The whole build is four formulas plus a reconciliation check.
The Sheet Layout
On a sheet named Data:
| Col | Contents |
|---|---|
| A | SKU name |
| B | Status: Comparable, New, or Discontinued |
| C | PY Quantity |
| D | PY Revenue |
| E | CY Quantity |
| F | CY Revenue |
| G | PY Price (derived) |
| H | CY Price (derived) |
Derive prices rather than typing them — realized price is revenue ÷ units, not list price:
=IF(C2=0, 0, D2/C2)
=IF(E2=0, 0, F2/E2)
💡 Pro Tip: Guard both with
IF(qty=0, 0, ...). A new SKU has zero base units and a discontinued SKU has zero current units — without the guard you'll get#DIV/0!propagating into every subtotal, which is the second-most-common reason a PVM build stalls.
The Base Average Price
Create a named range BasePrice:
=SUMIFS($D:$D, $B:$B, "Comparable") / SUMIFS($C:$C, $B:$B, "Comparable")
Using SUMIFS on the status column — instead of a hard-coded range — means adding a SKU next quarter requires no formula edits.
The Three Effect Formulas
Volume, in column I:
=IF($B2<>"Comparable", 0, ($E2-$C2)*BasePrice)
Mix, in column J:
=IF($B2<>"Comparable", 0, $E2*($G2-BasePrice))
Price, in column K:
=IF($B2<>"Comparable", 0, $E2*($H2-$G2))
New and discontinued bars:
=SUMIFS($F:$F, $B:$B, "New")
=-SUMIFS($D:$D, $B:$B, "Discontinued")
The Reconciliation Check — Non-Negotiable
Put this in a visible cell at the top of the sheet, not buried at the bottom:
=ROUND(
SUM($D:$D) + SUM(I:I) + SUM(J:J) + SUM(K:K)
+ SUMIFS($F:$F,$B:$B,"New") - SUMIFS($D:$D,$B:$B,"Discontinued")
- SUM($F:$F), 2)
It must return 0. Wrap it in conditional formatting — green at zero, red at anything else — and it becomes an always-on tripwire. Our financial model audit checklist covers the broader pattern of putting tie-out checks where reviewers actually look.
A Single-Cell Version With LET
If you want the whole decomposition in one auditable formula rather than three helper columns:
=LET(
status, Data!$B$2:$B$100,
q0, Data!$C$2:$C$100,
r0, Data!$D$2:$D$100,
q1, Data!$E$2:$E$100,
r1, Data!$F$2:$F$100,
comp, --(status="Comparable"),
base, SUMPRODUCT(comp,r0)/SUMPRODUCT(comp,q0),
vol, SUMPRODUCT(comp,(q1-q0))*base,
mix, SUMPRODUCT(comp, q1, (IF(q0=0,0,r0/q0)-base)),
pri, SUMPRODUCT(comp, q1, (IF(q1=0,0,r1/q1)-IF(q0=0,0,r0/q0))),
HSTACK(vol, mix, pri)
)
This spills three cells — volume, mix, price. The LET names make each step reviewable, and SUMPRODUCT with the comp flag array handles the comparable filter without helper columns. See our guides on the LET function for readable formulas and SUMPRODUCT for multi-criteria analysis if either pattern is new.
Which PVM Method Should You Use?
Use the current-quantity price convention for anything that goes to a board or lender, because it reconciles by construction. Reserve the residual method for quick directional reads where an unexplained plug is acceptable, and use the four-factor extension when unit costs matter as much as revenue.
| Method | Price basis | Mix basis | Ties out exactly? | Drills to SKU? | Best for |
|---|---|---|---|---|---|
| Current-qty (recommended) | q₁ × Δp | q₁ × (p₀ − P̄₀) | Yes, by identity | Yes | Board decks, lender reporting |
| Base-qty naive | q₀ × Δp | q₁ × (p₀ − P̄₀) | No — leaves cross-term | Yes | Nothing; avoid |
| Mix as residual | q₀ × Δp | Total variance − price − volume | Yes, but mix is a plug | No | Quick directional reads |
| Mid-point (average price) | q̄ × Δp | q̄-weighted | Yes | Yes | Symmetric period comparisons |
| Four-factor (with cost) | q₁ × Δp | q₁ × (p₀ − P̄₀) | Yes | Yes | Gross margin bridges |
When the Mid-Point Method Is Better
The current-quantity convention is not symmetric: run PY→CY and then CY→PY and the price effects won't be mirror images, because each uses a different quantity basis. If you regularly present the bridge in both directions — or compare across restated periods — use the mid-point convention, where price uses the average of both quantities. It sacrifices a little interpretability for symmetry.
ℹ️ Note: Whichever you pick, write the convention into a footnote on the chart itself. Two analysts using different conventions on the same data will produce different price effects, and the resulting meeting is unrecoverable.
How Do You Extend PVM to Gross Margin?
Run the identical three formulas a second time on unit cost instead of unit price, then subtract. Price effect minus cost effect gives you the rate variance on margin; the volume and mix effects on margin use contribution per unit in place of price per unit.
Concretely, define m₀ᵢ = p₀ᵢ − c₀ᵢ (base-period unit margin) and substitute it wherever p₀ᵢ appeared, with M̄₀ as the base-period average unit margin across the comparable set:
Volume Marginᵢ = (q₁ᵢ − q₀ᵢ) × M̄₀
Mix Marginᵢ = q₁ᵢ × (m₀ᵢ − M̄₀)
Price Marginᵢ = q₁ᵢ × (p₁ᵢ − p₀ᵢ)
Cost Marginᵢ = −q₁ᵢ × (c₁ᵢ − c₀ᵢ)
These four sum to the gross margin variance across the comparable set — same identity, same proof. The mix effect on margin is frequently the opposite sign from the mix effect on revenue: shifting toward a high-ASP, low-margin SKU adds revenue mix and subtracts margin mix. That divergence is often the most valuable single insight in the entire deck.
From there, the natural next step is layering cost inflation, productivity, and FX onto the same structure — which is exactly what an EBITDA bridge in Excel does.
How Should You Choose the Decomposition Structure?
graph TD
A[Revenue variance to explain] --> B{Do you have<br/>unit-level data?}
B -->|No| C[Report Net Revenue<br/>only - PVM impossible]
B -->|Yes| D{Any SKUs added<br/>or dropped?}
D -->|Yes| E[Split comparable /<br/>new / discontinued first]
D -->|No| F[Use full set as<br/>comparable]
E --> G{Multi-currency?}
F --> G
G -->|Yes| H[Add FX bar at<br/>constant rates]
G -->|No| I[3-factor PVM]
H --> I
I --> J{Margin in scope?}
J -->|Yes| K[Add cost effect -> 4-factor]
J -->|No| L[Publish revenue bridge]
Handling FX Before You Handle Mix
If your SKUs sell in multiple currencies, translate current-period revenue at base-period rates first, run PVM on the constant-currency figures, then add a separate FX bar for the difference. Running PVM on reported figures mixes a translation effect into your price effect and makes local-currency pricing decisions unreadable. The mechanics of the constant-currency restatement are covered in our multi-currency model guide.
Scaling Past a Few Hundred SKUs
At real-world granularity — 5,000 SKUs across 40 countries — helper columns get slow and fragile. Two better paths:
- Power Query to reshape and join the two periods into a single tidy table, then a small set of added columns for the effects. Our Power Query for financial reporting guide covers the merge pattern.
GROUPBYto aggregate the raw transaction table to SKU level dynamically before the PVM math runs, so the analysis re-scopes when you change the filter. See GROUPBY and PIVOTBY.
Charting It
Select the bridge table from Step 4, then Insert → Charts → Waterfall. Right-click the PY Revenue and CY Revenue points and choose Set as Total so they anchor to the axis instead of floating. Label every bar with its value — an unlabeled waterfall invites the audience to estimate, and they will estimate wrong. Full formatting mechanics are in our waterfall chart guide.
Frequently Asked Questions
What is the mix effect in price volume mix analysis?
The mix effect measures how much revenue changed because the composition of units sold shifted between products at different price points, holding total units and all prices constant. It is calculated as current-period quantity multiplied by the gap between that SKU's base price and the portfolio's base-period average price, summed across all comparable SKUs.
Why is my mix effect so large compared to price and volume?
Large mix effects are normal when SKU prices are widely dispersed. In the example above, prices span $50 to $600 — a shift of a few hundred units toward the $600 SKU swamps a $2 price increase on the $50 SKU. Check the dispersion before assuming an error: if your highest and lowest ASPs differ by 10x, mix will usually dominate.
How do you handle new products in a PVM analysis?
New products get their own bridge bar equal to their full current-period revenue, and they are excluded from the comparable set used to compute price, volume, mix, and the base average price. They have no base-period price, so any attempt to assign them a price or mix effect is meaningless. Discontinued products are handled symmetrically as a negative bar.
Should price effect use current or prior period volume?
Use current-period volume. It absorbs the quantity-price cross-term automatically, so the three effects reconcile to the total variance by algebraic identity rather than by luck. Prior-period volume leaves Σ Δq × Δp unassigned — a gap that is zero only by coincidence and reappears without warning.
Can you do price volume mix analysis without unit data?
No. PVM requires a quantity denominator to separate price from volume; with revenue alone you can only report a single net revenue variance. If unit data isn't available at SKU level, a workable interim proxy is customer count or contract count for subscription businesses, treating ARPU as the price term.
Wrapping Up
A PVM bridge is worth building only if it reconciles, and reconciling is a matter of picking the right convention rather than chasing rounding. Compute price on current-period quantity, measure volume and mix against a comparable-set average, bridge new and discontinued products as their own bars, and put the tie-out check where a reviewer can see it.
If you're rebuilding this every month across segments and currencies, Dezzmond can generate the effect formulas from a description of your data layout and flag the reconciliation break before it reaches the deck. Next time you present a revenue bridge, run the margin version alongside it — when revenue mix and margin mix point in opposite directions, that contradiction is usually the most important thing on the slide.