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 fromCalculation
yearly÷52\div 52
monthly×12÷52\times 12 \div 52
quarterly×4÷52\times 4 \div 52
fortnightly÷2\div 2

Jordan's comprehensive car insurance costs $1222 a year. How much is that per week?

Round to 2 decimal places.

Worked example

A weekly budget with costs in different periods

  • Complex familiar
  • Technology active

Jordan works full-time in a hardware store and takes home $812.40 a week after tax. His regular costs are:

ItemCost
Rent$245 a week
Phone plan$52 a month
Car registration (including CTP insurance)$910 a year
Comprehensive car insurance$1222 a year
Groceriesabout $115 a week
Fuelabout $58 a week
Entertainment and eating outabout $95 a week
Clothesabout $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?

6 steps

Try it yourself first, then check the working one step at a time.

Method

  1. Convert every amount to the same period (here, per week).

  2. Total the spending and subtract it from take-home pay.

  3. 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 ($52÷4=$13\$52 \div 4 = \$13) 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.

in \%

Round to 1 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 $52×12÷52=$12\$52 \times 12 \div 52 = \$12 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*52 turns a weekly amount in B2 into a yearly amount.
  • =C3*12/52 turns a monthly amount in C3 into a weekly amount.
  • =SUM(B2:B9) adds the amounts in B2 to B9.
  • =B11-B10 finds 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?

Choose one answer

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: fuel cost=distance100×fuel use (L/100 km)×price per litre\text{fuel cost} = \dfrac{\text{distance}}{100} \times \text{fuel use (L/100 km)} \times \text{price per litre}.

Worked example

Can Mia afford to run a car?

  • Complex unfamiliar
  • Technology active

Mia is deciding whether she can afford to run a small used car. She sets up this spreadsheet.

ABCD
1ItemCost per year ($)InputValue
2Registration (incl. CTP)910Distance (km per year)11 500
3Comprehensive insurance1080Fuel use (L/100 km)6.8
4Servicing420Petrol price ($/L)1.95
5Tyres (averaged)240
6Roadside assistance105
7Fuel
8Total

(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.

6 steps

Try it yourself first, then check the working one step at a time.

Method

  1. Use rates to turn usage into cost (distance → litres → dollars).

  2. Let the spreadsheet add the parts with a SUM formula.

  3. Convert the total to the same period as the income.

  4. 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.

in cents per km

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 ÷52\div 52, monthly ×12÷52\times 12 \div 52, fortnightly ÷2\div 2 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.

Lesson 4 of 80% read

Save your progress with a free account