Microsoft Office Tutorials and References
In Depth Information
Exhibit 8.1 Probability distribution of a fair coin toss
The analogy will produce random outcomes of the uncertain events and can be
extended to all the uncertain elements in the model.
2. The RAND( ) function in Excel is the tool we will use to perform sampling in
MCS. By using RAND() we can create a virtual bowl from which to sample.
The output of the RAND() function is a Continuous Uniform distribution, with
output greater than or equal to 0 and less than 1; thus, numbers from 0.000000
to 0.999999 are possible values. The RAND() function results in up to sixteen
digits to the right of the decimal point. A Uniform distribution is one where
every outcome in the distribution has the same probability of being randomly
selected. Therefore the sample outcome 0.831342 has exactly the same proba-
bility of being selected as the sample outcome of 0.212754. Another example of
a Uniform distribution is our fair coin toss example, but in this case the outcomes
are discrete (only heads or tails) and not continuous. Is the distribution of color-
ful stones a Uniform distribution? The answer is no since the blue stones have a
higher probability of being selected in a random sample than white or red.
We now turn to a spreadsheet model of our sampling of colorful stones. This
model will allow us to discuss some of the basic tenants of sampling. Exhibit 8.2
shows a table of 100 RAND() functions in cell range B2:K11. We will discuss
these functions in greater detail in the next section, but for now, note that cell
K3 contains a RAND() function that results in a randomly selected value of
0.2570. Likewise, every cell in the range B2:K11 is the RAND() function, and
importantly, every cell has a different outcome. This a key characteristic of the
RAND(): each time it is used in a cell, it is independent of other cells containing
the RAND() function.
3. How do we use the RAND() function to sample from the distribution of 30%
Red, 30% White, and 40% Blue? We’ve already stated that the RAND() func-
tion returns Uniformly distributed values from 0 up to, but not including, 1.
So how do we use RAND() to model our bowl of colorful stones? In Exhibit