Build a Price-Volume-Mix Analysis

"Revenue is up 20% on last year" invites the obvious follow-up: because we sold more, because we charged more, or because we sold more of the expensive products? A price-volume-mix analysis answers it by splitting the change in revenue into three effects that add up exactly to the total. The volume effect is what the change would have been if only the number of units had changed, at last year's average price. The mix effect is what moving sales towards more or less expensive products contributed. The price effect is what charging different prices for the same products contributed. Finance teams present the result as a bridge from last year's revenue to this year's. This page builds the analysis from two periods of sales lines with pandas, handles products that are new this year or no longer sold, proves the effects reconcile to the penny, breaks each effect down by product and writes an Excel report. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The input is a year of sales lines for 2025 and 2026: product, quantity and revenue per line. Four products sold in 2025; in 2026 one of them, Legacy, was discontinued and a new one, Eco, was launched — the situation that most simple PVM templates handle badly.

Step 1 — Quantity, revenue and price per product and period

import pandas as pd

lines = pd.read_excel("sales_lines.xlsx")
pv = lines.pivot_table(index="Product", columns="Year", values=["Qty", "Revenue"], aggfunc="sum", fill_value=0)
Q0, Q1 = pv["Qty"][2025], pv["Qty"][2026]
R0, R1 = pv["Revenue"][2025], pv["Revenue"][2026]
P0, P1 = (R0 / Q0).where(Q0 > 0), (R1 / Q1).where(Q1 > 0)
summary = pd.DataFrame({"Qty 2025": Q0, "Qty 2026": Q1, "Price 2025": P0.round(2), "Price 2026": P1.round(2),
                        "Revenue 2025": R0, "Revenue 2026": R1})
print(summary.to_string())
print(f"revenue {R0.sum():,.2f} -> {R1.sum():,.2f} ({R1.sum() - R0.sum():+,.2f}, {R1.sum() / R0.sum() - 1:+.1%})")

Price per product is revenue divided by quantity for the period — the average price actually achieved, which already includes discounts, so the price effect will capture discounting as well as list-price changes. A product with no sales in a period has no price, and where(Q > 0) leaves it missing rather than dividing by zero. Revenue grew from 191,907.68 to 229,940.46, up 38,032.78 or almost 20%; the question is what that is made of.

Step 2 — The three effects for continuing products

Separate the products sold in both years from those in only one, and apply the standard formulas to the continuing ones:

both = Q0.gt(0) & Q1.gt(0)
q0, q1, p0, p1 = Q0[both], Q1[both], P0[both], P1[both]
avg_p0 = (q0 * p0).sum() / q0.sum()                         # last year's average price per unit
share0, share1 = q0 / q0.sum(), q1 / q1.sum()

volume = (q1.sum() - q0.sum()) * avg_p0
mix = (q1.sum() * (share1 - share0) * p0)
price = (p1 - p0) * q1
new = R1[Q0.eq(0) & Q1.gt(0)].sum()
lost = -R0[Q0.gt(0) & Q1.eq(0)].sum()

bridge = pd.Series({"Revenue 2025": R0.sum(), "Volume": volume, "Mix": mix.sum(), "Price": price.sum(),
                    "New products": new, "Discontinued": lost, "Revenue 2026": R1.sum()})
print(bridge.round(2).to_string())
explained = bridge["Revenue 2025"] + bridge[["Volume", "Mix", "Price", "New products", "Discontinued"]].sum()
print(f"reconciles: {abs(explained - R1.sum()) < 0.005} (difference {explained - R1.sum():.6f})")

The volume effect values the change in total units of continuing products at last year's average price per unit: if nothing but the number of units had changed, this is what revenue would have moved by. The mix effect values each product's change in share of units at its own prior price — selling a larger share of Pro and a smaller share of Standard raises revenue even at the same total volume and the same prices. The price effect is the change in each product's price times this year's quantity. For continuing products these three add up exactly to the change in their revenue, which is the property that makes PVM trustworthy; Eco and Legacy cannot be split, because one has no prior price and the other no current quantity, so they appear as their own lines. The final check proves the whole bridge reconciles. Of the 38,033 increase, 25,058 came from selling more units, 12,594 from a richer mix, 5,341 from higher realised prices and 6,130 from the new Eco range, offset by 11,090 of Legacy sales that disappeared.

Step 3 — Which products drove each effect

by_product = pd.DataFrame({"Volume share": volume * share0, "Mix": mix, "Price": price},
                          index=Q0.index).fillna(0)                   # volume spread by prior unit share, for display
