Variance analysis in Excel for budget reviews
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.
Financial Planning and Analysis: Budgeting and Forecasting in Excel
The Gen Z Analyst
Open on YouTube (opens in a new tab)
Financial Modeling and Valuation Analysis FREE COURSE
The Gen Z Analyst
Open on YouTube (opens in a new tab)Variance analysis explains the difference between what you expected and what actually happened.
Use both amount and percent
An amount variance shows scale. A percentage variance shows proportion. You usually need both.
=Actual-Budget=IF(Budget=0,"",Variance/Budget)Add comments for material variances
Numbers show where to look. Comments explain what happened. Add a comment field for variances above a materiality threshold.
Separate price and volume when possible
If revenue changed, ask whether the movement came from price, volume, mix, or timing. A single total variance may hide the real driver.
Use flags sparingly
Flag only items that need review. If every row is red, no row is truly urgent.
Go deeper with this skill
Build a model where assumptions, calculations, outputs, and checks are easy to trace. For this article, the goal is to practice: Compare actuals to budget with variance amount, variance percent, comments, and simple management flags.
Practice workbook setup
Create a small model with assumptions, historical values, forecast rows, and one summary output.
Practice workflow
- Put assumptions in one clearly marked area.
- Use consistent formulas across time periods.
- Add checks for totals, signs, and balance relationships.
- Create a scenario area before changing base-case assumptions.
Quality checks
- Hard-coded assumptions are visually different from formulas.
- Formulas are consistent across rows and columns.
- Outputs reconcile to the supporting schedules.
Common mistakes
- Hiding assumptions inside formulas.
- Copying formulas across periods without checking references.
- Using formatting to imply precision the model does not really have.
Next actions
- Add sensitivity cases for the two assumptions that matter most.
- Create a model review checklist before sharing the workbook.
Formula focus: test SUMIFS(), IF() with normal rows, blank inputs, and at least one edge case.
