Variance Analysis Template for Excel: Build One That Ties

Every finance team has a variance report. Budget in one column, actual in the next, the difference, and a percent. It gets pasted into the month-end deck, someone writes "revenue unfavorable due to lower sales" beside it, and the meeting moves on.

That is a report, not an analysis. It tells you what moved and by how much. It does not tell you why, and the commentary written next to it usually restates the number in words. The fix is not more columns. It is a template with three things most versions skip: one sign convention, a decomposition that splits price from volume and proves it, and thresholds that decide which variances deserve a sentence.

Here is how to build it. About an hour the first time, then reusable every month.

Start with the layout, not the formulas

Three tabs: Actuals, Budget, Report.

The Actuals and Budget tabs hold data in the same shape, one row per account per month, with columns for account number, line label, month, and amount. Paste your GL export or planning system export there and touch nothing else on those tabs. The Report tab holds the analysis, and every number on it arrives by formula. The moment someone pastes a value onto the Report tab, the template starts lying.

Pull with SUMIFS, never with links to individual cells:

=SUMIFS(Actuals!$D:$D, Actuals!$A:$A, $B5, Actuals!$C:$C, $D$2)

where D2 is a single cell holding the reporting month. Change that one cell and the whole report moves to the new month. At close, that cell and the pasted export are the only things you should have to touch.

One sign convention, applied everywhere

Many broken variance reports turn out to be sign problems. Revenue $50K over budget is good news. Hosting $50K over budget is bad news, and a template that computes both as actual minus budget shows them the same way. Readers then flip signs in their heads, and someone eventually flips one the wrong way in front of the CFO.

The rule: favorable is positive, everywhere. Add a hidden helper column holding +1 for revenue rows and -1 for cost rows, then:

variance = (actual - budget) * direction

Revenue up prints positive. Cost up prints negative. Nobody has to know which kind of row they are reading, and subtotals add correctly because every row already speaks the same language. This assumes revenue and costs both arrive as positive amounts. If an export shows revenue as negative credits, multiply that tab's revenue pulls by -1 so they do, and reverse that flip in the report total that the first proof row below compares with the Actuals tab, or that check never reaches zero.

For the percent column, divide by the absolute value of budget and guard the small bases:

=IF(ABS(budget)<min_base, "n/m", variance/ABS(budget))

where min_base is a labeled input cell, 1,000 to start. The ABS keeps a negative budget line from flipping the sign of the percent. The guard prints n/m instead of a 400% variance on a $200 base. Large percentages on small bases often pull a variance meeting away from the real business problems.

Split price from volume, then prove it

For any revenue line where you know units and price, two formulas turn "revenue missed" into something a person can act on:

Volume variance = (actual units - budget units) * budget price
Price variance = (actual price - budget price) * actual units

These two sum exactly to the total revenue variance. Not approximately, exactly, by algebra. So put a proof row directly beneath the split:

proof = volume variance + price variance - total variance

It must show zero. If it does not, either a number was typed over a formula or the units and prices do not multiply back to the revenue in the ledger. Either way, you found it before the meeting did.

Costs decompose with the same shapes. A spend line with a rate and a quantity behind it splits into rate variance, (actual rate - budget rate) * actual quantity, and usage variance, (actual quantity - budget quantity) * budget rate, each multiplied by the direction column like every other variance so the proof row still reads zero. Salaries split into rate and heads the same way.

Two honest rules. If a line has no real unit and price behind it, leave it undecomposed; a single true number beats an invented split. And if you run the split per product line, the pieces already add to the company total, so mix shows up where it belongs, in the lines. A separate total-level mix calculation is only worth its complexity when margins differ sharply across lines. Add it later if the meetings keep asking for it.

Thresholds decide what gets a sentence

Explain everything and you have explained nothing. The template should decide, mechanically, which variances earn commentary.

Set two floors in labeled input cells: a dollar floor and a percent floor. A variance earns a sentence only when it clears both. For a company with $5M of monthly revenue, $25K and 5% is a workable start; scale both to your size. Then a flag column does the triage:

=IF(AND(ABS(variance)>=dollar_floor, ABS(variance)>=pct_floor*ABS(budget)), "EXPLAIN", "")

Everything below the floors rolls into one line at the bottom, "all other variances, net," with a number and no story. That single line is often what keeps a variance review at thirty minutes instead of ninety.

Commentary that cannot restate

Every flagged line gets one sentence with three parts: the operational driver, the dollars that driver explains, and whether it repeats.

Bad: "Cloud hosting unfavorable due to higher hosting costs."

Good: "Cloud hosting unfavorable $38K: on-demand charges for usage above the reserved plan added $22K and continue until the plan is resized at the Q4 renewal; a test environment left running after a load test added $16K, one-time."

The dollars inside the sentence should come close to the variance being explained. Show the residual as "unexplained" rather than stretching a driver to cover it. If unexplained is bigger than your dollar floor, the analysis is not finished, and it is better for the template to say so than for someone to discover it in Q&A.

The proof rows that keep it honest

Three checks, one cell each, sitting at the top of the Report tab with conditional formatting that turns them red when nonzero:

1. Report total minus the Actuals tab total for the month. Proves the SUMIFS pulls dropped nothing, which they will the first time a new account shows up in the export.

2. The sum of the absolute decomposition proofs across every split line. Proves volume plus price still equals total.

3. The YTD column minus the sum of the months. Proves the year-to-date view and the monthly view are the same report.

New accounts are the quiet killer. Add a fourth check if you want to sleep well: one cell on the Report tab that counts the month's Actuals rows whose account number appears nowhere in the report's account column.

=SUMPRODUCT((Actuals!$C$2:$C$5000=$D$2)*(COUNTIF($B$5:$B$200, Actuals!$A$2:$A$5000)=0))

Zero means complete coverage. Size both ranges well past your longest export and your last report row, and keep the month cell the same type as the export's month column, a real date against real dates or text against text, or this check can read zero while accounts are missing.

Reusing it at close

Month-end becomes five moves. Paste the new export into Actuals. Change the month cell. Read the proof rows; all zero or you stop and fix. Write one sentence for each EXPLAIN flag. Save it as a new file with the month in the name. Last month's file is your audit trail, and a template you never overwrite is a template you can defend a year later.

None of this needs macros, add-ins, or anything beyond SUMIFS, SUMPRODUCT, COUNTIF, IF, AND, and ABS. What makes it work is not the formulas. It is that the template refuses to let a number through without proving it ties, and refuses to let a sentence through that only restates the number.