A budget vs actual analysis in Excel compares planned financial results with recorded results, calculates the differences, and identifies which items need attention. You can build one with columns for budget, actual, absolute variance, percentage variance, and status. The worked example below uses hypothetical monthly revenue, expenses, and profit, with formulas you can copy into your own spreadsheet.
What a budget vs actual analysis shows
A budget is the financial plan for a period, while actual results are the amounts recorded after or during that period. The difference between them is called a variance. Managers use these variances to investigate performance, revise forecasts, and decide where action may be necessary.
This type of report often supports an annual operating plan, but it can also be prepared weekly, monthly, or quarterly. Monthly analysis is common because it provides regular feedback without relying only on annual totals.
The main limitation is that a variance identifies a difference, not its cause. Revenue could fall below budget because of lower sales volume, lower prices, delayed orders, or an unrealistic budget. You need operational context before reaching a conclusion.
Set up the budget vs actual analysis in Excel
Create the following headers in row 1:
- A1: Account
- B1: Budget
- C1: Actual
- D1: Absolute Variance
- E1: Percentage Variance
- F1: Result
Enter revenue on row 2, individual expense categories on rows 3 through 6, total expenses on row 7, and profit on row 8. Format columns B through D as currency and column E as a percentage.
This exercise is a practical way to develop spreadsheet skills alongside broader continuing finance education. It resembles an entry-level financial planning and analysis assignment because it combines formulas with business interpretation.
Absolute variance formula
In D2, enter:
=C2-B2
This formula defines variance as actual minus budget. A positive number means the actual amount exceeded budget, while a negative number means it fell below budget. Copy the formula down through D8.
Percentage variance formula
In E2, enter:
=IF(B2=0,NA(),D2/B2)
The calculation is: percentage variance = absolute variance ÷ budget. The IF function prevents division by zero, and NA() returns an #N/A result when no meaningful percentage can be calculated. Copy the formula down through E8.
If a cleaner presentation is more important than making the missing calculation visible, use =IF(B2=0,"",D2/B2) instead. This returns a blank cell. A zero percentage should generally not be used when the budget is zero but the actual amount is nonzero, because zero would incorrectly suggest there was no variance.
Worked monthly budget comparison
The following hypothetical example uses USD and assumes all figures relate to one month. Expenses are entered as positive amounts. Profit equals revenue minus total expenses.
| Account | Budget | Actual | Variance | Variance % | Result |
|---|---|---|---|---|---|
| Revenue | USD 100,000 | USD 92,000 | USD -8,000 | -8.00% | Unfavorable |
| Cost of goods sold | USD 40,000 | USD 38,000 | USD -2,000 | -5.00% | Favorable |
| Payroll | USD 25,000 | USD 27,000 | USD 2,000 | 8.00% | Unfavorable |
| Marketing | USD 10,000 | USD 8,000 | USD -2,000 | -20.00% | Favorable |
| Other expenses | USD 5,000 | USD 6,000 | USD 1,000 | 20.00% | Unfavorable |
| Total expenses | USD 80,000 | USD 79,000 | USD -1,000 | -1.25% | Favorable |
| Profit | USD 20,000 | USD 13,000 | USD -7,000 | -35.00% | Unfavorable |
For example, the hypothetical revenue calculation is:
Absolute variance = USD 92,000 actual − USD 100,000 budget = USD -8,000.
Percentage variance = USD -8,000 ÷ USD 100,000 = -0.08, or -8%.
The profit calculation also checks the relationship between the income statement lines. Budgeted profit is USD 100,000 − USD 80,000 = USD 20,000. Actual profit is USD 92,000 − USD 79,000 = USD 13,000. Therefore, profit is USD 7,000 below budget.
Favorable and unfavorable variances
Positive and negative are not the same as favorable and unfavorable. The interpretation depends on the type of account:
- For revenue and profit, actual above budget is generally favorable.
- For expenses, actual below budget is generally favorable.
- A zero variance is on budget, or neutral.
For the example, enter an account type in column G, using Revenue, Expense, or Profit. Then place this formula in F2 and copy it down:
=IF(D2=0,"On budget",IF(OR(G2="Revenue",G2="Profit"),IF(D2>0,"Favorable","Unfavorable"),IF(D2<0,"Favorable","Unfavorable")))
This formula treats lower expenses as favorable and higher revenue or profit as favorable. It is a useful starting rule, not a substitute for judgment. Lower marketing spending, for example, might look favorable while contributing to weaker sales. Likewise, payroll above budget could reflect overtime caused by unexpectedly strong demand rather than poor control.
Expense timing also matters. A favorable monthly variance may simply mean an invoice has not arrived. Accounting knowledge helps you determine whether the difference is economic or temporary, which is one reason a structured college finance class can complement spreadsheet practice.
Turn the figures into management commentary
Good commentary explains what changed, why it may have changed, whether the issue is temporary or recurring, and what should happen next. Avoid merely restating every number in the table.
A concise commentary for the hypothetical example could read:
Monthly revenue was USD 8,000, or 8%, below budget. Total expenses were USD 1,000 below budget, but the savings were insufficient to offset the revenue shortfall, leaving profit USD 7,000, or 35%, below plan. Lower cost of goods sold and marketing spending partly offset higher payroll and other expenses. Management should review sales volume and pricing, confirm whether marketing activity was delayed, and determine whether payroll overspending is temporary or recurring.
This wording separates observed facts from questions requiring investigation. It does not assume, without evidence, that cutting expenses caused the revenue shortfall.
Materiality should guide the review. Materiality means whether a difference is large or important enough to affect a decision. A small dollar variance may be operationally unimportant even if its percentage is large, especially when the underlying budget is small. Conversely, a modest percentage variance on revenue can have a substantial profit effect.
Improve the spreadsheet and check your work
- Use formulas for totals. In B7, enter
=SUM(B3:B6); in C7, enter=SUM(C3:C6). - Calculate profit. In B8, enter
=B2-B7; in C8, enter=C2-C7. - Reconcile the variance. The profit variance should equal the revenue variance minus the total expense variance: USD -8,000 − USD -1,000 = USD -7,000.
- Investigate source data. Confirm that the budget and actual figures cover the same dates, accounts, currency, and accounting basis.
- Add conditional formatting carefully. Use the Result column rather than coloring every negative number red, because a negative expense variance is favorable under this sign convention.
You can extend the exercise by adding prior-month actuals, a year-to-date view, or separate price and volume variances. If customer payment timing is relevant, combine profitability analysis with operating measures such as days sales outstanding, which tracks how quickly receivables are collected.
Frequently asked questions
What is the formula for budget vs actual variance in Excel?
Use =Actual-Budget. If budget is in B2 and actual is in C2, enter =C2-B2.
How do I calculate budget variance percentage?
Divide the absolute variance by the budget: =(C2-B2)/B2. Use an IF formula when the budget could be zero.
How should Excel handle a zero budget?
Use =IF(B2=0,NA(),(C2-B2)/B2) to show that the percentage is not available. You may return a blank instead, but you should not label a nonzero actual result as a 0% variance.
Is a negative variance always unfavorable?
No. A negative revenue or profit variance is generally unfavorable, but a negative expense variance is generally favorable because actual spending was below budget.
What should management commentary include?
Commentary should identify the significant variance, explain supported or possible drivers, distinguish temporary timing differences from recurring issues, and state the next investigation or action.