Monte_Carlo_Simulation_Using_Excel_Chapter_9

Monte_Carlo_Simulation_Using_Excel_Chapter_9 - Monte Carlo...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
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 replications of possible values for the variables in your model. Worksheet-based and VBA-based approaches There are two basic approaches for developing Monte Carlo simulations in Excel: the worksheet- based approach, and the VBA approach. In this tutorial we explain only the former approach because: it is generally easier to implement, does not require use or knowledge of the Visual Basic programming language, and provides a very efficient solution to many types of problems. In this approach, you build a compact model of the problem on an Excel worksheet— typically in a single row—and then copy and paste the model (the spreadsheet row) as many times as you wish in order to generate the iterations of the model. For example, if you want to run the model 10,000 iterations to generate your data set for analysis, you would copy & paste the row containing your model 10,000 times. There are at least three significant limitations of the worksheet approach. First, the current
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 8

Monte_Carlo_Simulation_Using_Excel_Chapter_9 - Monte Carlo...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online