Microsoft Office Tutorials and References

In Depth Information

Carlo is due to the games of chance that are common in the gambling establishments

of Monte Carlo, in the Principality of Monaco. Ulam and his colleagues determined

that by using statistical sampling and performing the sampling repeatedly, they were

able to arrive at solutions to problems that would be impossible, or at a minimum,

very difﬁcult by standard analytical methods. For the types of complex simulations

we are interested in performing in Chap. 8, this approach will be extremely useful. It

will require knowledge of a number of well known probability distributions and an

understanding of the use of random numbers. The probability distributions will be

used to describe the behavior of the uncertain events, and the random numbers will

become input for the functions generating sampling outcomes for the distributions.

8.3.1 Implementing Monte Carlo Simulation Methods

Now, let us consider the basics of Monte Carlo simulation (MCS) and how we will

implement them in Excel. MCS relies on sampling through the generation of random

events. The cell function which is absolutely essential to our discussion of MCS,

RAND()
, is contained in the
Math
and
Trig
functions of Excel. In the following,

I present six steps that utilize the RAND() function to implement MCS models:

1.
Uncertain events are modeled by sampling from the distribution of the possi-

ble outcomes for each uncertain event.
A sample is the random selection of a

value(s) from a distribution of outcomes, where the distribution speciﬁes the

outcomes that are possible and their related probabilities of occurrence. For

example, the
random sampling
of a coin toss is an experiment where a coin is

tossed a number of times (the sample size) and the distribution of the individual

coin outcomes is heads with a 50% probability and tails with a 50% probability.

Exhibit 8.1 shows the probability distribution for the fair (50% chance of head or

tail) coin toss. If I toss the coin and record the
outcome
, this value is referred to

as the
resolution of an uncertain event
—the coin toss. In a model where there

are many uncertain events and many uncertain outcomes are possible for each

event, the process is repeated for all relevant uncertain events and the resulting

values are then used as a fair (random) representation of the model’s behavior.

Thus, these resolved uncertain events tell us what the condition of the model is at

a point in time. Here is a simple example of how we can use sampling to provide

information about a distribution of unknown outcomes. Imagine a very large

bowl containing a distribution of one million colorful stones: 300,000 white,

300,000 blue, and 400,000 red. You, as an observer, do not know the number of

each colorful stone contained in the bowl. Your task is to try to determine what

the true distribution of colorful stones is in the very large bowl. Of course, we can

use the colors to represent other outcomes. For example, each color could rep-

resent one of the three weather conditions of the
OLPS
problem in the previous

chapter. We can physically perform the selection of a colorful stone by randomly

reaching into the bowl and selecting a stone, or we can use a convenient analogy.

Search JabSto ::

Custom Search