Lesson 5 of 13 · 24 min
Variance reporting: favourable, unfavourable and material
By the end of this lesson you can calculate budget variances with correct signs, classify them as favourable or unfavourable for revenue and for expenses, apply a materiality threshold and report year-to-date variance with a window function.
With budget and actual side by side (previous lesson), a variance report adds three things:
| Measure | Definition in this course |
|---|---|
| Variance | actual − budget, both signed in the account's natural direction |
| Variance % | variance ÷ budget × 100 (undefined when the budget is 0) |
| Assessment | favourable or unfavourable, depending on account type |
The sign of the variance alone does not tell you whether it is good news:
- Revenue: actual above budget is favourable (variance > 0 is good).
- Expenses: actual above budget is unfavourable (variance > 0 is bad); spending less than planned is favourable.
That is why a single rule such as positive = favourable is wrong for half of the P&L. Write the rule per account type with CASE.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons