PROJECT 1

QUANTIATIVE BUSINESS ANALYSIS - BUS 190

Professor RONALD DAVIS
EXECUTIVE SUMMARY





Student: Bao Lam
ID: 008700276

2
I. WESTERN HILLS SHOPPING CENTER EXPANSION PROBLEM
The owner of the Western Hills Shopping Center is planning to modernize and expand the current
32-business shopping center complex. The project is expected to provide room for 8 to 10 new
businesses. Financing has been arranged through a private investor. All that remains is for the owner
of the shopping center to plan, schedule, and complete the expansion project. The activities and time
estimates for this project are shown in the following table, where Modal Time refers to the most
likely activity duration within the interval from the Min and Max times.
The project manager, Jennifer Frost, wants to know the probability of completing this project in 32
weeks, as requested by her boss, and how much more time to ask for in order to have a 99% chance
of timely completion.
II. METHODOLOGIES/TOOLS USED
1. Critical Path Method
The Critical Path Method (CPM) is one of several related techniques for doing project planning.
CPM is for projects that are made up of a number of individual "activities." If some of the activities
require other activities to finish before they can start, then the project becomes a complex web of
activities.
CPM can help you figure out:
- How long the complex project will take to complete
- Which activities are "critical," meaning that they have to be done on time or else the whole
project will take longer.

2. PERT Method
The PERT method includes the following at the preliminary stage:
- Precise breakdown of the project into tasks;
- Estimation of the duration of each task;
- The appointment of a project manager in charge of assuring monitoring of the project,
reporting, if necessary, and making decisions in the event of deviations from projections.
3
In this project we only use the normal distribution as a means of getting a first cut, ballpark estimate
based on your three scenario analyses.
3. Microsoft Excel
Using Microsoft Excel to apply Critical Path Method will help us save much time in calculating and
choosing the appropriate result. Moreover, there is a function of Microsoft Excel is called Macro
which can help us get the result automatically without any calculation step. The macro can also help
us in checking the first result to make sure it is right.
III. DATA TABLES/DIAGRAMS DEFINING PROBLEM
1. Data table


Activity Predecessor Min Time Modal Time Max Time
A Prepare architectural drawings 4 5 13
B Identify potential new tenants 9 11 13
C Develop prospectus for tenants A 1 4 5
D Select Contractor A 2 3 4
E Prepare Building Permits A 1 1 4
F Obtain approval for building permits E 3 4 5
G Perform Construction D, F 10 10 12
H Painting & detail work G 2.5 4 7.5
I Finalize contracts with tenants B, C 11.5 12 13.5
J Tenants move in H, I 1 2 3
Z EOP J 0 0 0
4
2. Diagrams
This diagram show us the relationship between activities in this project, the predecessor and duration
of each activity. Based on this diagram we can easily check the accuracy of the formulas we use in
Excel Workbook. The very first node is BOP which is Begin of Project and this activity does not
have duration.
5
IV. PRINCIPAL RESULTS
1. When we refer to the Modal time to run macro then we have the following result
Critical Path: AEFGHJ
Critical Path Duration: 26 days

2. When we refer to the Minimal time to run macro then we have the following result
Activity Predecessor Modal Time EST EFT LST LFT Slack Critical Acts Critical Path
A Prepare architectural drawings 5 0 5 0 5 0 1 A
B Identify potential new tenants 11 0 11 1 12 1 0
C Develop prospectus for tenants A 4 5 9 8 12 3 0
D Select Contractor A 3 5 8 7 10 2 0
E Prepare Building Permits A 1 5 6 5 6 0 1 E
F Obtain approval for building permits E 4 6 10 6 10 0 1 F
G Perform Construction D, F 10 10 20 10 20 0 1 G
H Painting & detail work G 4 20 24 20 24 0 1 H
I Finalize contracts with tenants B, C 12 11 23 12 24 1 0
J Tenants move in H, I 2 24 26 24 26 0 1 J
Z EOP J 0 26 26 26 26 0 1 AEFGHJ
Activity Predecessor Min Time EST EFT LST LFT Slack Critical Acts Critical Path
A Prepare architectural drawings 4 0 4 0 4 0 1 A
B Identify potential new tenants 9 0 9 0 9 0 1 B
C Develop prospectus for tenants A 1 4 5 8 9 4 0
D Select Contractor A 2 4 6 6 8 2 0
E Prepare Building Permits A 1 4 5 4 5 0 1 E
F Obtain approval for building permits E 3 5 8 5 8 0 1 F
G Perform Construction D, F 10 8 18 8 18 0 1 G
H Painting & detail work G 2.5 18 20.5 18 20.5 0 1 H
I Finalize contracts with tenants B, C 11.5 9 20.5 9 20.5 0 1 I
J Tenants move in H, I 1 20.5 21.5 20.5 21.5 0 1 J
Z EOP J 0 21.5 21.5 21.5 21.5 0 ABEFGHIJ
6
Critical Path: ABEFGHIJ
Critical Path Duration: 21.5 days

