Transcript Example 2.3

MATH 528
Operations Models
Example 12.1
Bidding for a Government Project
Background Information
 The Miller Construction Company is trying to decide
whether to make a bid on a construction project.
 Miller believes it will cost the company $10,000 to
complete the project, and it will cost $350 to prepare
a bid.
 Four potential competitors are going to bid against
Miller. The lowest bid will win the contract.
 Based on past history, Miller believes that each
competitor’s bid will be a multiple of its cost to
complete the project, where this multiple has a
triangular distribution with minimum, most likely, and
maximum values 0.9, 1.3, and 2.5.
3
Background Information -continued
 These four competitor’s bids are also
assumed to be independent of one another.
 If Miller decides to prepare a bid, then it has
decided that its bid amount will be a multiple
of $500 in the range $10,500 to $15,000.
 The company wants to use simulation to
determine which strategy to use to maximize
its expected profit.
4
Solution
 The logic is straightforward.
 We first simulate the competitor’s bids. Then
for any bid Miller makes, we see whether
Miller wins the contract, and if so, what its
profit is.
5
BIDDING.XLS
 The spreadsheet model appears on the next
slide.
 This file contains the model.
6
The Spreadsheet
7
Developing the Simulation Model
 The model can be developed with the following steps.
 Inputs. Enter the inputs in the shaded cells. These
include Miller’s costs, Miller’s possible bids, and the
parameters of the triangular distribution for the
competing bids.
 Miller’s bid. We can test all of Miller’s possible bids
simultaneously with the RISKSIMTABLE function. Do
this in cell B15 with the formula
=RISKTABLE(PossibleBids).
 Competitor’s bids. Generate random bids for the four
competitors in the CompBid range by entering the
formula
=RISKTRIANG($B$9,$B$10,$B$11)*ProjectCost in
cell B15 and copying across. Of course Miller will not
see these other bids until it has submitted its own bid.8
Developing the Simulation Model - continued


Win contract?. See whether Miller wins the bid by
entering the formula
RISKOUTPUT( )+IF(MillerBid<MIN(CompBids),1,0)
in cell B23. Here, 1 means that Miller wins the bid, and
0 means a competitor wins the bid. Note that we are
designating this cell as an output cell for @RISK.
Miller’s profit. If Miller submits a bid, the bid cost is
lost for sure. Beyond that, the profit to Miller is the bid
amount minus the cost of completing the project if the
bid is won. Otherwise, Miller makes nothing. So enter
the formula
=RISKOUTPUT( ) +IF(B23=1,MillerBidProjectCost,0)-BidCost in cell C23. We also
designate this as an output cell.
9
Using @RISK
 We set the number of iterations to 1000 and the
number of simulations to 10.
 The summary results appear on the next slide.
 For each simulation – that is, each bid amount –
there are two outputs: 1 or 0 to indicate whether
Miller wins the contract and Miller’s profit.
 A little thought should convince you that each of
these can have only two possible values for any bid
amount.
10
Using @RISK
11
Using @RISK -- continued
 For example, if Miller bids $12,000, it will
either win or lose the contract, and its profit
will be either $1650 or -$350.
 This is reflected in the histogram of profit for
this bid amount shown on the next slide,
where there are only two bars.
 The two possible values of the outputs
appear in the Minimum and Maximum
columns of the table on the previous slide.
12
Using @RISK -- continued
13
Using @RISK -- continued
 The Mean column, on the other hand,
indicates the average of these values over
the 1000 iterations.
 For example, the mean of 0.545 for the “Win
Bid?” output for simulation #4 indicates that
Miller wins the contract on 54.5% of the
iterations when bidding $12,000.
 The mean profit of $740 for this bid amount is
simply a weighted average of the two
possible profits, $1650 and -$350.
14
Using @RISK -- continued
 Specifically, you can check that it is
0.545(1650)+0.455(-350) = 740. The other
means in the output can be interpreted
similarly.
 What should Miller bid?
 First, it is clear that Miller should bid. Not
