cpm, crashing, resource leveling using ms excel & ms project david s.w. lai sept 24, 2013 1
TRANSCRIPT
![Page 1: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/1.jpg)
1
CPM, Crashing, Resource Leveling
using MS Excel & MS ProjectDavid S.W. LaiSept 24, 2013
![Page 2: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/2.jpg)
2
Scope
Linear Programming (LP) approachMS Excel 2010
MS Project 2010
• Critical Path Analysis
• Crashing
• Resource leveling
![Page 3: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/3.jpg)
3
Critical Path Method
A Linear Programming Approach
![Page 4: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/4.jpg)
4
Activity Immediate Predecessors
Expected Time (days)
1 Walls and Ceiling 2 5
2 Foundation - 3
3 Roof Timbers 1 2
4 Roof Sheathing 3 3
5 Electrical Wiring 1 4
6 Roof Shingles 4 8
7 Exterior Siding 8 5
8 Windows 1 2
9 Paint 6, 7, 10 2
10 Inside Wall Board 8, 5 3
The example description is modified from the exercises described in Moore and Weatherford, Decision Modelling, Pearson 2001.
The Build-Rite Construction Company has identified the following ten activities that take place in building a house.
Determine the critical path and the critical activities.
Example Problem
![Page 5: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/5.jpg)
5
Solution
Critical activities: 1, 2, 3, 4, 6, 9
ActivityEarly Start Schedule Late Start Schedule
Total SlacksES EF LS LF
1 3 8 3 8 02 0 3 0 3 03 8 10 8 10 04 10 13 10 13 05 8 12 14 18 66 13 21 13 21 07 10 15 16 21 68 8 10 14 16 69 21 23 21 23 0
10 12 15 18 21 6
The project manager should adjust accordingly the budgets and resource allocations to avoid any delay on these activities.
![Page 6: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/6.jpg)
6
Critical Path Method• Step 1: Forward pass • Step 2: Backward pass• Step 3: Calculating slacks
Early Start Schedule
Late Start Schedule
Slacks
![Page 7: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/7.jpg)
7
Precedence Constraints
Predecessor Successor Duration of the Predecessor
2 1 3
1 3 5
3 4 2
1 5 5
4 6 3
8 7 2
1 8 5
6 9 8
7 9 5
10 9 3
5 10 4
8 10 2
Objective Functionminimize the project duration.
Constraintse.g. activity 6 precedes activity 9
Decision Variablesstart times of the activities
A LP Model for CPM analysis
![Page 8: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/8.jpg)
8
Nodes Decision Variables
ArcsPrecedence Constraints
92 1
3
5
8 7
3 5
2
4
2
83
4
10
6
3
5
2
Longest Path
Optimal Solution
AON network & LP Model
Note that an alternative LP model can be derived from the AOA network.Critical activities can then be identified via sensitivity analysis.
![Page 9: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/9.jpg)
9
Parameters
Activity Time (days)1 Walls and Ceiling 52 Foundation 33 Roof Timbers 24 Roof Sheathing 35 Electrical Wiring 46 Roof Shingles 87 Exterior Siding 58 Windows 29 Paint 2
10 Inside Wall Board 3Project Start Time 0
Precedence ConstraintsPredecesor Successor
2 1
1 3
3 4
1 5
4 6
8 7
1 8
6 9
7 9
10 9
5 10
8 10
• The start time of the project• The (expected) times of the activities• Precedence Relations of two activities
![Page 10: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/10.jpg)
10
A Linear Programming Approach for
Critical Path AnalysisA Spreadsheet Implementation
Activity Time (days) Start time Finish time1 Walls and Ceiling 5 - -2 Foundation 3 - -3 Roof Timbers 2 - -4 Roof Sheathing 3 - -5 Electrical Wiring 4 - -6 Roof Shingles 8 - -7 Exterior Siding 5 - -8 Windows 2 - -9 Paint 2 - -
10 Inside Wall Board 3 - -Project Start Time 0
Objective Value
Start time Finish time3 80 38 10
10 138 12
13 2110 158 10
21 2312 15
23 days
![Page 11: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/11.jpg)
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.
ES EF3 80 38 10
10 138 12
13 2110 158 10
21 2312 15
LS LF3 80 38 10
10 1314 1813 2116 2114 1621 2318 21
![Page 12: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/12.jpg)
12
Critical Activities
Early Start/Late Start ScheduleTotal Slacks
Activity ES EF LS LF1 3 8 3 8 02 0 3 0 3 03 8 10 8 10 04 10 13 10 13 05 8 12 14 18 66 13 21 13 21 07 10 15 16 21 68 8 10 14 16 69 21 23 21 23 0
10 12 15 18 21 6
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
![Page 13: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/13.jpg)
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
![Page 14: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/14.jpg)
14
Crashing
A Linear Programming Approach
![Page 15: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/15.jpg)
15
Activity
Normal
Time
Normal
Cost
Crash Time
Crash Cost
1 5 50 3 72
2 3 20 2 30
3 2 15 1 30
4 3 8 1 20
5 4 30 4 30
6 8 13 4 21
7 5 45 1 65
8 2 45 1 52
9 2 40 2 40
10 3 22 2 34
2 3 4 5 640
60
80
Activity TimeC
ost
e.g. Cost for Activity 1
Build-Rite’s engineers have calculated the cost of completing each activity. Their results are given below.
How much would it cost to complete the project within 22 days? 21 days? 20 days?...
Example Problem
![Page 16: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/16.jpg)
16
Solution: Time-Cost Trade-Off
Project Duration
ProjectCost
13 18 23280
300
320
340
360
380
The normal schedule obtained using CPM
each activity is performed at its lowest cost and at a normal 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.
![Page 17: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/17.jpg)
17
Notations
crashMax. Crash Days
![Page 18: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/18.jpg)
18
A LP Model for Crashing with a fixed project due date
Precedence Constraints
Max. Clashed Days
Project due date
Minimize the cost for crashing
No. of days to crash
Start times of the activities.
![Page 19: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/19.jpg)
19
Crashing
Activity Crashing (days)
Normal Time
Normal Cost
Crash Time
Crash Cost
Max. Crash Days
Cost per Crash Day
1 ? 5 50 3 72 2 112 ? 3 20 2 30 1 103 ? 2 15 1 30 1 154 ? 3 8 1 20 2 65 ? 4 30 4 30 0 06 ? 8 13 4 21 4 27 ? 5 45 1 65 4 58 ? 2 45 1 52 1 79 ? 2 40 2 40 0 0
10 ? 3 22 2 34 1 12
Crash Cost ?Project Cost ?
Obj. Value ?
A Linear Programming Approach for Crashing
A Spreadsheet Implementation
Crashing (days)
2112041001
84288
372.1
![Page 20: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/20.jpg)
20
Demo
![Page 21: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/21.jpg)
21
Resource Leveling
MS Project 2010
![Page 22: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/22.jpg)
22The example question is modified from Project Management (Shtub, Bard, Globerson) Exercise 10.1
Activity Immediate Predecessor
s
Expected Time (days)
Work hours
1 Walls and Ceiling 2 5 30 hrs2 Foundation - 3 22 hrs3 Roof Timbers 1 2 8 hrs4 Roof Sheathing 3 3 16 hrs5 Electrical Wiring 1 4 6 hrs6 Roof Shingles 4 8 4 hrs7 Exterior Siding 8 5 6 hrs8 Windows 1 2 12 hrs9 Paint 6, 7, 10 2 8 hrs
10 Inside Wall Board 8, 5 3 4 hrs
The working hours requirements of the activities are estimated. They are described below.
Example
![Page 23: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/23.jpg)
23
The resource profile after leveling.• Minimized resource fluctuation• No delay in the project
Resource leveling
The resource profile before leveling.• large resource fluctuation• Overallocation of resource
The reallocation of slacks in activities to minimize fluctuations in resource requirement profile.
![Page 24: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/24.jpg)
24
Demo
![Page 25: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/25.jpg)
25
1. Create a Project.
Set the working hours per day. E.g. 8 hours.
The durations of activities (or tasks) are fixed in our case.
1. File New Blank Project
2. File Options Schedule
![Page 26: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/26.jpg)
26
2. Input the task information
1. Task Gantt Chart 2. Input the task
information3. Select all the tasks
and then press “Auto Schedule”
![Page 27: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/27.jpg)
27
3. Set the Project Start Date• Project Project Information
Statistics
![Page 28: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/28.jpg)
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.
![Page 29: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/29.jpg)
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
![Page 30: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/30.jpg)
30
5. Type in the resource usage
• Input the work hours of the activities • Select the column right click Assign
Resources Select “Manpower” AssignTask Work hour
1 30 hrs2 22 hrs3 8 hrs4 16 hrs5 6 hrs6 4 hrs7 6 hrs8 12 hrs9 8 hrs10 4 hrs
![Page 31: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/31.jpg)
31
6. Resource Graph• Task Gantt chart Resource Graph
Large frustration
![Page 32: CPM, Crashing, Resource Leveling using MS Excel & MS Project David S.W. Lai Sept 24, 2013 1](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c805503460f94937fda/html5/thumbnails/32.jpg)
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 ffrustration