Integrating Monte Carlo Simulation and MS Project 2000

for Project Time Analysis

Ali Akbar Taheri 1 - Mahmood Alborzi 2 - Ali Vahedi 3

Abstract

ah
ed

iD

iz.

co

Time risk management is one of the important aspects in project management. This is because
typical projects often overrun their estimates. PERT methodology is often employed to compute the
project completion time probability profile. This methodology, however, does not cater for near critical
activities. Monte Carlo Simulation has been suggested in the literature to overcome this shortcoming
(Van Slyke, 1963, p.839). However this suggestion has not been much employed in project management
so far, mainly because of the high volume of computation required and the limited availability of
computation facilities in the past. With the widespread use of PC's at present, one may readily deploy the
Monte Carlo Simulation technique and integrate it with present project management software, such as
Microsoft Project 2000 (MSP2000), to enhance the quality of project planning and control. This may be
achieved by writing appropriate macros and incorporate it within MSP2000. This is what is put forward
in this paper. A macro has been developed in Visual Basic for MSP2000. The capabilities of the
software hence developed are described with the example of a construction project called Clever Clogs
Project.

liV

Introduction

.A

Typical projects often overrun their time estimates. Overruns are common on
government and commercial projects, even when changes in the design are taken into
account (Hulett, 1999, p.1). One reason this happens is because time estimating
traditionally fails to take into account the risk that the work will actually time more (or
less) than provided by even the most competent estimates.

Future estimates are not facts but statements of probability about how things will
turn out. Because estimates are probabilistic assessments, times may actually be higher or
lower than estimated, even by seasoned professional estimators. The reasons are often
causes that are outside the control of the project manager, but may also be endemic to

) Ali Akbar Taheri, MBA, e-mail: [email protected]

SAPCO, Automotive Parts Company, Tehran, Iran

) Mahmood Alborzi, Ph.D., e-mail: [email protected]

Petroleum University of Technology, Tehran, Iran

) Ali Vahedi, M. Sc., e-mail: [email protected]

IKCO, Iran Khodro Company, Tehran, Iran

the estimating process, the project strategy or the corporate culture within the project
contractor.
Time risk analysis methods are required to provide more accurate estimates of total
project time. PERT methodology is often employed to compute the project completion
time probability profile. This methodology, however, do not cater for near critical
activities. Monte Carlo Simulation has been suggested in the literature to overcome this
shortcoming (Van Slyke, 1963, p.839). Monte Carlo Simulation helps to gain better
information that traditional methods not using simulation could not provide.

Objectives of a Project Time Risk Analysis

iz.

co

Monte Carlo Simulation has not been, however, much employed in project
management so far, mainly because of the high volume of computation required and the
limited availability of computation facilities in the past. With the widespread use of PC's
at present, one may readily deploy the Monte Carlo Simulation technique and integrate it
with present project management software, such as MSP2000, to enhance the quality of
project planning and control, as proposed in this paper.

iD

Time risk analysis using Monte Carlo Simulation can answer some questions that
the traditional estimating methods not using simulation cannot. Included are:

ah
ed

"What is the most likely time?" The traditional methods assume that this is the
baseline time computed by summing the estimates of time for the project
elements. But this is not necessary the case.
"How likely is the baseline estimate to be overrun?" Traditional methods do
not accurately address this problem.

liV

"What is the time risk exposure?" This is also the answer to the question;
"How much contingency do we need on this project?"

.A

"Where is the risk in this project?" This is the same as: "Which time elements
cause the most need for contingency?" Risk analysis principles can be used to
answer this question.

Time Risk Analysis Using Monte Carlo Simulation within MSP2000

To show how a time risk analysis is done using Monte Carlo Simulation within
MSP2000, we first take a look at a traditional estimate for an exemplar construction
project called Clever Clogs Project. The traditional estimate has been constructed. The
table below shows the summary of the Clever Clogs project time estimates.
Table 1 gives a time estimate of 493.83 days. How likely is it that this project will be
completed for this time duration? In fact, is 493.83 days indeed the most likely estimate?
To answer these questions, we need to examine the uncertainties in the baseline
estimates.

Table 1: Traditional Time Estimates for Clever Clogs Project

WBS

Task Name

Duration

Start

Finish

Clever Clogs Projects

493.83d

05/10/00

03/20/02

1.1

Start

0d

05/10/00

05/10/00

1.2

Eviction of Squatters

25d

05/10/00

06/13/00

1.3

Designing

30.33d

05/24/00

07/04/00

1.4

Land Preparation

43.5d

06/14/00

08/10/00

10

1.5

Foundation Work

90.83d

08/11/00

15

1.6

Structural Work

73.5d

12/15/00

03/27/01

20

1.7

Masonry Work

116.33d

03/28/01

08/29/01

30

1.8

Utilities Installation

15d

37

1.9

Finishing Works

124.67d

46

1.10

Facilities

51

1.11

Yard and Gardens

57

1.12

Finish

ID

iz.

co

12/14/00

09/19/01

08/30/01

02/20/02

8d

02/21/02

03/04/02

20d

02/21/02

03/20/02

