Budget vs actual template (free Excel download)

What this budget vs actual template does

A budget vs actual template is a spreadsheet that compares the results a business actually achieved with the budget it set, line by line, and shows the difference as a variance. This free Excel template covers 12 months, calculates month and year-to-date variances in money and as a percentage, flags each one favourable or adverse, and highlights the variances large enough to explain.

It is the report most management meetings start from. Our guide on budgeting and variance analysis covers the theory, including flexible budgets and price and volume variances; this page is about producing a clean, reliable budget vs actual report every month and reading it correctly.

What is inside the workbook

The workbook has six sheets. You type into three of them; the other three are reports.

• How to use: steps, how variances are calculated and the limitations.
• Settings: the first month of the financial year, the latest month with actuals, and two significance thresholds, an amount and a percentage.
• Budget: up to 20 lines, each with a group (Revenue, Cost of sales or Operating expenses) and a budget for each of the 12 months, with subtotals for revenue, cost of sales, gross profit, operating expenses and operating profit.
• Actual: the same layout; line names and groups copy across from the Budget sheet so the two can never drift apart.
• BvA report: for the selected month and for the year to date, budget, actual, variance, variance percent, a favourable or adverse flag and a significance flag, plus full-year budget, remaining budget and a commentary column.
• Monthly variances: actual minus budget for every line and every month so far, coloured green for favourable and red for adverse, with a check that the year-to-date total agrees to the report.

How the variances are calculated

Variance is always actual minus budget, and both are entered as positive numbers for revenue and for costs. That single convention keeps the arithmetic simple; the template then decides what the sign means. For revenue and profit lines a positive variance is favourable, because the business earned more than planned. For cost lines a negative variance is favourable, because it spent less. A zero variance is marked On budget.

Variance percent is the variance divided by the budget. It is left blank when the budget is zero, because a percentage of nothing is meaningless. Year to date is the sum of months 1 to the latest month with actuals, calculated with SUMIFS on the month number, so changing one cell in Settings moves the whole report forward a month. Remaining budget is the full-year budget less the year-to-date actual, which for a cost line is what is left to spend and for a revenue line is what is still to be earned.

How to fill it in, step by step

The set-up is done once a year when the budget is approved; after that, each month takes a few minutes of pasting and a longer time thinking about the commentary.

• On Settings, enter the first month of your financial year and the significance thresholds your management team agrees on.
• On Budget, list your lines using the same groupings as your management accounts, choose a group for each, and enter the approved monthly budget.
• Phase the budget realistically: seasonal sales, annual fees and pay rises belong in the months they happen, not spread evenly.
• After each month is closed, paste the actuals from the trial balance or management accounts into the Actual sheet, as positive numbers.
• Change the latest month with actuals on Settings.
• On the BvA report, write a commentary for every line flagged as significant: what happened, whether it will reverse, and what action is being taken.
• Leave future months blank on the Actual sheet; blanks are treated as nothing, not as zero spending to celebrate.

The worked example: June year to date

The example is a fictional trading company with a calendar financial year, reporting in EUR at the end of June 2026. For June alone, product sales were 96,000 against a budget of 100,000, an adverse variance of 4,000 or 4.0 percent. Year to date, however, product sales were 571,900 against 570,000, a favourable 1,900. Reading only the month would suggest a problem; reading only the year to date would hide a weak June. The template shows both side by side for that reason.

Year-to-date revenue was 707,325 against 703,500, a favourable 3,825. Cost of sales was 388,073 against 378,900, an adverse 9,173, so gross profit was 319,252 against 324,600, an adverse 5,348. Operating expenses were 307,641 against 301,840, an adverse 5,801. Operating profit was therefore 11,611 against a budget of 22,760, an adverse variance of 11,149, or 49.0 percent of the budgeted figure.

With thresholds of 1,000 and 10 percent, two variances are flagged as significant: professional fees, 14,700 against 13,000 (an adverse 1,700 or 13.1 percent), and operating profit itself. The largest money variance, cost of goods sold at an adverse 6,764, is not flagged because it is only 2.2 percent of budget, and that is a reminder that thresholds guide attention but do not replace judgement.

Reading the variances correctly

