Cellwise Start free

Excel guide · 5 min read

How to do a price volume mix analysis in Excel

Quick answerPer product: price effect = (new price - old price) x new units; volume effect = (new total units x old share of units - old units) x old price; mix effect = (new units - new total units x old share) x old price. The three add up exactly to new revenue - old revenue. Put new products in their own column and check that price + volume + mix + new equals the total change.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1ProductUnits 2025Price 2025Units 2026Price 2026Revenue 2025Revenue 2026Price effectVolume effectMix effectNew products
2House blend420012450012.55040056250225033632640
3Single origin1800162300162880036800019278080
4Decaf9001375013.5117001012537578-20280
5Gift box6003502100000140-211400
6Cold brew cans012004.5054000005400
7Total750087501119001085752625746-120965400
8CheckChange-3325Explained-3325Difference0

Download this sheet as an Excel practice file (.xlsx)

The setup: two periods, units and price per product

A small coffee roaster sold more bags in 2026 than in 2025 (8,750 vs 7,500) and raised some prices, yet revenue fell from 111,900 to 108,575, down 3,325. Price-volume-mix (PVM) analysis explains a change like that by splitting it into three causes: prices changed, the number of units changed, and the mix shifted toward cheaper or dearer products.

Each row holds one product with its units and average selling price for both years (B:E). Revenue is units x price (F and G). Two products didn't sell in both years: the gift box was discontinued, and cold brew cans are new, so their missing price is left blank. Those two need special handling, covered below.

If your data is a sales log, build this table first with SUMIFS for units and revenue per product and year, then work out price as revenue / units. Use the same price definition in both years (net of discounts, or not) or the price effect picks up the difference.

Price effect

The price effect is what the price change earned on the units actually sold this year: (new price - old price) x new units. House blend went from 12.00 to 12.50 on 4,500 bags, which is 2,250 more revenue from price alone.

Products that didn't sell in both years have no price change to measure, so the IF returns 0 for them. Without it, the new cold brew would show its whole 5,400 of revenue as a "price increase" from a blank old price of 0.

=IF(OR(B2=0,D2=0),0,(E2-C2)*D2)

Result: 2250

2,250: house blend's 0.50 increase on 4,500 bags. Price effects across all products total 2,625.

Volume and mix effects

Volume asks: if total units had changed but every product kept its 2025 share of units, what would revenue have done at 2025 prices? Mix is everything else in the unit change: products gaining or losing share.

First get the totals. N1 holds 2025 units (7,500). N2 holds like-for-like 2026 units: units of products that also sold in 2025, =SUMIFS(D2:D6,B2:B6,">0"), which is 7,550. New products are left out of N2 so they don't inflate the volume of existing ones; they get their own column.

House blend had 56% of 2025 units (4,200 / 7,500), so at the old mix it would have sold 7,550 x 56% = 4,228 bags. Volume effect: (4,228 - 4,200) x 12 = 336. It actually sold 4,500, so mix effect: (4,500 - 4,228) x 12 = 3,264.

=(D2-$N$2*B2/$N$1)*C2

Result: 3264

3,264: house blend gained share, selling 272 bags more than its 2025 share of 7,550 would give, valued at the 2025 price of 12.

What's the volume effect formula?

Units at the old mix minus old units, times the old price. Summed over all products it's small here (746) because like-for-like units barely grew: the gift box going to zero cancelled most of the growth elsewhere.

=($N$2*B2/$N$1-B2)*C2

Result: 336

336: house blend's share of the 50-unit like-for-like growth, at 12 a bag.

New and discontinued products

A new product has no 2025 units or price, so it can't have a price, volume or mix effect in the usual sense. K shows its 2026 revenue as "New products" with =IF(B6=0,G6,0): 5,400 for cold brew.

A discontinued product needs no special formula. Its new units are 0, so its price effect is 0, and its volume and mix effects add up to minus its whole 2025 revenue: 140 - 21,140 = -21,000 for the gift box. That one line explains most of the -12,096 mix effect. If you'd rather show lost products in their own column like new ones, use =IF(D5=0,-F5,0) and set their volume and mix to 0.

=IF(B6=0,G6,0)

Result: 5400

5,400: all of cold brew's 2026 revenue, shown separately because it had no 2025 sales to compare with.

The check cell: the effects must add up exactly

Price + volume + mix + new products must equal the total change, to the cent. I8 adds the four totals and K8 subtracts them from the change in G8. If K8 isn't 0, a formula is pointing at the wrong row, an absolute reference is missing a $, or a product is in the table for one year only without being handled.

Here the story reads: prices added 2,625, volume added 746, new cold brew added 5,400, and mix took away 12,096, mostly because the 35 gift box disappeared and cheaper products grew. Net: -3,325.

=ROUND(G8-I8,2)

Result: 0

0: the four effects (2,625 + 746 - 12,096 + 5,400 = -3,325) explain the whole revenue change.

Why not just compare average prices?

Average revenue per unit fell from 14.92 to 12.41. Multiply that drop by 2026 units and you'd report a -21,975 "price effect", when in fact no product was cut in price. The average fell because the mix moved toward cheaper items. Splitting by product is the only way to see that.

For margin instead of revenue, use the same formulas with unit margin (price - variable cost) in place of price; see contribution margin by product. To report each effect against a budget instead of last year, put the budget units and prices in B:C. For simple actual-vs-plan gaps, see how to calculate variance.

=(G7/D7-F7/B7)*D7

Result: -21975

-21,975: the misleading "price effect" from average prices. It mixes the shift to cheaper products in with real price changes.

Common mistakes

Letting new products count as a price increase

Wrap the price effect in IF(OR(old units=0,new units=0),0,...) and show new products' revenue in their own column.

Including new products in the total used for volume

Use like-for-like units (SUMIFS on old units > 0) for the old-mix calculation, or new products inflate every existing product's volume effect.

Mixing price and units from different periods

Price effect uses new units; volume and mix use old prices. Swap them and the parts no longer add up to the change.

No check cell

Always subtract the sum of the effects from the total change. Anything other than 0 means a broken formula.

FAQ

What is price volume mix analysis?

It splits the change in revenue (or margin) between two periods into the part caused by price changes, the part caused by selling more or fewer units, and the part caused by a shift between products with different prices.

Do price, volume and mix always add up to the total change?

Yes, if price is measured on new units and volume and mix at old prices, the effects add up exactly. A check cell that should be 0 proves it on your data.

How do I handle a discontinued product in PVM?

Its new units are 0, so its price effect is 0 and its volume and mix effects add up to minus its old revenue. You can also show lost revenue in its own column, as with new products.

Can I do price volume mix on gross margin?

Yes. Use margin per unit instead of price per unit in the same formulas. The effects then explain the change in total margin.

Functions used

Related guides

Updated