Spreadsheet Modeling

1
Spreadsheet Modeling Decision Analysis

• A Practical Introduction to Management Science
• 4th edition
• Cliff T. Ragsdale

2
Project Management
Chapter 14
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
Summary of Activities
6
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
An Activity-on-Arc (AOA) Network
9
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
Results of the Forward Pass
Note ESTHMAX(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
Results of the Backward Pass
38
42
33
38
22
25
25
33
8
25
33
42
46
0
3
7
3
17
25
7
17
10
35
40
42
40
17
7
Note LFTHMIN(LSTI,LSTJ)33 LFTDMIN(LSTE,LSTF
,LSTG)17 LFTBMIN(LSTC,LSTD)7
21
25
25
19
16
The Critical Path
38
42
33
38
22
25
Slack15
Slack0
Slack0
25
33
42
46
8
M
4
25
33
42
46
0
3
7
3
Slack0
Slack0
Slack0
Slack0
17
25
7
17
Slack0
10
35
40
42
40
17
7
Slack2
Slack2
Slack0
21
25
Slack4
Note Slack LSTi-ESTi or LFTi-EFTi
25
19
Slack2
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
  CtrlShiftEnter to enter the formula.

20
Array Formula Examples-I

• Lets compare several standard Excel functions
  with their equivalent array formulas
• Excel Function
• SUMPRODUCT(E5E17,F5F17)
• Array Formula
• SUM(E5E17F5F17)

21
Array Formula Examples-II

• Lets compare several standard Excel functions
  with their equivalent array formulas
• Excel Function
• SUMIF(E5E17,"gt10",F5F17)
• Array Formula
• SUM(IF(E5E17gt10,F5F17))

22
Array Formula Examples-III

• Lets compare several standard Excel functions
  with their equivalent array formulas
• Excel Function
• COUNTIF(E5E17,"gt0")
• Array Formula
• SUM(IF(E5E17gt0,1))

23
Array Formula Examples-IV

• Lets compare several standard Excel functions
  with their equivalent array formulas
• Excel Function
• SUMXMY2(E5E17,F5F17)
• Array Formula
• SUM((E5E17-F5F17)2)

24
Array Formula Examples-IV

• Lets compare several standard Excel functions
  with their equivalent array formulas
• Excel Function
• VARP(E5E17)
• Array Formula
• AVERAGE((E5E17-AVERAGE(E5E17))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 wont 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
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, overtime, 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 gt Ti ti - Ci

36
Summary of the Earliest Completion Time Model
MIN TM tm - CM

• S.T. TB - TA gt tA - CA
• TC - TB gt tB - CB
• TD - TB gt tB - CB
• TE - TD gt tD - CD
• TF - TD gt tD - CD
• TG - TD gt tD - CD
• TH - TC gt tC - CC
• TH - TE gt tE CE
• TH - TF gt tF - CF

TH - TG gt tG - CG TI - TH gt tH - CH TJ - TH gt
tH - CH TK - TI gt tI - CI TL - TJ gt tJ - CJ TM
- TK gt tK - CK TM - TL gt tK CL Ti, Ci gt 0,
for all i
Note This model can easily be modified to
minimize crash costs.
Ci lt allowable crash days for activity i
37
Implementing the Model

• See file Fig14-14.xls

38
Cost/Time Trade-Off Curve
39
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.
• PERTs reasoning may be flawed...

41
PERT Example
B
a8
t 9
m9
v0.111
b10
A
D
a2
a3
t 4
t 5
m4
m5
v0.444
v0.444
b6
b7
C
a2
t 8
m8
v4.0
b14
Variance
Path
Expected Time
1.000
A - B - D
4 9 5
18
4.889
4 8 5 17
A - C - D
42
Distribution of Completion Times
If we want to finish within 21 days, which path
is most critical?
43
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