CHAPTER4 Selecting Crystal Ball Assumptions
his chapter reviews basic concepts of probability and statistics using graphics from Crystal Ball’s distribution gallery, a portion of which is shown in Figure 4.1.
If you have not had a class in basic probability and statistics at some point in your life or you need a refresher on these topics, consult a business statistics textbook such as Mann (2007). This chapter is intended to show the basics of how to specify probability distributions to be used as stochastic assumptions with Crystal Ball.
Version 7.2 of Crystal Ball has 20 distributions from which to choose when deﬁning assumptions. To see them, click the All button at the upper left of the distribution gallery.Sixbasicdistributions aredescribedherealongwiththebinomial distribution.
CRYSTAL BALL’S BASIC DISTRIBUTIONS
Yes-No
Probabilists named the Bernoulli distribution in honor of the mathematician who showed analytically around 1700 the truth of the intuitive notion that when a fair coin is tossed repeatedly, it will come up heads about 50 percent of the time. It is perhaps the simplest of all probability distributions. The random variable Y has the Bernoulli distribution if it can take only one of two possible values, y = 0 or y = 1. The value y = 1 is called a ‘‘success,’’ and y = 0 is called a ‘‘failure’’ in probability parlance. In Crystal Ball, the Bernoulli distribution is known as the yes-no distribution.
Crystal Ball calls y = 1 ‘‘yes’’ and y = 0 ‘‘no’’ because these terms often make sense in a modeling context. For example, Figure 4.2 shows Crystal Ball’s yes-no distribution for Pr(yes) = 0.5, where y represents the number of heads obtained in one toss of a fair coin. ‘‘Yes’’ means a head was tossed so y = 1, while ‘‘no’’ means a tail was tossed so y = 0.
NowconsiderthetypeofsituationthatdrewBernoulli’sinterest.Thespreadsheet segment in Figure 4.3 shows a simple model to be used for ﬁnding the number of heads observed when tossing a fair coin ﬁve times. Each of the assumptions in cells B3:B7 are yes-no distributions with Pr(yes) = 0.5, so each assumption cell will
36
Selecting Crystal Ball Assumptions 37
FIGURE 4.1 The basic distributions listed in Crystal Ball’s distribution gallery.
contain 1 on approximately 50 percent of the trials and 0 on the remaining trials. Each assumption cell’s value is generated independently of the other cells’ values. The forecast in cell B8 has the formula =SUM(B3:B7).
Of course, we need not use simulation to model this situation because it is easy to determine the forecast distribution analytically. However, simulating a situation for which we know the analytical solution can be comforting. If we get results with simulation that are in accord with the analytical results, then we have some assurance that simulation will provide good approximate answers to questions regarding situations where analytical results are difﬁcult or impossible to attain.
For a simple example of ﬁnding an analytical result, consider the spreadsheet model FiveTosses.xls shown in Figure 4.4, which shows each of the 25 = 32 combinations of 0s and 1s that can occur on ﬁve tosses of a fair coin. Each combination is equally likely to occur. The number of heads in each combination is
38 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL
FIGURE 4.2 Yes-no distribution to represent getting a head (y = 1) on one toss of a fair coin.
FIGURE 4.3 Spreadsheet segment showing model for determining the distribution of ﬁve ﬂips of a fair coin. Cells B3:B7 are yes-no(0.5) assumptions, and their sum in cell B8 is a Crystal Ball forecast.
Selecting Crystal Ball Assumptions 39
FIGURE 4.4 Spreadsheet segment showing model for determining the distribution of ﬁve ﬂips of a fair coin. Cells B3:B7 are yes-no(0.5) assumptions, and their sum in cell B8 is a Crystal Ball forecast.
found by summing across the row for each combination. So to ﬁnd the probability of each of the possible numbers of heads, we simply divide the frequency of occurrence of {0,1,2,3,4,5} by 32, the total number of combinations to get the probabilities listed in cells C11:C16 in Figure 4.4. These are the probabilities associated with the binomial(0.5,5) distribution used below.
Binomial
While not included in Crystal Ball’s distribution gallery list of basic assumptions, the binomial distribution is so closely related to the yes-no distribution that it is included here and used later in the chapter. The binomial(p,n) is the distribution of
40
FIGURE 4.5
FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL
Binomial(0.5,5) distribution to represent the number of heads on ﬁve tosses of a fair coin.
the sum of a ﬁxed number, n, of Bernoulli trials that all have the same probability of success, p. Thus, the problem of determining the distribution of the number of heads in ﬁve tosses of a fair coin can be solved by using one Crystal Ball assumption—the binomial(0.5,5) assumption shown in Figure 4.5.
Figure 4.6 depicts a model that gives the same results as that in Figure 4.3 by using Crystal Ball to simply generate the number of heads in ﬁve tosses from the distribution in Figure 4.5, and displaying the results in the forecast deﬁned in cell B4 with the Excel formula =B3. The forecast distribution in Figure 4.6 looks almost identical to the forecast distribution in Figure 4.3, because the differences are due only to sampling error.
Discrete Uniform
The discrete uniform(L,H) distribution assigns equal probability to the set of integers between L and H, inclusive. For L = 1 and H = 6, it is the probability distribution representing the number of spots showing on the top face of a fair die rolled randomly. To illustrate the use of the discrete uniform, consider a problem with which Sir Isaac Newton dealt in the seventeenth century (Andel 2001).
The problem can be stated as follows:
Player A has 6 fair dice and wins if he rolls at least one ace (one spot showing on the top face of a die).
Player B has 12 fair dice and wins if he rolls at least two aces. Player C has 18 fair dice and wins if he rolls at least three aces.
Which player has the greatest chance of winning?
...
- tailieumienphi.vn