0d

03/20/02

03/20/02

ah
ed

iD

08/30/01

liV

Time Risk Analysis Data Requirements

.A

Time risk analysis would certainly need more data. Gathering these data can be a
difficult task but the rewards are valuable. A risk analyst may be assigned with the task
of gathering the necessary data.

To follow along the case of Clever Clog, the risk analyst has to choose well various
project experts who should be interviewed. These experts will probably include the
project team and the team leader. They may also include experienced project
professionals from the company who are not currently assigned to this project. Outside
experts may sometimes be included too, although this is rare except in the cases of public
projects.
The risk analyst may use the opinions of the experts to determine the type of
probability distributions pertaining to the project activity times and the distributions
parameters. The types of distributions proposed in the developed software include
"normal", "beta", "triangular", "uniform" and "exponential". The parameters include,
"mean", "standard deviation", "optimistic times", "pessimistic times", "most likely times",
etc.

The rationale for the distributions and the parameters is explored and recorded in
the notes of the meeting. The rationale is most important because it points to the risk
mitigation, which is also discussed in the risk interview.
Suppose that this interview has occurred and the following estimates are secured.
Table 2: The Results of Interview with Experts
WBS

Task Name

Dist.

Min

Max

M. Likely

Clever Clogs Projects

Beta

391

633

486

1.1

Start

Beta

1.2

Eviction of Squatters

Beta

15

35

25

1.3

Designing

Beta

27

35

1.4

Land Preparation

Beta

36

10

1.5

Foundation Work

Beta

74

15

1.6

Structural Work

Beta

20

1.7

Masonry Work

Beta

30

1.8

Utilities Installation

37

1.9

Finishing Works

46

1.10

Facilities

51

1.11

57

1.12

ID

co

30
42

111

90

97

131

110

128

224

160

39

70

54

Beta

135

196

165

Beta

12

28

20

Yard and Gardens

Beta

36

59

47

Finish

Beta

iD

iz.

57

liV

ah
ed

Beta

.A

The "min" and "max" ranges are not often symmetrical about the estimates. In fact,
they often exhibit a greater likelihood for over-runs than for under-runs. This is in part
because there is a natural barrier (zero) to the lowest time possible and there are many
ways the project can run into trouble on the high side.

In the example above, it is assumed that the baseline estimate is the "most likely"
time. In fact, many estimates are not the most likely when the estimators are questioned
closely. Sometimes, the risk interview turns up some baseline estimates that should be
changed in order to represent the most likely time. This is one clear benefit of a risk
interview, or indeed of any honest and careful scrubbing of the baseline. But, in this
example it will be assumed that the baseline was carefully estimated without being
"shaded" or biased in any way, and that new information has been recently incorporated
in it.
Monte Carlo Simulation method is simple to describe. Yet, the software of
Microsoft Project 2000 (Microsoft Company, 2000) does not include Monte Carlo
Simulation capabilities. A macro was developed in Visual Basic to augment the
MSP2000 package, to enable it to simulate the activities duration times.
4

Simulating the Time Risk Model Simulation Results

Using the time ranges presented in Table 2 and assuming beta distribution for all
activities in the Clever Clogs project, Monte Carlo simulation was run using the Monte
Carlo Macro v.1.0 developed in this study. The Monte Carlo simulation was run for
many iterations. For each iteration, the simulation program selected a time at random,
from the probability distribution specified by the analyst for each of the uncertain time
elements.

iD

iz.

co

A question that needs to be addressed in similar simulation studies is: "How many
iterations is enough?" The answer would depend on the potential use of the information.
When the main objective of the analysis is to get a mere estimate of the most likely time,
less iterations are needed. If accuracy in the tails of the resultant distribution is important,
more iterations will be required. To illustrate this fact, four sets of iterations i.e. 100,
500, 1000 and 5000 were used. Figure 1 and Table 3 show the sensitivity and normality
of each case.

Normality

100

492.890

Very Low

500

493.290

Low

1000

494.151

Medium

494.007

Well

3
2
1

Freq

.A
w

Freq

7
6

5000

120

30

600

100

25

500

80

20

400

60

15

300

40

10

200

20

100

Cum Freq

Duration Mean

60

1200 300

6000

50

1000 250

5000

40

800

200

4000

30

600

150

3000

20

400

100

2000

10

200

50

1000

Cum Freq

Iteration

liV

No

ah
ed

Table 3: The Results of Sensitivity and Normality Analysis

Figure 1: The Project Duration Probability Diagram for Each Number of Iteration
In order to have a more accurate distribution, the simulation with 5000 iterations
was selected in this study. The result is shown in figure 2.

Project Duration Diagram

120.00%

co

300
Prob. = 50%

100.00%

150

iD

Frequency

200

ah
ed

100
50

80.00%
60.00%

Probability

iz.

250

40.00%
20.00%

Mean = 494.007

0.00%

liV

463 469 473 477 481 485 489 493 497 501 505 509 513 517 521 527
Duration

.A

Figure 2: The Project Duration Probability Diagram for 5000 Iterations

