Prompt: a price, volume, mix and unit-cost margin bridge computed in code, with an AI assistant to explain it and flag bad lines

In two sentences

“Margin is up, but the margin percentage is down” is one of the most common sentences in a management meeting, and one of the hardest to explain without a bridge. This entry gives you the formulas, a script that applies them to invoice lines and proves that the effects add up, and a prompt that asks an AI assistant to explain the result instead of calculating it.

The four effects, defined

For each product i and two periods (0 = comparison, 1 = current), take units Q, average net price P (net sales divided by units) and average unit cost C. The unit margin is m = P − C, total units are Q_T and the mix share of a product in period 0 is s_0,i = Q_0,i / Q_T0.

• Volume: (Q_T1 − Q_T0) × Σ s_0,i × m_0,i. More units, at the old mix and the old margins.
• Mix: Σ (Q_1,i − Q_T1 × s_0,i) × m_0,i. The same total units, but with more weight on some products.
• Price: Σ Q_1,i × (P_1,i − P_0,i). What the change in net price brought on today’s units.
• Unit cost: −Σ Q_1,i × (C_1,i − C_0,i). What the change in unit cost took away on today’s units.

With these definitions the four effects add up exactly to the change in gross margin, with no residual. There are other valid conventions (for example, valuing price at old volumes), but pick one, write it down and keep it every quarter; changing the convention changes the story.

The fictitious data

The sample is a distributor with three products and ten customers, comparing the third quarter of 2025 with the third quarter of 2026: 278 invoice lines with units, list price, discount, net sales and cost. List prices rose 4% and purchase costs about 6%; the sales mix moved towards the most expensive product. We planted three suspicious lines in 2026.

• Invoice lines, two quarters (CSV)
• Bridge and data-check script (Python, standard library only)

Run python3 pvm.py sales-lines.en.csv. The bridge part of the script is short:

volume = (q1t - q0t) * sum(share0[p] * m0[p] for p in prods)
mix = sum((Q[P1][p] - q1t * share0[p]) * m0[p] for p in prods)
price_eff = sum(Q[P1][p] * (price[P1][p] - price[P0][p]) for p in prods)
cost_eff = -sum(Q[P1][p] * (ucost[P1][p] - ucost[P0][p]) for p in prods)
assert abs((volume + mix + price_eff + cost_eff) - (M1 - M0)) < 0.01, "bridge does not tie"

Before the bridge, the script checks each line and flags, without fixing: units equal to zero with sales, cost above net sales, and discounts of 25% or more. Flagged lines are left out of the bridge and reported with their totals so that you can reconcile them separately.

In a spreadsheet

With one row per product and columns B to G for Q0, Q1, P0, P1, C0 and C1 (rows 2 to 4), the four effects are:

Share 0 (H)      =B2/SUM($B$2:$B$4)
Unit margin 0 (I)=D2-F2
Volume           =(SUM(C2:C4)-SUM(B2:B4))*SUMPRODUCT(H2:H4,I2:I4)
Mix              =SUMPRODUCT(C2:C4-SUM(C2:C4)*H2:H4,I2:I4)
Price            =SUMPRODUCT(C2:C4,E2:E4-D2:D4)
Unit cost        =-SUMPRODUCT(C2:C4,G2:G4-F2:F4)

Add a cell that subtracts the four effects from the change in margin: it must read zero. Python in Excel, which Microsoft documents as a way to run Python inside a workbook, is an alternative if the invoice lines do not fit comfortably in formulas.

What the script returns

Gross margin goes from 340,628 (39.7%) to 397,768 (37.2%) on the clean lines.

Three lines are excluded, with 18,336 of net sales and 17,934 of cost: one P-200 sale with a 38% discount that is also below cost, one P-300 sale below cost and one line with 1,250 of sales and zero units.

How to use the prompt

Paste the script’s output as BRIDGE and ISSUES, set DISCOUNT_LIMIT to the same threshold as the script, and add CONTEXT. In the example: “list prices +4% from January; two suppliers raised prices 5% to 7%; push on P-200 with a new distributor in the north”.

What a good answer should contain

Written by us as a reference, not model output:

• The % paradox. Margin in euros rose because more units of P-200 were sold, and P-200 earns more euros per unit than the others. But P-200 has the lowest margin percentage (about a third, against about 38% for P-100 and about 56% for P-300), so the average percentage falls. Both statements are true.
• Price against cost. Price added 39,018 and unit cost took away 38,005: the price increase almost exactly covered the cost increase in euros. Product by product, P-200’s cost rose more than its net price (+3.33 against +2.72 per unit), so it is the product where pricing did not keep up.
• Issues. The 38% discount may be a special deal for the new distributor; confirm it with sales before calling it an error. The zero-unit line could be a service charge or a price adjustment invoiced without quantity; ask billing.
• Actions. Review P-200’s price against its new cost; set an approval rule for discounts above 25%; measure next quarter the net price of P-200 and the number of lines excluded.

Variants by ERP: where the invoice lines are

The script needs one row per invoice line (or per product and customer) with units, net sales and cost of sales:

• Odoo: Accounting › Reporting › Invoice Analysis, grouped by product and customer, with quantity, untaxed amount and cost if product costs are kept up to date.
• Microsoft Dynamics 365 Business Central: value entries of type sale, which carry sales amount and cost amount per item entry, opened in Excel.
• NetSuite: a saved search on sales transactions with item, quantity, amount and estimated cost or gross profit.
• SAP S/4HANA: margin analysis line items, which already split revenue and cost of goods sold by product and customer.
• Sage and A3: the sales statistics or invoice-line listing by item and customer, exported to Excel, joined with the item cost if the listing does not include it.

Check two things before trusting the cost: whether it is the actual cost at the date of sale or today’s standard cost, and whether credit notes are included as negative lines.

Human review steps

1. Reconcile net sales and cost of the clean lines plus the excluded ones with the P&L for each period.
2. Decide what to do with each excluded line (include, correct in the ERP, or leave out) and write it down.
3. Check that the bridge ties (the script stops if it does not).
4. Read the assistant’s explanation against the table: every number it uses must appear there.
5. Agree the actions with sales and purchasing before they appear in a report.

Common mistakes

• Mixing gross and net prices. Use net sales after discounts; list price with a separate discount column hides where the margin went.
• Standard cost in one period and actual in the other. The unit-cost effect then measures a change of method, not of cost.
• New products. A product that did not exist in period 0 has no old margin. Treat it on a separate line (“new products”) instead of forcing it into mix.
• Changing the order of the effects. Valuing price at old volumes or mix at new margins gives different splits; keep one convention.
• Silently dropping bad lines. Excluded lines must be reported and reconciled, otherwise the bridge no longer matches the P&L.
• Letting the assistant compute the bridge. It will produce effects that do not add up, or add up thanks to an invented residual.

Limits

The bridge explains gross margin at product level. It does not allocate overheads, logistics or rebates that are not on the invoice, and it does not tell you which customers are profitable after service costs. Customer and product names are commercial information: anonymise them or use an approved business plan before pasting them into an AI service.