What it shows
A running balance formula adds income and subtracts spending on every row, and a line chart shows how the balance changes over the month. It teaches saving habits and cell references.
The science
A running balance adds up all earlier results: today’s balance equals yesterday’s balance plus money in minus money out. In a spreadsheet this uses a reference to the cell above. A line chart is the best choice for values that change over time. Looking at the lowest point shows how close you came to running out.
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)
- Your own record of pocket money received and spent for a month, 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 A1 type Date, B1 Money in, C1 Money out, D1 Note and E1 Balance.
From row 2 enter each day’s record, for example 1 Oct, 100, blank, Pocket money.
In E2 type =B2-C2 for the first balance.
In E3 type =E2+B3-C3 and drag the fill handle down so every row refers to the one above it.
In G1 type Total in and in H1 =SUM(B2:B32), then in G2 type Total out and H2 =SUM(C2:C32).
In G3 type Lowest balance with H3 =MIN(E2:E32), and in G4 type Average spend with H4 =AVERAGE(C2:C32).
Select columns A and E and insert a Line chart titled Balance through the month.
Look at the lines for big dips and write one saving goal, such as keeping Rs 20 unspent each week.
What you should see
The balance column updates on every entry, the totals and lowest balance are shown, and the line chart makes spending dips easy to see.
Viva questions
A teacher or a judge will ask how it works. Try to answer each question before you open it, then compare.
1What is a running balance?
A total that is updated after every entry.
2Why does E3 refer to E2?
The new balance builds on the previous balance.
3Which chart suits change over time?
A line chart.
4What does MIN(E2:E32) tell you?
The lowest balance in the month.
Take it further
Add a savings goal cell and a formula that shows how many days it will take to reach it at the average saving rate.
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