Lesson 4 of 8 · 18 min
Budgets and spreadsheets
Prepare a personal budget by converting income and spending to the same period and separating fixed and discretionary spending, then use a spreadsheet to total costs such as running a car for a year.
What you will be able to do
- Tell fixed spending from discretionary spending.
- Convert weekly, fortnightly, monthly and yearly amounts to the same period.
- Prepare a budget for a given income and find the surplus or shortfall.
- Use spreadsheet formulas such as
=SUM(B2:B8)to total a budget or the yearly cost of running a car.
A budget is a plan for your money: what comes in, what goes out, and what is left over. It starts from your net pay — your take-home pay after your employer has withheld income tax (PAYG withholding) and any other deductions — because that is the money you actually have to spend.
Definition
Fixed and discretionary spending
- Fixed spending is money you are committed to, in set amounts: rent, loan repayments, insurance, car registration, a phone plan.
- Discretionary spending is money you choose to spend and could cut: entertainment, eating out, new clothes, hobbies.
- Everyday essentials such as groceries, fuel and electricity sit in between. You can't skip them, but the amount changes, so budget a realistic average for them.
Key idea
One period for everything
Before you add or compare amounts, convert every one of them to the same period.
| To a weekly amount from | Calculation |
|---|---|
| yearly | |
| monthly | |
| quarterly | |
| fortnightly |
Jordan's comprehensive car insurance costs $1222 a year. How much is that per week?
Worked example
A weekly budget with costs in different periods
Jordan works full-time in a hardware store and takes home $812.40 a week after tax. His regular costs are:
| Item | Cost |
|---|---|
| Rent | $245 a week |
| Phone plan | $52 a month |
| Car registration (including CTP insurance) | $910 a year |
| Comprehensive car insurance | $1222 a year |
| Groceries | about $115 a week |
| Fuel | about $58 a week |
| Entertainment and eating out | about $95 a week |
| Clothes | about $65 a month |
(a) Convert every cost to a weekly amount. (b) Which items are discretionary, and how much does Jordan spend on them each week? (c) How much can Jordan save each week, and what percentage of his take-home pay is that? (d) Can he save $6000 for a trip in 26 weeks?
Step 1: Convert monthly costs to weekly
Monthly to weekly: multiply by 12, then divide by 52. Phone: . Clothes: .
A month is not 4 weeks. Going through the year (12 months = 52 weeks) keeps the conversion exact.
Step 2: Convert yearly costs to weekly
Registration: . Insurance: .
Step 3: Total the weekly spending
Step 4: Identify the discretionary spending
Entertainment and eating out, and clothes, are choices Jordan could cut: a week. Rent, the phone plan, registration and insurance are fixed. Groceries and fuel are essentials that vary, so he budgets an average for them.
Step 5: Find the savings and the percentage
Savings: a week. As a percentage:
Step 6: Test the savings goal
In 26 weeks: . That is just over $6000, so yes, but with only $16.40 to spare. One expensive week would put him behind, so trimming his discretionary spending a little would make the goal safer.
Try it yourself first, then check the working one step at a time.
Answer
(a) Rent $245, phone $12, registration $17.50, insurance $23.50, groceries $115, fuel $58, entertainment $95, clothes $15 (total $581.00 a week). (b) Entertainment and eating out, and clothes: $110 a week. (c) $231.40 a week, about 28.5% of his take-home pay. (d) Yes — $6016.40 in 26 weeks — but only just.
Method
Convert every amount to the same period (here, per week).
Total the spending and subtract it from take-home pay.
Use the surplus to test savings goals; look first at discretionary items if the budget is tight.
Watch out for
Dividing a monthly cost by 4 () instead of multiplying by 12 and dividing by 52.
Budgeting with gross pay. Tax comes out before Jordan is paid, so use take-home (net) pay.
Jordan's weekly budget at a glance. The four bars add up to his take-home pay of $812.40.
Use the bar chart. What percentage of Jordan's take-home pay of $812.40 goes on fixed costs? Give your answer correct to one decimal place.
Common mistake
Mixing periods
Adding a $52-a-month phone bill to $245-a-week rent gives a total that means nothing. Convert first: $52 a month is a week. And budget with take-home pay, not gross pay, because the tax is gone before the money reaches you.
A spreadsheet is the natural home for a budget. Each item gets a row, formulas do the conversions and totals, and when one cost changes every total updates. Typical formulas:
=B2*52turns a weekly amount in B2 into a yearly amount.=C3*12/52turns a monthly amount in C3 into a weekly amount.=SUM(B2:B9)adds the amounts in B2 to B9.=B11-B10finds the surplus: income minus total spending.
In a budget spreadsheet, cell C4 holds a monthly cost. Which formula in D4 gives the equivalent weekly cost?
One of the biggest costs for a young adult is a car, and the purchase price is only the start. Each year there is registration (in Queensland it includes compulsory third party, or CTP, insurance), comprehensive insurance, servicing, tyres, roadside assistance and fuel, plus any loan repayments, parking and tolls.
Fuel is a rate calculation: .
Worked example
Can Mia afford to run a car?
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.
Try it yourself first, then check the working one step at a time.
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.
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.
A car costs $5130 a year to own and run, and it is driven 13 500 km a year. Calculate the cost per kilometre, in cents.
Exam tip
In the exam
Label every amount with its period ("$23.50 per week"), and show the conversion. Exam questions often give a spreadsheet extract and ask for the formula in a cell or the value it shows: write the formula with cell references, starting with =, and check it against one row by hand.
Summary
A budget starts from net (take-home) pay and lists fixed spending, everyday essentials and discretionary spending.
Convert everything to one period before adding: yearly , monthly , fortnightly for weekly amounts.
Surplus = income − total spending. If the budget is tight, look first at the discretionary items.
Spreadsheets do repeated budget calculations:
=SUM(...)for totals, cell references so results update.The yearly cost of a car includes fixed costs and running costs; fuel = distance ÷ 100 × L/100 km × price per litre.
Finished the lesson?
Try the 4 quick checks above and the lesson completes itself, or mark it complete now.