Budget vs Actual Variance Analysis: The Sign Trap and the Percent Trap

1. The variance formula, and the sign trap

Before the explanation: two lines both came in over budget, revenue by $12,400 and contractor spend by $11,800. One of those is good news. Say which, and say what told you.

Variance is actual minus budget. Budget is the number you planned, actual is the number that happened, and the variance is the gap between them, with a direction. Revenue budgeted at $250,000 that came in at $262,400 has a variance of positive $12,400. Contractor spend budgeted at $20,000 that came in at $31,800 has a variance of positive $11,800.

Same formula, same plus sign, opposite meanings. More revenue than planned is good. More spending than planned is bad. The sign only tells you the direction of the miss; the line type tells you whether that direction helped. This is why finance teams do not trust the sign. They add a column that states the verdict directly: F for favorable, meaning the miss helped profit, and U for unfavorable, meaning it hurt profit. The rule in one breath: on revenue lines, over budget is F. On cost lines, over budget is U.

Some shops flip the formula to budget minus actual so that positive always means favorable on cost lines. That works until someone merges two workbooks with opposite conventions. The F/U label survives both conventions, which is exactly why it exists. Write the formula you used in a cell comment or a header note, and label F/U regardless.

2. The worked table, labeled F and U

Here is the department. Two revenue lines, six cost lines. Variance is actual minus budget throughout, percent is variance divided by budget, and the F/U column applies the rule from section one.

Check one row by hand to trust the rest. Contractors: 31,800 minus 20,000 is 11,800 over, and 11,800 divided by 20,000 is 0.59, which is 59.0%. It is a cost line, over budget, so U.

The totals: revenue came in at $316,600 against a $310,000 budget, favorable by $6,600. Costs came in at $217,850 against $200,800, unfavorable by $17,050. Net, the department planned to contribute $109,200 and actually contributed $98,750, which is $10,450 short of plan.

Say why the same plus sign produced an F on the first row and a U on the third row, in your own words, before moving on. If you can say it, the rest of this page is bookkeeping.

3. Percent variance, and its two traps

Before the explanation: in the table above, Travel is off by 96.7% and Salaries by 2.5%. Which one costs the company more money?

Percent variance is the variance divided by the budget. It answers "how far off the plan was this line, relative to its own size," and it makes lines of different sizes comparable. It also has two reliable failure modes.

Trap one: a zero or tiny budget makes the percent meaningless. Divide by a budget of zero and the formula errors, or worse, someone hardcodes it to show a number. Divide by a tiny budget and you get a giant percent from pocket change: Office supplies here is 43.8% over, and the entire miss is $350. A percent with a small denominator is a loud noise about a small thing.

Trap two: percent and dollars rank the lines in different orders, and the percent order is the wrong one to act on. Salaries is off by just 2.5%, the second-smallest percent among the misses, yet that 2.5% is $3,500, which is ten times the office supplies miss. Money is spent in dollars, not in percents. So the working habit is: rank by dollars, flag by percent. Dollar order tells you where the money went. The percent flag tells you which lines drifted furthest from their own plan, which is a control question rather than a money question. Both are worth knowing. Only one decides what you investigate first.

Ranked by absolute dollars, this table reads: Product revenue +12,400 F, Contractors +11,800 U, Services revenue -5,800 U, Salaries +3,500 U, Travel +2,900 U, then everything under a thousand. Contractors is the story. It is second in dollars and second in percent, the only line that is loud on both measures.

4. Set a materiality floor before you read

Before the explanation: how big does a variance have to be before you say its name in the review meeting? If you do not have a number, the table decides for you.

A materiality floor is a threshold you set before reading the table: below this many dollars, or below this percent, a variance is noise and gets no airtime. The habit that matters is the word "before." Decide the floor first, then read. If you read first, the interesting-looking rows will pull your floor down to wherever they happen to sit, and every month's floor will be different.

A common shape is a two-part test: flag a line only if it is off by more than a dollar floor AND more than a percent floor. For this department, $2,000 and 5% would flag Contractors, Services revenue, and Travel, while letting Salaries pass as within tolerance and silencing Office supplies entirely. Whether those are the right floors is a judgement about this department, not arithmetic, and the floor itself should come from the data rather than from taste. Choosing thresholds from the data is the full method: pick it, write down why, and check the read survives a nearby choice.

Write the floor into the workbook where readers can see it. "Lines within $2,000 and 5% of plan are not discussed" is one sentence, and it converts your silence about small rows from an omission into a policy.

5. Build it in Excel with SUMIFS

Before the explanation: your actuals arrive as hundreds of individual transactions, and your budget is eight tidy lines. What has to happen to the transactions before the two can sit in one table?

