I have been playing around with simple Monte Carlo modelling systems for a while. I started doing this because I realised that if I was to design games for teaching I needed to have some idea of how long it would take the games to play! I’d built some simple VBA macros in Excel before I had come across a description of the Monte Carlo method, but having the ‘proper name’ for the concept allowed me to access other resources and ideas. It still took me a while to get past the jargon and into a sensible (if simplistic) understanding. I’m just at the beginning of my understanding of Monte Carlo methods, but it seems that Excel appears to have the sort of simple Monte Carlo modelling functionality that suit my simple purposes.
One of my ideas was to use the Snake and Ladders game as a simple activity for project management. I used the game in a teaching session about three years ago, but it didn’t work very well: I didn’t have a suitable (large sized) gaming board or a proper understanding of how the game dynamics would work. It felt very ‘experimental’ and I’m not sure how much the 4 or 5 delegates got from the activity. However, having now modeled the game via the Monte Carlo method I now have the option of teaching both Monte Carlo modelling and project management.
What is the Monte Carlo method?
In science we like to build mathematical models (I’ve written about this a few times before, for example in the case of viruses.) These maths models allow us to predict certain outcomes. One example might be Newton’s equations of motion: if you were to drop a ball off the cliff, Newton’s equations would allows you to work out what the speed of the ball would be and how far it had travelled at any point in time (assuming that air resistance could be ignored). In this case there’s only one answer to the equation: if you throw one ball off a cliff and 5 seconds later it’s travelling at 50 m/s, when you throw a second ball of the cliff after 5 seconds it too will be travelling at 50 m/s. Using these kind of equations allows us to solve problems analytically.
However, there are many processes that are far more complicated than gravity that need to be solved ‘numerically’. Snakes and Ladders is a nice example: if you start as player one, 5 moves later you may be on square 15, if you start as player two, 5 moves latter you may only be on square 9. (Completely different from throwing two balls off a cliff!) A simple set of equations cannot help you predict what square you will get to by which move, and even if equations could be found, they would need to give you a ‘range of possible squares’ you could be on after a certain number of moves. (<Afterthought> It may well be that Markov matrices may give a series of potential solutions.</Afterthought>)
In the mid-1940’s the scientists at Los Alamos (where the first atomic bombs were developed) ran into problems when trying to understand the way neutrons pass through different materials. In nuclear reactions it’s the neutrons that start and propagate the famous ‘chain reaction’ and miscalculating how neutrons interact with matter caused some near misses (called critically accidents) and fatalities (for example the so-called Demon Core) in the early days on atomic research. Some of the world’s most clever scientists struggled to develop math equations that would allow them to ‘analytically’ model how neutrons would behave. While convalesing from an illness, one of the Los Alamos’ scientists, Stanislaw Ulam, wondered what proportion of random Solitare decks could be completely solved (that’s the deck stacked again in the right order) and realised that if writing a computer program to randomly deal Solitare decks could solve that problem, the same solution could also be applied to the neutron diffusion problem. Computers were in there infancy and Ulam’s friend and colleague John von Neumann – who had developed the early computers – was able to write the Monte Carlo program. The name Monte Carlo was given to the new technique as a codeword because Los Alamos was a secret, military lab.
Spreadsheet
The Excel spreadsheet is here – Monte Carlo SnL3. As with the other projects I blog, I’d developed in my free time, so it’s here for free, under Creative Commons BY-CA (This means you may remix, adapt, and build upon this work for both commercial and non-commercial purposes, but you should credit ‘SFord’ and license new versions under the identical terms). If you do use it and you could let me know how you go on with it that would be cool.
Large scale/fast gameplay
A game of Snakes and Ladders can take a long time to play (die rolls, counting etc). I needed a fast version for class, so I built a Snake and Ladders (or more accurately a Snakes and no Ladders) simulator in Scratch (here). Clicking on the green flag will start the game (currently coded with a 1 to 6 random number) and using the slider at the bottom (annoyingly over square number 10) will speed the game up (or slow it down for suspense!)
Spreadsheet design
Column A contains the square number a ‘counter would be on’. Each cell of 60 rows is the sum of three RAND expressions (see die values in C1:D4) and value of the previous cell after any ‘penalties/bonuses’ are calcuated in column B. The RAND expressions use the equation ROUNDUP(‘no of die sides’*RAND(),0) to obtain a count for each die
Column B: This column looks at the value in the same row in column A and compares it to the table in columns E and F, adding any bonuses or penalties. It contains an errorif function so it the value from column A is not found in the table then 0 is returned. The equation is:= value from col A+IFERROR((VLOOKUP(value from col A,the s/l table,2,FALSE)),0)
Cells C1 to D4: contains the option for three dice to be rolled and for each die to have a different number of sides. Snakes and Ladders can have different scenarios for different die numbers and types. For example, two 6 sided die with produce 7 as a total number three times more often than 2 or 12. (See more about combination and permutations here.)
Columns E and F: These columns contain the squares that a snake’s mouths or ladder’s bases would be on (in column E), and the penalty or bonus awarded when that square is found in column A. There are 20 possible entries in this table (they can be expanded if needed), but if fewer than 20 snakes or ladders are required, set the extra squares to 0.
Cells A65 and E65. This is a countif equation to determine how many cells are listed before the number in column A gets to greater than 100 (ie the counter reaches the top of the board).
Columns H and I are the entries needed for the Excel What-If analysis. The details of the What-If analysis can be found in the video I used to help with with this What-If table here. The What If data table option was used, and 500 simulations were run (that the size of column H). The row input cell was ignored and an empty cell selected for the input cell.
Columns K to U contains a histogram and methods (using countif statements) for drawing the histogram. The minimum and maximum values of the list are rounded down and up to the nearest tens value to try and ‘scale the chart’ a bit better.
Other interesting/random facts
A detailed maths account of Snakes and Ladders gameplay can be found here.
Fording Life 