Week 15 · 2.1 Spreadsheets and analysing data · 3 lessons
1
Is the model worth it?
Computing 9: Topic 2.1, pp. 168–197
GoalWeigh the benefits and drawbacks of a spreadsheet model, and test it with ordinary and unusual data.
New words
model · a representation of a real object or system, used to study it or to predict how it will behavewhat-if analysis · changing one value in a model to see how the results changescenario · one situation, with its own set of values, that is tried in a model
Explanation
A model is a simplified copy of something real. It can be a physical object, such as a model boat, or a set of numbers and formulas in a spreadsheet. People use models because trying an idea on a model is cheaper and safer than trying it for real, and because you can ask what-if questions again and again. Models also have drawbacks. Testing takes time and sometimes money, and a model cannot copy everything, especially when nobody knows enough about the real thing. A model can also be wrong in a place where it was not built to be used. The results of a model can be no better than the data put into it, so test it with ordinary values, extreme values and unusual values, and check that the results are sensible.
Benefits of a model
Drawbacks of a model
cheaper than a real trial
takes time to build and test
safer for people and equipment
testing may cost money
what-if questions are quick
not enough is known about some real things
mistakes show up before building
may fail in places it was not designed for
Worked examples
B4fx=B2*B3
A
B
1
Item
Value
2
Roof area (m²)
40
3
Rain (mm)
25
4
Water collected (litres)
1000
A tank model. A school wants to know how much rainwater a roof can collect. One millimetre of rain falling on one square metre of roof gives one litre. The roof is 40 m² and the rain is 25 mm, so the model in B4 calculates 40 × 25 = 1000 litres.
Scenario
Roof (m²)
Rain (mm)
Litres collected
dry spell
40
0
0
light rain
40
10
400
usual rain
40
25
1000
storm
40
60
2400
What-if questions. Only the rain in B3 is changed each time, and everything else stays the same. The results are 400 litres for 10 mm, 0 litres for 0 mm, and 2400 litres for 60 mm. Changing one value at a time shows which input has the effect.
B5fx=B2*B3
A
B
1
Item
Value
2
Roof area (m²)
40
3
Rain (mm)
25
4
Tank size (litres)
800
5
Water collected (litres)
1000
Testing finds a weakness. The school’s tank holds only 800 litres, but the model says that 1000 litres were collected. The model does not know that a tank has a limit, so it gives an impossible answer. A model that is tested with many scenarios shows up gaps like this before anybody trusts it.
Class activity
Model the classroom (no computer). Build a small model on paper: a table that shows how many glasses of water a class of pupils drinks in a day, from the number of pupils and the glasses per pupil. Try three what-if scenarios by changing one number at a time. Then list two things that your model leaves out, such as visitors or hot days, and decide whether leaving them out matters.
Practice
1
Use the tank model. The roof is 60 m² and the rain is 15 mm. How many litres are collected? What if the rain doubles? What if the roof area is halved and the rain is 15 mm?
B4fx=B2*B3
A
B
1
Item
Value
2
Roof area (m²)
60
3
Rain (mm)
15
4
Water collected (litres)
?
60 × 15 = 900 litres. If the rain doubles to 30 mm the model gives 60 × 30 = 1800 litres. If the roof is halved to 30 m², it gives 30 × 15 = 450 litres.
2
Say whether each is a benefit or a drawback of using a model. (a) Testing a new layout for a school pond on a model costs very little. (b) Little is known about a rare insect, so the model is partly guesswork. (c) One hundred settings can be tried in an hour. (d) A model of a pond in summer is used to predict the pond in winter.
(a) benefit: cheaper than a real trial. (b) drawback: too little knowledge. (c) benefit: quick what-if questions. (d) drawback: the setting is not the one the model was designed for.
3
A learner tests the tank model with a 40 m² roof and 500 mm of rain, and the model says that 20000 litres were collected by an 800-litre tank. What is wrong, and what should the learner do next?
The result is impossible for a tank of 800 litres, because the model has no limit for the tank size. The learner should write the problem down, change the model so that it stops at the tank size, and test it again, including with extreme values such as 0 and very large rainfall.
4
Why is it not enough to test a model with only usual values?
Usual values may all work, while extreme or unusual values show where the model breaks. Without those tests, nobody knows how it behaves in the situations where mistakes matter most.