EXAMPLES OF PERFORMING SIMULATIONS ON SPREADSHEETS WITH ANALYTIC SOLVER PLATFORM:PROJECT MANAGEMENT

PROJECT MANAGEMENT

One of the most important responsibilities of a project manager is to meet the deadline that has been set for the project. Therefore, a skillful project manager will revise the plan for conducting the project as needed to ensure a strong likelihood of meeting the deadline. But how does the project manager estimate the probability of meeting the deadline with any particular plan? Section 22.4 described one method provided by PERT/CPM. We now will illustrate how simulation provides a better method.

This example illustrates a common role for simulation—refining the results from a preliminary analysis conducted with approximate mathematical models. You also will get a first look at uncertain variable cells where the values shown are times. Another interesting feature of this example is its use of a special kind of ASPE chart called the sensitivity chart. This chart will provide a key insight into how the project plan should be revised.

The Problem Being Addressed

Like the example in the preceding section, this one also revolves around the story of the Reliable Construction Co. that was introduced in Sec. 22.1 and continued throughout Chap 22. However, rather than preceding the part of the story described in Chap. 22, this

example arises in the middle of that story. In particular, Sec. 22.4 discussed how a PERT/CPM procedure was used to obtain a rough approximation of the probability of meeting the deadline for the Reliable Construction Co. project. It then was pointed out that simulation could be used to obtain a better approximation. We now are in a position to describe how this is done.

Here are the essential facts that are needed for the current example. (There is no need for you to refer to Chap. 22 for further details.) The Reliable Construction Company has just made the winning bid to construct a new plant for a major manufacturer. However, the contract includes a large penalty of $300,000 if construction is not completed by the dead- line 47 weeks from now. Therefore, a key element in evaluating alternative construction plans is the probability of meeting this deadline under each plan. There are 14 major activities involved in carrying out this construction project, as listed on the right-hand side of Figure 28.5 (which repeats Fig. 22.1 for your convenience). The project network in this figure depicts the precedence relationships between the activities. Thus, there are six sequences of activities (paths through the network), all of which must be completed to fin- ish the project. These six sequences are listed below.

The numbers next to the activities in the project network represent the estimates of the number of weeks the activities will take if they are carried out in the normal manner with the usual crew sizes, and so forth. Adding these times over each of the paths (as was done in Table 22.2) reveals that path 4 is the longest path, requiring a total of 44 weeks. Since the project is finished as soon as its longest path is completed, this indicates that the project can be completed in 44 weeks, 3 weeks before the deadline.

Now we come to the crux of the problem. The times for the activities in Fig. 28.5 are only estimates, and there actually is considerable uncertainty about what the duration of each activity will be. Therefore, the duration of the entire project could well differ substantially from the estimate of 44 weeks, so there is a distinct possibility of missing the deadline of 47 weeks. What is the probability of missing this deadline? To estimate this probability, we need to learn more about the probability distribution of the duration of the project.

This is the reason for the PERT three-estimate approach described in Sec. 22.4. This approach involves obtaining three estimates—a most likely estimate, an optimistic estimate, and a pessimistic estimate—of the duration of each activity. (Table 22.4 lists these estimates for all 14 activities for the project under consideration.) These three quantities are intended to estimate the most likely duration, the minimum duration, and the maximum duration, respectively. Using these three quantities, PERT assumes (somewhat arbitrarily) that the form of the probability distribution of the duration of an activity is a beta distribution. By also making three simplifying approximations (described in Sec. 22.4), this leads to an analytical method for roughly approximating the probability of meeting the project deadline.

One key advantage of simulation is that it does not need to make most of the simplifying approximations that may be required by analytical methods. Another is that there is great flexibility about which probability distributions to use. It is not necessary to choose an analytically convenient one.

When dealing with the duration of an activity, simulations commonly use a triangular distribution as the distribution of this duration. The triangular distribution fits the PERT three-estimate approach very well because it has three parameters that correspond to the three estimates in a very natural way. Figure 28.1 shows the shape of this distribution and its three parameters—min (the minimum possible value), likely (the most likely value), and max (the maximun possible value). Thus, the duration of an activity is assumed to have a triangular distribution where min 5 optimistic estimate, likely 5 most likely estimate, and max 5 pessimistic estimate. For each uncertain variable cell containing this distribution, a Triangular Distribution dialog box (such as the one shown in Fig. 28.3) is used to enter the values of the three estimates by entering their respective cell references into the min, likely, and max boxes.

A Spreadsheet Model for Applying Simulation

Figure 28.6 shows a spreadsheet model for simulating the duration of the Reliable Construction Co. project. The values of o, m, and p in columns D, E, and F are obtained directly from Table 22.4 in Chap. 22. The equations entered into the cells in columns G and I give the start times and finish times for the respective activities. For each trial of the simulation, the maximum of the finish times for the last two activities (M and N) gives the duration of the project (in weeks), which goes into the results cell ProjectCompletion (I21).