bidding means no profit, Whereas all of the
possible bids except for the last one lead to a
positive expected profit with at most a $350
loss.
15
Using @RISK -- continued
 If Miller is an EMV maximizer, as we
discussed in Chapter 10, then the $12,000
bid should be chosen because it has the
highest mean profit.
 However, if Miller is risk averse, a smaller bid
amount might be attractive.
 As the bid amounts increase, the upside
potential is greater, but the chance of not
winning the bid and losing $350 increases.
16
Example 12.2
Tampering with a Stable Process
Background Information
 Suppose that you are in the business of drilling a tiny
hole in the exact center of a square piece of wood.
 In the past, the holes you have drilled were, on
average, in the center of the wood, and the x- and ycoordinates each had a standard deviation of 0.1
inch.
 Also, the drilling process has been stable – that is,
the holes average being in the center of the square,
and the deviations from the center of the square
follow a normal distribution with mean 0 and standard
deviation 0.1 inch.
18
Background Information -continued
 This mean, for example, that the x-coordinate is
within 0.1 inch of the center for 68% of the holes, the
x – coordinate is within 0.2 inch of the center for 95%
of the holes, and the x-coordinate is with 0.3 inch of
the center for 99.7% of the holes.
 This describes the inherent variability in the drilling
process.
 Without changing the hole-drilling process, you must
live with this amount of variation.
 Now suppose that you drill a hole and its x- and ycoordinates are x=0.1 and y=0.
19
Background Information -continued
 A natural reaction is to reduce the x-setting of the drill
by 0.1 to correct for the fact that the x-coordinate was
too high.
 Then if the next hole has coordinates x = -0.2 and y =
0.1, you might try to increase the x-coordinate by 0.2
and decrease the y-coordinate by 0.1.
 Deming’s funnel experiment shows that this method
of continually readjusting a stable process – he calls
it “tampering” – will actually increase the variability of
the coordinates of the position where the hole is
drilled. In other words, tampering will generally make
a process worse!
20
Background Information -continued
 To illustrate the effects of tampering, Deming
placed a funnel above a target on the floor
and dropped small balls through the funnel in
an attempt to hit the target.
 As he demonstrated, many balls did not hit
the target. His goal, therefore, was to make
the balls fall as close to the target as
possible.
 Deming proposed four rules for adjusting the
positioning of the funnel.
21
Deming’s Rules
1. Never move the funnel.
2. After each ball is dropped, move the funnel –
relative to its previous position – to
compensate for any error. To illustrate,
suppose the target has coordinates (0,0) and
the funnel begins directly over the target. If
the ball lands at (0.5,.1) on the first drop, we
compensate by repositioning the funnel at (00.5,0-0.1) = (-0.5,-1). If the second drop has
coordinates (1,-2), we then reposition the
funnel at (-0.5 – 1, -0.1 – (-2)) = (-1.5, 1.9).
22
Deming’s Rules -- continued
3. Move the funnel – relative to its original
position at (0,0) – to compensate for any
error. For example, if the ball lands at
(0.5,0.1) on the first drop, we compensate by
repositioning the funnel at (0-0.5, 0-0.1) = (0.5, -1). If the second drop has coordinates
(1, -2), we then reposition the funnel at (0, -1,
0 – (-2)) = (-1,2).
23
Deming’s Rules -- continued
4. Always reposition the funnel directly over the last
drop. Therefore, if the first ball lands at (0.5,1), we
position the funnel, (0.5, 1). If the second drop has
coordinates (1, 2), we position the funnel at (1, 2).
This rule might be followed, for example, by an
automobile manufacturer’s painting department.
With each new batch of paint, they attempt to match
the color of the previous batch – regardless of
whether the previous color was “correct”.
 Do you believe any of the latter three rules will
outperform rule 1, the “leave it alone” rule? If so,
read on – you might be surprised.
24
Solution
 To see how these rules work, we assume that
the x-coordinate on each drop is normally
distributed with mean equal to the xcoordinate of the funnel position and standard
deviation of 1.
 A similar statement holds for the y-coordinate.
