Tools
Price volume mix analysis: split a revenue movement by driver
Paste units and revenue by product for two periods. It splits the revenue movement into volume, mix, price, new products and lost products — and the pieces add up to the movement, with any rounding shown on its own line.
"Revenue up $11,150" is a restatement. "More units, mostly of the cheapest product, partly offset by a mix shift, plus a new product that more than replaced one that stopped selling" is an explanation. This does the arithmetic that gets you from the first to the second.
Runs entirely in your browser. Nothing is uploaded, nothing is stored, nothing leaves your machine.
Units and revenue by product
One row per product, five columns, tab or comma separated: product, prior units, prior revenue, current units, current revenue. Use 0 where a product didn't sell in a period. A text header row is skipped. Copy from Excel or Sheets as it is (that pastes tab-separated), and leave out any total row. Numbers need US formatting (1,000.50); comma-separated rows work only if the numbers have no thousands commas or are in quotes.
Mix only means something when the units are the same kind of thing — cases with cases, hours with hours. Group at the level where that's true.
The GL movement is current minus prior revenue from the ledger. By default an increase is positive. If you pull it with ledger signs (revenue credits negative, as the commentary test does), switch the sign setting and enter it that way; the driver lines you copy follow the same setting. If you enter it, the tool shows how much of it the product data doesn't account for.
How each piece is calculated
Every product is first sorted into one of three groups. Continuing products sold in both periods. New products sold only in the current period. Lost products sold only in the prior period. Price is always revenue divided by units, per product, per period.
Volume, mix and price are calculated on continuing products only:
| Effect | Calculation | What it answers |
|---|---|---|
| Volume | Change in total units × prior average price | How much revenue moved because more or fewer units sold in total. |
| Mix | For each product: change in its share of units × current total units × (its prior price − the prior average price). Summed. | How much moved because the units shifted toward higher- or lower-priced products. |
| Price | For each product: change in price × current units. Summed. | How much moved because the same products sold at different prices. |
| New products | Current revenue of products with no prior units | Kept separate rather than forced into price or volume — there's no prior price to compare to. |
| Lost products | Prior revenue of products with no current units, as a decrease | The same, the other way. |
Volume plus mix comes to the revenue the continuing products would have earned at last period's prices, minus what they actually earned last period. Price is the rest. So before rounding, the five effects add up to the movement exactly — the tool checks that on every run and won't show results if it doesn't hold. Amounts are shown in whole dollars, and any rounding difference appears as its own line.
One convention worth knowing. When price and volume change together, part of the movement belongs to both at once. Price here is measured on current units, so that overlap sits in the price effect. Other conventions put it in volume or show it as its own line. None of them is wrong; they answer slightly different questions, and the total doesn't change.
Rows that don't get split
Some rows can't honestly be split into price and volume, so they're shown on their own line, inside the group they belong to, with the amount still counted in the total:
- Revenue without units — revenue in a period with zero units, like a credit memo or rebate booked against a product. There's no price to calculate.
- Negative rows — negative units or negative revenue, usually returns that exceeded sales. The formulas would run, but the price they imply means nothing, so it isn't presented as a price effect.
Units sold with zero revenue (free goods) get a price of $0.00, with a note. On a continuing product that flows into price and mix; on a new or lost product it adds nothing to revenue.
Product-level mix is measured against the prior average price: a positive figure means units moved toward that product while it was priced above average, or away from it while it was priced below average. The total is the same either way.
The GL tie-out
Product-level data and the ledger rarely agree exactly. Header-level discounts, rebates, returns reserves, manual adjustments and cutoff differences all hit revenue without touching any product. If you enter the GL movement, the gap shows up as a dollar amount that the product data doesn't explain.
It's a dollar amount on purpose, not a percentage — the same reasoning as the residual in flux commentary. Whether it matters is a call against your own threshold. When you copy the driver lines into the commentary test, the account movement is the GL figure, so the gap shows up there as the unexplained residual, which is where it belongs.
What it doesn't do
It quantifies; it doesn't explain. It can tell you that $19,143 of revenue went to mix. Why the units shifted toward the cheaper product — a promotion, a lost customer, a stockout on the other one — is in the business, not in the numbers.
It compares two periods of actuals. It covers revenue only, not gross margin or cost variances. It isn't set up for budget against actual, and it doesn't separate currency effects. It also doesn't pick your product grouping, and grouping changes the mix number: split one product into three sizes and some of what was price becomes mix.
Want this as a workbook you keep? The free flux template is the single-sheet version. The CloseOps Flux & Variance System ($79) starts from your trial balance instead: confirm each account's classification and it builds the income statement and balance sheet flux statements, then ranks what to investigate.
Related: income statement flux analysis, where volume, rate and mix come from; the commentary test, to grade the driver lines once they're written; and flux analysis vs variance analysis.
Found something wrong or missing? Tell me here — anonymous, thirty seconds.