Spreadsheet Modeling & Decision Analysis

1. Spreadsheet Modeling & Decision Analysis A Practical Introduction to Management Science 5th edition Cliff T. Ragsdale

2. Chapter 14 Project Management

3. Introduction to Project Management • Projects can be simple (planning a company picnic) or complex (planning a space shuttle launch). • Successfully completing a project requires: • Knowledge of the tasks involved • Accurate estimates of time and resources required • Knowledge of physical and logical relations between the various tasks • Project management techniques • Critical Path Method (CPM) • Program Evaluation and Review Technique (PERT) • Spreadsheets can be used to manage projects, but dedicated project management software is often more effective.

4. An Example: Lightner Construction • Tom Lightner owns Lightner Construction, a general contracting company specializing in the construction of single-family residences and small office buildings. • Tom frequently has numerous construction projects going on at the same time and needs a formal procedure for planning, monitoring, and controlling each project. • He is aware of various project scheduling techniques but has never used them. • He wants to see how he might apply such techniques to one of the home-building projects he will be undertaking in the near future. • The following slide summarizes each of the major activities required for this project.

5. Time Immediate Required Predecessor Activity Description (in days) Activities A Excavate 3 -- B Lay foundation 4 A C Rough plumbing 3 B D Frame 10 B E Finish exterior 8 D F Install HVAC 4 D G Rough electric 6 D H Sheet rock 8 C, E, F, G I Install cabinets 5 H J Paint 5 H K Final plumbing 4 I L Final electric 2 J M Install flooring 4 K, L Summary of Activities

6. Install Cabinets Final Plumbing Rough Plumbing K I C Sheet Rock Excavate Lay Foundation M H B A Finish Exterior E Install Flooring L J D Frame HVAC Final Electric Paint F Rough Electric G An Activity-On-Node (AON) Network

7. A Comment of Project Networks • Projects can also be depicted using Activity-On-Arc (AOA) networks. • This book uses AON networks (which the author views as superior to AOA). • Some software packages use AOA networks, so you should at least be aware that they exist.

8. Final Plumbing Install Cabinets 10 K Lay Foundation Rough Plumbing I B C 12 13 8 9 2 1 3 M H A Install Flooring Sheet Rock Excavate D J L 11 Frame Paint Final Electric Finish Exterior 5 E 4 F HVAC G 6 Rough Electric 7 An Activity-on-Arc (AOA) Network

9. A D C B E D A C start finish E B Start and Finish Points • AON networks should have unique start and finish points.

10. CPM: An Overview • A Forward Pass through the network determines the earliest times each activity can start and finish. • A Backward Pass through the network determines the latest times each activity can start and finish without delaying completion of the project. • The longest path through the network is the “critical path”.

11. Information Recorded for Each Node ESTi EFTi i ti LFTi LSTi ti = time required to perform activity i ESTi = earliest possible start time for activity i EFTi = earliest possible finish time for activity i LSTi = latest possible start time for activity i LFTi = latest possible finish time for activity i

12. The Forward Pass • The earliest start time (EST) for the initial activity in a project is “time zero”. • The EST of an activity is equal to the latest (or maximum) early finish time of the activities directly preceding it. • The EFT of an activity is equal to its EST plus the time required to perform the activity.

13. 38 42 33 38 7 10 4 I 5 K 3 C 42 46 25 33 3 7 0 3 4 8 M 4 H 3 B A 17 25 8 E 33 38 38 40 5 2 J L 7 17 10 D 17 21 4 F 17 23 6 G Results of the Forward Pass Note: ESTH=MAX(EFTC,EFTE,EFTF,EFTG)=25

14. The Backward Pass • The latest finish time (LFT) for the final activity in a project is equal to its EFT as determined by the forward pass. • The LFT for any other activity is equal to the earliest (or minimum) LST of the activities directly following (or succeeding) it. • The LST of an activity is equal to its LFT minus the time required to perform the activity.

