Transcript Spreadsheet Modeling & Decision Analysis:

Spreadsheet Modeling
& Decision Analysis
A Practical Introduction to
Management Science
5th edition
Cliff T. Ragsdale
Chapter 14
Project Management
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.
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.
Summary of Activities
Activity
A
B
C
D
E
F
G
H
I
J
K
L
M
Description
Excavate
Lay foundation
Rough plumbing
Frame
Finish exterior
Install HVAC
Rough electric
Sheet rock
Install cabinets
Paint
Final plumbing
Final electric
Install flooring
Time
Immediate
Required Predecessor
(in days) Activities
3
-4
A
3
B
10
B
8
D
4
D
6
D
8
C, E, F, G
5
H
5
H
4
I
2
J
4
K, L
An Activity-On-Node (AON) Network
Install
Cabinets
Rough
Plumbing
I
C
A
B
K
Sheet
Rock
Excavate
Lay
Foundation
Final
Plumbing
Finish
Exterior
M
H
Install
Flooring
E
Frame
D
HVAC
F
Rough
Electric
G
J
L
Paint
Final
Electric
A Comment of Project Networks
 Projects can also be depicted using Activity-OnArc (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.
An Activity-on-Arc (AOA) Network
Lay
Foundation
1
A
2
Excavate
B
Install
Cabinets
Rough
Plumbing
C
3
I
8
H
Sheet
Rock
D
Frame
Finish
Exterior
E
4
G
Rough
Electric
5
F HVAC
6
7
10
K
Final
Plumbing
12
9
J
Paint
11
L
M
13
Install
Flooring
Final
Electric
Start and Finish Points
 AON networks should have unique start and
finish points.
A
D
C
B
E
A
D
start
C
B
finish
E
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”.
Information Recorded for Each Node
ESTi
i
EFTi
ti
LSTi LFTi
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
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.
Results of the Forward Pass
7
33 38
10
I
C 3
0
3
A 3
3
7
B 4
5
38 42
K 4
25 33
42 46
H 8
M 4
17 25
E 8
33 38
7
J
17
5
38 40
L 2
D 10
17 21
F 4
Note:
ESTH=MAX(EFTC,EFTE,EFTF,EFTG)=25
17 23
G 6
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.
Results of the Backward Pass
33 38
7 10
I
C 3
33 38
22 25
0
3
A 3
0
3
3
7
B 4
3
5
7
38 42
K 4
38 42
25 33
42 46
H 8
M 4
25 33
42 46
17 25
E 8
17 25
33 38
J
7 17
D 10
5
35 40
38 40
L 2
40 42
7 17
17 21
F 4
21 25
17 23
G 6
19 25
Note:
LFTH=MIN(LSTI,LSTJ)=33
LFTD=MIN(LSTE,LSTF ,LSTG)=17
LFTB=MIN(LSTC,LSTD)=7
The Critical Path
33 38
7 10
I
C 3
22 25
Slack=15
0
3
A 3
0
3
Slack=0
3
7
B 4
3
7
17 25
Slack=0
E 8
17 25
7 17
D 10
Slack=0
7 17
Slack=0
5
38 42
K 4
33 38
38 42
Slack=0
Slack=0
25 33
42 46
H 8
M 4
25 33
42 46
Slack=0
Slack=0
33 38
J
5
35 40
Slack=2
38 40
L 2
40 42
Slack=2
17 21
F 4
21 25
Slack=4
17 23
G 6
19 25
Slack=2
Note:
Slack = LSTi-ESTi or LFTi-EFTi
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).
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.
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.
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)
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))
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))
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)
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)
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.
Project Management Example
See file Fig14-11.xls
Use the
Learning Tip!
Tools, Formula Auditing, Evaluate Formula
command to step through the evaluation of
the array formulas for the EST and LFT
calculations.
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.
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.
A Gantt Chart for the Example Problem
Activity
Gantt Chart
M
L
K
J
I
H
G
F
E
D
C
B
A
Activity Time
Slack
0
5
10
15
20
25
Time Period
30
35
40
45
50
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
Computing Crash Times and Costs
See file Fig14-14.xls
Determining the Earliest Crash
Completion Time
 We can determine the earliest possible
(crashed) completion time of a project
by solving an LP problem…
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
Defining The Objective
Minimize the completion time of the last
activity (activity M):
MIN: TM + tM - CM
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
Summary of the
Earliest Completion Time Model
MIN: TM + tm - CM
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
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
Ci <= allowable crash days for activity i
Note: This
model can
easily be
modified to
minimize
crash costs.
Implementing the Model
See file Fig14-14.xls
Cost/Time Trade-Off Curve
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
Completion Time (in days)
40
41
42
43
44
45
46
47
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
 PERT then estimates expected duration ti and variance vi
of each activity’s duration as:
ai  4mi  bi
6
(bi  ai ) 2
vi 
36
ti 
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...
PERT Example
B
a=8 t =9
m=9 v=0.111
b=10
A
a=2
m=4
b=6
t =4
v=0.444
Path:
A-B-D
A-C-D
a=3
m=5 t =5
b=7 v=0.444
C
a=2
m=8
b=14
D
t =8
v=4.0
Expected Time:
4 + 9 + 5 = 18
4 + 8 + 5 = 17
Variance:
1.000
4.889
Distribution of Completion Times
Probability Density
If we want to finish
within 21 days, which
path is most critical?
"Critical" Path
A-B-D
"Non-Critical" Path
A-C-D
10
11
12
13
14
15
16
17
18
Path Completion Time
19
20
21
22
23
24
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...
Probability
Density
a
0
1
m
3
2
4
5
Time Required
b
6
Simulating The Project Network
See file Fig14-25.xls
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.
End of Chapter 14