Why gross margin changed: building a price, volume and mix bridge that holds up.

The question the P&L cannot answer

A monthly income statement tells you that gross margin fell from 39.2% to 36.4%. It does not tell you what to do on Monday morning. Four completely different things can produce the same two-point drop:

• Price realisation: sales teams gave larger discounts to close the quarter.
• Unit cost: supplier prices, freight or production scrap rose on the same goods.
• Volume: overall unit volume changed across the business.
• Mix: customers bought more of a lower-margin product line or through a lower-margin channel, even though not a single price list or supplier cost changed.

Each cause calls for a different decision. Repricing a product line when the real problem is a shift toward wholesale orders will lose customers without fixing the margin.

Four drivers, one worked example

Consider a synthetic two-line distributor across two quarters. Total revenue grew 4.1% from €1,000,000 to €1,041,000, while gross profit fell from €380,000 (38.00%) to €364,200 (34.99%)—a drop of €15,800 and 3.01 percentage points. Here are the underlying subledger inputs by product line:

Synthetic subledger input table. Unit margin m = p − c; portfolio average margin in Q2 is m̄₀ = €380,000 ÷ 10,000 = €38.00 per unit.

Decomposing the €15,800 variance on a prior-period margin base for volume and mix, and actual shipped quantities for price and unit cost, yields four additive steps with zero residual:

Reconciliation check: €380,000 + €30,400 − €19,200 − €15,000 − €12,000 = €364,200 (residual = €0). Margin points: 38.00% − 0.95 − 0.91 − 1.15 = 34.99%.

Looking only at the P&L, leadership sees a three-point margin drop and suspects across-the-board discounting. Looking at the bridge, selling 800 additional units of Line B added €30.4k of volume profit less €19.2k of mix dilution (a net +€11.2k contribution), while the two operational leaks to fix are a €2.50 rebate slippage on Line A (−€15.0k) and a €2.50 freight increase on Line B (−€12.0k).

Five reasons spreadsheet bridges fail

Most finance teams have tried to build a price-volume-mix bridge in Excel. Usually it breaks within two quarters for one of five reasons:

1. Starting from general-ledger totals. The general ledger records euros, not quantities or SKUs. A real bridge has to start at the ERP invoice-line level, where units, net price and unit cost sit on the same row, and then roll up to the ledger.
2. The interaction residual. When both price and volume change between periods, multiplying the old volume by the price change and the old price by the volume change leaves a cross-term (ΔP × ΔQ). Naive spreadsheets dump this into an “Other / unexplained” column that is sometimes larger than the price effect itself. Splitting the interaction evenly across price and volume—or using midpoint weights—eliminates the residual cleanly.
3. New and discontinued SKUs. A product launched in Q3 has no Q2 price (p₀ = 0), which distorts like-for-like price and mix formulas. New, discontinued and one-off custom items should be separated into their own bridge bucket before calculating like-for-like price, cost and mix on continuing SKUs.
4. Unit-of-measure drift. If an item is billed in boxes on one invoice and pallets on another, apparent “price” swings by a factor of 24 and ruins the bridge. Quantities must be normalised to a single base unit per SKU in the staging layer.
5. Off-invoice credit notes and rebates. If credit notes, retrospective rebates or freight charges are booked as lump sums at month-end rather than allocated back to the product lines that caused them, SKU-level prices look healthy while ledger margin falls.

Define realised unit margin once

Before writing any SQL, finance and commercial leads need to sign off on what belongs in net price and unit cost at the line level.

Put the bridge in the warehouse

Once the grain and rules are agreed, the bridge is a single window-function query in the warehouse transformation layer. Running the CTE below on the synthetic inputs above reproduces the exact +€30,400 volume, −€19,200 mix, −€15,000 price and −€12,000 cost effects:

WITH with_totals AS (
  SELECT
    sku, q0, p0, c0, q1, p1, c1,
    (p0 - c0) AS m0,
    SUM(q0) OVER () AS total_q0,
    SUM(q1) OVER () AS total_q1,
    SUM(q0 * (p0 - c0)) OVER () / NULLIF(SUM(q0) OVER (), 0) AS portfolio_m0
  FROM mart_continuing_sku_periods
)
SELECT
  sku,
  ROUND((total_q1 - total_q0) * (q0 / total_q0) * portfolio_m0, 2) AS volume_eur,
  ROUND((q1 - total_q1 * (q0 / total_q0)) * m0, 2)                 AS mix_eur,
  ROUND(q1 * (p1 - p0), 2)                                         AS price_eur,
  ROUND(-q1 * (c1 - c0), 2)                                        AS cost_eur
FROM with_totals;

Instead of spending four days after month-end assembling a spreadsheet, the finance team opens the review meeting knowing which half-point of margin was mix, which was freight, and which three customer accounts drove the price variance.