3. When we refer to the Maximum time to run macro then we have the following result

Critical Path: AEFGHJ
Critical Path Duration: 44.5 days

4. Result abstract


CP Duration(Days) CP Activities
MIN times 21.5 ABEFGHIJ
MODAL times 26 AEFGHJ
MAX times 44.5 AEFGHJ


Activity Predecessor Max Time EST EFT LST LFT Slack Critical Acts Critical Path
A Prepare architectural drawings 13 0 13 0 13 0 1 A
B Identify potential new tenants 13 0 13 15 28 15 0
C Develop prospectus for tenants A 5 13 18 23 28 10 0
D Select Contractor A 4 13 17 18 22 5 0
E Prepare Building Permits A 4 13 17 13 17 0 1 E
F Obtain approval for building permits E 5 17 22 17 22 0 1 F
G Perform Construction D, F 12 22 34 22 34 0 1 G
H Painting & detail work G 7.5 34 41.5 34 41.5 0 1 H
I Finalize contracts with tenants B, C 13.5 18 31.5 28 41.5 10 0
J Tenants move in H, I 3 41.5 44.5 41.5 44.5 0 1 J
Z EOP J 0 44.5 44.5 44.5 44.5 0 1 AEFGHJ
7
5. Cumulative probability of finishing the project in 32 days

As we can see in the table, the median time of this project is 28.3333 days, and the standard
deviation is 3.8333 shows how much variation or dispersion from the average exists. The
standard deviation is not too high so that the data points tend to close to the mean but not too
close and nor too far.
The result also show us the probability of finishing this project within 32 days is 83 percent and
99 percent of probability that this project will be finished in 37.25 days.
V. FORMULAS/FORMULATION ALGEBRAIC EXPRESSION/EQUATIONS
1. To calculate the finish time of this project we have to use Critical Path Method in Excel.
In order to do that, we copy the table given above into a spreadsheet named "CPMmodel", and
then add four more columns labeled "EST" for Early Start time, "EFT" for Early Finish time,
"LST" for Late Start time, and "LFT" for Late Finish time. The EST and EFT columns will
carry the forward pass results, and the LST and LFT columns will carry the backward pass
results.
2. The Early Finish time for an activity is the Early Start time of the activity plus the
duration of the activity,
EF(j) = ES(j) + duration(j)
We insert this formula in column H row 2 for activity A (=E2+G2) and then fills it down through
all the other activities in column H. In like manner, the Late Start for an activity is the Late
Finish of the activity minus the duration of the activity,
LS(i)=LF(i)-duration(i).
PERTmean 28.3333333
PERTstdev 3.83333333
Pr(T<=32) 83%
T(0.99) 37.2510002
8
Hence in Column I row 2 we put the formula =J2-E2 and then copies the formula down through
all the activity rows in the table.
3. The early start time at a node j is the maximum of the preceding early finish times, so we
will be using the following formula:
ES(j) = MAX{ EF(i): all i preceding j}
The number of arguments in the MAX function is equal to the number of predecessors that node
j has. Except that when the BOP is the only predecessor, the argument becomes the number 0.
The formulas in this column cannot be copied down from one row to the next, instead, the
network structure must be observed and each formula based on the stated precedence relations.
4. Now we turn to the late finish time to be computed in column J. We start at the end of the
columns because this is the backward pass, and by convention the Late Finish time at the end is
set equal to the early finish time at the end. Hence in cell J11 we place the formula =H11. Now
working back from the end, the Late Finish at node i is computed as the MIN of the Late Starts
and nodes j following node i. That is we have the following formula:
LF(i) = MIN{ LS(j): all j following i}
Again, these formulas cannot be copied from one row to the next. They are based on the network
structure in terms of which activities follow each given activity.
Start this column (J) at the bottom by setting the Late Finish equal to the Early Finish at the EOP
and then work back up the column using the formula given above as a guide.
5. Then add formulas to column K computing LF(i) - EF(i) for each activity, which is the
activity slack, and then another column in L computing a 1 if the activity is critical or a 0 if not
critical. This last column makes use of the function
=IF(condition, val1, val2)
which returns val1 if the condition is true, and val2 if it is false. To allow for the possibility of
round-off error in the process of arithmetic, we do not check to see if the activity slack is zero;
9
instead we check to see if it is less than some suitably small tolerance around zero, in this case
0.00000001 is found to be quite small enough. Thus the formula in this last (0,1) column (L) is:
=IF(Slack(i) < 0.00000001, 1, 0)
We actually put cell references to column K in where it says Slack(i). This set of zeros and ones
shows which activities are critical using the given activity durations.
The final column (M) in the table will be used to compute the Critical Path name in terms of the
activities that are critical as shown in column L.
Now at the bottom of this column, in M12, we put a function
=CONCATENATE ( )
call that puts the letter names of the critical activities together into a single string. The argument
list is M2,M3, ... , M11 since the strings being concatenated have to be named one at a time, that
is, M2:M11 will not work. Then M12 shows the letter name sequence that identifies the critical
path determined by the formulas in the forward and backward passes. The duration of the critical
path is just the EF time for the last activity at the end of the project.
6. To get a feeling for the range of possible project durations, we compute the critical paths
also for MIN times (Column D) and MAX times (Column F). In each case, the formulas in
columns H and I for EF and LS times must be adjusted to refer to activity durations in Column D
or F instead of E. We record the results in a table showing critical path and critical path duration
for each set of times (MIN, MODAL, and MAX).
CP Duration CP Activities
MIN times (A)
MODAL times (M)
MAX times (B)

