Download Exel File When building models we frequently have need to represent a non-deterministic phenomenon. That is, our model includes something that happens sometimes and sometimes does not.
The simplest version of this would be something that we know occurs X% of the time on average.
Let's stay in our discrete world where everything happens step-by-step, one clock tick at a time. I'm working at a youth diversion program and months and months of data suggest that during an eight hour evening shift an average of 5 kids show up but they do so at an apparently random schedule.
I've been asked to build a model of our work load that breaks the shift up into 15 minute pieces. I want a spread sheet that will capture the random arrival of, on average, 5 kids per night.
If I expect 5 to arrive during the 8 hour shift, what's the probability that one will arrive during a given 15 minute interval? How many such intervals in an 8 hour shift? 32.

Pr(arrival during 15 minute window)=5/32=0.156

image001.gif

Remember how we calculate expected value. It's the probability of an event times the "payoff" of the event times that frequency with which we give it a spin. Here that would mean

Ex(32)= 32 (fifteen minute periods)/shift × 5/32 chance/period×1 arrival/chance=5arrivals/shift

image002.gif

We can implement this with a magic coin that we can flip – heads someone arrives, tails no one arrives – one that's weighted so it comes up heads 5/32 of the time.

image003.gif

We can implement this with an Excel "if()" function and the function "rand()" which always produces a random number between the value of 0 and 1:

=if(rand()<0.156,1,0)

Which puts 1 in the cell if someone arrives and zero otherwise.

That was a very simple situation in which two outcomes are possible – an arrival or no arrival. More common is when we have several outcomes, each assigned a probability. Thus, in the text on pp 78ff, we have a clinic in which the gaps between the arrivals of patients is given by table 5-1

Table 5-1

Minutes Probability
m p
0 .2
1-5 .5
6-10 .2
11-27 .1

When dealing with ranges like this, it's common to think of any case in a range as having the value of the mid-point of the range. Thus, there is a 50% chance that the next case will arrive in 3 minutes (the mid-point of the 1-5 range). The other mid-points here are 8 and 19.

The Excel function, rand() returns a random value between 0 and 1. This range of possible random numbers is split up among the "outcomes" proportional to the probability of each outcome

image004.gif

The logic of how we "flip" this coin to figure out what happens each time can be shown in a flow chart.

image005.gif

We can implement this in Excel with the formula
image007.gif

G H
11 =rand() =if(g11<0.2,0,if(g11<0.7,3,if(g11<0.9,8,19)))