by_product["New / discontinued"] = pd.Series({"Eco": new, "Legacy": lost}).reindex(by_product.index, fill_value=0)
by_product["Total"] = by_product.sum(axis=1)
by_product = by_product.round(2)
print(by_product.to_string())

The product view shows where each effect came from. Its Total column adds up to the overall change, but an individual product's total is not that product's own change in revenue — Plus shows 21,823 against a revenue increase of 23,457 — because the volume figure is shared out by prior share for display rather than measured product by product. Mix is positive for products whose share of units rose and that are priced above average — Plus and Pro here — and negative for Standard, whose share fell. The price effect shows which products' realised prices moved: Standard and Plus up, Pro slightly down, perhaps through discounting to win volume. Volume is a single figure in the method above; the product column spreads it by each product's prior share purely so the table sums, and is labelled accordingly. Analysing margins rather than revenue uses the same structure with unit margin in place of unit price, as in Build a Product Margin Report.

Step 4 — The bridge in Excel

from openpyxl import load_workbook
from openpyxl.styles import Font

path = "pvm_2026_vs_2025.xlsx"
table = bridge.round(2).rename("Amount").to_frame()
with pd.ExcelWriter(path, engine="openpyxl") as xw:
    table.to_excel(xw, sheet_name="Bridge", index_label="Step")
    by_product.to_excel(xw, sheet_name="By product")
    summary.to_excel(xw, sheet_name="Price and quantity")
wb = load_workbook(path)
ws = wb["Bridge"]
n = ws.max_row
ws.cell(n + 2, 1, "Check: 2025 + effects - 2026").font = Font(italic=True)
ws.cell(n + 2, 2, f"=B2+SUM(B3:B{n - 1})-B{n}")
for r in range(2, n + 3):
    ws.cell(r, 2).number_format = "#,##0.00;[Red]-#,##0.00;-"
for r in (2, n):
    ws.cell(r, 1).font = ws.cell(r, 2).font = Font(bold=True)
ws.column_dimensions["A"].width = 30
wb.save(path)
print(table.to_string())

The Bridge sheet runs from 2025 revenue through each effect to 2026 revenue, with a check formula that should show a dash — zero — beneath it; the other sheets hold the product detail and the underlying prices and quantities, so every number can be traced. Drawn as a waterfall chart, the bridge is the standard way to present PVM in a board pack; Build a Waterfall Chart in Excel with Python shows how to build one.

Choices that change the numbers

PVM looks objective, but several choices move amounts between the effects, and readers should know which were made. Measuring price at current quantities and mix at prior prices, as here, is one convention; using prior quantities for price leaves an extra price × volume interaction term that some teams show separately. The level at which mix is measured matters too: mix between product families and mix between individual SKUs give different answers, and the finer the level, the more of the change is attributed to mix. Units must be comparable across products for the volume and mix effects to mean anything — pieces of very different products, or units versus kilograms, are better analysed within families. Whatever the choices, keep them fixed from period to period and state them on the report, so a change in the effects reflects the business rather than the method.

Common pitfalls

• Effects that do not add up. Use formulas that reconcile exactly, and check them.
• Forcing new and discontinued products into price or mix. Show them as their own lines.
• List prices instead of realised prices. Use revenue ÷ quantity so discounts count.
• Mixing incomparable units. Analyse within families when units differ.
• Changing conventions between periods. State the method and keep it.

Conclusion

A price-volume-mix analysis splits the change in revenue into volume — the change in total units at last year's average price — mix — shifts in product share at prior prices — and price — changes in realised price on current quantities — with new and discontinued products shown separately. Compute average prices from revenue and quantity, separate continuing products, apply the three formulas, prove the bridge reconciles, break the effects down by product and write the bridge with its check to Excel.

Frequently asked questions

How do I calculate price, volume and mix effects in pandas? Aggregate quantity and revenue by product for both periods, compute average prices, then volume = change in total units × prior average price, mix = Σ current total units × change in share × prior price, and price = Σ change in price × current quantity.

Why do my price, volume and mix effects not add up to the revenue change? The formulas must use consistent quantities and prices; with prior average price for volume, prior prices for mix and current quantities for price, the three add up exactly for continuing products.

How do I handle new and discontinued products in a PVM analysis? Show their revenue as separate new-product and discontinued-product lines, because they have no price in one of the periods.

Can PVM be done on margin instead of revenue? Yes. Use unit margin instead of unit price in the same formulas to explain the change in gross margin.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Budget vs Actual Variance Report — variance against plan.
• Compare Two Periods Side by Side in a Pivot — the period comparison.
• Build a Customer Profitability Report — profit by customer.