This tutorial shows how to perform a Monte Carlo simulation. A video demonstration of a basic simulation is available here.
Prior to simulation, it is assumed that you have created a spreadsheet model that has Simulation Master random variable input functions used to generate an output. We will be assuming that input variables are independent. For the tutorial on using correlated inputs, refer to this tutorial.
For this tutorial, we will use the model shown below to simulate project net present value (NPV). The random variables are shaded green and the output is cell B27.
To start the simulation, click the Run Simulation button on the ribbon. The run simulation form will appear.
The first thing to do is to enter a simulation name. The name must be unique from any other existing simulations that are present in the workbook.
Then select the cell where the model output is calculated. Click the minimize button next to the model output cell box.
Once the output cell is selected, click OK to return to the run simulation form.
The remaining simulation parameters are now entered in the form.
Histogram Columns on Output Display
This setting is for the histogram that is displayed when the simulation is finished. The histogram can be omitted by checking the do not show histogram checkbox.
Normally auto continuous bins will be used. The number of bins will be determined by Scott's rule. Each bin is equally spaced between minimum and maximum outputs.
The specify continuous bins setting can be used to set a specific number of columns. With this setting, specify the number of data bins to use which will correspond to the number of histogram columns on the output display. Each bin is equally spaced between minimum and maximum outputs.
Discrete bins are used when there is a limited number of possible outcomes. Each outcome will have its own data bin.
If all inputs to the model are on the same worksheet as the output cell, only calculating that sheet will improve performance. If variables are located on multiple sheets, check calculate all open workbooks.
Speed mode is normally used. Safe mode can be used for extremely large models with a high number of iterations to avoid running out of system memory.
Multiple simulation runs can be performed to evaluate the effect discrete decision variables have on output. If one or more discrete decision variables are present, the first simulation run will use the first value in each decision variable's range of values. The second run will use the second value and so on.
If the Run optimization check box is selected, there will only be one simulation run. The optimization page selector will appear on the left menu. For more on optimization, refer to this tutorial.
Iterations per Simulation
Enter the number of iterations to perform for each simulation run. During each iteration, a value for each random variable input is sampled and the output is calculated.
If inputs are correlated with each other, we can account for this with a correlation matrix. We are assuming that the inputs for this tutorial are independent so we will not have correlated inputs. Refer to the correlated inputs tutorial for more information.
Random Number Generation
Select the random number generator to use.
Random Number Seed
If the MCG is selected, a seed value can be used to start the pseudo-random number sequence. If the sequence needs to be repeated later the same seed value can be used to replicate the random numbers.
When all parameters are set, click the Run button to start the simulation.
When the simulation is finished, the simulation results window will appear.
We can look at probabilities of outcomes using the probability analysis tool. In this example, we want to know what is the probability that NPV is greater than zero. We enter zero in the lower set point box and click the Update button.
The probabilities will be shown in the results pane. The probability of NPV greater than zero is about 91.56%.
The red probability grab bars can also be moved across the histogram. The probability of < the left bar, probability between the bars, and the probability > the right bar are shown below the histogram. The grab bars are for a quick visual analysis of probability. For exact values, use the probability analysis boxes.