Also, we assume that the x- and ycoordinates are selected independently of
one another. These assumptions describe the
inherent variability in the process of dropping
the balls.
25
Solution -- continued
 To see how the rules work, let F0, X0, F1 be
respectively, the x-coordinates of the funnel position
on the previous drop, the outcome of the previous
drop, and the repositioned funnel position for the next
drop.
 Then rule 1 never repositions, so that F1 = F0. Rule 2
repositions relative to the previous funnel position, so
that F1 = F0 – X0. Rule 3 repositions relative to the
original position (at 0), so that F1 = 0 – X0 = -X0.
Finally rule 4 repositions at the previous drop, so that
F1 = X0. Similar questions hold for the y-coordinate.
26
Solution -- continued
 For the simulation model, we simulate 50
consecutive drops of the ball from each of the
four rules.
 Our single output measure is the (straightline) distance of the final drop from the target.
 A rule is presumably a good one if the mean
distance is small and the standard deviation
of this distance is also small.
27
FUNNEL.XLS
 Given the repositioning equations for the
rules, the simulation model is straightforward.
 In fact, we use a RISKSIMTABLE function to
test all four rules simultaneously.
 The spreadsheet model appears on the next
slide.
 This file contains the model.
28
The Spreadsheet
29
Developing the Simulation Model
 The model can be developed with the following steps.
 Rule. Enter the formula =RISKSIMTABLE({1,2,3,4}) in
cell B3 to indicate that we want to simulate all four
rules. Note that if individual values are listed in
RISKSIMTABLE, they must be enclosed in curly
brackets. No curly brackets should be used if the list is
referenced by a range.
 Position funnel. Enter 0 in cells B7 and C7 to indicate
that the original funnel position is above the target at
(0,0). Then implement the positioning equations by
entering the formula =IF(Rule=1,B7,IF(Rule=2,B7D7,IF(Rule=3,-D7,D7))) in cell B8 and copying it to the
range B8:C56. Note how this formula references the
location of the previous drop. The IF function captures
the logic for all four rules.
30
Developing the Simulation Model - continued


Simulate drops Simulate the positions of the drops by
entering the formula =RISKNORMAL(B7,1) in cell D7
and copying it to the range D7:E56. This says that the
ball’s drop position is normally distributed with mean
equal to the funnel’s position and standard deviation 1.
Distance. Calculate the final distance from the target
in cell C58 with the formula =RISKOUTPUT( )
+SQRT(SUMSQ(D56:E56)). Here we have used
SUMSQ function to get the sum of squares for the
distance formula. We have also indicated that this is an
output cell for @Risk.
31
Using @RISK
 We set the number of iterations to 1000 and
the number of simulations to 4.
 Selected summary measures for the final
distance from the target for all four rules
appears in the table shown here.
 We also show histograms of this distance for
rules 1, 2, 3 on the next three slides.
32
Using @RISK
33
Using @RISK
34
Using @RISK
35
Using @RISK -- continued
 These results prove Deming’s point about tampering.
 Rule 2 might not appear to be too much worse than
rule 1, but its mean distance and standard deviation
of distances are both about 40% higher than rule 1.
 Rules 3 and 4 are disastrous. Their mean distances
are more than seven times higher than for rule 1, and
their standard deviations are also much higher.
 The moral of the story, as Deming preached, is that
you should not tamper with a stable process. If the
process is not behaving as desired, then fundamental
changes to the process are required, not a lot of
tinkering.
36
Example 12.3
Order Due Dates at Wozac
Background Information
 The Wozac Company is a drug manufacturing
company.
 Wozac has recently accepted an order from
its best customer for 8000 ounces of a new
miracle drug, and Wozac wants to plan its
production schedule to meet the customer’s
promised delivery date of December 1, 2000.
 There are three sources of uncertainty that
make planning difficult.
38
Background Information -continued
 First, the drug must be produced in batches, and
