Monte Carlo simulation in Excel - how it works
Monte Carlo simulations are used in Excel to calculate probabilities. In this practical tip you can read how you can create such a simulation.
Possible uses of a Monte Carlo simulation in Excel
The Monte Carlo simulation in Excel tries to solve problems that cannot be solved analytically or only with great effort using probability theory. With this simulation, it is therefore possible to simulate and calculate complex processes, simulate static behavior and calculate distribution properties of random variables.
- This makes it possible, for example, to predict the probability of rain or snowfall.
- The non-central distribution of the correlation coefficient can be calculated in mathematics.
- You can also calculate all kinds of probabilities using Monte Carlo simulation.
- This simulation is very important for companies because it not only enables you to predict the likelihood of risks occurring, but can also improve production processes or administrative processes.
- Even gaming outcomes can be roughly predicted.
Creation of a Monte Carlo simulation in Excel
In the picture gallery we have illustrated the procedure for creating a Monte Carlo simulation based on a gambling exit step by step. The functions are given in parentheses.
In the next article, you will learn how the OR function works in Excel.
Latest videos
In the first cell, in this case A1, enter the value 0. Then create an if function with a random number in the cell below, A2. If the random number is greater than 0.5, the value above it should be added with 1, otherwise subtracted with 1. (= IF (RANDOM NUMBER ()> 0.5; A1 + 1; A1-1))
0.5; A1 + 1; A1-1)) ">0.5; A1 + 1; A1-1)) "/>
Then copy this function from A2 into cell A1000. You type A1000 in the top left (see figure) and then press [SHIFT] + [Enter] to jump to the cell. To insert the function into all cells, go to the formula bar (the function is located there) and then press [CTRL] + [Enter].
Next, calculate the average (= AVERAGE (A1: A1000)), the maximum loss (= MIN (A1: A1000)), the maximum gain (= MAX (A1: A1000)), the average deviation (= STDEV.N (A1: A1000)) and the outcome of the game (= A1000). The frame lines are only used for a better overview and are not necessary for the simulation.
Now create a what-if analysis. Mark the area for the simulation and then go to the "Data" tab and there to "What-if analysis". Select the data table.
Now you are asked from which row or column you have the values. You can leave the first field, i.e. values from the row, empty, in the second field you can insert any cell that is not used. Excel only needs this value to temporarily store the data.
This completes the what-if analysis. You will now see the outcome of 20 different games of chance.
To make the results more visible, you can also format the values to a limited extent. To do this, go to the "Start" tab and then to "Conditional formatting" and select data bars there.
Using the "Count IF" function, you can still count how many times you have won (= COUNTIF (H3: H23; "> 0")) or lost (= COUNTIF (H3: H23; "<0")). Your Monte Carlo simulation is now complete.
0 ")) or lost (= COUNTIF (H3: H23;"0 ")) or lost (= COUNTIF (H3: H23;"
0 ")) or lost (= COUNTIF (H3: H23;"