Off and on I would wonder if a spreadsheet illustrating a Monte Carlo type simulation would be of interest, but could never think of a suitably simple, but interesting problem to apply it to. Today, the Monty Hall problem (https://en.wikipedia.org/wiki/Monty_Hall_problem ) came across my desk, and I decided this would be a simple example to apply a Monte Carlo simulation to.
A quick description of the problem. The Monty Hall problem is based on the old "Let's Make a Deal" game show. A car is randomly placed behind one of three doors (the others have goats). The player randomly selects a door. The show host then selects one of the two doors (not the car door) that the player did not select and opens the door to reveal the goat that is behind that door. At this point, the host will give the player the opportunity to switch doors or stay with the door they originally picked. Should the player switch? Our simulation will show, as indicated by others who have studied the problem, that the player should switch, because there is a better chance of winning if they do.
The spreadsheet uses Excel's built in random number generator (the RANDBETWEEN() function specifically) to generate the random numbers that are part of each game (see columns A, B, L, P, T). All of the calculations for a single game play are arranged so they fit on a single row, and appropriate relative and absolute references are selected in these formulas. Then, the row is copied as many times as desired (I made about 1000 copies) to simulate the desired number of games. The number of wins for each scenario (stay or switch) is tallied up and the proportion of wins is calculated. Things to do:
1) You can press F9 to execute a new calculation and see how the proportion of wins changes (Repeatedly pressing F9, I see between 62% and 70% win rate for switching at 1000 simulations).
2) One could increase or decrease the number of simulations (the number of rows) to see how much the win rates change with fewer or more simulations included. How does having more or fewer rows in the calculation effect the "error" in the calculation?
3) Adjust input parameters/assumptions to see how they change. For example, what if you assumed that the car would be behind door 1 50% of the time instead of 33% of the time?
Bookmarks