there is uncertainty in the time required to produce a
batch, which could be anywhere from 5 to 11 days.
This uncertainty is described by the discrete
distribution of this table.
Distribution of Days to Complete a Batch
Days
Probability
5
0.05
6
0.10
7
0.20
8
0.30
9
0.20
10
0.10
11
0.05
39
Background Information -continued
 Second, the yield (usable quantity) from any batch is
uncertain. Based on historical data, Wozac believes
the yield can be modeled by a triangular shaped
distribution with minimum, most likely, and maximum
values equal to 600, 1000, 1100.
 Third, all batches must go through a rigorous
inspection once they are completed. The probability
that a typical batch passes this inspection is only 0.8.
With probability 0.2, the batch fails inspection, and
none of it can be used to help fill the order.
40
Background Information -continued
 Wozac wants to use simulation to help decide
how many days prior to the due date it should
begin production.
41
Solution
 The idea is to simulate successive batches – their
days to complete, their yield, and whether they pass
inspection – and keep a running total of the usable
ounces obtained so far.
 We then use IF functions to see whether the order is
complete or another batch is required.
 We simulate only as many batches as are required to
meet the order, and we keep track of the days
required to produce all of these batches.
 In this way we can “back up” to see when production
must begin to meet the due date.
42
Developing the Simulation Model
 The completed model appears on the next slide. It
can be developed as follows.


Inputs. Enter all inputs in the shaded cells.
Batch indexes. We do not know ahead of time how
many batches will be required to fill the order. We want
to have enough rows in the simulation to cover the
worst case that is likely to occur. After some
experimentation we found that 25 batches are almost
surely enough. Therefore, enter the batch indexes 1-25
in column A of the simulation section. The idea, then, is
to fill the entire range B29:F53 with formulas. However,
we will use IF functions in these formulas so that if
enough has already been produced to fill the order,
blanks are inserted into the remaining cells.
43
Developing the Simulation Model
44
Developing the Simulation Model - continued


Days for batches. Simulate the days required for
batches in column B. First, enter the formula
=RISKDISCRETE(Days,Probs) in cell B29. Then
enter the general formula
=IF(OR(F29=“Yes”,F29=“”),””,RISKDISCRETE(Days
,Probs)) in cell B30 and copy it down to cell B53. Note
how the IF function enters a blank in this cell if either of
two conditions is true; the order was just completed in
the previous batch or it has been completed for some
time. Similar logic will appear in later formulas.
Batch yields. Simulate the batch yield in column C.
First, enter the formula =RISKTRIANG(B23,C23,D23)
in cell C29. Then enter the general formula
=IF(OR(F29=“Yes”,F29=“”),””,RISKTRIANG($B$23,
$C$23,$D$23)) in cell C30 and copy it down to C53.45
Developing the Simulation Model - continued

Pass inspection? Check whether each batch
passes inspection with the formulas
=IF(RAND()<PrPass,”Yes”,”No”) and
IF(OR(F29=“Yes”,
F29=“”),””,IF(RAND()<PrPass,”Yes”,”No”)) in
cells D29 and D30 and copy the latter down to
cell D53. Note that we could use @Risk’s
RISKUNIFORM(0,1) function instead of
RAND(), but there is no advantage to doing
so.
46
Developing the Simulation Model - continued

Order filled? We keep track of the cumulative
usable production and whether the order has
been filled in column E and F. First, enter the
formulas =IF(D29=“Yes”,C29,0) and
=IF(E29>=AmtReqd,”Yes”,”Not yet”) in cells
E29 and F29 for batch 1. Then enter the
general formulas
=IF(OR(F29=“Yes”,F29=“”),””,IF(D30=“Yes”,C3
0+E29,E29)) and
=IF(OR(F29=“Yes”,F29=“”),””,IF(E30>=AmtRe
qd,“Yes”,”Not yet”)) in cells E30 and F30, and
copy them down to row 53.
47
Developing the Simulation Model - continued


