📎 This article includes 1 downloadable practice file ↓

📘 Excel for Finance Course · Lesson 6 of 8

A budget vs actual report answers one question: where are we off plan, and does it matter? The trick is getting favourable and adverse right, because the logic flips for costs.

Columns

Variance:    =[@Actual] - [@Budget]
Variance %:  =IF([@Budget]=0, "", [@Actual]/[@Budget] - 1)
F/A:         =IF([@Type]="Income",
                 IF([@Actual]>=[@Budget], "Favourable", "Adverse"),
                 IF([@Actual]<=[@Budget], "Favourable", "Adverse"))

Income above budget is good; costs above budget are bad. A single “positive = good” rule gets half the lines wrong.

Totals and profit

Budget profit:  =SUMIFS(T[Budget], T[Type], "Income") - SUMIFS(T[Budget], T[Type], "Cost")
Actual profit:  =SUMIFS(T[Actual], T[Type], "Income") - SUMIFS(T[Actual], T[Type], "Cost")
Cost heads over budget: =SUMPRODUCT((T[Type]="Cost")*(T[Actual]>T[Budget]))
Largest variance:       =INDEX(SORTBY(T[Head], ABS(T[Actual]-T[Budget]), -1), 1)

Formatting

• Conditional formatting on F/A: green for Favourable, red for Adverse.
• Data bars on Variance % centred at zero.
• A commentary column next to material lines: what happened, and what’s being done.

Monthly vs year-to-date

Show both: a month can look bad while the year is on track. With months in columns, YTD is =SUM($C2:C2) copied across.

Where people go wrong

Practice

Download the workbook below. Build each figure in the yellow column of the Practice sheet; the Check column turns green when it matches, and the Answers sheet has a working formula for every task.