15. 38 42 33 38 7 10 4 5 K I 3 C 42 46 0 3 3 7 H 4 3 4 M A B 17 25 8 E 33 38 38 40 5 2 J L D 17 21 4 F 17 23 6 G Results of the Backward Pass 38 42 38 33 22 25 25 33 8 25 33 42 46 0 3 7 3 17 25 7 17 10 35 40 40 42 7 17 Note: LFTH=MIN(LSTI,LSTJ)=33 LFTD=MIN(LSTE,LSTF ,LSTG)=17 LFTB=MIN(LSTC,LSTD)=7 21 25 19 25

16. 33 38 38 42 7 10 5 4 I K 3 C 3 7 0 3 H 4 3 B A 17 25 8 E 33 38 38 40 5 2 J L D 17 21 4 F 17 23 6 G The Critical Path 38 42 38 33 22 25 Slack=15 Slack=0 Slack=0 25 33 42 46 8 M 4 25 33 42 46 0 3 7 3 Slack=0 Slack=0 Slack=0 Slack=0 17 25 7 17 Slack=0 10 35 40 40 42 7 17 Slack=2 Slack=2 Slack=0 21 25 Slack=4 Note: Slack = LSTi-ESTi or LFTi-EFTi 19 25 Slack=2

17. Determining The Critical Path • Critical activities have zero slack and cannot be delayed without delaying the completion of the project. • The slack for non-critical activities represents the amount of time by which the start of these activities can be delayed without delaying the completion of the entire project (assuming that all predecessor activities start at their earliest start times).

18. Project Management Using Spreadsheets • The early and late start and finish times for project activities can be done in a spreadsheet using array formulas and circular references.

19. Array Formulas • An array formula can perform multiple calculations using a range of cells and then return either a single result or multiple results. • You create array formulas in the same way that you create other formulas, except that you press [Ctrl]+[Shift]+[Enter] to enter the formula.

20. Array Formula Examples-I • Let’s compare several standard Excel functions with their equivalent array formulas… Excel Function =SUMPRODUCT(E5:E17,F5:F17) Array Formula =SUM(E5:E17*F5:F17)

21. Array Formula Examples-II • Let’s compare several standard Excel functions with their equivalent array formulas… Excel Function =SUMIF(E5:E17,">10",F5:F17) Array Formula =SUM(IF(E5:E17>10,F5:F17))

22. Array Formula Examples-III • Let’s compare several standard Excel functions with their equivalent array formulas… Excel Function =COUNTIF(E5:E17,">0") Array Formula =SUM(IF(E5:E17>0,1))

23. Array Formula Examples-IV • Let’s compare several standard Excel functions with their equivalent array formulas… Excel Function =SUMXMY2(E5:E17,F5:F17) Array Formula =SUM((E5:E17-F5:F17)^2)

24. Array Formula Examples-IV • Let’s compare several standard Excel functions with their equivalent array formulas… Excel Function =VARP(E5:E17) Array Formula =AVERAGE((E5:E17-AVERAGE(E5:E17))^2)

25. Circular References • The array formulas used to implement the project management calculations create circular references. • A circular reference occurs when the value in a cell depends on the value in another cell that, in turn, is dependent on the value in the original cell. • Usually, a circular reference indicates a formula contains an error – and Excel displays a warning telling you so! • Occasionally, a circular reference is needed to accomplish a particular task. This is such an occasion. • To tell Excel you intend to use circular references: • Click Tools, Options • Click the Calculation tab. • Select the Iteration option. • Click OK.

26. Project Management Example See file Fig14-11.xls Learning Tip! Use the Tools, Formula Auditing, Evaluate Formula command to step through the evaluation of the array formulas for the EST and LFT calculations.

27. Important Implementation Issue • It is important to use activity labels that are unique and do not appear as substrings within other activity labels. • For example, the 26 letters of the English alphabet may be used to uniquely identify up to 26 activities in a project. • Using the strings "A1" and "A11" as activity labels won’t work • The FIND( ) function would locate "A1" within "A11" (i.e., FIND("A1","A11")=1) -- erroneously identifying activity A11 as a predecessor or successor of activity A1. • Using the strings "A01" and "A11" as activity labels easily remedies this situation.