They have to be summed per line. That is the whole build: the budget table supplies the rows, and SUMIFS pulls each line's actual total out of the transaction pile. SUMIFS adds up one column wherever another column matches a condition, and the SUMIFS guide covers the function itself in depth.

Set up two Excel tables. One named Actuals with the raw transactions, columns Line, Month, Amount. One named Report with columns Line, Type, Budget, and the formulas. Then, in the Report table:

Actual      =SUMIFS(Actuals[Amount], Actuals[Line], [@Line])
Variance    =[@Actual]-[@Budget]
Variance %  =IF([@Budget]=0, "", [@Variance]/[@Budget])
F/U         =IF([@Type]="Revenue",
               IF([@Variance]>=0, "F", "U"),
               IF([@Variance]>0, "U", "F"))

The IF([@Budget]=0, "") wrapper is trap one from section three, handled at build time: a line with no budget shows a blank percent instead of an error or a fake number. The F/U formula is the sign rule made mechanical, so the verdict column can never disagree with the convention.

Verify the machinery once with a line you can add in your head. The Contractors actuals in this example are three monthly invoices: 9,800 plus 10,400 plus 11,600, which is 31,800, exactly what the SUMIFS returns and what the worked table shows. Salaries is 47,800 plus 47,800 plus 47,900, which is 143,500. One hand-check per build catches a mis-pointed range, and a mis-pointed range is the most common way a variance report is wrong.

Picture running this build on your own cost center's transactions. Which line would you hand-check, and do you already know a month where SUMIFS would surprise you? That suspicion is worth chasing; it is usually a naming mismatch between the transaction labels and the budget lines.

6. The full before and after

Same eight lines, same numbers, two tables.

Before

Budget, actual, variance. Sorted by percent, no F/U, no floor. Travel tops the table at 96.7%, Office supplies sits second at 43.8%, and the reader's eye spends its first ten seconds on $3,250 of combined misses. Salaries, off by more than both of those together, sits sixth of eight. And a director skimming the variance column sees +12,400 and +11,800 as the same kind of number, when one grew the business and the other blew a plan by half.

After

Sorted by absolute dollars, verdict labeled on every line, and a flag column applying the stated floor: off by more than $2,000 and more than 5%. The same information now reads in the order the money moved. Contractors surfaces immediately as the problem, Product revenue as the offsetting good news, and Office supplies drops to the bottom where its $350 belongs, still visible, no longer shouting.

7. Present it: the variance walk and the one-sentence claim

Before the explanation: your director has thirty seconds. What is the one sentence this table earns?

The walk first, because it is how finance audiences expect to travel from plan to result. A variance walk starts at the budgeted total, adds or subtracts the largest variances in order of size, and lands exactly on the actual. In one paragraph, this department's walk: we planned to contribute $109,200; product revenue added 12,400; contractor overruns took back 11,800; the services shortfall took 5,800 more; salaries, travel, and small items netted out another 5,250 against us; and we landed at $98,750. Every step is a labeled cause, the steps sum exactly, and drawn as a chart this paragraph is a waterfall: floating bars stepping from the budget column down or up to the actual column.

Then the claim. A variance report is not eight rows; it is one sentence with a number, backed by eight rows: "We missed plan by $10,450, and contractor spend at 159% of budget is the driver." That is a claim someone can act on, question, or own. Sending the table alone makes the reader do the analysis you were asked to do, and the difference between shipping rows and shipping a claim is the whole subject of report vs analysis.

8. Edge cases that flip meanings in real reports

Before the explanation: a cost line comes in 40% under budget. The F/U formula stamps it favorable. Name a reason it might be the worst news in the table.

Underspend is not automatically good. The hiring you budgeted and did not do, the maintenance that got deferred, the campaign that never launched: each prints as F and each may be a plan failing to happen. F and U describe the effect on this period's profit, nothing more. Read big favorable cost variances with the same suspicion as big unfavorable ones.

Timing masquerades as performance. An invoice that slipped from June into July makes June favorable and July unfavorable, and both are illusions. Before escalating any single-month variance, check the year-to-date column; timing noise cancels there while real drift accumulates.

The convention flips between shops. Actual minus budget here; budget minus actual elsewhere, so that positive means favorable everywhere. Neither is wrong. A workbook that does not state which one it uses is wrong. One header note fixes it forever.

Mixed signs hide inside totals. The two revenue lines net to +6,600 F, which quietly contains a $5,800 services shortfall. Every subtotal you present should be openable back into its lines, because someone in the meeting will ask, and because the netted number genuinely conceals a U.

Zero-budget lines need words, not percents. Spending that had no budget line at all is a different conversation from an overrun, and it deserves a sentence ("$4,000 of unbudgeted legal fees") rather than a blank or an error in the percent column.