Year 7 · Spring 1
Spreadsheets · Lesson 5 of 5
Build a model: a class trip budget
ProjectTopic 18 Data, spreadsheets and AI
Year 7 · Spreadsheets · Lesson 5 of 5
You will learn to
- Explain what a spreadsheet model is and why it is useful.
- Create a trip budget with each input in its own cell and formulas that use them.
- Use the model to answer a what-if question, like 'What if 5 pupils drop out?'
- Find the number of pupils or the price that makes the trip break even.
Year 7 · Spreadsheets · Lesson 5 of 5
Success criteria
You have got it when you can say:
- I can change the number of pupils and see the total update by itself.
- I can point to the input cells and the formula cells in my model.
- I can find the break-even point and explain what it means.
Year 7 · Spreadsheets · Lesson 5 of 5
Retrieval starter
Questions from before. Write your answers, then show them.
- Last lesson
=B2*C2 in D2 is filled down to D5. What is in D5?
A =E2*F2B =B2*C2C =B5*C5
C. =B5*C5
- Last unit
Packets arrive out of order. How are they fixed?
A Sequence numbersB The router sorts themC They're sent again
A. Sequence numbers
- Earlier this year
A shop keeping your data for 20 years 'just in case' breaks…
A Data protection lawB The Computer Misuse Act
A. Data protection law
Year 7 · Spreadsheets · Lesson 5 of 5
Key words (1 of 2)
- Formula
- A spreadsheet calculation. Always starts with =.
- Function
- A subroutine that returns a value.
- Record / field
- One row in a database table: everything stored about one thing.
- Primary key
- A field whose value is unique for every record.
Year 7 · Spreadsheets · Lesson 5 of 5
Key words (2 of 2)
- SQL
- Structured Query Language: asks databases questions.
- Machine learning
- The computer finds rules itself from labelled examples.
The explanation · step 1 of 4 · part 1 of 2
What a model is
A model is a spreadsheet that copies a real situation with numbers and formulas.
You change one number. The spreadsheet works out the rest. This is called asking what if.
| Part | What it is | Trip example |
|---|---|---|
| Inputs | Numbers you can change | Number of pupils, coach cost, entry price |
| Formulas | The rules that link the numbers | Total = coach + entry × pupils |
| Outputs | The answers you want | Total cost, cost per pupil |
The explanation · step 1 of 4 · part 2 of 2
What a model is (continued)
Weather forecasts and game balancing both use models. They ask 'what if' before it happens for real.
The explanation · step 2 of 4 · part 1 of 3
Building the trip model
Put each input in its own cell. Then write formulas that point to those cells.
| Row | Column A (label) | Column B (value or formula) |
|---|---|---|
| 1 | Number of pupils | 30 |
| 2 | Coach (whole trip) | 450 |
| 3 | Entry per pupil | 12 |
| 4 | Total cost | =B2+B3*B1 |
| 5 | Cost per pupil | =B4/B1 |
The explanation · step 2 of 4 · part 2 of 3
Building the trip model (continued)
B3*B1= 12 × 30 = 360. That is the entry for everyone.B2+ 360 = 450 + 360 = 810. The total cost is £810.B4/B1= 810 ÷ 30 = 27. Each pupil costs £27.
Spreadsheets multiply before they add, just like in maths. =B2+B3*B1 works out B3*B1 first.
The explanation · step 2 of 4 · part 3 of 3
Building the trip model (continued)
Never type a number that might change inside a formula. Give it its own cell and point to it. Then the model updates by itself.
The explanation · step 3 of 4 · part 1 of 2
Asking what if
Change the number in B1. Every formula updates at once. You do not redo any sums.
| Pupils (B1) | Total cost (B4) | Cost per pupil (B5) |
|---|---|---|
| 20 | 450 + 240 = £690 | £34.50 |
| 25 | 450 + 300 = £750 | £30.00 |
| 30 | 450 + 360 = £810 | £27.00 |
| 40 | 450 + 480 = £930 | £23.25 |
The explanation · step 3 of 4 · part 2 of 2
Asking what if (continued)
Why does each pupil pay less when more go?
The coach costs £450 however many pupils go. More pupils share it, so each share is smaller.
The explanation · step 4 of 4 · part 1 of 2
Breaking even
Breaking even means the money coming in equals the money going out. There is no profit and no loss.
Add three rows to the model:
| Row | Column A (label) | Column B (value or formula) |
|---|---|---|
| 6 | Price we charge each pupil | 25 |
| 7 | Money in | =B6*B1 |
| 8 | Money in minus cost | =B7-B4 |
The explanation · step 4 of 4 · part 2 of 2
Breaking even (continued)
- B8 below 0: the trip loses money.
- B8 equal to 0: the trip breaks even.
- B8 above 0: there is money left over.
Change the price or the number of pupils until B8 reaches 0 or just above it.
Year 7 · Spreadsheets · Lesson 5 of 5
Worked example
The school charges £25 per pupil. How many pupils must go so the trip does not lose money?
- Put 25 in B6. Try 30 pupils in B1: money in = £750, cost = £810. B8 = -£60, a loss.
- Try 34 pupils: money in = 25 × 34 = £850, cost = 450 + 12 × 34 = £858. B8 = -£8, still a loss.
- Try 35 pupils: money in = 25 × 35 = £875, cost = 450 + 12 × 35 = £870. B8 = £5.
- So at least 35 pupils must go. With 34 the trip loses £8.
Year 7 · Spreadsheets · Lesson 5 of 5
Quick quiz · 1 of 3
Which field makes a good primary key?
- A FirstName
- B House
- C StudentID
C. StudentID
It is unique for every student and doesn't change.
Year 7 · Spreadsheets · Lesson 5 of 5
Quick quiz · 2 of 3
To show change over time, use a…
- A Line chart
- B Pie chart
- C Scatter graph
A. Line chart
A line joins the points in time order, so you see the trend.
Year 7 · Spreadsheets · Lesson 5 of 5
Quick quiz · 3 of 3
SELECT FirstName FROM Students WHERE Year = 8; returns…
- A The number of Year 8 students
- B Only the first names of Year 8 students
- C Every field of every student
B. Only the first names of Year 8 students
WHERE keeps the Year 8 records. SELECT keeps only the FirstName field.
Year 7 · Spreadsheets · Lesson 5 of 5
Exit ticket
Answer on paper before you go. Then check.
A face-recognition model works badly on faces unlike its training photos. This is…
A OverflowB Bias from unrepresentative training dataC A syntax error
B. Bias from unrepresentative training data The model only learns what it is shown. Missing groups in the data means worse results for those groups.
Every spreadsheet formula starts with…
A $B =C #
B. = The = tells the spreadsheet: calculate this, don't just show it.
Year 7 · Spreadsheets · Lesson 5 of 5
Homework and the challenge
Homework
Study page 18 steps 1 to 5 and the SQL toy (a first look).
End-of-unit challenge
Repair the broken formulas in a game-shop inventory sheet, then answer questions from it.