28. Important Implementation Issue • If you use numbers rather than letters to identify activities, using the numbers 1, 2, 3, …, 9 as activity labels would create matching problems within activity labels 11, 12, 13, …, 19 (among others). • This can be avoided easily by applying Excel's "Text" format to cells containing activity labels and immediate predecessors and using two-digit numbers for all activity labels (e.g., 01, 02, 03, …, 09, 10, 11, 12, 13, …, 99). • If more than 100 numeric activity labels are needed, three-digit numbers (formatted as text) should be used.

29. Activity Gantt Chart M L K J I H G F E D Activity Time C Slack B A 0 5 10 15 20 25 30 35 40 45 50 Time Period A Gantt Chart for the Example Problem

30. Project Crashing • It is often possible to complete activities more quickly than normal by applying more resources (better equipment, over time, etc). • This is referred to as “crashing” the project. • We may want to determine the optimal way of crashing a project to: • complete it more quickly than originally scheduled • keep it on schedule if critical activities were delayed

31. Computing Crash Times and Costs See file Fig14-14.xls

32. Determining the Earliest Crash Completion Time • We can determine the earliest possible (crashed) completion time of a project by solving an LP problem…

33. Defining The Decision Variables Ti = the time at which activity i begins ti = the normal activity time of activity i Ci = the amount of time by which activity i is crashed

34. Defining The Objective Minimize the completion time of the last activity (activity M): MIN: TM + tM - CM

35. Defining The Constraints For each arc in the project network from activity i to activity j, we need a constraint of the form: Tj >= Ti + ti - Ci

36. Summary of the Earliest Completion Time Model S.T.: TB - TA >= tA - CA TC - TB >= tB - CB TD - TB >= tB - CB TE - TD >= tD - CD TF - TD >= tD - CD TG - TD >= tD - CD TH - TC >= tC - CC TH - TE >= tE – CE TH - TF >= tF - CF MIN: TM + tm - CM TH - TG >= tG - CG TI - TH >= tH - CH TJ - TH >= tH - CH TK - TI >= tI - CI TL - TJ >= tJ - CJ TM - TK >= tK - CK TM - TL >= tK – CL Ti, Ci >= 0, for all i Note: This model can easily be modified to minimize crash costs. Ci<= allowable crash days for activity i

37. Implementing the Model See file Fig14-14.xls

38. Crash Cost $20,000 $18,000 $16,000 $14,000 $12,000 $10,000 $8,000 $6,000 $4,000 $2,000 $0 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Completion Time (in days) Cost/Time Trade-Off Curve

39. PERT then estimates expected durationtiand varianceviof each activity’s duration as: PERT: An Overview • CPM assumes all activity times are known with certainty or can be estimated accurately. • PERT accounts for uncertainty in activity times by using three time estimates: ai= duration of activity i assuming the most favorable conditions bi = duration of activity i assuming the least favorable conditions mi = estimate of the most likely duration of activity i

40. PERT Overview Continued • The expected (or mean) time required to complete any path in the network is the sum of the expected times (the ti) of the activities on the path. • Assuming the individual activity times in a project are independent of one another, we may also calculate the variance of the completion time for any path as the sum of the variances (the vi) of the activities on the path. • PERT considers the path with the largest expected completion time to be the critical path. • PERT’s reasoning may be flawed...

41. PERT Example B a=8 t =9 m=9 v=0.111 b=10 A D a=2 a=3 t =4 t =5 m=4 m=5 v=0.444 v=0.444 b=6 b=7 C a=2 t =8 m=8 v=4.0 b=14 Variance: Path: Expected Time: 1.000 4 + 9 + 5 = 18 A - B - D 4.889 4 + 8 + 5 = 17 A - C - D

42. "Critical" Path A-B-D Probability Density "Non-Critical" Path A-C-D 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Path Completion Time Distribution of Completion Times If we want to finish within 21 days, which path is most critical?

43. Probability Density a m b 3 0 1 2 4 5 6 Time Required Simulating a Project Network • The solution to the “problem” with PERT is to use simulation. • We can model activity times easily using a triangular distribution...

44. Simulating The Project Network See file Fig14-25.xls

45. Microsoft Project • Dedicated project management software such as MS Project can greatly simplify the process of organizing, planning, and controlling projects. • A trial version of MS Project is included on the CD-ROM accompanying this book.

46. End of Chapter 14