Building a Corporate Budget vs. Actual Variance Analysis in Excel
Variance analysis evaluates the quantitative difference between planned financial budgets and actual operational results. In management reporting and executive dashboards, presenting raw dollar variance alone is insufficient—finance teams must separate volume effects, pricing fluctuations, and cost overhead to deliver actionable commentary to department heads.
In this article
Formula Framework: Favorable (F) vs. Unfavorable (U) Variance
Revenue and cost variances behave inversely:
- Revenue Items:
Actual - Budget> 0 is Favorable. - Expense Items:
Budget - Actual> 0 is Favorable (spending under budget).
Dynamic Excel Formula with Materiality Flag:
=LET(
item_type, UPPER(TRIM(B2)),
actual, C2,
budget, D2,
dollar_var, IF(item_type="REVENUE", actual - budget, budget - actual),
pct_var, IF(budget=0, 0, (actual - budget) / ABS(budget)),
status, IF(dollar_var >= 0, "FAVORABLE", "UNFAVORABLE"),
material, IF(AND(ABS(dollar_var) > 50000, ABS(pct_var) > 0.05), "YES", "NO"),
status & " | " & TEXT(dollar_var, "$#,##0") & " | Material: " & material
)Visualizing Variances with Waterfall Charts
Waterfall charts provide immediate visual clarity on how baseline Budget Revenue transitions into final Actual EBITDA across cost of goods sold (COGS), operating expenses (OPEX), and foreign exchange (FX) impacts. Highlight negative variances in crimson and positive additions in forest green.
Pair this framework with our Month-End BRS Checklist and test your formulas in our interactive Formula Lab.
Design the source table
Use one row per account, cost centre, period and scenario. Keep Budget, Actual, Prior Year and Forecast as separate measures. Add an account type column so revenue and expense favorable logic is explicit rather than inferred from a sign.
Explain the movement
A useful variance report answers whether the change came from volume, price, mix, timing, foreign exchange or a one-off item. Use a bridge table for the waterfall, and link every bridge amount back to a source row. A chart should explain a reconciliation, not replace it.
Controls before publishing
- Budget totals reconcile to the approved version.
- Actual totals reconcile to the general ledger extract.
- Variance percentages handle zero or negative bases.
- Materiality thresholds are visible and documented.
- Every commentary line has an owner and review date.
Keep a review copy of the source extract and record the refresh timestamp. That makes the report reproducible when management asks why a number changed after the close.
Commentary that decision-makers can use
Describe the driver, size, owner and action. Avoid vague comments such as “unfavorable variance” without a cause or next step.
Keep the calculation visible so a reviewer can trace each explanation from the chart to account-level source rows and confirm the refresh timestamp.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
Stuck on a step? Ask a question and the AI answers using this article.