Reporting and finance

Build a variance bridge people trust

10 min read

A step-by-step approach to explaining the gap between budget and actual so the numbers tie out.

The problem

Someone asks why actual spending came in above budget. You answer with a list of reasons: new hires, overtime, a bonus true-up. Then they add up the numbers, and the list explains $31,000 of a $26,600 gap. Or only $14,000 of it. After that, nobody believes the rest of the report either.

A variance bridge fixes this because it makes a claim anyone can check with arithmetic. Start at budget, add each driver, and land on actual. If it does not land, the gap is visible on the page instead of hidden in a paragraph.

If the two endpoints do not agree in the first place, fix that first. Why month-end reports break covers the five usual reasons.

What a trusted bridge looks like

Four parts, in this order. Skip any one and the reader has a reason to doubt the others.

BudgetStarting point, agreed to the source
→
DriversEach dollar in exactly one bucket
→
UnexplainedShown on the page, never buried
→
ActualEnding point, agreed to the source

The sample below is a monthly salaries line: $412,000 budgeted, $438,600 actual, so $26,600 over budget. For costs, a positive variance means you spent more than planned.

Try it

Here is the bridge for that month. Uncheck a driver to see what an unexplained gap looks like. Turn on the double count to see what a bridge looks like when one dollar gets two homes.

Build the bridge

Pick the drivers you have explained.


Orange bars push spending over budget. Green bars bring it down. The amber dashed bar is whatever the drivers do not explain.
LineAmountRunning total

Notice that the bridge always lands on actual. That is by design: Unexplained absorbs whatever is left. So the number to watch is not whether it lands, but how large the Unexplained bar is.

Build it in six steps

  1. Fix the two endpoints and the sign convention. Budget and actual must come from the same source, the same period and the same scope. Write the sign rule on the page: for costs, positive means over budget. For revenue, flip it.
  2. Calculate the total variance once. Put Actual - Budget in a single cell. Every other number on the page should point back to it, so there is only one version of the gap.
  3. Choose drivers that cannot overlap. Good drivers answer different questions, such as headcount, rate, timing and one-offs. Test each pair: could a single dollar belong to both? If yes, redefine them. Four to six drivers is usually enough.
  4. Size each driver from the data, not from the gap. Calculate every driver from its own source rows, for example hires times their monthly cost. If you size the last driver as "whatever is left," the bridge ties by construction and proves nothing.
  5. Add an Unexplained line and show it. It equals total variance minus the sum of the drivers. Set a threshold, such as 5 percent of the total variance. If Unexplained is bigger than that, keep digging before you publish.
  6. Tie out and sign off. Confirm Budget and Actual agree to the ledger, every driver traces to source rows, and Unexplained is under your threshold. Add the as-of date so nobody confuses a later refresh with this version.

The three formulas

Assume budget in B2, actual in C2, and your drivers in E5:E9.

=C2-B2

Total variance, calculated once.

=(C2-B2)-SUM(E5:E9)

Unexplained: what the drivers do not cover. A negative result means the drivers add up to more than the variance, so something is counted twice.

=IFERROR(ABS(E10)/ABS(C2-B2),0)

Unexplained as a share of the total variance. Compare it to your threshold.

Where bridges lose trust

These are the patterns that make a careful reader stop believing the chart.

What the reader seesWhy it happensFix
Drivers add up to more than the varianceOne dollar sits in two categories, such as overtime inside both "new hires" and "overtime"Redefine categories so each dollar has one home. Check each driver against its source rows.
A big "Other" barIt is the easy place to put anything unexplainedItemise it, keep it small, and show real Unexplained as its own line.
The last driver looks like a plugIt was calculated as the gap minus the othersSize every driver independently and let Unexplained take the difference.
Colors or signs flip between pagesFavorable and unfavorable were defined differently in different placesState the convention once and use the same colors everywhere.
The drivers are invisible next to the totalsThe axis starts at zero, so a $6,000 bar is a sliverTruncate the axis and say so on the chart, or bridge the variance alone.
Numbers change after reviewThe source refreshed, or someone overwrote a value by handLink the bridge to the source, lock the period and print the as-of date.

Before you send it

  • Total variance is calculated once, and Budget and Actual agree to the ledger.
  • Every driver is sized from source rows, and none is a plug.
  • No dollar sits in two drivers.
  • Unexplained is on the page and under your threshold.
  • The sign convention and as-of date are stated.

Need a bridge your CFO will not question?

We build variance reporting in Excel that ties to the ledger, flags unexplained amounts and refreshes each month. Start with a free 30 minute strategy session.

Book a free strategy session