CPM, Crashing, Resource Leveling using MS Excel & MS Project

1. CPM, Crashing, Resource Levelingusing MS Excel & MS Project David S.W. Lai Sept 24, 2013

2. Scope • Critical Path Analysis • Crashing • Resource leveling • Linear Programming (LP) approach • MS Excel 2010 • MS Project2010

3. Critical Path Method A Linear Programming Approach

4. Example Problem Determine the critical path and the critical activities. The Build-Rite Construction Company has identified the following ten activities that take place in building a house. The example description is modified from the exercises described in Moore and Weatherford, Decision Modelling, Pearson 2001.

5. Solution Critical activities: 1, 2, 3, 4, 6, 9 The project manager should adjust accordingly the budgets and resource allocations to avoid any delay on these activities.

6. Critical Path Method Early Start Schedule • Step 1: Forward pass • Step 2: Backward pass • Step 3: Calculating slacks Late Start Schedule Slacks

7. A LP Model for CPM analysis Objective Function minimize the project duration. Constraints e.g. activity 6 precedes activity 9 Decision Variables start times of the activities

8. AON network & LP Model 2 3 8 3 4 6 Nodes Decision Variables Precedence Constraints 2 Arcs 3 5 4 3 9 2 1 10 Optimal Solution 5 Longest Path 2 5 8 7 Note that an alternative LP model can be derived from the AOA network. Critical activities can then be identified via sensitivity analysis.

9. Parameters • The start time of the project • The (expected) times of the activities • Precedence Relations of two activities

10. A Linear Programming Approach for Critical Path AnalysisA Spreadsheet Implementation 23 days

11. Early start schedule Any activity will be started at its earliest start time. • Late start schedule Any activity will be started at its latest start time.

12. Critical Activities Since the total slacks can be determined using the early start schedule and the late start schedule, the critical activities can be identified as well. Critical activities: 1, 2, 3, 4, 6, 9

13. Demo • To enable the solver in EXCEL 2010 • File  Options Add-Ins  Select “Solver Add-in”  Go Select “Solver Add-in”  OK • You may find the solver in • Data  Solver Objective Function Decision Variables Constraints Use simplex method for the LP models

14. Crashing A Linear Programming Approach

15. Example Problem Build-Rite’s engineers have calculated the cost of completing each activity. Their results are given below. e.g. Cost for Activity 1 How much would it cost to complete the project within 22 days? 21 days? 20 days?...

16. Solution: Time-Cost Trade-Off Project Cost The normal schedule obtained using CPM each activity is performed at its lowest cost and at a normal duration. Project Duration The crashing process has revealed a relationship between the cost and the schedule of the project, which allows us to prepare our budget by considering the possible trade-offs between cost and time.

17. Notations crash Max. Crash Days

18. A LP Model for Crashing with a fixed project due date Minimize the cost for crashing Precedence Constraints Max. Clashed Days No. of days to crash Project due date Start times of the activities.

19. A Linear Programming Approach for CrashingA Spreadsheet Implementation

20. Demo

21. Resource Leveling MS Project 2010

22. Example The working hours requirements of the activities are estimated. They are described below. The example question is modified from Project Management (Shtub, Bard, Globerson) Exercise 10.1

23. Resource leveling The reallocation of slacks in activities to minimize fluctuations in resource requirement profile. • The resource profile before leveling. • large resource fluctuation • Overallocation of resource • The resource profile after leveling. • Minimized resource fluctuation • No delay in the project

24. Demo

25. 1. Create a Project. File  New  Blank Project File  Options  Schedule Set the working hours per day. E.g. 8 hours. The durations of activities (or tasks) are fixed in our case.

26. 2. Input the task information • Task  Gantt Chart • Input the task information • Select all the tasks and then press “Auto Schedule”

27. 3. Set the Project Start Date • Project  Project Information  Statistics

28. 3. Identify the critical path • Task  Gantt Chart  Network Diagram • Gantt chart  Add New Column  “total slack” Note that the project can be finished within 23 days.

29. 4. Add a renewable resource • Task  Gantt Chart  Resource Sheet • In the first row, input • Resource Name: Manpower • Type: work • Max. Units: 100% • Examples of renewable resource • Manpower • Materials • Machines

30. 5. Type in the resource usage • Input the work hours of the activities • Select the column  right click  Assign Resources  Select “Manpower”  Assign

31. 6. Resource Graph • Task  Gantt chart  Resource Graph Large frustration

32. 7. Resource Leveling • Resource  Leveling Options  tick “level only within available slack. • Resource  level all • Frustration is minimized. • No over-allocation • The project duration remains the same (total slacks are reduced ) Smaller f frustration