GoalExplain what a database report does, use what-if analysis in a spreadsheet model, and tell a model from a simulation.
New words
database report · a neat summary of data from a table or query, which can group records and add countswhat-if analysis · changing a number in a model to see what happens to the resultssimulation · a model that imitates a real situation as it changes over timegroup · to put records that have the same value in a field together in a report
Explanation
A query finds records, but a report presents them. A report can put records into groups and add a calculation, such as a count for each group. Conditional formatting can then highlight a count that is too low or too high. Spreadsheets are good for what-if analysis: you change one number, such as a price, and watch how the other cells change. You can try many values until you reach a target. This is cheaper and safer than trying things out in real life. A simulation is a model that imitates a real situation over time, for example a flight trainer or a flood forecast. A model is only as good as the data and rules inside it.
Event
Entrants
Fewer than 3?
Sprint
4
no
Relay
3
no
Long jump
2
yes
Hurdles
5
no
Worked examples
Pupil
Event
Ali
Sprint
Ana
Relay
Yuki
Long jump
Aisha
Hurdles
Mia
Sprint
Omar
Relay
Ben
Long jump
Diego
Hurdles
Leo
Sprint
Chen
Relay
Ravi
Hurdles
Nodira
Sprint
Tom
Hurdles
Lola
Hurdles
A school lists the pupils who signed up for sports day events, one record for each pupil. A report groups the records by Event and counts them. An event can run only if at least 3 pupils enter, so the report uses a red highlight for any count below 3. Look at the report in the picture at the top of the lesson. Only the long jump has fewer than 3 entrants, so it would be shown in red.
B4fx=B3*(B2-B1)
A
B
1
Cost per cake
2
2
Price per cake
4
3
Cakes sold
40
4
Profit
80
A spreadsheet models a cake sale. Each cake costs 2 to make, the club sells 40 cakes, and the profit is cakes sold × (price − cost). With the price at 4 the profit is 40 × (4 − 2) = 80. The club wants to reach 100. Trying different prices is a what-if analysis.
Here are the results of four what-ifs. A price of 5 gives 120, so a price between 4 and 5 will give 100. Trying 4.50 gives 40 × 2.50 = 100 exactly. Some spreadsheet programs have a goal-seek tool that finds such a value for you.
Class activity
What-if with coins (no computer). Plan a bake sale on paper. The club makes 20 biscuit packs at a cost of 3 for each pack. Write the formula in words: profit = packs sold × (price − cost). Try three prices (4, 5 and 6) and work out the profit if all 20 packs are sold, and if only 15 are sold. Make a small table of the results. Which price would you choose, and why? Talk about what the model does not know, such as bad weather or customers who think the price is too high. If you have a computer or tablet, an adult can help you put the same model into a spreadsheet and change the price cell.
Practice
1
Nine children joined clubs. A report groups the records by Club and counts them. What counts does it show? Which clubs are highlighted by a rule that shows red when the count is less than 3?
Child
Club
Ana
Art
Omar
Chess
Yuki
Choir
Lola
Art
Ravi
Choir
Ben
Art
Mia
Chess
Leo
Choir
Aisha
Art
Art 4, Chess 2, Choir 3. Only Chess has fewer than 3, so it is highlighted. Choir has exactly 3, which is not less than 3.
2
In the cake model the price is changed to 6. What is the profit? What price gives a profit of 120?
B4fx=B3*(B2-B1)
A
B
1
Cost per cake
2
2
Price per cake
6
3
Cakes sold
40
4
Profit
?
At price 6 the profit is 40 × (6 − 2) = 160. A price of 5 gives 40 × 3 = 120.
3
Is each item a model or a simulation? Explain your choice for the second one.
Item
Model or simulation?
A spreadsheet of this month’s savings
?
A flight trainer that reacts when the pilot moves the controls
?
A program that shows how a flood spreads hour by hour
?
The spreadsheet of savings is a model. The flight trainer is a simulation, because it imitates flying and reacts as time passes and the pilot makes choices. The flood program is a simulation, because it imitates how the water changes hour by hour.
4
A town wants to test a new bridge design on a computer before building it. Give two advantages and one limit of using a simulation.
Advantages: it is safe, because nobody can be hurt, and it is cheaper, because nothing has to be built. The test can also be repeated many times with different data. Limit: it is only as good as the data and rules that were used, so real life may be different.