Note that the entry in column F is “Not enough” if the
order is not yet complete. In the row that completes,
the order, it changes to “Yes”, and then it is blank in
succeeding rows.
Summary measures. Calculate the batch and days
required in cell I28 and I29 with the formulas
=RISKOUTPUT() + COUNT(B29:B53) and
=RISKOUTPUT()+SUM(B29:B53) These are the two
cells we will use as output cells for @Risk. Also,
calculate the day the order should be started to just
meet the due dates in cell I30 with the formula
=DueDate-I29 This formula uses date subtraction to
find an elapsed time. Of course, it assumes that
production occurs every day of the week, which we will
assume.
48
Using @RISK
 We set the number of iterations to 1000 and the
number of simulations to 1.
 After running @Risk, we obtain the histograms of the
number of batches required and the number of days
required on the next two slides.
 How should Wozac use this information? The key
question are how many batches will be required and
when to start production.
 We have entered several of @Risk’s statistical
functions directly in the spreadsheet to help answer
these questions.
49
Using @RISK
50
Using @RISK
51
Using @RISK -- continued
 For the first question, we use the formula
=RISKMAX(I28) in cell I33. It shows that the worst
case from the iterations, in terms of batches required
is 20 batches.
 We can answer the second question in two ways.

First, we can calculate summary measures for days
required and then back up from the due date. We do
this in the range I35:J39. The formulas in column I are
=INT(RISKMEAN(DaysReqd)), =RISKMIN(DaysReqd),
=RISKMAX(DaysReqd),
=RISKPERCENTILE(DaysReqd,0.05) and
=RISKPERCENTILE(DaysReqd,0.95)
We then subtract each of these from the due date to
obtain the potential starting dates in column J. Wozac
should realize the pros and cons of these starting
52
dates.
Using @RISK -- continued

Alternatively, we can use @Risk’s RISKTARGET
function to find the probability of meeting the due date
for any starting date, such as those in the range
H42:H46. We enter the formula
=RISKTARGET(DaysReqd,DueDate-H42) in cell I42
and copy it down. This function returns the fraction of
iterations where the value in the first argument is less
than or equal to the value in the second argument.
 What is our recommendations to Wozac?
 We suggest going with the 95th percentile – begin
production on August 2. Then there is only a 5%
chance of failing to meet the due date.
53
Example 12.4
Component Redundancy at Failsafe
Background Information
 The FailSafe Company operates a machine
that consists of 3 identical module in series.
 This means that the machine works as long
as all the modules work.
 Each of modules depends on a specific
component. If the component fails, the
module fails – and the machine fails.
 To extend the time until machine failure,
redundancy is built in at the component level.
55
Background Information -continued
 Specifically, 15 identical components are placed in
parallel in each module.
 This means that a module works as long as at least
one component in that module is working.
 Each component lasts a random time before failure,
where these times are probabilistically independent
and each has mean 100 hours and standard
deviation 20 hours.
 FailSafe wants to use simulation to find the
distribution of the time until machine failure.
56
Solution
 Let Cij be the time until failure for component j in
module i. Also let Mi be the time until failure for
module i, and let T be the time until machine failure.
Then from the system configuration we have
Mi = max(Ci1, Ci2,…,Ci15) and T = min (M1,M2,M3).
 In other words, a module lasts as long as the best of
its components, and the machine lasts as long as the
worst of its modules.
 Therefore, all we need to do is generate the random
component times, the Cij’s, and use Excel’s MAX and
MIN functions to find the time until machine failure, T.
57
Choosing a Probability
Distribution
 We are told that times until component failure
have mean 100 and standard deviation 20,
but which distribution of component failure
times should we use?
 Should it be symmetric and bell shaped, or
should it be skewed in one direction or the
other?
 We will test four possible distributions:
normal, lognormal, gamma and Weibull.
58
Choosing a Probability
Distribution -- continued
 The normal is the symmetric bell-shaped distribution.
The other three are less well known, but they are
frequently used to model times until failure.
 They all have the attractive property that they
generate positive values only.
 To see the possibilities, we open @Risk’s Model
window and select the Insert/Distribution Window
menu item. This allows us to choose from many
distributions, including the four we are recommending
here, and to adjust their parameters.
59
Choosing a Probability
Distribution -- continued
 For the normal and lognormal, the
