Year 7 › Spreadsheets › Lesson 5 of 5Year 7 · Spring 1 · Spreadsheets Build a model: a class trip budget Topic 18 Data, spreadsheets and AI
KS3 Computing · Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, and AI learns patterns from examples.
Managing Data Cambridge Lower Secondary Computing 0860
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. Success criteria 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.
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.
SQL Structured Query Language: asks databases questions.
Machine learning The computer finds rules itself from labelled examples. 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 .
e.g. ExampleWeather forecasts and game balancing both use models. They ask 'what if' before it happens for real.
Building the trip model Put each input in its own cell. Then write formulas that point to those cells.
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.★ RememberSpreadsheets multiply before they add, just like in maths. =B2+B3*B1 works out B3*B1 first.
! Watch outNever type a number that might change inside a formula. Give it its own cell and point to it. Then the model updates by itself.
Asking what if Change the number in B1. Every formula updates at once. You do not redo any sums.
? Why?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.
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:
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.
e.g. Worked exampleThe 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. Mark this lesson as done Saved on this device.
← Previous lesson Sorting, filtering and charts End of unit → The end-of-unit challenge