A monthly variance analysis turns a budget-versus-actual report into a practical management tool. This Excel-ready approach shows how to structure income and cost categories, calculate favourable and unfavourable differences, apply materiality thresholds, record explanations and update the rolling forecast before the next review meeting.
Overview
Variance analysis compares planned results with what actually happened. A useful monthly variance report does more than display a difference: it identifies which movements matter, explains the operational reason, and shows whether the next forecast needs to change.
The basic comparison is:
Variance = Actual - Budget
For income, a positive variance is usually favourable because revenue is higher than planned. For costs, a positive variance is usually unfavourable because spending is higher than planned. This means the same mathematical result can have a different management interpretation depending on the row.
A practical variance analysis template for Excel should therefore include a direction field or a formula that recognises whether each line is income or cost. A useful monthly layout might contain:
- Reporting month
- Department or cost centre
- Category
- Type: income or cost
- Budget
- Actual
- Variance value
- Variance percentage
- Favourable or unfavourable status
- Materiality flag
- Explanation and owner
- Updated forecast
Keep the source data separate from the review sheet. One tab can hold imported budget and actual figures, another can calculate variances, and a summary tab can show the largest movements for management discussion. Separating inputs from formulas makes the workbook easier to audit and reuse.
How to estimate and calculate monthly variances
Start by standardising the monthly data. Use consistent category names, reporting periods and signs. For example, record costs as positive amounts in the cost section rather than mixing positive and negative conventions. If actuals come from different systems, reconcile totals before analysing individual lines.
Assume the following columns in an Excel table named VarianceData:
TypeBudgetActualThreshold
Use these formulas:
- Variance value:
=[@Actual]-[@Budget] - Variance percentage:
=IFERROR(([@Actual]-[@Budget])/ABS([@Budget]),0) - Favourable status:
=IF([@Type]="Income",IF([@Actual]>=[@Budget],"Favourable","Unfavourable"),IF([@Actual]<=[@Budget],"Favourable","Unfavourable")) - Materiality flag:
=IF(ABS([@[Variance percentage]])>=[@Threshold],"Review","Monitor")
The percentage formula uses the absolute value of budget as the denominator, which keeps the interpretation consistent if a cost account is presented with a negative accounting sign. If the budget is zero, the formula returns zero rather than producing a divide-by-zero error. Zero-budget rows should still be reviewed separately because any unexpected activity may be important even when a percentage cannot be calculated.
Do not rely on percentage alone. A small budget can produce a very large percentage from a modest value, while a large budget can produce a material cash impact from a small percentage. Use both a value threshold and a percentage threshold where appropriate. For example, a row could be flagged when either the absolute variance exceeds a management-set amount or the percentage variance exceeds a management-set percentage.
For a monthly management pack, sort the review list by the largest absolute value first, then consider the largest percentage movements. This keeps the meeting focused on decisions rather than on every minor fluctuation.
Inputs and assumptions
A variance model is only as reliable as its inputs and definitions. Document the following assumptions at the top of the workbook or on an Instructions tab:
- Budget basis: State whether the comparison uses the original annual budget, a phased monthly budget, or an approved reforecast.
- Actuals cut-off: Record the date on which transactions were extracted and whether late invoices, accruals or journals are included.
- Tax treatment: Use a consistent basis, such as figures excluding or including VAT, according to the reporting purpose. Do not mix the two.
- Sign convention: Explain whether income and costs are shown as positive values or follow the accounting ledger convention.
- Materiality thresholds: Set thresholds with the report owner. They should reflect the scale of the business, the decision being made and the volatility of the category.
- Forecast method: Define whether the updated forecast uses actuals plus the remaining budget, a revised monthly estimate, or a separate bottom-up forecast.
Use a notes column for the explanation, not just a colour. A good note answers three questions: what changed, why did it change, and what action follows? “Above budget” is a description, not an explanation. “Three delayed projects moved contractor costs into this month; the next two months have been reduced after confirming revised delivery dates” is more useful.
Assign an owner to material variances. Finance can maintain the calculation, but the budget holder is usually best placed to explain operational causes. Add a status such as Open, Explained, Action agreed or Closed so unresolved issues remain visible.
If your workbook also supports cash planning, compare timing separately from underlying performance. A cost may be favourable in one month because an invoice was delayed, while the full-quarter position is unchanged. A 13-week cash flow forecast template can complement the monthly performance view when payment timing is important.
Worked examples and explanation types
Suppose a monthly report contains the following rows:
| Category | Type | Budget | Actual | Variance | Interpretation |
|---|---|---|---|---|---|
| Product sales | Income | £50,000 | £54,000 | £4,000 | Favourable |
| Software licences | Cost | £3,000 | £3,600 | £600 | Unfavourable |
| Training | Cost | £2,000 | £0 | -£2,000 | Potentially favourable, but investigate timing |
The sales variance is 8% favourable. The explanation might be higher order volume, a price change, a customer mix shift or an earlier-than-expected contract. The software variance is 20% unfavourable and may result from additional users, a renewal billed earlier, or an unplanned tool. The training variance should not automatically be treated as a saving: if the session was postponed, the cost may move into a later month.
Use this comparison when deciding how to explain a material movement:
| Explanation type | Use it when | Useful follow-up |
|---|---|---|
| Price | The unit rate, supplier charge or selling price changed. | Check contracts, invoices and approved price lists. |
| Volume | The number of units, customers, hours or transactions differed. | Compare activity levels with the operational KPI. |
| Timing | The transaction occurred earlier or later than planned. | Update the remaining monthly forecast rather than treating it as a permanent change. |
| One-off | The item is unusual and unlikely to repeat. | Record the event and exclude or separately identify it in the run-rate view. |
For deeper performance context, connect financial variances to operational measures. A sales variance may be decomposed into price and volume, while a payroll variance may reflect headcount, hours, salary changes or recruitment timing. The payroll cost calculator can help separate direct pay assumptions from the wider employer cost when staffing is driving the result.
After explanations are agreed, update the forecast. A simple approach is:
Year-end forecast = Actuals to date + Forecast for remaining periods
Do not automatically carry every variance forward. Classify each movement as recurring, temporary, timing-related or one-off, then apply the appropriate treatment. For investment decisions or projects, keep return assumptions separate from routine trading variances; an ROI calculator for business investments can be used for that scenario work.
When to recalculate and review the template
Refresh the workbook each month after the accounting period is closed or the agreed management cut-off has passed. Import actuals, validate totals, recalculate formulas, review flagged rows, collect explanations and update the remaining-period forecast before distributing the report.
Revisit the model sooner when a major price changes, a supplier contract is renewed, headcount changes, a project is delayed, a new revenue stream begins, or a one-off cost occurs. These events can make the original monthly phasing or forecast assumptions unsuitable.
At the end of each quarter, review the template itself. Remove unused categories, check that thresholds still focus attention on meaningful items, confirm that formula ranges include new rows, and compare forecast accuracy with the original plan. If your underlying budget needs restructuring, the Excel budget template for UK small businesses provides a useful starting point for annual planning and monthly tracking.
Before the next review meeting, complete this checklist:
- Confirm the budget and actual data use the same period and sign convention.
- Reconcile totals to the accounting or reporting source.
- Flag both large value variances and large percentage variances.
- Separate price, volume, timing and one-off explanations.
- Assign an owner and action to each material unresolved item.
- Update the rolling forecast using current evidence.
- Save a dated version of the workbook for an audit trail.
Used consistently, a monthly variance analysis template becomes a repeatable planning cycle: measure the result, explain the movement, decide what changes, and carry the best available information into the next forecast.
