The PERT/CPM networks can be drawn on Excel with little difficulty.
1. Shrink the column widths, so more will fit on a page. On this page
columns A, B, and C are normal, D through Z have been made smaller.
I did this by clicking on the column heading D and dragging to Z, highlighting
these columns. Then I dragged the line between the D and E column, to shrink
the size. I could also have selected "Format Column Width"
2. List activities, times, and immediate predecessors
Activity Time Imm. Pred.
A 5 -
B 7 A
C 12 A
D 4 B, C
3. Make a template for the nodes, three cells wide by two cells high.
First select the six cells, click on the format button to center text, then
click on the border option to put a border line around and between all
six cells. It will look like this:
This will be copied and pasted for each activity and will ultimately contain the following values:
ES Act. EF
LS time LF
EF is always ES + time, and LS is always LF - time. So enter those formulas
in the respective cells. The results you see now will not make sense, because
the formulas don't have any values to work with yet. That's alright, the formulas
still work and will give correct results as the network is built.
It will look like this:
0
0
Now, put in a formula that will automatically put in the time, when you type in
the letter of the activity. This uses a VLOOKUP function and looks up the time
in your table of activities, times, and immediate predecessors.
=VLOOKUP(activity letter, table of activities and times, column # in the table that contains the times)
### The formula is cell E45 is:
### ### =VLOOKUP(E44,$A$12:$C$15,2)
Note in the formula in cell E45 that the table ranges includes $, this is necessary so that where ever
you put this network node template, the correct cells will be referenced to get the times.
Also notice that we have errors in the three cells with formulas now. That's because we have no activity
letter entered, so it can't look up the time yet. Notice in the example below, however, I simply copied
the six cells above (D44:F45) , pasted it to cell D53 and entered "A" into cell E53.
A 5
-5 5
4. Place the activities on the spreadsheet in order of precedence, leave room to draw arrows to show
the precedence relationships. You place an activity by copying your template (the 6 cells) and pasting
it. Then type in the activity letter.
A 5 B 7
-5 5 -7 7 D 4
-4 4
C 12
-12 12
5. Now use the drawing tool (you might have to turn this on under View Toolbars) and select the arrow
drawing tool. Click and drag to draw the precedence arrows.
A 5 B 7
-5 5 -7 7 D 4
-4 4
C 12
-12 12
6. Now you can add in the values for the ES times, working from left to right. For activity A, put in a zero
since there is no predecessor. For B, click on the cell for the ES time and enter "=", then click
on the cell for the EF time of activity A. Do the same for C. Your network will now look like:
0 A 5 5 B 12
-5 5 -7 7 D 4
-4 4
5 C 17
-12 12
To compute the ES time for activity D, we need to consider the EF time of both activity B and C.
The ES time for D is the greater of the EF times of the two predecessors. Let the spreadsheet do
the comparison by using a MAX function. enter "=MAX(cell with EF of B, cell with EF of C).
See how this is done in the example below, by inspecting the formula in the ES cell for D (L92)
0 A 5 5 B 12
-5 5 -7 7 17 D 21
-4 4
5 C 17
-12 12
7. Now enter formulas for the LF times. The LF for an activity with no followers, is the largest
EF time for all activities without followers. (See problem 2, activities F and G on the next page
in this workbook). Other cells will use formulas that are basically the reverse of what was used
for the ES values. There is a MIN function that works like the MAX function. See below for Activity A.
0 A 5 5 B 12
0 5 5 10 7 17 17 D 21
17 4 21
5 C 17
5 12 17
From this network diagram, you can read that the critical path is ACD and that B has a slack of 5.
Problem 2 in chapter 4, Kraj and Ritz, 6th Ed. Performed on spreadsheet
Activity Time Imm. Pred.
A 7 -
B 2 A
C 4 A 7 B 9
D 4 B,C 9 2 11
E 4 D 0 A 7 11 D 15
F 3 E 0 7 7 11 4 15
G 5 E 7 C 11
7 4 11
Problem 3, same book template: #N/A Has the simple formulas implemented for time
#N/A #N/A Copy and paste to build the network. Type in
upper middle. Enter formulas for ES and LF.
Activity Time Imm. Pred.
A 4 - 0 A 4 4 C 9
B 10 A 2 4 6 6 5 11
C 5 B, C, D
D 15 E, F 0 B 10 10 D 25
E 12 F 0 10 10 11 15 26
F 4 G
G 8 G
H 7 H 10 E 22
10 12 22
19 F 22
21 3 24
15 E 19
15 4 19
19 G 24
19 5 24
mulas implemented for time, EF, and LS.
build the network. Type in Activity letter in
r formulas for ES and LF.
25 F 29
26 4 30
30 H 37
30 7 37
22 G 30
22 8 30
