Monte Carlo Simulation Tutorial
This tutorial shows how to use Microsoft Excel to develop Monte Carlo simulations without the use of add-ins or special software (such as @RISK or Crystal Ball). (Note, however, that there are some important advantages of using these dedicated software packages.)
After completing the tutorial you should have a sufficient understanding of Monte Carlo concepts and Excel capabilities to begin building your own Monte Carlo simulations applied to a wide range of business problems, including the construction of short-term profit plans (otherwise referred to as “Cost-Volume-Profit” models, covered in Chapter 9 of the text).
Monte Carlo refers to a widely used approach for solving complex problems using computer algorithms to simulate the variables in the model (e.g., a CVP model). Typically, an algorithm is developed to "model" the problem, and then the algorithm is run many times (from a few hundred up to millions) in order to develop a statistical data set for how the model behaves.
Simple Example: Tossing a “Fair” Coin—Heads vs. Tails?
For the simplest example, consider the basic coin toss. This is a process which has two possible outcomes (heads or tails), each with a 50% probability. In a million coin tosses, roughly half will be “heads” and half will be “tails.”
A simple Monte Carlo simulation would support this conclusion. If you were to develop a spreadsheet with a random number generator resulting in 0 for “heads” and 1 for “tails,” then have the spreadsheet recalculate a million times, you would see that very close to 50% of the recalculations resulted in 0 (“heads”) and the other 50% in 1 (“tails”).
The basic approach would be to build a spreadsheet with random number generators that replicate the probabilities in the model. This is the essence of simulation: you are simulating the output(s) of a model given many