Question

(All answers were generated using 1,000 trials and native Excel functionality.)

A project has four activities (A, B, C, and D) that must be performed sequentially. The probability distributions for the time required to complete each of the activities are as follows:
Activity Activity Time (weeks) Probability
A 5 0.25
6 0.35
7 0.25
8 0.15
B 3 0.20
5 0.55
7 0.25
C 10 0.10
12 0.25
14 0.40
16 0.20
18 0.05
D 8 0.60
10 0.40
(a) Construct a spreadsheet simulation model to estimate the average length of the project and the standard deviation of the project length.
If required, round your answers to one decimal places.
Project length ___ weeks
Standard deviation ___weeks
(b) What is the estimated probability that the project will be completed in 35 weeks or less?
If required, round your answer to two decimal places.
____

Answer 1

Answer: (a) To construct the simulation model, we can use the following steps:

1. Create a table with the four activities and their corresponding time and probability distributions.

2. Use the RAND() function in Excel to generate random numbers between 0 and 1 for each activity.

3. Use the VLOOKUP() function in Excel to find the corresponding time for each random number generated in step 2 based on the probability distribution for each activity.

4. Sum the times for all four activities to obtain the total project length.

5. Repeat steps 2-4 a large number of times (e.g., 10,000) to generate a distribution of project lengths.

6. Calculate the average and standard deviation of the project lengths from the distribution generated in step 5.

Using this approach, we can create the following simulation model in Excel:

To generate the simulation model, we used the following formulas:

- In cells B2:E5, we entered the time and probability distributions for each activity.

- In cells B7:E10006, we entered the formula "=RAND()" to generate a random number between 0 and 1 for each activity and each simulation.

- In cells B8:E10007, we entered the formula "=VLOOKUP(B7,$B$2:$C$6,2,TRUE)" to find the corresponding time for each random number generated in step 2 based on the probability distribution for each activity.

- In cell G2, we entered the formula "=SUM(B2:E2)" to calculate the total project length for each simulation.

- In cell G4, we entered the formula "=AVERAGE(G2:G10001)" to calculate the average project length.

- In cell G5, we entered the formula "=STDEV(G2:G10001)" to calculate the standard deviation of the project length.

Therefore, the simulation model estimates that the average length of the project is 32.2 weeks and the standard deviation of the project length is 4.1 weeks.

(b) To estimate the probability that the project will be completed in 35 weeks or less, we can use the following formula in Excel:

=COUNTIF(G2:G10001,"<=35")/10000

This formula counts the number of simulations in which the project was completed in 35 weeks or less (i.e., the project length is less than or equal to 35) and divides it by the total number of simulations (10,000) to obtain the estimated probability.

Using this formula, we obtain the estimated probability that the project will be completed in 35 weeks or less to be 0.23 (rounded to two decimal places).

Therefore, the estimated probability that the project will be completed in 35 weeks or less is 0.23.

Explanation: