About this lesson
Budget vs actual variances, in dollars and percent in Google Sheets
In Google Sheets, with costs entered as negatives, variance = actual − budget (=C2-B2) is positive when it's good news on every line. Variance % = variance ÷ ABS(budget) (=D2/ABS(B2)), so a cost overrun shows as negative. Flag it with =IF(D2>=0,"Fav","Unfav").
Using Excel? Do this lesson in Excel.
=C2-B2What you'll practise
Prepare Red Gum Hotels' first-quarter variance table: dollar and percentage variances, a favourable or unfavourable flag on every line, and the total.
4 steps · 40 points · the cells outlined in gold below
- budget vs actual
- variance analysis
- favourable and unfavourable
- ABS
- IF
The data
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Q1 FY26 ($k) | Budget | Actual | Variance | Variance % | Flag |
| 2 | Room revenue | 8,420 | 8,915 | |||
| 3 | Food and beverage revenue | 2,310 | 2,187 | |||
| 4 | Staff costs | -4,180 | -4,392 | |||
| 5 | Food and beverage costs | -760 | -731 | |||
| 6 | Energy | -395 | -448 | |||
| 7 | Marketing | -240 | -226 | |||
| 8 | Repairs and maintenance | -310 | -287 | |||
| 9 | Hotel contribution |