parameters are the mean and standard
deviation directly.
 For the gamma and Weibull, the parameters
are called alpha and beta. These parameters
control the exact location and shape of the
distributions, but they are not the mean and
standard deviation.
 If we want them to have a mean 100 and
standard deviation 20, we have to manipulate
alpha and beta appropriately.
60
Choosing a Probability
Distribution -- continued
 The lognormal, gamma, and Weibull distributions with
mean 100 and standard deviation 20 appear on the
next three slides.
 They all look similar to each other and to the normal
distribution, and only the Weibull indicates some
“obvious” skewness.
 If we were fitting distributions to historical failure time
data, these might all provide very good fits.
 The question, then is whether it matters which we
use in the simulation. Will they all give approximately
the same results? We will see shortly.
61
Choosing a Probability
Distribution -- continued
62
Choosing a Probability
Distribution -- continued
63
Choosing a Probability
Distribution -- continued
64
REDUNDANCY.XLS
 The simulation model appears on the next
slide.
 This file contains the model.
65
The Spreadsheet
66
Developing the Simulation Model
 It can be developed as follows.
 Machine configuration. We stated that there are 3
modules in series, each consisting of 15 components.
Enter these values in cells B4 and B5. We actually
develop a slightly more general model, where the
machine can consist of up to 10 modules in series,
each of which can have up to 20 modules in parallel.
 Parameters of probability distributions. Enter the
parameters of the four candidate probability
distributions in the shaded range. These are the
parameters from the @Risk Model window that yield
means of 100 and standard deviations of 20.
67
Developing the Simulation Model - continued


Distribution to use. We can actually test all four
distributions simultaneously by using a
RISKSIMTABLE function. DO this by entering the
formula =RISKSIMTABLE({1, 2, 3, 4}) in cell B16. Then
get the name and parameters of this distribution in
cells B17 to B19 with the formulas
=HLOOKUP(Dist,Ltable,2), =HLOOKUP(Dist,Ltable,3)
and =HLOOKUP(Dist,Ltable,4) The latter of these
cells, range-named Par 1 and Par 2, will supply the
parameters for the random values in the next step.
Component times. In simulation section we enter 1-10
along the top and 1-20 along the sides for the
maximum number of modules and components,
respectively, that our model can handle. We want to
generate enough component lifetimes in this table for
the number of modules and components in cells B4 68
and B5.
Developing the Simulation Model - continued

Do this with the following IF Formula, entered in cell
B20 and copied to the range B20:K39:
IF(AND($A24<=Ncomps,B$23<=Nmods),
IF(Dist=1,RISKNORMAL(Par1,Par2),
IF(Dist=2,RISKLOGNORM(Par1,Par2),IF(Dist=3,RISK
GAMMA(Par1,Par2),RISKWEIBULL(Par1,Par2)))),””)
Although this looks intimidating, it is really
straightforward. The AND condition checks whether the
component and module indices are less than or equal
to the values in the Ncomps and Nmods cells. If they
aren’t blank is entered. Otherwise, the appropriate
@Risk function is called with the parameters in the
Par1 and Par2 cells.
69
Developing the Simulation Model - continued


Module times. Calculate the times until module
failures in row 45 by entering the formula
=IF(B23<=Nmods,MAX(B24:B43),””) in cell
B45 and copying it across to column K. Note
that the MAX (or MIN) of a range that includes
blanks ignores the blanks.
Machine time. Calculate the time until
machine failure in cell B47 with the formula
=RISKOUTPUT()+MIN(ModLives) This is the
only cell we designate as an @Risk output
cell.
70
Using @RISK
 We set the number of iterations to 1000 and
the number of simulations to 4.
 After running @Risk, we form the histograms
of time until machine failure, one for each
distribution, on the following three slides.
 Does the input distribution affect the
distribution of machine lifetime? At first
glance, the answer appears to be “No”.
 The four distributions have similar shapes
and their means and standard deviations are
similar.
71
Using @RISK
72
Using @RISK
73
Using @RISK
74
Using @RISK
75
Using @RISK -- continued
 However, means and standard deviations
