- [Instructor] So what is a key performance indicator,
also called a KPI?
A KPI is anything that helps us measure
how well we reach our goals.
And you can see for project management,
this is really important.
Now the thing is a KPI could be anything,
but thankfully, there are a few industry standard KPIs
that we can use for project management,
which we'll be using here.
So let's talk about the ones
that I would like to use going forward.
So the first two are going to require a little bit of math.
Don't worry, I'm going to walk you through it.
Those are cost performance index
and schedule performance index.
We're going to show you how to do that in a second.
But the last three - safety incidences,
quality score,
and stakeholder satisfaction,
these are just numbers
and they would come out in the real world
from surveys and research.
So let's talk about two important calculations
before we move into the bigger calculations
because we're going to use these both,
and they're both very important to project management.
If you ever sit for the PMP,
I believe that they're on that test.
So earned value,
that's the percentage complete that we have so far
on a project multiplied by the budget.
So that basically tells us
how much of the budget have we already earned.
Then planned value,
well, that is how much we plan to complete,
which may be different, right,
than the amount we've earned,
than is actually complete.
And both of those are going to help us
with the indices we're about to calculate.
Now if you're looking at this
and you're thinking this is about to be a lot of math,
and wouldn't it be better if you were just sitting in Excel
and typing it out?
I'm with you. Sometimes it's hard for me to fall along too,
but I think that this part is important.
So just fall along and it'll all come together.
All right, so the cost performance index
is that earned value,
that's the percentage complete multiplied by the budget
divided by the actual cost.
I wrote expenses here
because that's the name of our actual cost column.
But just so you know,
it would be divided by the actual cost.
Now, values that are greater than 1
means that the project is under budget.
Ding, ding, ding, we like that.
Over 1 means that they're over budget.
So a very quick and easy way to understand
where you are with regard to the budget.
Now, schedule performance index, very similar.
Okay, so this is calculated by taking the earned value,
we just talked about that
and dividing it by the planned value.
Are you earning
what you thought you'd be earning by this point?
Or are you behind schedule?
Or are you ahead of schedule?
So if you're greater than 1, you're ahead of schedule.
If you're less than 1, you are on a delay.
All right, so safety incidents.
These are just account of the safety incidents
that have occurred on-site.
Ideally, you would just be collecting
that information anyway.
The quality score, again, hypothetical score
based off of a review of the workmanship of your product.
And then finally, stakeholder satisfaction.
Are your clients and stakeholders
happy with the project progress?
Now as I switch to Excel,
what I'm going to say here is
you can notice here that CPI and SPI are already here,
but we're going to actually have to add two more metrics,
and you're going to see that in a second.
So first thing I'm going to do is put my cursor
anywhere in this continuous region.
My table has headers.
I'm going to hit OK on that.
I'm going to remove the banded rows for personal reasons,
and then I'm going to right-click on column J.
I'll hit Insert to put in a new column.
Here we're going to type in EV for earned value.
Right-click on K, hit Insert,
we're going to do PV for planned value.
This is not present value as it would be in finance,
but rather planned value.
So keep that in mind.
Now, the earned value calculation
is basically just going to be
the progress multiplied by the budget.
So here's what we've allotted for the year,
here's how much we've gotten done.
That is our earned value.
And if you feel so inclined,
highlight that whole thing there
and put a nice currency on it.
I'm actually going to just reduce that down
so that we don't have decimal places.
Okay, so let's talk about planned value.
This is going to be equal to how much we plan
to have spent of that budget.
So let's think about how to calculate this.
There's a few different ways,
but I'm going to give you the one that I'm going to use.
So think about this, right?
We have an end date and a start date,
that's going to give us our elapsed time in total.
So I'm just going to build this formula from the inside out.
So start date less end date.
Or actually, let me do this differently.
Let's do end date less start date.
So that's the total duration of the project.
And then we want to figure out,
well, based off of the time
we expect this project to be complete,
where are we?
So I'm going to take a lapse days here
and I will divide it by this quantity.
So that gets us our percentage complete in terms of time.
And then we could take this whole thing here
and we multiply it by the budget.
And I'm just going to use the format painter here
and format all of this.
Okay, so this is our planned value.
So to find our cost performance index,
we're going to do equals,
and in my case, I'm just going to do an IFERROR here,
because in case it's a divide by zero,
I just want to handle it.
So I'm going to do IFERROR,
and we're going to do our earned value
divided by our actual cost,
which is going to be the expenses right there.
And don't forget to put a zero at the end of this.
And then let's do our schedule performance index.
Okay, so we're going to do this very similarly.
I'll do an IFERROR here,
and then I'm going to take the earned value
and divide it by the planned value.
And don't forget to add a zero here.
Okay, so we have our indices created here,
and then if we just look over to the right,
these KPIs are already built in.
So now what we're going to do is we're going to actually
build a dashboard that's similar to this
so that we can see how well we're doing.
Isn't it great when we can do that?
So I'm just going to create a new sheet
and we can call this Dashboard like that.
And in this case, I'm just going to label
some of these cost performance index,
call this schedule performance index over here.
And you can lay this out however you want.
We're going to do safety incidents right there.
And let's see, what were my other two?
Quality score
and stakeholder satisfaction.
I'm going to highlight both of these.
Let's see if this works.
So I highlighted both.
Hit Ctrl + C.
I'm just going to do a paste values here.
So I'll drop that there.
And then I'm going to drop this one over here.
Now, there's a few ways to do this.
Some people really disagree with me
on the use of merge and center.
I don't mind.
If it bothers you
obviously you're welcome to write in the comments.
I don't hate merge and center,
so I'm going to highlight these cells right here,
B3 through B34.
I'm going to hit merge and center on that.
Then I'm just going to do that little paintbrush copy
and go through the rest of these.
I'm going to highlight all of these.
I'm holding Ctrl right now,
and I'm just going to center them so that it doesn't bother me.
And let's see if we can just
kind of make 'em all just a little bit bigger.
Okay, so to get to this reporting,
we're going to go back here.
We're going to go to the bottom of our table here.
So just click anywhere in the bottom of the table,
go to table design.
Let's add a total row.
So this is going to allow us to actually
do some reporting off of the information in this table.
So I'm going to go to CPI right here.
We're going to make this all of these just averages
so we can see how we're doing overall.
So I'll just click down there
and make sure that these are all average.
And then I'm going to go back to my dashboard.
So we'll just do a simple link.
So I'll do equals here,
put that one right there.
Equals here,
put that one right there.
And I'm just going to keep going.
So we'll do equals here,
put this right here,
equals like that,
put that one right there.
Okay, so now let's do some interactive driving of this.
So I'm going to go back to my table here
and I'll click Insert
and I'm going to insert a slicer.
Let's insert a status slicer.
Of course, if you're playing around,
you could do whatever you want.
So I've inserted this here.
Now that this is on this page,
I'm going to hit Ctrl + X on my keyboard,
and then I'm going to go to the dashboard
and drop it in over here.
So this is going to actually drive changes on the dashboard.
At this point, all we have to do is some formatting.
So I'm going to go to View,
I'm going to turn off the grid lines first,
and then I'm going to highlight all of these,
make them bold,
maybe make them a little smaller,
and I'll give it kind of a peach top.
That's one of my favorite things to do there.
And then here,
I'm just going to apply some borders like that.
Again, I'll just do that format painter.
But let's actually fix this up.
Just one last way here,
I'll click in the middle of here,
and then we're going to make this a regular number like that.
So I'm going to highlight this whole thing here
and just keep doing this.
But still, you may look at this and say,
well, I want these to be bigger.
And that, by you, I mean me.
So we can just shorten everything up.
And of course, if we want,
we can always wrap to the next line.
Okay, so that's how to create a quick dashboard
using these KPIs.
These are KPIs that people use out in the real world.
But remember that you aren't beholden
to what the industry standard is.
Of course, you need to do that
to communicate your project status,
but also be creative
because there's many ways to measure a project.
So let's think about everything that we've done so far.
We've used data visualization
to get record level information.
We created our own KPIs,
and then we even created a dashboard
to help us visualize the results of project data.
So now we're actually ready to visualize
the schedule of projects,
and we're going to talk about more how to do that
next up in the Gantt chart video.