← All Kits · Excel Kit

Variance Analysis: What the Number Means Before You Explain It

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

A variance is the gap between what you said would happen and what did. One subtraction. The reason it takes up so much of a month end is that the subtraction is never the question anybody is actually asking. They want to know which gaps matter, which direction is good, and which ones are artefacts of the way the percentage was worked out.

What you do: put the variance in its own column as actual - budget, put the percentage next to it, and then check the three rows where a percentage is the wrong tool. On the six regions below, the total is 28,750 ahead of plan and only two of the six rows are worth talking about.

The short version. Variance equals actual minus budget. The percentage divides by the plan, not by the actual, and it stops working the moment the plan is zero or negative.

The table this page works on

Six regions, a full-year plan against the full-year result. Every number below is computed from these two columns, so you can rebuild any of it.

The one order that keeps the signs honest

Write it as actual - budget, never the other way round. That single convention means a positive number always says the same thing: reality came in above the plan. Swap the order on one worksheet and every reader who has seen the other one now has to check which convention you used before they can read a single row.

Above the plan is not the same as good. North beat a revenue plan by 11,400, which is good. A cost centre that beats its plan by 11,400 has overspent. Finance handles this with two words, favourable and unfavourable, which carry the direction of the account rather than the direction of the arithmetic. If your table mixes revenue lines and cost lines, you need that column, or a reader will get half the rows backwards.

The percentage, and what it divides by

Variance percent is variance / budget. The budget is the denominator because the plan is the thing you are measuring against. Dividing by the actual answers a different question and produces a smaller number every time you are ahead, which is why it turns up in decks that want a miss to look small.

West shows why the percentage is worth having at all. Its variance is 14,800, larger than North's 11,400, and it is a bigger deal in percentage terms too: 24.7 percent against 9.5 percent. But West is a 60,000 region. If you are choosing where to send a manager next month, the percentage tells you West behaved unusually and the absolute number tells you North moved more money. Neither column answers on its own. Show both.

The two rows where the percentage breaks

Central: the plan was zero

Central was not in last year's plan, so its budget is 0 and its actual is 4,200. Excel returns #DIV/0!, and the temptation is to wrap it in IFERROR and move on. Do not print a zero there. A zero says the region performed as planned, and the truth is that no plan existed. Leave the cell blank and put the word new in the comment column. That is one of the few places where a word is a more accurate output than a number.

Online: the plan was negative

Online was planned to lose 15,000 and lost 9,000. That is 6,000 better than plan. The standard formula gives 6,000 / -15,000 = -40%, and a minus sign in a variance column reads as a miss to every person who scans it. The improvement did not become a shortfall; the denominator changed the sign.

The fix is one function. Divide by the size of the plan rather than the plan itself, so the sign can only come from the numerator:

=IF(budget=0, "", variance / ABS(budget))

On Online that returns +40 percent, which matches the direction of the 6,000. Use this version everywhere, not just on the rows you noticed. The rows you noticed are the ones where a negative plan was obvious.

Three variances that are not one variance

A gap of 28,750 at the total line is almost never one story. Before you explain it, split it once:

Those three add back to the total, which is the check. If they do not add back, one of them has been worked on the wrong base. The reason the split is worth the ten minutes: "we beat plan by 28,750" invites no decision, while "we sold the planned volume at a better rate in one region and lost volume in another" names two different actions.

Which variances get discussed

Set the threshold before you look at the results, and write it on the page. A common pair is flag anything over 5 percent and over 10,000, both conditions, not either. On this table that leaves North and West, and it drops East's 900 without anybody having to argue that 900 is small.

Deciding the threshold after you have seen the numbers is how a report turns into an argument. The threshold set in advance is the same tool a control chart uses, and it is the difference between reporting a result and defending one. There is a fuller version of this in setting thresholds from data.

Cheat sheet

How to apply this to your own work

1. Open the variance report you send most often and check the column order is actual minus budget. If it is not, fix it once and say so in the covering line.
2. Search the percentage column for #DIV/0!, and for any row whose plan is negative. Those are the rows a reader will misread.
3. Replace the plain division with the ABS version everywhere, not only in the rows that broke.
4. Add the absolute variance next to every percentage, then sort by absolute value to see whether your sort order was being driven by small bases.
5. Write your materiality threshold at the top of the sheet before month end opens, so the rows that get discussed are chosen by a rule and not by whoever asks first.

The one habit to keep

Read every variance out loud as a sentence before it leaves your desk. "Online is forty percent down" is a sentence you can say about a region that lost less money than planned, and the moment you say it you hear that it is wrong. The formula cannot hear itself.

Which line on your last variance report would change meaning if the plan had been a negative number?