How to analyze budget versus actual variance
Analyze budget versus actuals by aligning account, entity, period, currency, and sign conventions; calculating absolute and percentage variance with sensible denominator rules; classifying material gaps into price, volume, mix, timing, one-off, and mapping effects; and building a bridge whose drivers reconcile exactly from budget to actual.
Why this gets messy
Budget and actual files often use different account mappings, entity coverage, currencies, signs, or calendar versions. A percentage column can create extreme values and colorful tables without explaining a single business driver.
The short version
- Align dimensions before calculating variance.
- Do not use percentage variance when budget is zero or immaterial.
- Separate timing differences from structural performance.
- A variance bridge must reconcile exactly to the reported gap.
From the field · School finance manager
Explain a school department's budget variance including commitments
Department budgets are annual, actuals arrive monthly, purchase orders create commitments, and an exchange program incurs EUR expenses. Treating unposted commitments as savings would understate likely year-end cost.
Columns used in the example
department · account · period · budget · actual · commitment · currency · rate_date
Follow it with your own file
- Align budget, actual, and commitment accounts and fiscal periods.
- Calculate actual variance and projected variance separately.
- Convert foreign amounts with a documented rate policy.
- Decompose material variances into volume, price, timing, scope, and unresolved drivers.
Check: Department details must reconcile to the approved budget and ledger, commitments must not be double counted after posting, and currency effects must bridge local to reporting amounts.
A method you can follow
- Align scope and mappings: Match period, scenario, account hierarchy, entity, currency, sign, and version. Reconcile both inputs before comparison.
- Calculate material gaps: Use absolute variance, percent where meaningful, and contribution to total; set thresholds by decision significance.
- Classify drivers: Test volume, rate, mix, timing, one-off, mapping, and data-quality explanations using transaction or operational evidence.
- Build the bridge and commentary: Order quantified drivers from budget to actual, reconcile the remainder, and assign owner and next action.
importance comes before commentary
Rank gaps by absolute effect and contribution to the total, not percentage alone. A 300% variance on a tiny budget may matter less than a 4% gap in a major cost line.
Use different thresholds for financial statements, operational budgets, and regulated disclosures.
Explain mechanisms, not labels
Saying travel is unfavorable only repeats the table. Useful commentary identifies who, when, why, whether it reverses, and what evidence supports the explanation.
Keep an unexplained residual visible. Forcing every dollar into a weak story makes the report less trustworthy.
Clues in the source data
- Variance totals do not equal actual minus budget.
- New accounts have infinite percentage variance.
- Favorable and unfavorable signs reverse across statements.
- A reclassification appears as offsetting departmental gaps.
Common variance lenses
| Lens | Use | Caution |
|---|---|---|
| Absolute variance | Measure financial impact | Favors large accounts |
| Percentage variance | Compare relative movement | Unstable near zero |
| Contribution to gap | Prioritize explanations | Needs a reconciled total |
| Driver bridge | Explain mechanism | Requires quantified evidence |
Final checks before using the result
- Scope reconciled
- Signs consistent
- Zero budgets handled
- Threshold documented
- Drivers evidence-backed
- Bridge equals total variance
Doing these steps in Prepyr
Upload a CSV working copy in Prepyr. Describe the result you need: Analyze budget versus actual variance with commitments, timing, and currency handled consistently. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Variance analysis · Waterfall charts · Evidence citations · Executive reporting
A concise variance narrative that distinguishes real operational changes from timing, mappings, and data issues and assigns a clear next action.
Questions readers usually ask next
How do I calculate variance?
State the sign convention. A common approach is actual minus budget, then label favorable or unfavorable based on whether the line is revenue or cost.
What if budget is zero?
Show the absolute gap and mark percentage as not meaningful rather than displaying infinity.
How many variances should be explained?
Explain items that collectively cover most of the material gap plus any control, compliance, or strategic exceptions.