might not be as relevant in a real situation as
worst-case results.
 Therefore, it might be better to compare
maximums or 95th percentiles.
 Admittedly, even these do not vary greatly but
there are differences.
76
Conclusions
 We conclude from this example that the input
distribution(s) can make a difference in the
results, particularly when best-case or worstcase results are of primary interest.
 Therefore, it pays to spend some time in real
simulation applications fitting distributions to
any relevant historical data that exist.
 Fortunately, as we saw in the previous
chapter, @Risk’s fitting capabilities make this
fairly easy.
77
Example 12.5
Room Construction Project
Background Information
 Tom Lingley, an independent contractor, has agreed




to build a new room on an existing house.
He plans to begin work on Monday morning June 1.
The main question is when will he complete his work,
given that he works only weekdays.
The owner of the house is particularly hopeful that
the room will be ready by Saturday, June 27, that is,
in 20 or fewer working days.
The work proceeds in stages, labeled A through J, as
summarized in the table on the next slide.
79
Background Information -continued
Activity Time Data
Description
Index
Predecessors
Expected Duration
Prepare foundation
A
None
4
Put up frame
B
A
4
Order custom windows
C
None
11
Erect outside walls
D
B
3
Do electrical wiring
E
D
4
Do plumbing
F
D
3
Put in duct work
G
D
4
Hang dry wall
H
E, F, G
3
Install windows
I
B, C
1
Paint and clean up
J
H
2
 Three of the activities, E, F, and G, will be done by separate
independent subcontractors. The expected durations of the
activities are shown in the table.
80
Background Information -continued
 However, these are only best guesses.
 Lingley knows that the actual activity times
can vary because of unexpected delays,
worker illnesses, and so on.
 He would like to use a computer simulation to
see



How long the project is likely to take, and
How likely it is that the project will be
completed by the deadline, and
Which activities are likely to be critical.
81
Solution
 We first need to choose distributions for the
uncertain activity times.
 Then, given any randomly generated activity
times, we will illustrate a method for
calculating the length of the project and
identifying the activities on the critical path.
82
The Pert Distribution
 As always, there are several reasonable
candidate probability distributions we could
use for the random activity times.
 Here we illustrate a distribution that has
become popular in project scheduling, called
the Pert distribution. As shown on the next
slide, it is “rounded” version of the triangular
distribution that is specified by three
parameters: a minimum value, and a
maximum value.
83
The Pert Distribution -- continued
84
The Pert Distribution -- continued
 The distribution in the figure uses the values 7, 10,
and 19 for these three values, which implies a mean
of 11. We will use this distribution for activity C.
 Similarly, for the other activities, we choose
parameters for the Pert distribution that lead to the
means in the table.
 In reality, it would be done the other way around. The
contractor would estimate the minimum, most likely,
and maximum parameters for the various activities,
and the means would follow from these.
85
Developing the Simulation Model
 The key to the model is representing the project
network in activity-on-arc form, as in the diagram
below, and then finding Ej for each j, where Ej is the
earliest time we can get to node j.
 We stated in Chapter 5 that when the nodes are
numbered so that all arcs go from lower-numbered
nodes to higher-numbered nodes, we can calculate
the Ej’s iteratively, starting with E1=0, with the
equation Ej = max(Ei + tij).
 Here, the maximum is taken overall arcs leading into
node j, and tij is the activity time on such an arc.
86
Developing the Simulation Model - continued
 Then En is the time to complete the project, where n
is the index of the finish node.
 This will make it very easy to calculate the project
length.
 We also need a method for identifying the critical
activities for any given activity times.
 By definition, an activity is critical if a small increase
in its activity time causes the project time to increase.
Therefore, we will keep track of two sets of activity
times, and associated project times.
87
Developing the Simulation Model - continued
 The first uses the simulated activity times.
The second adds a small amount, such as
0.001 day, to a “selected” activity’s time. By
using the RISKSIMTABLE function with a list
as long as the number of activities, we can
make each activity the “selected” activity in
this method.
88
PROJECTSIM.XLS
 The spreadsheet model appears on the next
