Project Management using Excel
Yuzhi Zhao
Week2-PORTA-VAC.xlsxActivity List
| Porta-Vac Project | ||||||||||||
| Activity List for the PORTA-VAC Project | ||||||||||||
| Note: This is not the correct map… | ||||||||||||
| Activity | Description | Immediate Predecessor | Estimated Completion Time (weeks) | |||||||||
| A | Develop product design | --- | 6 | Instructions: | ||||||||
| 1. Draw the PERT Diagram on a separate sheet of paper based on this Activity List. | ||||||||||||
| B | Plan market research | --- | 2 | |||||||||
| C | Prepare routing | A | 3 | 2. Check the next sheet ("Project Flow") for a solution and compare your work! | ||||||||
| D | Build prototype | A | 5 | |||||||||
| E | Prepare marketing brochure | A, B | 3 | 3. Continue to the "Project Flow" sheet for further instructions. | ||||||||
| F | Prepare cost estimate | C | 2 | |||||||||
| G | Do preliminary design testing | D | 3 | |||||||||
| H | Complete Market survey | B, E | 4 | |||||||||
| I | Prepare pricing and forecast report | H | 2 | |||||||||
| J | Prepare final report | F, G, I | 2 | |||||||||
Project Flow
| Porta-Vac Project | ||||||||||||||||||||||
| Format | Example | |||||||||||||||||||||
| Earliest Start | Activity Name | Earliest Finish | Slack | 1 | Z | 3 | 3 | |||||||||||||||
| Latest Start | Activity Duration | Latest Finish | 4 | 3 | 6 | |||||||||||||||||
| C | F | J | ||||||||||||||||||||
| A | D | G | ||||||||||||||||||||
| Finish | ||||||||||||||||||||||
| Start | ||||||||||||||||||||||
| E | ||||||||||||||||||||||
| B | H | I | ||||||||||||||||||||
| Instructions: (see class notes for more details) | Questions: | |||||||||||||||||||||
| Note: Activity Times are listed on the Activity List. | ||||||||||||||||||||||
| 1. What is the expected completion time? | ||||||||||||||||||||||
| Step 1:: Insert the activity times under the activity names, "Actitivity Duration" as shown in the Format example above. | ||||||||||||||||||||||
| Step 2: Perform a forward pass -- computing the Earilest Start and Earliest Finish times. Insert these in the TOP row. | ||||||||||||||||||||||
| Step 3: Perform a backward pass -- computing the Latest Finish and Latest Start times. Insert these in the BOTTOM row. | 2. Where is the Critical Path? | |||||||||||||||||||||
| Step 4 Calculated the slack time for each activity. Insert this in the YELLOW box. | (describe by the series of letter for the activities on the Crtical Path) | |||||||||||||||||||||
| Step 5: Determine the Critical Path. | ||||||||||||||||||||||
| Then, proceed to the next sheet to determine the RISK of the project. |
Project Risk
| Porta-Vac Project | ||||||||||||||||
| Instructions: | Questions: | |||||||||||||||
| Complete the tables below to answer the questions to the right. | 1. What is the probability that this project will be completed within the goal of 20 weeks? | |||||||||||||||
| 2. What is the probability that this project will be completed faster, within 19 weeks? | ||||||||||||||||
| Data given by Porta-Vac Production Leaders | (see formulas and hints in green to the right) | A-E-H-I-J | A-C-F-J | A-D-G-J | B-H-I-J | |||||||||||
| Activity | Optimistic (a) | Most Probable (m) | Pessimistic (b) | Expected Completion Time (weeks) | Variance (weeks^2) | Slack (weeks) | On critical Path? (Yes=1 or No=blank) | Path 1 (Yes=1 or No=blank) | Path 2 (Yes=1 or No=blank) | Path 3 (Yes=1 or No=blank) | Path 4 (Yes=1 or No=blank) | |||||
| A | 4 | 5 | 12 | |||||||||||||
| B | 1 | 1.5 | 5 | |||||||||||||
| C | 2 | 3 | 4 | |||||||||||||
| D | 3 | 4 | 11 | |||||||||||||
| E | 2 | 3 | 4 | |||||||||||||
| F | 1.5 | 2 | 2.5 | |||||||||||||
| G | 1.5 | 3 | 4.5 | |||||||||||||
| H | 2.5 | 3.5 | 7.5 | |||||||||||||
| I | 1.5 | 2 | 2.5 | |||||||||||||
| J | 1 | 2 | 3 | |||||||||||||
| Expected completion time= | weeks | (Hint: Try =SUMPRODUCT(Path,Expected Completion Time) | ||||||||||||||
| Goal to complete = | 20 | weeks | Path variance= | weeks-squared | (Hint: Same =SUMPRODUCT as above) | |||||||||||
| Path StDev= | weeks | (Hint: take the square-root of the variance. =SQRT(x) | ||||||||||||||
| z-score= | ||||||||||||||||
| Probability to complete within Goal = | Note: P = NORM.S.DIST(z-score), as Standard-Normal Distribution has a Mean=0 and StDev=1. | |||||||||||||||
| Probability to complete Critical Path on-time = | ||||||||||||||||
| Probability to complete all paths by the goal = | Note: All tasks must be completed on time, not just the Critical Path. Therefore, P(all) = P1 * P2 * P3 * P4 | |||||||||||||||
| Hint: Change the goal in cell D21, then paste the answers above. | ||||||||||||||||
