Price, volume and mix

Revenue is 270 above budget. Is that because you charged more, sold more, or sold more of the expensive products? Price / volume / mix answers it: it splits the variance in a value (revenue, gross margin) between two slices into the part each cause explains.

The two slices are any two items of one dimension: Actual against Budget, or June against last June when the dimension you compare along is time. Below, 0 is the reference slice and 1 the actual one.

What it needs

• a cube with a quantity line (units, tonnes, hours) and either a value line (revenue, margin) or a unit price line, all items of one dimension, such as Measures;
• a dimension to decompose across: products, customers, regions;
• the two slices to compare.

With a price line, the value is price × quantity cell by cell.

The convention

For each item i, with Q its quantity, P = value ÷ quantity its unit price, and Qt the total quantity of the continuing items:

Volume + mix = P0ᵢ × (Q1ᵢ − Q0ᵢ), so the three effects add up to P1ᵢ × Q1ᵢ − P0ᵢ × Q0ᵢ, the item's whole variance. They tie per item and in total within the check-row tolerance, half a cent.

• Price is what the new prices earned on what was actually sold.
• Volume is what selling more or less in total would have earned at the old prices and the old mix.
• Mix is what the shift between items earned at the old prices. Selling more of the dear products is a positive mix. An unchanged product still carries a volume and a mix effect when the rest of the portfolio moved, and the two cancel out on that product.

A worked example

C was discontinued and D launched, so only A and U continue: Q0t = 140 and Q1t = 160. A's price effect is (11 − 10) × 120 = 120. Volume across A and U is 20 more units at the budget average of 10, so 200, and mix is 0 because A and U have the same budget price. C and D give −50. Altogether 120 + 200 + 0 − 50 = 270 = 1,970 − 1,700.

New and lost items

A product sold in only one slice has no price on the other side, so there is no honest way to give it a price or a mix effect. Its whole change is reported on its own as New / lost, and its quantity stays out of the totals Q0t and Q1t. Otherwise a launch would show up as volume and mix on the products that were there all along.

Edge cases

• No reference quantity. When the continuing items' reference quantity adds up to zero (returns netting out sales, say), there is no reference mix to compare against. Their whole change is reported as volume, and the result is flagged.
• Value without quantity. A rebate or a provision line that moved with no quantity on either side is reported as Other, also flagged.
• Units must match. Quantities are added across the items, so the decomposition only means something where they share a unit. It runs at the leaves of the dimension by default. Pick a hierarchy level to decompose by product group instead. The level changes how the effects split, never the total. With a price line the decomposition always runs at the leaves, because a group's price is not the price of its parts.
• Exchange rates are not split out as an effect of their own yet.

On a dashboard

A variance widget has a Show setting: Variance table or Price / volume / mix waterfall. The waterfall starts at the reference value, steps through price, volume, mix and new/lost, and lands on the actual value. The caption below it says which level it decomposed at, how many items were new or lost, and any flag. Pick the dimension that holds the quantity and value lines, then the two lines and the level. The widget's page filters and the dashboard's nav bar select the rest of the slice, as in table mode.

With the assistant

get_price_volume_mix returns the same decomposition per item and in total, with the flags and a ties check: "how much of the revenue miss is price?", "is it mix or volume?". add_variance_widget with a pvm block puts the waterfall on a dashboard. Both use the same calculation as the widget, so the numbers agree.