Mr Elemas

Only available on StudyMode
  • Download(s) : 55
  • Published : January 9, 2013
Open Document
Text Preview
ntroduction to Monte Carlo simulation
This article was adapted from Microsoft Office Excel 2007 Data Analysis and Business Modeling by Wayne L. Winston. Visit Microsoft Learning to learn more about this book. This classroom-style book was developed from a series of presentations by Wayne Winston, a well known statistician and business professor who specializes in creative, practical applications of Excel. So be prepared — you may need to put your thinking cap on. In this article

* Overview
* Who uses Monte Carlo simulation?
* What happens when I enter =RAND() in a cell?
* How can I simulate values of a discrete random variable? * How can I simulate values of a normal random variable?
* How can a greeting card company determine how many cards to produce? * Problems
Overview
* Who uses Monte Carlo simulation?
* What happens when I type =RAND() in a cell?
* How can I simulate values of a discrete random variable? * How can I simulate values of a normal random variable?
* How can a greeting card company determine how many cards to produce? We would like to accurately estimate the probabilities of uncertain events. For example, what is the probability that a new product’s cash flows will have a positive net present value (NPV)? What is the risk factor of our investment portfolio? Monte Carlo simulation enables us to model situations that present uncertainty and then play them out on a computer thousands of times.  NOTE    The name Monte Carlo simulation comes from the computer simulations performed during the 1930s and 1940s to estimate the probability that the chain reaction needed for an atom bomb to detonate would work successfully. The physicists involved in this work were big fans of gambling, so they gave the simulations the code name Monte Carlo. In the next five chapters, I’ll provide some examples of how you can use Microsoft Office Excel 2007 to perform Monte Carlo simulations.  TOP OF PAGE

Who uses Monte Carlo simulation?
Many companies use Monte Carlo simulation as an important part of their decision-making process. Here are some examples. * General Motors, Proctor and Gamble, Pfizer, Bristol-Myers Squibb, and Eli Lilly use simulation to estimate both the average return and the risk factor of new products. At GM, this information is used by the CEO to determine which products come to market. * GM uses simulation for activities such as forecasting net income for the corporation, predicting structural and purchasing costs, and determining its susceptibility to different kinds of risk (such as interest rate changes and exchange rate fluctuations). * Lilly uses simulation to determine the optimal plant capacity for each drug. * Proctor and Gamble uses simulation to model and optimally hedge foreign exchange risk. * Sears uses simulation to determine how many units of each product line should be ordered from suppliers—for example, the number of pairs of Dockers trousers that should be ordered this year. * Oil and drug companies use simulation to value "real options," such as the value of an option to expand, contract, or postpone a project. * Financial planners use Monte Carlo simulation to determine optimal investment strategies for their clients’ retirement.  TOP OF PAGE

What happens when I type =RAND() in a cell?
When you type the formula =RAND() in a cell, you get a number that is equally likely to assume any value between 0 and 1. Thus, around 25 percent of the time, you should get a number less than or equal to 0.25; around 10 percent of the time you should get a number that is at least 0.90, and so on. To demonstrate how the RAND function works, take a look at the file Randdemo.xlsx, shown in Figure 60-1.

Figure 60-1 Demonstrating the RAND function
 NOTE    When you open the file Randdemo.xlsx, you will not see the same random numbers shown in Figure 60-1. The RAND function always automatically recalculates the numbers it generates when a...
tracking img