What you will build
Build a small monthly budget that compares Budget with Actual by spending category and calculates Variance = Actual − Budget.
Prepare the sample data
Open the practice workbook or make a copy, then make sure A1:C5 matches the sample below.
Practice in your own copy
Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.
Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.
Open the sample workbook in Google Sheets ↗
Add the Variance column
Step 1: Enter Variance in D1. In D2 enter =C2-B2. The formula subtracts Budget from Actual, so a positive value means overspending and a negative value means spending below budget.
=C2-B2Step 2: Fill the formula from D2 down through D5. With the sample data, the variances are 0, 25, -15, and -15.
Add a totals row
Step 3: Enter Total in A6. Use =SUM(B2:B5) in B6, =SUM(C2:C5) in C6, and =C6-B6 in D6. The correct totals are Budget 920, Actual 915, and Variance -5.
Test a changed input
Step 4: Temporarily change Food Actual in C3 from 245 to 260. D3 should change to 40 and total Variance in D6 should change to 10. After checking, restore C3 to 245 to return to the sample data.
Checkpoint
| Category | Budget | Actual | Variance |
|---|---|---|---|
| Housing | 500 | 500 | 0 |
| Food | 220 | 245 | 25 |
| Transport | 90 | 75 | -15 |
| Utilities | 110 | 95 | -15 |
| Total | 920 | 915 | -5 |
If the Variance sign is reversed, confirm that the formula is Actual minus Budget (=C2-B2), not Budget minus Actual.
Common mistakes and fixes
If copied formulas keep pointing to the same row, check whether you accidentally used absolute references such as $B$2 or $C$2.
Independent practice
Add one discretionary category on a new row, update the SUM ranges to include it, and verify the total Variance again. Do not mix multiple months in the same sample table.
Key takeaways
- ✓Variance in this lesson is Actual − Budget.
- ✓Use relative references when filling the formula down by row.
- ✓The Total row should show Budget 920, Actual 915, and Variance -5 for the sample data.
- ✓Changing one input and then restoring it confirms that the formulas respond correctly.
Check your understanding
Answer four questions about the practice sheet.
Continue learning
Next lesson: Weekly task planner.
Comments
No comments yet. Be the first to join the discussion.