Week 10 · Chapter 11 Simulations: models and modelling, simulating with a spreadsheet, random numbers and Monte Carlo, queues · 3 lessons
2
Simulating with a spreadsheet and with chance
ICT Secondary 1 Term 1, Chapter 11: pp. 142–154
GoalBuild a simulation in a spreadsheet table with a fixed rate, and use random trials to estimate a chance.
New words
relative / absolute reference · a cell address that moves when copied / one that stays fixedcompound interest · interest that is added to the base, so next year’s interest is biggersimulation · running a model many times to see what happensMonte Carlo method · estimating a quantity from many random trials
Explanation
A spreadsheet is a good place for simulations: each row is one year or one trial. Take savings of 1,000 pounds with an invented yearly interest rate of 10%. The rate is typed once, in a fixed cell. When a formula is copied down, a relative reference such as B4 moves to B5, B6 and so on, but an absolute reference such as $B$1 stays on the rate cell. So the interest in each row is the start of the year times the rate. With compound interest, each year’s interest is added to the base, so the next year’s interest is larger: after t years the amount is P × (1 + r)ᵗ, which for 1,000 pounds, 10% and 3 years is 1,000 × 1.1 × 1.1 × 1.1 = 1,331 pounds. A simulation with chance uses random numbers. A die has six equal chances. If we throw two dice, the chance that the sum is 7 is 6 out of 36, because 6 of the 36 pairs make 7: 1 + 6, 2 + 5, 3 + 4 and their reversals. That is 1/6. The Monte Carlo method estimates such a chance by repeating the experiment many times and counting. With few trials the estimate can be far off; with many it gets close.
C4fx=B4*$B$1
A
B
C
D
1
Rate
0.1
2
3
Year
Start (£)
Interest (£)
End (£)
4
1
1000
100
1100
5
2
1100
110
1210
6
3
1210
121
1331
Worked examples
Year
Start
Interest = start × 0.1
End
1
1000
100
1100
2
1100
110
1210
3
1210
121
1331
The table shows the simulation. Year 1: interest 1,000 × 0.1 = 100, end 1,100. Year 2 starts at 1,100: interest 110, end 1,210. Year 3: interest 121, end 1,331. The formula in the picture uses $B$1 for the rate, so copying it down does not move the rate.
Trial
1
2
3
4
5
6
7
8
9
10
11
12
Sum
7
3
12
7
8
8
6
7
2
10
7
7
Twelve trials of throwing two dice have been recorded in the table (the pairs are given, not random now). The sum was 7 in 5 trials out of 12, which is 0.42, far from 1/6 = 0.17. Few trials are not a safe estimate. With 600 trials the share would usually be close to 1/6.
A die was thrown 60 times; the counts of each face are in the chart: 9, 11, 10, 8, 12, 10. A fair die would give 10 of each, and the counts are close to 10, but not exactly, because chance varies.
Class activity
Continue the table to year 5 by hand (1,000 pounds, 10%, interest added each year). Then say what changes if the rate is 20%. Why would a spreadsheet keep the rate in one fixed cell?
Practice
1
500 pounds at 10% a year with compound interest: how much after 2 years?
500 × 1.1 × 1.1 = 605 pounds.
2
In the formula =B4*$B$1, which reference moves when the formula is copied down?
B4 moves (to B5, B6, …); $B$1 stays.
3
How many of the 36 pairs of two dice give the sum 4?
3 pairs (1+3, 2+2, 3+1), a chance of 3/36 = 1/12.
4
A simulation of two dice gives the sum 7 in 4 of 30 trials. Is that a safe estimate of the chance?
Not very: 4 ÷ 30 is about 0.13 and 30 trials are few. More trials would come closer to 1/6.
