Blog · Board and management reporting · Finance and FP&A teams
Use a reusable input and bridge layout to explain product-level revenue changes. The tables include filled example rows, formulas and checks that reconcile price, volume and mix to reported revenue.
A price volume mix analysis template should reconcile the revenue movement at both product and company level. Copy the input table and formula columns below into a workbook, replace the example rows, and keep the residual checks visible. This is a reusable on-page layout, with no separate download.
Record the baseline period, comparison period, currency, unit definition and row level above the input table. Add a short method note: price is valued at comparison-period units; volume and mix are valued at baseline prices. That note prevents someone from comparing effects produced under a different allocation convention.
Use net revenue after the deductions included in the sales measure, with physical quantities from the same transactions and cutoff. For the full derivation and the choices behind the method, use the existing price volume mix formula tutorial. This template concentrates on the fields and controls needed to reuse it each reporting cycle.
Start with comparable products sold in both periods. Keep new, discontinued and unmatched records in a review section rather than supplying made-up baseline prices. Decide how to handle returns before aggregating: a credit with no quantity can change realized price, while a physical return changes both revenue and units.
The example is a fictional US consumer-products company comparing two equal-length reporting periods. All rows use equivalent individual units; the products have different realized prices. Columns A:E are the inputs, and the totals occupy row 5.
| Row | A: Product | B: Baseline units | C: Baseline revenue (USD) | D: Current units | E: Current revenue (USD) |
|---|---|---|---|---|---|
| 2 | Standard | 1,000 | 10,000 | 1,200 | 13,200 |
| 3 | Premium | 500 | 10,000 | 600 | 12,600 |
| 4 | Value | 500 | 2,500 | 700 | 3,500 |
| 5 | Total | 2,000 | 22,500 | 2,500 | 29,300 |
In B5 use =SUM(B2:B4) and repeat for C:E. Microsoft's SUM documentation confirms that the function adds values or ranges. Expand the referenced ranges whenever you insert products; the formula examples assume exactly the three displayed rows.
Keep revenue as an input from the export. Rebuilding it from a rounded display price can create a false difference against the ledger. A price with more decimal places in the source should keep that precision in all calculations.
Place these columns to the right of E. The formulas are for row 2 and copy down through row 4. Dollar effects are signed: positive increases current revenue relative to the baseline; negative reduces it.
| Column | Heading | Formula in row 2 | Purpose |
|---|---|---|---|
| F | Baseline unit price (USD) | =C2/B2 |
Observed baseline selling price |
| G | Current unit price (USD) | =E2/D2 |
Observed current selling price |
| H | Baseline unit share | =B2/$B$5 |
The product's share of baseline volume |
| I | Price effect (USD) | =(G2-F2)*D2 |
Price movement at current units |
| J | Volume effect (USD) | =($D$5-$B$5)*H2*F2 |
Total unit change in the baseline blend |
| K | Mix effect (USD) | =(D2-$D$5*H2)*F2 |
Departure from the baseline blend |
| L | Residual (USD) | =I2+J2+K2-(E2-C2) |
Unexplained revenue movement |
Do not replace division errors with zero. An error in F or G signals that a row needs review, often because a product was not sold in one period. Keep it out of the comparable-product calculation until its treatment is agreed.
For readers entering the formulas directly in Excel, the effect columns are:
I2 =(G2-F2)*D2
J2 =($D$5-$B$5)*H2*F2
K2 =(D2-$D$5*H2)*F2
L2 =I2+J2+K2-(E2-C2)
In row 5, sum H:L. F5 is =C5/B5, the baseline weighted-average price; G5 is =E5/D5, the current weighted-average price. Do not sum unit prices.
Baseline prices from the input table are $10.00, $20.00 and $5.00. Current prices are $11.00, $21.00 and $5.00. Baseline unit shares are 50%, 25% and 25%, so current total volume of 2,500 would imply 1,250 Standard units, 625 Premium units and 625 Value units at the old blend.
| Product | Price effect (USD) | Volume effect (USD) | Mix effect (USD) | Revenue change (USD) | Residual (USD) |
|---|---|---|---|---|---|
| Standard | 1,200 | 2,500 | -500 | 3,200 | 0 |
| Premium | 600 | 2,500 | -500 | 2,600 | 0 |
| Value | 0 | 625 | 375 | 1,000 | 0 |
| Total | 1,800 | 5,625 | -625 | 6,800 | 0 |
For Standard, price is (11 - 10) multiplied by 1,200 = $1,200. Volume is the total unit increase of 500 multiplied by its 50% baseline share and $10 baseline price = $2,500. Mix is (1,200 - 1,250) multiplied by $10 = -$500.
The portfolio's units increased by 25%, calculated as (2,500 - 2,000) / 2,000. Mix reduced revenue because the current blend moved toward Value relative to the baseline shares. That statement describes price-weighted product mix; it does not establish whether contribution improved.
Display monetary effects to the nearest dollar here because the inputs produce whole-dollar results. In your own workbook, calculate with full precision and round only the presentation.
First, tie the input totals to the two source sales reports. Baseline revenue must be $22,500 and current revenue $29,300 for these rows. A bridge can reconcile perfectly while explaining an incomplete export.
Second, every L cell must be zero. Standard's effects give 1,200 + 2,500 - 500 = 3,200, matching 13,200 - 10,000. Premium gives 600 + 2,500 - 500 = 2,600; Value gives 0 + 625 + 375 = 1,000.
Third, the portfolio check is 1,800 + 5,625 - 625 = 6,800, equal to 29,300 - 22,500. Keep an independent volume check using the total unit change and baseline average price:
=($D$5-$B$5)*($C$5/$B$5)-SUM(J2:J4)
It returns zero: 500 multiplied by $11.25 equals the $5,625 volume effect. An additional input check uses prices and units:
=SUMPRODUCT(B2:B4,F2:F4)-C5
=SUMPRODUCT(D2:D4,G2:G4)-E5
Both return zero. Microsoft's SUMPRODUCT guide documents the multiplication and summation used in those checks. Covirage is not affiliated with Microsoft.
If the task expands into other budget movements, use variance analysis. For the presentation layer, see the neighboring price volume mix analysis dashboard; it should use the reconciled results rather than a second calculation method.
Bring your product-level units and net revenue for both periods, with your row mappings and comparison definitions. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See revenue driver analysis for the supporting business context.
This page supplies an on-page layout you can copy into a workbook, including populated inputs, formulas and reconciliations. It does not provide a separate download. Replace the example product rows with your own comparable-period sales and retain the check columns.
Yes. Use budget units and revenue as the baseline and actual units and revenue as the comparison. Keep product definitions, currencies and unit measures consistent. Label the periods clearly so a reader knows whether the bridge explains a budget variance or historical growth.
Treat it as new business outside the comparable-product calculations because no observed baseline price exists. Show its revenue in a separate bridge line. Apply the same discipline to discontinued products, then reconcile those movements and the comparable-product effects to total reported revenue.
Mix measures shifts between the rows you define. Product-only rows capture shifts between products. Product-and-customer rows also capture changes between customers paying different prices. Choose the level before comparing periods and keep it stable across successive reports.