In similar cases, the project manager may ask; "What is my exposure here?" This
question expresses the feeling that if the project were 90% likely to overrun by a day, it
does not matter, but if it were even 30% likely to overrun by 600 days that would be a
real problem.

Monte Carlo Simulation Macro v.1.0 gives the answer in the cumulative likelihood
distribution. This is just the distribution shown above cumulated from left to right
starting at 0 likelihood of time and cumulating to 100% (see figure 2). This cumulative
chart shows a vital result to the project manager. Suppose that the company is
conservative, and will only bid for a project that has at most a 15% likelihood of
overrunning. Should they get the bid, this level will be an acceptable risk. The chart
shows that the amount bid should be 503 days. Another way to look at this result is that
a contingency of only about 10 days would be necessary to provide the comfortable level
of the company.

Critical Index
One question that needs to be addressed is: "What is the risk of each activity in the
project?" The answer is the "critical index".

Critical indexing is the implementation of the Monte Carlo Simulation technique to

solve for the problem of uncertainty in times of a predetermined network. The problem is
to find the critical path of a network where times of the events are random, under a
certain probability function. So critical indexing can be defined as determining the
importance of an activity in the completion of a network, and the critical index of an
event is defined as the probability that the activity belongs to a critical path (El-Shayeb,
1996, pp. 43).

co

Activities with the highest critical index would be subjected to the most managerial
attention, since it has a high probability of belonging to the critical path and so high
probability to delay the network.

Two important results can be observed:

iD

iz.

Determination of critical indices for activities in a network could be achieved using

Monte Carlo Simulation. The capability to compute critical indices has been incorporated
into the Monte Carlo Simulation Macro v. 1.0, proposed in this paper. The simulation
result of the critical indices for the Clever Clogs Project is shown in table 4.

ah
ed

First. The activities with 100% critical index are very important and have high
probability to delay the project. The project manager should also focus on the activities
that have critical index higher than zero, since they may probably become critical during
the course of the project.

.A

liV

Second. If one activity had low critical index but long duration time, it should be
taken more into account. It means that if the activity was 90% likely to be critical by a
day, it may not matter. But if it was even 30% likely to be critical by 60 days, that would
be a real critical activity.

Summary

A macro was developed in this paper using Visual Basic and incorporated into the
MS Project 2000 software to enable it to do time risk analysis using Monte Carlo
Simulation.
PERT methodology is often employed in project management to compute the
probability profile of the project completion time. Yet this methodology is deficient in
that it does not cater for the near critical activities. Simulation has been often suggested
as the way to alleviate this deficiency. Yet this has not been deployed much in practice,
presumably because of the computation burden. In particular Microsoft Project 2000
does not currently have this capability.
The purpose of time risk analysis is to assist the project manager by indicating the
magnitude of the problems and high light the areas that needs more attention. This can be
achieved by developing the concept of critical index. This paper has demonstrated how
7

this can accomplished by integrating the Monte Carlo Simulation and the Microsoft
Project 2000 software.
The integrated software developed in this study was used on an exemplar case, a
construction company to demonstrate its capability.

WBS

Task Name

Exp. Dur.

Clever Clogs Projects

502.49

1.1

Start

0.00

100%

1.2

Eviction of Squatters

25.10

100%

1.3

Designing

30.65

9.18%

1.4

Land Preparation

45.29

1.4.1

Demolishing

20.68

1.4.2

Soil removal

16.00

100%

1.4.3

Site preparation

8.30

100%

1.4.4

End of land preparation

0.00

100%

10

1.5

Foundation Work

91.66

100%

30

1.8

Utilities Installation

15.07

0%

31

1.8.1

Electrical

14.99

0%

35

1.8.5

Lift

9.36

0%

36

1.8.6

End of Utilities Installation

0.00

0%

37

1.9

Finishing Works

125.27

100%

38

1.9.1

Plastering

39.33

100%

39

1.9.2

Marble works

29.32

100%

1.9.3

Door installation

20.03

51.02%

1.9.4

Window installation

19.99

48.98%

1.9.5

Glass works

11.01

100%

43

1.9.6

Painting

21.00

1.92%

44

1.9.7

Decorative Works

24.68

98.08%

49

1.10.3

Squash yard

5.34

0%

50

1.10.4

End of Facilities

0.00

0%

51

1.11

Yard and Gardens

19.97

100%

57

1.12

Finish

0.00

100%

42

co
100%

90.82%

iz.

iD

ah
ed

.A

41

40

Critical Index

ID

liV

Table 4: The Critical Indices for A Part of Clever Clogs Project Activities

References
El-Shayeb, Y. (1996). Risk Analysis in Mining: An Economical Aspect of Network
Simulation. Cairo University. M.Sc. Thesis.
Hulett, D.T. (1999). Project Cost Analysis Using Crystal Ball. International Institute
for Learning: Los Angeles.
Microsoft Company (2000). Microsoft Project 2000 Software Package. Microsoft
Company: USA.

.A

liV

ah
ed

iD

iz.

co

Van Slyke, R.M. (1963). Monte Carlo Methods and the PERT Problem. Operations
Research. Vol. 11. No. 5. 839-860.