A budget-versus-actual spreadsheet is most useful when it explains performance, not merely displays two columns of numbers. This guide shows how to build a practical Excel or Google Sheets workbook with budget, actuals, variance amounts, variance percentages, forecast-at-completion, owners, explanations, and corrective actions. You can use the structure for a department budget, project budget, or company-wide operating plan and revisit it each month as actual transactions and assumptions change.
Overview
A budget vs actual spreadsheet compares planned financial activity with what actually happened during the same period. The comparison can reveal overspending, missed revenue, delayed projects, favorable savings, or timing differences that require no permanent change to the plan.
The important distinction is between a variance report and a variance-management workbook. A basic report answers, “What is different?” A management workbook also records:
- How large the difference is in currency and percentage terms
- Whether the variance is favorable or unfavorable
- Whether it is caused by timing, volume, price, scope, or an error
- Who owns the explanation
- What corrective action is required
- How the approved explanation affects the remaining forecast
A customizable workbook can contain five tabs:
- Instructions: Define reporting periods, sign conventions, category rules, and materiality thresholds.
- Budget: Store the approved plan by month, department, project, account, or cost category.
- Actuals: Import or paste reconciled transaction totals with consistent labels.
- Variance review: Compare budget and actuals, flag exceptions, and record explanations.
- Forecast: Calculate the expected full-year or full-project result after actual performance and approved changes are considered.
Keep source data separate from review notes. This makes it easier to refresh the workbook without overwriting explanations or corrective actions. If imported files contain inconsistent dates, categories, or account names, use the Excel and Google Sheets data cleaning checklist before reporting.
How to estimate
Start with a comparison table in which each row represents one reporting unit and period. Depending on the purpose of the model, a row might be “Marketing,” “Software,” “Client Project A,” or “January payroll.” Useful columns include:
| Column | Purpose |
|---|---|
| Period | Month, quarter, or project phase |
| Department or project | Shows who or what owns the result |
| Category | Provides a consistent reporting level |
| Budget | Approved plan for the period |
| Actual | Reconciled activity recorded for the period |
| Variance amount | Difference between actual and budget |
| Variance percentage | Difference relative to budget |
| Forecast at completion | Expected total after updating assumptions |
| Owner | Person responsible for review |
| Explanation | Documented reason for a material difference |
| Corrective action | Specific response, if one is needed |
Choose and document a sign convention before adding formulas. For a cost line, the simplest calculation is:
Variance amount = Actual - Budget
Under this convention, a positive cost variance means spending exceeded plan and is generally unfavorable. For revenue, the interpretation is reversed: a positive difference may be favorable. To avoid confusing readers, add a separate status column or use labels such as “Favorable,” “Unfavorable,” and “Review.”
In Excel or Google Sheets, if Budget is in column D and Actual is in column E, a variance amount formula might be =E2-D2. A variance percentage can be calculated as =IFERROR((E2-D2)/D2,0). Format that result as a percentage. The error-handling function prevents blank or zero-budget rows from producing distracting errors, but it should not conceal a missing budget. Consider adding a data-quality flag when the budget is zero or blank.
For a simple full-period forecast, calculate:
Forecast at completion = Actuals to date + Remaining forecast
If a department has a full-year budget of 120,000, has recorded 52,000 in actual costs, and expects 71,000 in remaining costs, its forecast at completion is 123,000. The forecast variance is then:
Forecast variance = Forecast at completion - Full-period budget
This separates an already-recorded difference from an expected future difference. It also prevents the common mistake of treating an incomplete period as though all remaining activity will be zero.
Inputs and assumptions
The quality of a budget variance analysis depends more on consistent inputs than on elaborate formatting. Establish these assumptions before building charts or summary dashboards.
Use the same reporting basis
Compare like with like. If the budget is monthly, summarize actuals monthly. If the budget is assigned by project phase, do not compare it with company-wide calendar-month totals without a clear allocation method. Also document whether the workbook uses cash timing, invoice timing, or another internal reporting basis.
Map categories consistently
Actual transaction descriptions rarely match budget labels perfectly. Create a mapping table with columns for source account, standardized category, department, project, and inclusion status. Use lookup formulas or controlled dropdowns rather than repeatedly editing labels by hand. This reduces the risk that “subscriptions,” “software tools,” and “online services” are reported as unrelated categories when they belong together.
Set a materiality threshold
Not every small variance deserves a meeting. A practical threshold can combine an absolute amount and a percentage. For example, flag a line when the absolute variance is above a chosen currency amount or the variance percentage exceeds a chosen percentage. The appropriate values depend on the scale and volatility of the budget, so treat them as planning assumptions rather than universal rules.
A flag formula could follow this logic:
=IF(OR(ABS(F2)>ThresholdAmount,ABS(G2)>ThresholdPercent),"Review","Monitor")
Keep thresholds in a visible assumptions area so they can be changed without rewriting formulas. Add an exception for new categories, zero-budget items, and one-time approved purchases, since a percentage alone can exaggerate small values.
Separate timing from structural changes
A payment may be late, early, or posted in a different period without changing the expected annual result. By contrast, a price increase, staffing change, scope expansion, or permanent demand shift may require a revised forecast. Add a reason code such as Timing, Volume, Price, Scope, Classification, or One-time to make this distinction visible.
Worked examples
Department operating budget
Suppose a department budgets 8,000 for software in April and records 9,200 in actual costs. The variance amount is 1,200, and the variance percentage is 15%. The owner might explain that an annual renewal was charged in April rather than spread across the year. If the remaining annual costs are unchanged, the forecast may not need to increase. The explanation should say “timing” and identify the next expected charge, rather than simply labeling the line unfavorable.
Now consider contractor costs with a budget of 10,000 and actual costs of 13,500. The 3,500 unfavorable variance may result from additional project scope. The owner could record: “Two approved work packages moved into April; 2,000 is one-time scope and 1,500 is expected to continue.” The corrective action might be to update the remaining forecast by 1,500 and obtain approval before adding further work.
Project budget
For a project, compare budget and actuals by work package or milestone. Include planned completion dates and committed costs where possible. A project can appear under budget because invoices have not arrived, while purchase orders or approved commitments indicate that costs are still due. Add a committed-cost column when that distinction matters:
Estimated project cost = Actual cost to date + Open commitments + Remaining estimate
Use the result to support a completion forecast, not to replace transaction reconciliation. The project owner should explain whether the variance reflects delayed work, changed scope, procurement timing, or a genuine saving.
Company-wide operating plan
At the company level, summarize the detailed table by month, department, and major category. A summary section can show total revenue, total operating costs, operating result, forecast at completion, and the number of material exceptions. Link the summary to the detail rather than manually retyping totals. For revenue comparisons, pair the budget-versus-actual view with pipeline and conversion assumptions in a sales forecast spreadsheet. For cash timing, use a separate cash flow forecast, because an income or operating budget does not necessarily show when money will be collected or paid.
Use conditional formatting sparingly: highlight material exceptions, missing explanations, and overdue actions. Avoid coloring every positive or negative number, since the meaning of a variance depends on whether the row represents revenue, cost, volume, or another measure.
When to recalculate
Refresh the workbook on a regular monthly cycle, after the accounting period is closed or sufficiently reconciled. A repeatable workflow is:
- Copy the approved budget version into the protected Budget tab.
- Import actual transactions or summarized actuals into the Actuals tab.
- Standardize categories, dates, departments, and project codes.
- Reconcile totals to the relevant accounting or operating record.
- Refresh formulas, pivot tables, and dashboard summaries.
- Filter for material variances and assign an owner to each exception.
- Record an explanation that identifies cause, duration, and expected effect.
- Approve corrective actions and update the remaining forecast where assumptions changed.
- Archive the reviewed period so later edits do not erase the management record.
Recalculate sooner when pricing inputs change, benchmarks or rates move, a major contract is approved, staffing changes, project scope changes, or actual results make the existing forecast unrealistic. Review thresholds at each planning cycle: a limit that worked for a small launch budget may create too many exceptions after the business grows.
For broader planning, connect the workbook to a scenario analysis spreadsheet when management needs best-case, base-case, and downside outcomes. For recurring performance meetings, a small business KPI dashboard can summarize the most important trends while the variance workbook preserves the supporting explanations.
The goal is not to produce a more colorful report. It is to create a reliable monthly decision process: reconcile the inputs, isolate material exceptions, explain the cause, assign an owner, and update the forecast when the evidence supports a change.