Budget vs actual report: the variance formula, favorable or unfavorable, and an Excel template

What a budget vs actual report shows

A budget vs actual report puts each P&L line next to its budget for the same period. For every line it shows the budget, the actual, the variance as an amount, the variance as a percentage of budget, and whether the variance is favorable (F) or unfavorable (U). A monthly report shows the month and the year to date side by side, because one month can be noise and the year to date shows whether it is a trend.

The report answers one question: where is profit different from plan, and by how much? Explaining why is the next step, and the report is built to make that step quick.

The variance formula and the sign rule

Variance = Actual − Budget

Variance % = (Actual − Budget) / |Budget|

The sign of the variance is the same for every line. Whether it is good depends on the line. On revenue and profit lines a positive variance is favorable. On cost lines a positive variance is unfavorable, because more was spent than planned. UK reports say "adverse" for unfavorable.

One formula handles both, reading the line's type from column B:

=IF(E5=0,"-",IF(B5="Cost",IF(E5<0,"F","U"),IF(E5>0,"F","U")))

Keep the variance itself as actual minus budget on every line. Flipping the sign for costs makes the columns impossible to add up.

Worked example: one month

The sample company's May, in USD thousands, as the template's Report sheet shows it:

Operating profit is 141 below budget: gross profit is 125 short and operating expenses are 16 over, and −125 − 16 = −141.

Flex the budget before calling a cost a saving

Cost of sales shows 40 favorable. It is not a saving. Sales were 165 lower, and cost of sales should have fallen with them. A flexible budget recomputes budgeted variable costs at the actual level of activity, so performance is judged at the volume that actually happened; OpenStax's managerial accounting text sets out the method.

Flexed budget for a variable line = budget rate × actual volume

The budget rate for cost of sales is 3,120 / 5,200 = 60% of revenue. At actual revenue of 5,035 the flexed budget is 60% × 5,035 = 3,021. Actual cost of sales was 3,080, so against the flexed budget it is 59 unfavorable, not 40 favorable. Gross margin fell from 40.0% budgeted to 38.8% actual.

The Report sheet's second block does this for every line flagged Variable = Yes on the Budget sheet: revenue at actual, variable costs at the budget rate on actual revenue, fixed costs unchanged. Against the flexed budget, operating profit is 464 budgeted and 389 actual, 75 unfavorable. The other 66 of the 141 is the profit lost on lower sales.

What is in the template

• Budget. One row per cost center and line: cost center, line, type (Revenue or Cost), variable (Yes or No) and twelve months. Two helper columns pick the selected month and the year to date:

Budget!Q5:  =INDEX(E5:P5,Report!$C$2)
Budget!R5:  =SUMIFS(E5:P5,$E$3:$P$3,"<="&Report!$C$2)

• Actual. The same shape, pasted each month from the ledger through the same account mapping as the budget.
• Report. Type the month number in C2. Each line is a SUMIFS over the helper column, so several cost centers roll up into one line:

=SUMIFS(Budget!$Q$5:$Q$44,Budget!$B$5:$B$44,$A5)

Below the month block come the flexed budget, the year to date, and cost lines by cost center. Variance % is blank when the budget is zero. In the sample, May's cost lines by cost center put the 21 over on IT and 10 over on G&A, against 28 under on Marketing.

• Commentary. Set an amount and a percentage threshold (10 and 5% in the sample). A line is flagged only when it breaches both, and each flagged line gets an owner and an explanation.
• Check. Revenue and operating profit typed from the ledger, compared with the report.

Year to date to May, the sample shows revenue 24,285 against 24,500 budgeted and operating profit 1,883 against 2,090, 207 unfavorable. May alone accounts for 141 of it.

The template uses INDEX, SUMIFS, COUNTIFS and IF only, so it opens in Excel 2016 and 2019 as well as Microsoft 365. To build the budget it reads, start from the business budget template.

Budget vs actual vs forecast

The budget is fixed before the year; a forecast is updated during it. Budget vs actual says how far the year is from plan and holds budget owners to what they agreed. Actual vs forecast says whether the latest expectation was right. Once a reforecast is approved mid-year, report both: copy the forecast into the Budget sheet's layout as a second workbook, or add its rows with a version label, and never mix the two in one column.

The check that proves it

The Check sheet must show 0 on every row:

1. Revenue and operating profit tie to the ledger. Type the trial balance figures for the month; the report must match them.
2. The variances bridge. Gross profit variance minus operating expense variance equals the operating profit variance: −125 − 16 = −141. A bridge that does not sum has a sign wrong; how to read a forecast bridge shows the same logic as a bridge chart.
3. The data sheets tie to the report. Budget and Actual totals (revenue minus costs) equal the report's operating profit, and the cost-center block adds back to total costs.
4. No orphan lines. Any Budget or Actual row whose line is not on the Report is counted. It would otherwise drop out silently.

Where it goes wrong

• Lower volume reported as a saving. Flex variable costs first, as above.
• Variance % on a small or negative budget. It explodes. Read amounts first; the template blanks the percentage when the budget is zero.
• One-twelfth of the annual budget. If the budget was never phased, every seasonal month shows a variance that is only timing.
• Timing differences. An invoice early or late shows as overspend one month and a saving the next. Mark it in the commentary and expect the reversal.
• Different mappings. If budget and actual use different account mappings, a line compares two different sets of accounts. Months must be cut the same way too; see fiscal calendars and period cuts.

Beyond the template

The report shows that IT is 21 over; the question is why. Covirage's tools join the ledger to budget, headcount and purchase-order files, flex the budget and build the variance bridge, and the external AI model drafts the commentary, never the numbers. When the report outgrows one workbook, compare the options in FP&A software alternatives for variance analysis, or see cost variance analysis: each variance split into headcount, rate, volume, timing and one-time items, with the cost centers behind it. For the method behind the variance columns, see variance analysis; for reporting against the forecast as well, budget vs forecast; for the monthly P&L beside it, the profit and loss template.