- [Instructor] Okay, so now that we're in Excel,
let's create our EVM calculator.
First step is we want to identify our important columns.
So the Expenses column, that is our actual cost,
so I'm going to put in parentheses AC here,
just so we remember that.
And then over here,
I'm going to type an EV for the earned value
and then PV for the planned value.
So because every formula that we want to use for our KPIs
is a function of these three parameters,
we just want to easily identify them
so that when we use the formulas,
it all comes together really easily.
Okay, so let's start with earned value.
So earned value is just going to be the progress
multiplied by the budget.
If you think about it, that makes sense.
It's, how much have you earned of that budget
that you've allocated?
Now, planned value is based off of the time
that it has elapsed.
So what we're going to do
is we're going to take the amount of time that's elapsed,
so that's 180 days,
and we are going to divide it
by the end date less the start date,
that gives us our entire range.
And then this whole thing
is the proportion of how much time has passed,
and we multiply that by the budget.
That gives us our planned value.
And that makes sense, that's how much we plan to spend
based off of the amount of time that has elapsed.
Now that we have these three different parameters set,
let's just do some quick calculations.
So let's talk about the Schedule Variance.
I'm going to actually word wrap this here
just so I can see it bigger.
And then I'm going to put, for my own reference,
the calculations in there.
So schedule variance is EV less PV.
Okay, so this tells us:
Are we ahead of schedule? Are we behind schedule?
If we have a positive number, we are ahead.
If it's negative, we are behind in terms of spending.
Now, let's talk about cost variance.
Cost variance is going to be the earned value
less the actual cost.
Again, we already have this set up,
EV less actual costs, AC.
Boom.
Okay.
Next, we want to get the schedule performance index.
That's going to be the earned value
divided by the planned value.
And if you get dollar signs here,
what we're going to do is we're going to turn this into a number,
and then we're going to do the cost performance index.
And just for our reference, before I move on,
I'm going to type in EV divided by PV over here.
And then for this one we're going to do EV divided by AC.
So in cell O2, we're going to say equals EV divided by AC.
And I'm going to make that a number type.
Now, if you get this divided by zero error, which you might,
it is a good idea to go in here
and just update your formula to put an IFERROR around it.
If you get an error, just turn it into a zero.
You have to think:
Is there ever going to be a time
when your SPI or CPI is going to be a zero?
Well, only when it's ever going to be an error, right?
Or unless you are dividing, it has no earned value.
So it's zero divided by something.
Okay, so with that,
let's just do a little bit of conditional formatting
so we know which projects are going to have issues.
So if it's greater than one, that's good.
If it's less than one, that is bad.
So that is in the case for the index,
for the schedule variance and the cost variance,
the rule is zero, right?
So if it's positive, it's good.
Negative, it's bad.
So that's one of the great things about the EVM metrics
is that they're very easy and intuitive to understand.
So I went on the Home tab, I hit Conditional Formatting,
I made a new rule.
We're going to format only cells that contain
the cell value is less than zero.
And we're going to make that format a peach color here,
just kind of reddish so people notice.
And then for these ones over here,
the rule is greater than or less than one.
So we're going to add a new rule here.
Same deal, only cells that contain,
cell value is less than one.
Format, put on that peach.
And we should see is that they are parallel, right,
that the colors are very similar for both of them
'cause they're measuring the same thing in different ways.
Okay, so now that you know how to create an EVM calculator
for your projects,
in the final part of this course,
we're ready to take everything that we've learned,
put it all together into a way that you can understand it
and your manager can understand it.
I'll see you in the next video.