Written by the Biznerdly Editorial Team. Technical review by Tanzila Tanjim Tusra, Researcher, a statistics graduate & partially qualified chartered accountant (ICAB) and data analyst. Published August 18, 2026. Last verified: August 2026, tested on Excel 365 and Excel 2021.
Variance analysis compares what actually happened to what was budgeted or forecast, then labels the difference favorable or unfavorable. The sign convention flips depending on the line: higher-than-budget revenue is favorable, but higher-than-budget expense is unfavorable. Build it once as static Excel formulas for a quick one-off, or as a Power Pivot DAX measure that recalculates automatically every time you refresh, with a materiality threshold that flags only the variances worth investigating.
If you've ever stared at a report and had to pause to remember whether a positive number in the "Expense Variance" column is good news or bad news, you're not alone; this is genuinely the single most common error we see in variance reporting, and it's almost never a math mistake. It's a sign-convention mistake. We'll fix that first, then build the report two ways: a static version for a quick one-off, and a Power Pivot version that keeps itself current.
What variance analysis actually measures
Variance analysis is the practice of comparing actual results to a budget, forecast, or prior period, then explaining why the two don't match. In FP&A (financial planning and analysis) work, it's usually run monthly or quarterly against a budget-vs-actual report, but the same mechanics apply to comparing actuals against a rolling forecast, or this year against last year.
The output is more than a single number. A complete variance analysis answers three questions: how big is the gap, is it good or bad news, and is it big enough to actually investigate. Most competitor guides online stop at the first question. This article covers all three.
The sign convention: favorable vs unfavorable
This is the part worth memorizing, because getting it backwards in front of a manager is a fast way to lose credibility.
| Line item | Budget | Actual | Variance ($) | Favorable or unfavorable? |
|---|---|---|---|---|
| Sales Revenue | $200,000 | $215,000 | +$15,000 | Favorable: more revenue than planned |
| Sales Revenue | $200,000 | $188,000 | −$12,000 | Unfavorable: less revenue than planned |
| Marketing Expense | $50,000 | $54,000 | +$4,000 | Unfavorable: spent more than planned |
| Marketing Expense | $50,000 | $45,500 | −$4,500 | Favorable: spent less than planned |
Notice the pattern: for revenue lines, a positive variance (actual higher than budget) is favorable. For expense and cost lines, a positive variance is unfavorable, because you spent more than planned. The dollar sign never tells you the story on its own; you have to know which type of line you're looking at first.
The core formulas
| Metric | Formula | Notes |
|---|---|---|
| Variance ($) | Actual − Budget | Same formula for every line; interpretation depends on revenue vs expense |
| Variance (%) | Variance ÷ Budget | Always review alongside the dollar variance, never alone |
| Favorable / Unfavorable flag | IF logic keyed to account type (revenue vs expense) | Covered in the worked template; don't hardcode this per row, key it to an account-type column |
Two ways to build it: static Excel vs Power Pivot
The dataset for this example: a monthly Marketing Expense budget vs actual, six months of history.
Build 1: Static Excel formulas
- Lay out Budget and Actual as two columns, one row per month.
- Add
=Actual - Budgetfor the dollar variance, and=Variance/Budgetfor the percentage. - Add an IF formula that reads an "Account Type" column to decide whether a positive number should display as favorable or unfavorable.
- Apply conditional formatting so unfavorable variances render in red automatically.
This is fast to build and perfectly fine for a single report you'll run once or twice. The catch: next month, you paste in new numbers and drag every formula down again, and if you ever add a new account type, the IF logic needs manual updating everywhere it's used.
Build 2: Power Pivot DAX measure (self-refreshing)
If you're not yet comfortable with DAX syntax, covers the 15 measures that make the rest of this section easy to follow.
Variance Amount := [Total Actual] - [Total Budget]
Variance % := DIVIDE([Variance Amount], [Total Budget])
Favorable Flag :=
IF(
RELATED(Accounts[AccountType]) = "Revenue",
IF([Variance Amount] >= 0, "Favorable", "Unfavorable"),
IF([Variance Amount] <= 0, "Favorable", "Unfavorable")
)Once these three measures exist in the Data Model, every PivotTable built on it, this month's, next month's, a filtered view for one department, automatically applies the same correct sign logic. You never rewrite the favorable/unfavorable rule again.
Setting a materiality threshold
Not every variance deserves an investigation. A department that's consistently 2% over budget every month isn't newsworthy; a department that suddenly swings 15% over budget after months of sitting near zero is. A materiality threshold separates routine noise from something worth a conversation.
One practical approach, borrowed from statistical process control: flag a variance when it falls more than 1.5 standard deviations from that line's own historical variance pattern, rather than using a flat percentage for every account regardless of how volatile it normally is.
Historical Variance StdDev :=
CALCULATE(
STDEVX.P(VALUES(Calendar[Month]), [Variance %]),
DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -12, MONTH)
)
Materiality Flag :=
IF(
ABS([Variance %]) > 1.5 * [Historical Variance StdDev],
"Review",
"OK"
)Worked example: if Marketing Expense has swung between −3% and +4% over the past six months, its historical standard deviation is roughly 2.4 percentage points, putting the 1.5-standard-deviation threshold at about 3.6%. An 8% variance this month clears that bar easily and gets flagged; a 3% variance doesn't. Adapt the exact CALCULATE/DATESINPERIOD syntax to match your own Calendar table structure; the pattern is what matters.
For the underlying DAX statistical functions used here, see Microsoft's STDEVX.P function reference.
Common mistakes
| Mistake | Why it happens | Fix |
|---|---|---|
| Treating every positive variance as good news | Sign convention isn't applied consistently across revenue and expense lines | Key the favorable/unfavorable logic to an account-type field, never hardcode it per row |
| Reviewing dollar variance without the percentage | A large dollar number looks alarming even when it's small relative to the budget | Always show Variance % alongside Variance $ in the same report |
| Flagging every non-zero variance for investigation | No materiality threshold in place | Apply a threshold, whether a flat percentage or a statistical one, so attention goes to genuine outliers |
| Comparing this month's actual to last month's budget by accident | Copy-paste errors when rolling the report forward each period | Build the report on a Power Pivot Data Model with a proper Calendar table, so the correct period is selected by filter, not by manual reference |
Downloadable template
- Dual-Format Variance Template, the same Marketing Expense dataset built two ways: static Excel formulas on one tab, a Power Pivot Data Model with the measures above on another. [DOWNLOAD LINK]
- Materiality Threshold Calculator, a standalone tab that computes the historical standard deviation and threshold for any line item you paste in. [DOWNLOAD LINK]
Frequently asked questions
What is a favorable vs unfavorable variance?
Favorable means the actual result was better for the business than budgeted: more revenue than planned, or less expense than planned. Unfavorable means the opposite: less revenue than planned, or more expense than planned. The same positive dollar variance is favorable on a revenue line and unfavorable on an expense line.
How do you calculate variance percentage?
Variance % = (Actual − Budget) ÷ Budget. This should always be reviewed next to the dollar variance, since a small percentage on a large budget can represent a bigger dollar impact than a large percentage on a small budget.
What's a good variance threshold to flag?
There's no single universal number. A common starting point is a flat percentage, such as 5% or 10% of budget, but a more precise approach ties the threshold to each line's own historical volatility, for example flagging anything beyond 1.5 standard deviations from that line's typical monthly swing, as shown above.
How do I automate variance reporting in Excel?
Build the report once on a Power Pivot Data Model with DAX measures for the variance amount, variance percentage, and materiality flag. From then on, refreshing the workbook after pasting in new actuals recalculates the entire report automatically, with no formulas to drag down and no logic to rebuild.
Where to go from here
- Pivot Tables vs Power Pivot: The Complete Decision Framework, if you're not yet using Power Pivot and want to know whether it's worth adopting.
- Power Pivot DAX for Pivot Table Users, the DAX foundation this article builds on.
- Power BI vs Power Pivot, if the waterfall chart above is your first real reason to look at Power BI.
0 Comments