7. Based on these results, Jennifer estimates the probability of completion within 32 weeks,
but isn't very sure of her estimate. She has heard that PERT and PERT Simulation methods can
give more scientific probability estimates, but hasn't learned how to do those yet. Letting [A, M,
10
B] be the notations for the minimum, modal, and maximum critical path durations in your
analysis, use the PERT formulas given below to obtain a mean, and standard deviation estimates
for the project duration:

( )

( )

Built in normal distribution functions allow you to get cumulative probabilities and percentiles
for the normal distribution with parameters obtained as above for the PERT approximation. In
particular,
Pr(T <= 32) =NORMDIST(32,PERTmean,PERTstdev,TRUE)
and
T(0.99) =NORMINV(0.99,PERTmean,PERTstdev)
The TRUE argument at the end of the NORMDIST call gives you cumulative probability (rather
than density function value), which is what you want.
11
APPENDICES


Activity Predecessor Modal Time EST EFT LST LFT Slack Critical Acts Critical Path
A Prepare architectural drawings 5 =MAX(0) =G2+E2 =J2-E2 =MIN($I$4,$I$5,$I$6) =I2-G2 =IF(K2 < 0.00000001,1,0) =IF(L2=1,A2,"")
B Identify potential new tenants 11 =MAX(0) =G3+E3 =J3-E3 =MIN($I$10) =I3-G3 =IF(K3 < 0.00000001,1,0) =IF(L3=1,A3,"")
C Develop prospectus for tenants A
4 =MAX($H$2) =G4+E4 =J4-E4 =MIN($I$10) =I4-G4 =IF(K4 < 0.00000001,1,0) =IF(L4=1,A4,"")
D Select Contractor A 3 =MAX($H$2) =G5+E5 =J5-E5 =MIN($I$8) =I5-G5 =IF(K5 < 0.00000001,1,0) =IF(L5=1,A5,"")
E Prepare Building Permits A 1 =MAX($H$2) =G6+E6 =J6-E6 =MIN($I$7) =I6-G6 =IF(K6 < 0.00000001,1,0) =IF(L6=1,A6,"")
F Obtain approval for building permits E
4 =MAX($H$6) =G7+E7 =J7-E7 =MIN($I$8) =I7-G7 =IF(K7 < 0.00000001,1,0) =IF(L7=1,A7,"")
G Perform Construction D, F 10 =MAX($H$5,$H$7) =G8+E8 =J8-E8 =MIN($I$9) =I8-G8 =IF(K8 < 0.00000001,1,0) =IF(L8=1,A8,"")
H Painting & detail work G 4 =MAX($H$8) =G9+E9 =J9-E9 =MIN($I$11) =I9-G9 =IF(K9 < 0.00000001,1,0) =IF(L9=1,A9,"")
I Finalize contracts with tenants B, C 12 =MAX($H$3,$H$4) =G10+E10 =J10-E10 =MIN($I$11) =I10-G10 =IF(K10 < 0.00000001,1,0) =IF(L10=1,A10,"")
J Tenants move in H, I 2 =MAX($H$9,$H$10) =G11+E11 =J11-E11 =MIN($I$12) =I11-G11 =IF(K11 < 0.00000001,1,0) =IF(L11=1,A11,"")
Z EOP J 0 =MAX($H$11) =G12+E12 =J12-E12 =$H$12 =I12-G12 =IF(K12 < 0.00000001,1,0) =CONCATENATE(M2,M3,M4,M5,M6,M7,M8,M9,M10,M11)
Activity Predecessor Max Time EST EFT LST LFT Slack Critical Acts Critical Path
A Prepare architectural drawings 13 =MAX(0) =G2+F2 =J2-F2 =MIN($I$4,$I$5,$I$6) =I2-G2 =IF(K2 < 0.00000001,1,0) =IF(L2=1,A2,"")
B Identify potential new tenants 13 =MAX(0) =G3+F3 =J3-F3 =MIN($I$10) =I3-G3 =IF(K3 < 0.00000001,1,0) =IF(L3=1,A3,"")
C Develop prospectus for tenants A 5 =MAX($H$2) =G4+F4 =J4-F4 =MIN($I$10) =I4-G4 =IF(K4 < 0.00000001,1,0) =IF(L4=1,A4,"")
D Select Contractor A 4 =MAX($H$2) =G5+F5 =J5-F5 =MIN($I$8) =I5-G5 =IF(K5 < 0.00000001,1,0) =IF(L5=1,A5,"")
E Prepare Building Permits A 4 =MAX($H$2) =G6+F6 =J6-F6 =MIN($I$7) =I6-G6 =IF(K6 < 0.00000001,1,0) =IF(L6=1,A6,"")
F Obtain approval for building permits E 5 =MAX($H$6) =G7+F7 =J7-F7 =MIN($I$8) =I7-G7 =IF(K7 < 0.00000001,1,0) =IF(L7=1,A7,"")
G Perform Construction D, F 12 =MAX($H$5,$H$7) =G8+F8 =J8-F8 =MIN($I$9) =I8-G8 =IF(K8 < 0.00000001,1,0) =IF(L8=1,A8,"")
H Painting & detail work G 7.5 =MAX($H$8) =G9+F9 =J9-F9 =MIN($I$11) =I9-G9 =IF(K9 < 0.00000001,1,0) =IF(L9=1,A9,"")
I Finalize contracts with tenants B, C 13.5 =MAX($H$3,$H$4) =G10+F10 =J10-F10 =MIN($I$11) =I10-G10 =IF(K10 < 0.00000001,1,0) =IF(L10=1,A10,"")
J Tenants move in H, I 3 =MAX($H$9,$H$10) =G11+F11 =J11-F11 =MIN($I$12) =I11-G11 =IF(K11 < 0.00000001,1,0) =IF(L11=1,A11,"")
Z EOP J 0 =MAX($H$11) =G12+F12 =J12-F12 =$H$12 =I12-G12 =IF(K12 < 0.00000001,1,0) =CONCATENATE(M2,M3,M4,M5,M6,M7,M8,M9,M10,M11)
Activity Predecessor Min Time EST EFT LST LFT Slack Critical Acts Critical Path
A Prepare architectural drawings 4 =MAX(0) =G2+D2 =J2-D2 =MIN($I$4,$I$5,$I$6) =I2-G2 =IF(K2 < 0.00000001,1,0) =IF(L2=1,A2,"")
B Identify potential new tenants 9 =MAX(0) =G3+D3 =J3-D3 =MIN($I$10) =I3-G3 =IF(K3 < 0.00000001,1,0) =IF(L3=1,A3,"")
C Develop prospectus for tenants A 1 =MAX($H$2) =G4+D4 =J4-D4 =MIN($I$10) =I4-G4 =IF(K4 < 0.00000001,1,0) =IF(L4=1,A4,"")
D Select Contractor A 2 =MAX($H$2) =G5+D5 =J5-D5 =MIN($I$8) =I5-G5 =IF(K5 < 0.00000001,1,0) =IF(L5=1,A5,"")
E Prepare Building Permits A 1 =MAX($H$2) =G6+D6 =J6-D6 =MIN($I$7) =I6-G6 =IF(K6 < 0.00000001,1,0) =IF(L6=1,A6,"")
F Obtain approval for building permits E 3 =MAX($H$6) =G7+D7 =J7-D7 =MIN($I$8) =I7-G7 =IF(K7 < 0.00000001,1,0) =IF(L7=1,A7,"")
G Perform Construction D, F 10 =MAX($H$5,$H$7) =G8+D8 =J8-D8 =MIN($I$9) =I8-G8 =IF(K8 < 0.00000001,1,0) =IF(L8=1,A8,"")
H Painting & detail work G 2.5 =MAX($H$8) =G9+D9 =J9-D9 =MIN($I$11) =I9-G9 =IF(K9 < 0.00000001,1,0) =IF(L9=1,A9,"")
I Finalize contracts with tenants B, C 11.5 =MAX($H$3,$H$4) =G10+D10 =J10-D10 =MIN($I$11) =I10-G10 =IF(K10 < 0.00000001,1,0) =IF(L10=1,A10,"")
J Tenants move in H, I 1 =MAX($H$9,$H$10) =G11+D11 =J11-D11 =MIN($I$12) =I11-G11 =IF(K11 < 0.00000001,1,0) =IF(L11=1,A11,"")
Z EOP J 0 =MAX($H$11) =G12+D12 =J12-D12 =$H$12 =I12-G12 =CONCATENATE(M2,M3,M4,M5,M6,M7,M8,M9,M10,M11)