What it shows
A planned and an actual column are compared with formulas that give the difference and warn when spending exceeds the plan. It teaches the basics of budgeting and spreadsheet formulas.
The science
A budget is a plan for how to use money. A spreadsheet formula starts with an equals sign and uses cell addresses, so changing one number updates every answer that depends on it. SUM adds a range of cells, subtraction finds the difference, and IF shows a message when a test is true. Savings are income minus spending.
Materials
Cost: free. Many items come from home or from waste; spare parts such as LEDs, motors and wire are in the store.
- A computer, laptop or phone with a browser (free)
- A spreadsheet program: LibreOffice Calc (free) or Google Sheets (free)
- Rough figures for a made-up family for one month, or your own pocket money, in rupees
- A notebook and a pencil
Safety
This project uses safe, everyday materials. Still, read the rules below with a parent before you start.
Never use mains electricity: power models with cells, batteries or a low-voltage adapter. Sharp and hot tools, flames and lab chemicals need an adult or your teacher beside you. Keep magnets, button cells and small parts away from young children, and wash your hands after making.
All the safety rulesStep by step
8 steps. Do them in order, and test each part before you go on to the next. Write what you notice in a notebook as you go.
In cell A1 type Item, B1 Planned, C1 Actual, D1 Difference and E1 Check.
Type items down column A from A2: Rent, Food, Electricity, Transport, School, Mobile, Medicine, Other, and fill planned and actual amounts in rupees in columns B and C.
In D2 type =B2-C2 and press Enter, then drag the small square at the corner of the cell down to D9 to copy the formula.
In E2 type =IF(D2<0,"Over","OK") and copy it down to E9.
In A10 type Total, then in B10 type =SUM(B2:B9) and in C10 type =SUM(C2:C9) and D10 =B10-C10.
In A12 type Income, B12 an income amount, and in A13 Savings with B13 =B12-C10.
Select D2:D9 and use Format, Conditional Formatting to colour negative numbers red.
Change one actual amount and watch the totals, checks and savings update; note which category went over most.
What you should see
The sheet shows each item planned against actual, marks the over-budget rows, and gives the totals and monthly savings, which change instantly when you edit a number.
Viva questions
A teacher or a judge will ask how it works. Try to answer each question before you open it, then compare.
1What does a formula start with?
An equals sign.
2What does SUM(B2:B9) add?
All the numbers from B2 to B9.
3What does the IF formula do?
It shows Over when the difference is below zero, otherwise OK.
4How are savings found?
Income minus total spending.
Take it further
Add a column showing each item as a percentage of income with =C2/$B$12 formatted as percent.
Change one thing, measure what happens, and add a table or chart of your readings: that is what turns a model into your own investigation.
Free ebooks for this project
Computer & office
MS Excel Study Book
The complete study notes, practice questions and glossary for MS Excel
Computer & office
Excel Formulas Workbook
Sixty formulas and functions, each with a worked example from school, shop or office