Variance analysis without the jargon: a worked example

Variance analysis is the work of explaining the gap between what you planned and what happened. Revenue was budgeted at $4.2M, it came in at $4.8M, and variance analysis is how you say where the extra $600K came from, in dollars, instead of in adjectives.

Most explanations stop at "volume was strong". This post goes further, using one fictional company and real formulas, so you can rebuild every number in a spreadsheet as you read.

The setup

Imagine a company selling two products: Standard at $100 and Premium at $150. The budget for the quarter looked like this:

The quarter closed with these actuals:

The total variance is $4,799,860 minus $4,200,000, or +$599,860. Favourable, since more revenue than planned is good news.

Three different things happened at once. The company sold more units. It sold a different blend of products. And it charged more for both. Variance analysis separates those three effects so that each one can be discussed on its own.

Price variance: did we charge more?

Price variance isolates the effect of selling at a different price, on the units you actually sold.

For Standard, that is ($104 − $100) × 16,560 = $66,240. For Premium, ($155.75 − $150) × 19,760 = $113,620. Together, $179,860.

Using actual units here is a convention, and the most common one. It answers a clean question: given what we did sell, how much extra did pricing bring in?

In a spreadsheet with budget and actual side by side, the formula per row is =(E2-C2)*D2, where C is budget price, D is actual units and E is actual price. Sum the column.

Volume variance: selling more units

Volume variance isolates the effect of selling more or fewer units in total, valued at the budget's average price.

That is (36,320 − 33,600) × $125 = $340,000.

The budget average price matters here. Using it keeps volume "pure": it asks what the extra 2,720 units would have earned if they had arrived in the planned blend at the planned prices.

Mix variance: did we sell a different blend?

This is the part people skip, and it is often the most interesting.

The budget assumed a 50/50 split between Standard and Premium. The actual split was 45.6% Standard and 54.4% Premium. Premium is the more expensive product, so a shift toward it raises revenue even if total units and prices had stayed put.

Actual units at budget prices: 16,560 × $100 + 19,760 × $150 = $4,620,000. Actual total units at the budget average: 36,320 × $125 = $4,540,000. The difference is $80,000.

You can also compute mix per product, which helps when you have more than two:

"Units at budget mix" is the actual total of 36,320 split 50/50. The formula per row is =(ActualUnits - ActualTotal*BudgetShare)*BudgetPrice.

Putting it together: the bridge

Now add the three effects to the budget:

• Budget: $4,200,000
• Price: +$179,860
• Volume: +$340,000
• Mix: +$80,000
• Actual: $4,799,860

The pieces sum exactly to the actual, with nothing left over. That is the test of a good variance analysis. If you end up with a large "other" bar, your definitions overlap or leave a gap, and someone in the meeting will find it.

Drawn as a bridge (also called a waterfall), the story is obvious at a glance. Volume did most of the work. Price added a solid $180K. Mix helped too, because customers leaned toward the premium product. A slide that just said "+$600K vs budget" would have hidden all of that.

Three conventions that trip people up

Variance analysis has no single official method, which is why two analysts can produce two different bridges from the same data. Both may be valid. Agree on these before you present:

Which units value the price effect. This post uses actual units. Some teams use budget units, which pushes part of the effect into a separate "price-volume" term. Pick one and write it on the slide.

Whether mix is reported separately. Some finance teams fold mix into volume and show a single "volume and mix" bar. That is fine for a board, but it hides useful information from the people who set product strategy.

Sign conventions. For revenue, actual above budget is favourable. For costs, it is the reverse. A $45K overspend on payroll is an unfavourable variance even though the number is positive. Label the bars "favourable" and "unfavourable", or colour them consistently, so nobody has to think about it.

Reading the result

Each of the three effects points at a different owner and a different next question.

Price belongs to whoever sets prices and discounts. A favourable price variance is good news only if volume didn't suffer for it. Here both were positive, which suggests the price rise stuck. Next question: was it list price, or fewer discounts?

Volume belongs to sales and marketing. Next question: which channel or region brought the extra 2,720 units, and will it repeat?

Mix belongs to product. A shift toward Premium is usually welcome, but it is worth checking whether it came from new customers choosing Premium or from Standard customers upgrading, because those lead to different plans.

Taken together, the bridge turns "we beat budget by $600K" into three conversations with three named people, which is what a review meeting needs.

Building this in a spreadsheet

Here is the layout I use in Excel or Google Sheets. One row per product, with columns grouped:

Column H is the per-product version of volume. Summed, it gives the same $340,000 as the total formula above. Then a check cell: the sum of G, H and I should equal actual revenue minus budget revenue. If the check is not zero, fix it before anyone sees the numbers.

Two practical tips. Put the check cell somewhere visible, in bold, so it fails loudly. And keep budget and actuals on separate source tabs that you paste exports into, so next month you replace data without touching formulas.

Presenting it without losing the room

The analysis is the easy part. The meeting is where it usually falls apart, for one of two reasons.

The first is presenting the method instead of the answer. Nobody in the room needs to know what mix variance is called. They need to hear "we sold 2,720 more units than planned, customers leaned toward Premium, and we priced a little higher than budget". Lead with that sentence. Keep the formulas in the appendix for the person who asks.

The second is the follow-up question you didn't prepare for. Usually it is "which region?" or "which month?". A static slide can't answer it. This is where I have watched good analysis turn into a week of follow-up emails.

The bridge itself is what I put on a canvas in FlowProofs: budget and actuals uploaded, price, volume and mix written as formulas once, and the bars drawn from them. When the CFO asks about a single region, you filter the canvas and every bar recalculates. The finance teams page shows the setup. But the spreadsheet above will get you a correct bridge today, and correct comes first.

If your variance is on the cost side rather than revenue, the next post to read is how to build a budget vs actual report people read. And if the question in your meeting is "why did revenue change since last month" rather than "versus budget", the method shifts a little: see how to explain why revenue changed.

Build the check cell first. Then the bridge.