PERT in Excel - YouTube

My name is Dave Wilhelm.

I am an

associate professor in the

Business Administration department at

Wake Technical Community College in

Raleigh, North Carolina.

I teach seated and online classes.

This video

is one in a series that

tries to ensure

that my online students get

almost as much information as my seated students.

This particular information

is a response

to some students who

in discussing

using PERT charts,

the pictorial guide grammatic method,

why can't we just do it in Excel,

and it turns out,

you can do it in Excel

and this video shows how to do that

If you're not familiar

with PERT,

there is a PERT and Gantt video

in the Dave Wilhelm NC channel

on YouTube

that may get you in the right ball park.

So,

let's talk about how to do PERT in Excel.

This video covers using Excel spreadsheets exclusively,

rather than

pictorial representations

to determine a project's

cumulative elapsed time,

its critical path,

and its critical tasks.

[PowerPoint clicks]

Here's a typical PERT Table Representation

of a project.

Across the top,

the column identifiers are the task identity,

the tasks on which each tasks depends on,

in other words,

the ones that have to be done previously,

and the execution time of each task,

tExec.

The row identifiers down the left

are the tasks of the project

under the tasks column heading.

The other two columns are the tasks dependencies,

which will be

one or more other tasks,

and each tasks execution time.

In this case,

there are 10 tasks,

A through J.

The project starts with task A,

which has no dependencies,

and the project is complete

when task J is finished.

The execution times could be any time units,

call them days

if you'd like something to call them.

Here is

a typical pictorial ...

PERT ...

representation to determine ...

the ...

cumulative elapsed time,

the critical path and the critical task.

The first step

in this representation

is to

determine the typography

and then simply transfer from the table

to the diagram

the task IDs,

their execution times,

and the task dependencies

as are indicated by the arrows.

Then,

going from left to right,

calculate the elapsed times

after each task.

[PowerPoint clicks]

And we see

that the total cumulative elapsed time for this example

is 27 days.

[PowerPoint click]

To determine the cumulative elapsed time in an Excel table representation,

we add two columns,

tStart,

and tEnd,

which are

the start and

end times for each of the tasks.

A useful simplification

that can be made

when doing the

PERT calculations,

simply in Excel,

is to identify the start and end tasks,

in particular,

have a single

identified beginning task

and a single

identified ending task.

In this case,

our case,

for this example,

task A is the initial task,

and task J is the final task.

[PowerPoint click]

Just a reminder,

what we should have

as we go through the Excel

version,

is this table.

And

tStart of A,

0, end is 3, and on down through task J,

where the time start is 23

and the time end is 27.

The start time for

tasks F and J is

where we will get interesting

because tasks A through E have a single

task dependencies,

whereas F through J have multiple

task dependencies before they can start.

[PowerPoint click]

Now to determine

those values using just the PERT chart

rather than the diagrams.

The first task is simple,

task A has no dependencies,

so it can

start immediately at time 0

and we can enter

0 in time start A.

For all tasks,

the ending time is equal to the starting time plus the execution

and so in the time end cell for task A,

we enter tEnd equal tStart plus tExec,

which will be equal to 0 plus 3,

and be sure to enter the formula,

not the value in that cell.

But what we should see

is time end for A equals 3.

Next determine

the tStart and tEnd times for task B.

From the dependency column

we see that task B

depends only on task A,

so

the time of start B is equal to the time of end A,

or

3 days

and in that cell we should enter equals time end A,

not 3,

but 3 will show up.

The ...

task B end time

is simply equal to its start time plus its execution time,

we enter that formula into the cell,

and what shows up is 7,

and that's still correct

if you want to go back and check the pictorial version.

[PowerPoint click]

The next three tasks,

tasks C, D, and E

are similarly determined.

Each task has a single dependency

so time start C equals time end A,

time start D equals time end A,

and time start E equals time end B.

[PowerPoint click]

The values

for C are start at 3, end at 8.

For task D,

start at 3, end at 9.

For task E,

start at time 7, and end at time 15.

Now it's going to get interesting

because

task F has two dependencies,

tasks B and C.

Both must be finished before task F can start.

So,

time start F equals the maximum

of those two dependency end times,

max of tEnd B, and tEnd C,

we equal,

we enter that formula in the tStart column for task F

[PowerPoint click]

and that turns out to be 8,

if we go back and double check,

sure enough

time end C is greater than time end B,

and time end C is 8,

so

that's what we

see,

and that's what we should see.

The time end

[PowerPoint click]

F is just it's

execution time plus the start time.

[PowerPoint clicks]

And for the rest of the tasks,

G through J,

the same

type of formula

applies.

The time

start for any of those

tasks

is the maximum

of the time ends

of their dependent tasks.

If you're unfamiliar with the Excel function,

especially MAX,

you can find information

on those in Excel,

under the

Functions information tab.

And

time end J

is the cumulative elapsed time

for the entire project.

[PowerPoint click]

Well, we've got it, so what?

Well,

one actual advantage

over the Excel spreadsheet version of PERT charting

is that we can change the execution times

of any of the tasks

and they will automatically roll through the rest of the formulas

to show us what has changed.

In this case,

if we change the execution time of task C

from 5 to 7,

[PowerPoint click]

the

end time

of task C

will change

plus all the start and end times of tasks F through J,

and the total cumulative elapsed time will now be 28.

[PowerPoint click]

The next step

is to

determine the critical path.

Unfortunately,

there is no way to do this automatically as we did with the cumulative elapsed time,

but neither is it that bad.

[PowerPoint clicks]

Task J

is on the critical path,

there are no parallel tasks,

it is the final task,

so J is on the critical path.

What delays task J?

Well,

we see its dependencies

are tasks H and I.

Task H ends at 21,

task I ends at 23,

so task I

is the one on the critical path.

Task I's task dependencies are F and G,

task G time end is 16,

time F time End

is 14,

so task G is also on the critical path.

Task G's dependencies are C and D.

Time end C is 8,

time end D is 9,

so task D is also on the critical path.

Task D is dependent only on A,

so A is also on the critical path.

So, our critical path is A, D, G, I, J.

And if we click back to the pictorial version,

it agrees, so,

that's always a bit of a relief when something new

agrees with something

we have done previously.

[PowerPoint click]

The critical task is also manual,

in this case,

we look at the execution times

of the tasks that are on the critical path.

In this case,

task E has the longest execution time,

but since it's on not on a critical path,

it's not a critical task.

In this case,

task G and I are both on the critical pasks and-- critical path,

and they have the highest execution time of the tasks on the critical path.

So in this case we have two critical tasks,

task G and task I.

[PowerPoint click]

That's it

for our discussion

about how to solve PERT problems in Excel rather than in a diagram,

I hope this proved either interesting or useful,

both would be nice.

If you have questions or comments,

just send me an email,

and in subject line,

please put PERT in Excel.