Lesson 4: Budgets and spreadsheets
Can Mia afford to run a car?
SkillsPersonal budgetsSpreadsheet calculationsRates and percentages
Worked example
Mia is deciding whether she can afford to run a small used car. She sets up this spreadsheet.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Cost per year ($) | Input | Value |
| 2 | Registration (incl. CTP) | 910 | Distance (km per year) | 11 500 |
| 3 | Comprehensive insurance | 1080 | Fuel use (L/100 km) | 6.8 |
| 4 | Servicing | 420 | Petrol price ($/L) | 1.95 |
| 5 | Tyres (averaged) | 240 | ||
| 6 | Roadside assistance | 105 | ||
| 7 | Fuel | |||
| 8 | Total |
(a) Write a formula for cell B7 and calculate the yearly fuel cost. (b) Write a formula for cell B8 and calculate the total yearly cost. (c) Mia earns $420 a week from her part-time job. Calculate the weekly cost of the car and evaluate whether she can afford it.
Step 1: Write the fuel formula
Fuel used is distance fuel rate, and cost is litres price, so B7 is
=D2/100*D3*D4.Using cell references, not typed numbers, means the cost updates if Mia changes the distance or the petrol price.
Step 2: Calculate the fuel cost
L, and
Step 3: Total the yearly costs
B8 is
=SUM(B2:B7):Step 4: Convert to a weekly cost
a week
Step 5: Compare with her income
of her income, leaving a week for everything else.
Step 6: Evaluate
The car would take about a fifth of Mia's income before any car loan, parking or tolls, and costs about per km. If she lives at home with low costs, she can afford it; if she pays rent and bills from $337.69 a week, it would be very tight. The figures are estimates, so a cheaper insurer or driving less would make it more affordable.
Answer
(a) B7: =D2/100*D3*D4, fuel $1524.90 a year. (b) B8: =SUM(B2:B7), total $4279.90 a year. (c) About $82.31 a week, or 19.6% of her income. Affordable if her other costs are low (for example, living at home), but tight if she also pays rent.
Try a similar one
A fresh question of the same kind, checked as you answer, with hints and a full solution.
Method
Use rates to turn usage into cost (distance → litres → dollars).
Let the spreadsheet add the parts with a SUM formula.
Convert the total to the same period as the income.
Judge affordability by what is left for everything else, and state your assumptions.
Watch out for
Multiplying the distance by 6.8 without dividing by 100, which gives a fuel bill of $152 490.
Typing numbers into formulas (such as
=11500/100*6.8*1.95). It works once, but the sheet will not update when an input changes.