Budget vs actual in Excel: variance, variance % and favorable or unfavorable

A budget is a plan. The month-end numbers are what really happened. A budget vs actual report puts the two side by side, line by line, and shows how far apart they are. This page explains what the report is for, the three columns most reports add (variance, variance % and a favorable or unfavorable label), the formulas behind each one, and the mistakes that quietly turn a useful report into a misleading one.

Why accountants build a budget vs actual report

At the end of each month, the accounting team closes the books: it posts the last entries, reconciles the accounts and produces the month's results. Managers then ask a simple question. Did we do what we planned? The budget vs actual report answers it.

The report is sometimes called a variance report. A variance is the gap between the actual amount and the budgeted amount for one line. Large gaps are what managers want to see first, because they point to something worth a conversation: a sales line that fell short, a cost that ran over, or a budget that was unrealistic to begin with.

The report does not explain the gaps on its own. Its job is to make them easy to find and hard to misread. That depends on three things: a consistent sign, a percentage measured against the right base, and labels that respect the difference between revenue and expenses.

How the report works

A typical layout has one row per line of the income statement. Column A holds the line name, column B its type (Revenue or Expense), column C the budget and column D the actual amount. The report then adds its own columns.

Variance in dollars

In this lesson, variance is actual minus budget. In E5 the formula is =D5-C5, filled down to the last line. A positive variance means the actual amount came in above the budget. A negative variance means it came in below.

Not every company uses this sign. Some define variance as budget minus actual, so a positive number means "under budget". Neither convention is wrong. What matters is that the whole report uses one convention and says which one in a heading or a note, such as "Variance (actual − budget)". A reader who guesses the sign wrong will read every line backwards.

Variance as a percentage

A $1,000 variance is large on a $5,000 line and small on a $500,000 line. Variance % puts every line on the same scale by comparing the variance with what was planned:

=E5/C5, which is variance divided by budget.

The base is the budget because the question is "how far off the plan were we?" Dividing by the actual amount answers a different question and gives a different number.

Then apply a percentage format to the column. A percentage format multiplies the stored value by 100 for display only: a cell that holds 0.108 shows as 10.80%. So the formula should return the plain ratio. Do not multiply by 100 yourself.

One piece of general advice: if any line can have a budget of zero, the division fails with a #DIV/0! error. You can catch it with =IFERROR(E5/C5,""), or test the budget first with =IF(C5=0,"",E5/C5). The second form is more precise, because it only hides the one case you expect. The exercises on this page always have a budget above zero, so the plain formula is enough.

Favorable or unfavorable

A positive variance is not always good news. It depends on the line type.

• For a revenue line, earning more than budget is favorable. A positive variance is favorable; a negative one is unfavorable.
• For an expense line, spending more than budget is unfavorable. A positive variance is unfavorable; a negative one is favorable.

Because the rule flips with the type, one formula has to test the type first and then the sign of the variance. A nested IF does this cleanly:

=IF(B5="Revenue",IF(E5>0,"Favorable","Unfavorable"),IF(E5<0,"Favorable","Unfavorable"))

Read it from the outside in. If the line is revenue, a variance above zero is favorable. Otherwise the line is an expense, and a variance below zero is favorable. Because the formula reads the type from column B, the same formula works on every row, and it keeps working when someone adds a line or reorders the report.

Worked example 1: dollar variance

Copperleaf Studio compares its September results with the budget. The formula in E5 is =D5-C5, filled down. Here are five of its ten lines:

Product sales came in $5,829.81 below plan, so its variance is negative. Marketing ran $5,712.78 over, so its variance is positive. The signs tell you the direction at a glance, as long as the reader knows the report uses actual minus budget.

Worked example 2: variance as a percentage

Northside Print Shop adds a variance % column for August. Column E is =D5-C5 and column F is =E5/C5, formatted as a percentage with two decimals.

Look at Repairs and Product sales. Repairs is off by only $432.89, but that is 10.82% of a small budget. Product sales is off by $668.55, yet that is just 1.80% of a large one. The percentage column is what lets a manager see which gap is proportionally bigger.

Worked example 3: favorable or unfavorable

Pine Street Dental's July report already has the variance in column E. Column F uses the nested IF above to label each line.

Other income and Office supplies both have positive variances, but they get opposite labels. More income than planned helps the business. More spending than planned hurts it. Travel and Rent have negative variances and are favorable, because the clinic spent less than budgeted.

Common mistakes

These are the errors the exercises check for. Each one produces a sheet that looks finished but tells the reader the wrong thing.

Subtracting the wrong way: budget minus actual

=C5-D5 flips every sign. In the Copperleaf example, Product sales would show +5,829.81, which reads as "above budget" under this report's convention. If your company really does use budget minus actual, that is fine, but then the heading has to say so. Here the convention is actual minus budget.

Wrapping the variance in ABS

=ABS(D5-C5) turns every variance positive. Copperleaf's Utilities line (under budget by $2,976.07) and its Software line (over by $4,106.45) would both look like overruns. The size survives, but the direction is lost, and direction is half the point of the report.

Dividing by the actual amount

=E5/D5 measures the gap against what happened instead of what was planned. For Northside's Service revenue it gives 9.75% instead of 10.81%. The difference is small on some lines and large on others, so the error is easy to miss and hard to defend.

Multiplying by 100 and then formatting as a percent

=E5/C5*100 in a percentage-formatted cell shows Service revenue as 1080.85% instead of 10.81%. The format already multiplies by 100 for display. Return the ratio and let the format do the rest.

Ignoring the line type

=IF(E5>0,"Favorable","Unfavorable") treats every line like revenue. At Pine Street Dental, it would call the Office supplies overrun favorable and the savings on Travel and Rent unfavorable. Expense lines come out backwards. Test the type in column B first.

Reversed labels

Sometimes the structure is right but the words are swapped, so every revenue gain is "Unfavorable" and every expense saving is "Unfavorable" too. A quick sense check catches it: find one revenue line that beat its budget and confirm it says Favorable.

Budget vs actual and MO-220 practice

The MO-220 exam, Excel for Accounting, is a 50-minute, performance-based exam [Official]. Its tasks describe what a result should do rather than naming the function [Official], so you need to recognize that "label each line as favorable or unfavorable" calls for a nested IF, and that "show the variance relative to the plan" means dividing by the budget. Working through budget vs actual reports is good MO-220 practice for that kind of reading. Accounting Exercises is not affiliated with Microsoft or Certiport.