A favourable cost variance is not always good news. In June the example's cost of goods sold was 53,760 against 55,000, a favourable 1,240, but only because sales volume fell. Compare cost variances with revenue before praising the purchasing team.

The cost of goods sold variance for the half year shows how much a small rate change matters. The budget assumed cost of goods sold at 55 percent of product sales; actual was 56 percent. Of the 6,764 adverse variance, 1,045 is explained by the higher sales volume (55 percent of the extra 1,900 of sales) and 5,719 by the one-point margin erosion on 571,900 of sales. That split is a flexible-budget view: it separates what happened because the business was busier from what happened because each sale earned less.

Timing variances deserve a comment but not alarm. A marketing campaign run in May instead of June produces an adverse May and a favourable June that cancel out; the year-to-date column shows whether they have.

Writing variance commentary that helps

Variance commentary is what turns a report into a decision. Good commentary is short, specific and forward-looking, and it states whether the variance is permanent or timing. 'Professional fees over budget' repeats the number; 'Professional fees 1,700 over year to date: legal advice on a supplier dispute in May, not budgeted, no further cost expected' explains it.

Ask the budget holder, not the accountant, to write the first draft. The accountant knows what was posted; the budget holder knows why. Then keep the commentary with the report each month, so next month's review starts from what was promised last time.

Common mistakes in budget vs actual reports

Most of these mistakes make the variances look better or worse than they are, which is worse than having no report at all because it misdirects attention.

• Comparing actuals with a budget spread evenly over 12 months when the business is seasonal.
• Mixing sign conventions, entering costs as negatives in one place and positives in another.
• Comparing a closed month with an unclosed one, before accruals and depreciation are posted.
• Grouping actuals differently from the budget, so a cost moves lines and creates two offsetting variances.
• Explaining every variance, however small, until nobody reads the commentary.
• Reforecasting by overwriting the budget, which destroys the baseline you are measuring against.
• Treating future months with no actuals as zero and reporting them as favourable.

Budget, forecast and reforecast

The budget is the plan approved before the year starts and stays fixed as the yardstick. A forecast is the current best estimate of how the year will end, updated as the year goes on. Many teams keep both: actual versus budget shows performance against the plan, and actual year to date plus forecast for the remaining months shows where the year is heading.

To add a forecast to this template, copy the Budget sheet, name it Forecast, and replace the months still to come with your latest estimate. Do not overwrite the Budget sheet. If the plan changes so much that the original budget is no longer useful, approve a revised budget formally and keep the original for reference.

Adapting the template

For departments or cost centres, make one copy of the workbook per budget holder and a summary copy for the whole business; each budget holder then sees only the lines they control, which is where accountability for variances belongs. For a financial year that does not start in January, change the first month on Settings and every month heading and year-to-date total follows.

If you report below operating profit, add finance costs and income tax as further lines in the Operating expenses group, or rename a group and extend the subtotals; keep the rule that revenue and profit lines are favourable when higher and cost lines when lower. To show quarters, sum three monthly columns on a separate sheet rather than changing the monthly layout, because the year-to-date formulas rely on month numbers 1 to 12.

Every formula in the workbook was recalculated by two independent spreadsheet engines and compared with a separate calculation for every line, for June and for a March scenario, including the subtotals and the check that the monthly variances add up to the year-to-date operating profit variance.

Doing this in Skyline Nexus ERP

In Skyline Nexus ERP, budgets are held in the Fiscal Authority module under Budgets. A New Budget has a Budget Name, a Fiscal Year, a Budget Type of annual, quarterly or monthly, an optional Cost Center, and Budget Lines per account across the year's periods, with a Distribute Evenly option for lines that are genuinely flat. Budgets are saved as a draft or submitted for approval, and they can be imported from XLSX, XLS or CSV using a download template, so a budget built in a spreadsheet does not have to be retyped.

The Budget vs Actual report filters by fiscal year, account and cost centre and shows Total Budget, Actual Spent, the Variance as over or under, Utilization, a trend chart and a status of Over Budget, Warning or On Track. Because the budget lines are held against the same ledger accounts the business posts to, there is no monthly export and paste step. Cost centres, set up under Cost Centers with a Cost Center Analysis report, let each budget holder see their own lines.