slide.
 This file contains the model.
89
The Spreadsheet
90
Developing the Simulation Model - continued
 The details of the model are followed.
 Inputs. Enter the parameters of the Pert activity time
distributions in the shaded cells and the implied means
next to them. As discussed above, we actually chose
the minimum, most likely, and maximum values while
in @Risk’s Model window to achieve the means in the
activity table. Note that some of these distributions are
symmetric about the most likely value, whereas other
are skewed.
 Activity time. Generate random activity times in
column I by entering the formula =RISKPERT(E5, F5,
G5) in cell I5 and copying it down.
91
Developing the Simulation Model - continued


Augmented activity times. We want to successively
add a small amount of each activity’s time to determine
whether it is on the critical path. To do this, enter the
formula =RISKSIMTABLE({1, 2, 3, 4, 5, 6, 7, 8, 9, 10})
in cell B16. Then enter the formula
=I5+IF(Index=C5,0.001,0) in cell J5 and copy it down.
Event times. We want to use the equation to calculate
the node event times in the range B20:B27. There is
no quick way to enter the required formulas. We need
to use the project network as a guide for each node.
Begin by entering 0 in cell B20. Then enter the
appropriate formulas in the other cells. For example,
the formulas in cells B22, B23, and B27 are =B21+I6,
=MAX(B20+I7,B21+I6) and =RISKOUTPUT(
)+MAX(B23+I13,B26+I14)
92
Developing the Simulation Model - continued


To understand these, note the node 3 has only one arc
leading into it, and this arc originates at node 2. No
MAX is required for this mode’s equation. In contrast,
node 4 has two arcs leading into it, from nodes 1 and
2, so a MAX is required. Similarly, node 8 requires a
MAX because it has two arcs leading into it. Also, it is
the finish node, so we designate its event time cell as
an @Risk output cell – it contains the time to complete
the project.
Augmented even times. Copy the formulas in the range
B20:B27 to the range C20:C27 to calculate the even
times when the selected activity’s time is augmented
by 0.001.
93
Developing the Simulation Model - continued

Project time increase?. To check whether the
selected activity’s increased activity time
increases the project time, enter the formula
=RISKOUTPUT( )+IF(C27>B27,1,0) If this
calculates to 1, then the selected activity is
critical for these particular activity times.
Otherwise, it is not. Note that this cell is also
designated as an @Risk output cell.
94
Using @RISK
 We set the number of iterations to 1000 and the
number of simulations to 10.
 After running @Risk, we request the histogram of
project times shown on the next slide.
 Recall from the example in Chapter 5 that when the
activity times are not considered random, the project
time is 20 days. Now it varies from a low of 16.09
days to a high of 25.20 days, with an average of
20.38 days.
 Although the 5th and 95th percentiles appear in the
figure, it might be more interesting to Tom Lingley to
see the probabilities of various project times being
exceeded.
95
Using @RISK
96
Using @RISK -- continued
 For example, we entered 20 in the Left X box
next to the histogram. The Left P value
implies there is about a 59% chance that the
project will not be completed within 20 days.
 Similarly, the values in the Right X and Right
P boxes imply that the chance of the project
lasting longer/ than 23 days is slightly greater
than 5%.
 This is certainly not good news for Lingley,
and he might have to resort to the crashing
we discussed earlier.
97
Using @RISK -- continued
 The summary measures for the B29 output
cell appears in this table.
98
Using @RISK -- continued
 Each “simulation” in this output represents one
selected activity being increased slightly.
 The Mean column indicates the fraction of iterations
where the project time increases as a result of the
selected activity’s time increase.
 Hence, it represents the probability that this activity is
critical.
 For example, the first activity (A) is always critical, the
third activity (C) is never critical, and the fifth activity
(E) is critical about 44% of the time.More specifically,
we see that the critical path always includes activities
A, B, D, H, J, and one of the three “parallel” activities
E, F, and G.
99