Since the activity times generally are variable, the cells H6:H19 all need to be uncer- tain variable cells. Figure 28.7 shows the Triangular Distribution dialog box after it has been used to specify the parameters for the first uncertain variable cell, which records the time of activity A with a range name of ATime (H6). The right side of Fig. 28.7 notes that ASPE has automatically entered a formula (5PsiTriangular(D6,E6,F6)) into ATime (H6) to calculate a random value from this distribution. Rather than repeating this process for all the other uncertain variable cells, it is quicker to simply copy and paste. To copy the

formula in H6 down to H7 through H19, select cell H6 and drag the fill handle (the small box on the lower right corner of the cell cursor) down to cell H19. This copies the formula in H6 (5PsiTriangular(D6,E6,F6), used by ASPE to calculate a random value from the tri- angular distribution, with parameters min 5D6, likely 5E6, and max 5F6) into cells H7 through H19. Since the parameters in cell H6 (D6, E6, and F6) are relative references, the row numbers of the parameters will update appropriately to refer to the data in the correct rows during the copy-and-paste process. For example, the formula in H7 will update to 5PsiTriangular(D7,E7,F7).

Here is a summary of the key cells in this model.

Uncertain variable cells: Cells H6:H15 Results cell: ProjectCompletion (I21) Statistic cell: MeanProjectCompletion (I23)

(See Sec. 20.6 for the details regarding how to define uncertain variable cells, results cells, and statistic cells.)

The Simulation Results

We now are ready to evaluate the simulation of the spreadsheet model in Fig. 28.6. After running a simulation of 1,000 trials, Fig. 28.8 shows the results in the form of a frequency chart and a statistics table. These results show a very wide range of possible project dura- tions. Out of the 1,000 trials, the statistics table indicates that one trial had a duration as short as 36.74 weeks while another was as long as 60.66 weeks. The frequency chart indi- cates that the duration that occurred most frequently during the 1,000 trials is close to 47 weeks (the project deadline), but that many other durations up to a few weeks either shorter or longer than this also occurred with considerable frequency. The mean is 46.26 weeks, which is much too close to the deadline of 47 weeks to leave much margin for slippage in the project schedule.

A statistic of special interest to Reliable’s management is the probability of meeting the deadline of 47 weeks under the current project plan (Remember that the contract includes a severe penalty of $300,000 for missing this deadline.). Figure 28.8 shows that all you need to do to identify the exact percentage is to type the deadline of 47 in the Upper Cutoff box. The Likelihood box then reveals that about 57.7 percent of the trials met the deadline.

If the simulation run were to be repeated with another 1,000 trials, this percentage probably would change a little. However, with such a large number of trials, the difference in the percentages should be slight. Therefore, the probability of 0.577 provided by the Likelihood box in Fig. 28.8 is a close estimate of the true probability of meeting the dead- line under the assumptions of the spreadsheet model in Fig. 28.6. Note how much smaller this relatively precise estimate is than the rough estimate of 0.84 obtained by the PERT three-estimate approach in Sec. 22.4. Thus, the simulation estimate provides much better guidance to management in deciding whether the project plan should be changed to improve the chances of meeting the deadline. This illustrates how useful simulation can be in refining the results obtained by approximate analytical results.

A Key Insight Provided by the Sensitivity Chart

Given such a low probability (0.577) of meeting the project deadline, Reliable’s project manager (David Perty) will want to revise the project plan to improve the probability substantially. ASPE has another tool, called the sensitivity chart, that provides strong guidance in identifying which revisions in the project plan would be most beneficial.

To view a sensitivity chart after running a simulation, click on the Sensitivity tab above the chart for the results cell. This reveals a sensitivity chart, as shown in Fig. 28.9. Using range names, the left side of the chart identifies various uncertain variable cells (activity times) in column H of the spreadsheet model in Figure 28.6.

The bars in the chart give the correlation coefficient (based on moment values) between each uncertain variable cell and the results cell. A correlation coefficient between two variables measures the strength of the relationship between those variables. Thus, each correlation coefficient in Fig. 28.9 measures how strongly that activity time is influencing the project completion time. The higher the correlation coefficient, the stronger is this influence. Therefore, the activities with the highest correlation coefficients are those where the greatest effort should be made to reduce their activity times.

Figure 28.9 indicates that CTime has a far higher correlation coefficient than the times for any of the other activities. An examination of Figs. 28.5 and 28.6 suggests why. Figure 28.5 shows that activity C precedes all the other activities except activities A and B, so any delay in completing activity C would delay the start time for all these other activities. Furthermore, cells D8:F8 in Fig. 28.6 indicate that CTime is highly variable, with an unusually large spread of 9 weeks between its most likely estimate and its pessimistic estimate, so long delays beyond the most likely estimate may well occur.

This very high correlation coefficient for CTime suggest that the best way to reduce the project completion time (and its variability) is to focus on reducing this activity time (and its variability). This can be accomplished by revising the project plan to assign activity C more personnel, better equipment, stronger supervision, and so forth. ASPE’s sensitivity chart clearly highlights this insight into where the project plan needs to be revised.