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.
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.
| Line | Amount | Running 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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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-B2Total 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 sees | Why it happens | Fix |
|---|---|---|
| Drivers add up to more than the variance | One 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" bar | It is the easy place to put anything unexplained | Itemise it, keep it small, and show real Unexplained as its own line. |
| The last driver looks like a plug | It was calculated as the gap minus the others | Size every driver independently and let Unexplained take the difference. |
| Colors or signs flip between pages | Favorable and unfavorable were defined differently in different places | State the convention once and use the same colors everywhere. |
| The drivers are invisible next to the totals | The axis starts at zero, so a $6,000 bar is a sliver | Truncate the axis and say so on the chart, or bridge the variance alone. |
| Numbers change after review | The source refreshed, or someone overwrote a value by hand | Link 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.