A Monte Carlo simulation can be developed using Microsoft Excel and a game of dice. The Monte Carlo simulation is a mathematical numerical method that uses random draws to perform calculations and complex problems. Today, it is widely used and plays a key part in various fields such as finance, physics, chemistry and economics.
Monte Carlo Simulation
The Monte Carlo method was invented by Nicolas Metropolis in 1947 and seeks to solve complex problems using random and probabilistic methods. The term "Monte Carlo" originates from the administrative area of Monaco popularly known as a place where European elites gamble. We use the Monte Carlo method when the problem is too complex and difficult to do by direct calculation. A large number of iterations allows a simulation of the normal distribution.
The Monte Carlo simulation method computes the probabilities for integrals and solves partial differential equations, thereby introducing a statistical approach to risk in a probabilistic decision. Although many advanced statistical tools exist to create Monte Carlo simulations, it is easier to simulate the normal law and the uniform law using Microsoft Excel and bypass the mathematical underpinnings.
For the Monte Carlo simulation, we isolate a number of key variables that control and describe the outcome of the experiment, then assign a probability distribution after a large number of random samples is performed. Let’s take a game of dice as a model.
Game of Dice
Here's how the dice game rolls:
• The player throws three dice that have 6 sides 3 times.
• If the total of the 3 throws is 7 or 11, the player wins.
• If the total of the 3 throws is: 3, 4, 5, 16, 17 or 18, the player loses.
• If the total is any other outcome, the player plays again and re-rolls the die.
• When the player throws the die again, the game continues in the same way, except that the player wins when the total is equal to the sum determined in the first round.
It is also recommended to use a data table to generate the results. Moreover, 5,000 results are needed to prepare the Monte Carlo simulation.
Step 1: Dice Rolling Events
First, we develop a range of data with the results of each of the 3 dice for 50 rolls. To do this, it is proposed to use the "RANDBETWEEN (1.6)" function. Thus, each time we click F9, we generate a new set of roll results. The "Outcome" cell is the sum total of the results from the 3 rolls.
Step 2: Range of Outcomes
Then, we need to develop a range of data to identify the possible outcomes for the first round and subsequent rounds. There is provided below a 3-column data range. In the first column, we have the numbers 1 to 18. These figures represent the possible outcomes following rolling the dice 3 times: the maximum being 3*6=18. You will note that for cells 1 and 2, the findings are N/A since it is impossible to get a 1 or a 2 using 3 dice. The minimum is 3.
In the second column, the possible conclusions after the first round are included. As stated in the initial statement, either the player wins (Win) or loses (Lose), or he replays (Re-roll), depending on the result (the total of 3 dice rolls).
In the third column, the possible conclusions to subsequent rounds are registered. We can achieve these results using a function “If.” This ensures that if the result obtained is equivalent to the result obtained in the first round, we win, otherwise we follow the initial rules of the original play to determine whether we re-roll the dice.
Step 3: Conclusions
In this step, we identify the outcome of the 50 dice rolls. The first conclusion can be obtained with an index function. This function searches the possible results of the first round, the conclusion corresponding to the result obtained. For example, when obtaining 6, as is the case in the picture below, we play again.
One can get the findings of other dice rolls, using an "Or" function and an index function nested in an "If" function. This function tells Excel, "If the previous result is Win or Lose," stop rolling the dice because once we have won or lost we are done. Otherwise, we go to the column of the following possible conclusions and we identify the conclusion of the result.
Step 4: Number of Dice Rolls
Now, we determine the number of dice rolls required before losing or winning. To do this, we can use a "Countif" function, which requires Excel to count the results of "Re-roll" and add the number 1 to it. It adds one because we have one extra round, and we get a final result (win or lose).
Step 5: Simulation
We develop a range to track the results of different simulations. To do this, we will create three columns. In the first column, one of the figures included is 5,000. In the second column, we will look for the result after 50 dice rolls. In the third column, the title of the column, we will look for the number of dice rolls before obtaining the final status (win or lose).
Then, we will create a sensitivity analysis table by using the feature data or Table Data table (this sensitivity will be inserted in the second table and third columns). In this sensitivity analysis, the numbers of events of 1 – 5,000 must be inserted into cell A1 of the file. In fact, one could choose any empty cell. The idea is simply to force a recalculation each time and thus get new dice rolls (results of new simulations) without damaging the formulas in place.
Step 6: Probability
We can finally calculate the probabilities of winning and losing. We do this using the "Countif" function. The formula counts the number of "win" and "lose" then divides by the total number of events, 5,000, to obtain the respective proportion of one and the other. We finally see below that the probability of getting a Win outcome is 73.2% and getting a Lose outcome is therefore 26.8%.