Earned Value Calculation

[Buster]

I am using a resource pool that does not allow for the use of an hourly
rate, ie we have different rate internally for resources on different
projects.

As a result I export the project data to excel where I insert the rates
using vlookup against our rate sheet.

My superiors are asking for earned value statistics. As a result I need to
know the calculatioons required to calculate
BCWS
BCWP
ACWP
CPI
SPI

THanks for the help in advance

Jeff

 

[JulieD]

Hi Buster

Just a side norte: Project will allow you to set up 5 rates for each
resource (resource information / cost tab) ... then in the resource usage
view you can specify what cost rate table to use for each resource on each
task - if 5 would be enough for your company then would this approach negate
the problem?

Cheers
JulieD

 

[Buster]

Unfortunately, we are not allowed to fool with the rates and costs, trust me
we thought about your solution but was vetoed when we went to impliment it.

I am stuck pulling the costs/rates from the planning spreadsheet that is
part of my program. Therefor I am still stuck with the need for knowing the
formulas for
BCWS
BCWP
ACWP
CPI
SPI

Jeff

 

[Derrick Robinson]

BCWS - the budgeted cost of the work you scheduled
BCWP-the budgeted cost of work performed
ACWP- actual cost of work performed
CPI-cost performance index=BCWP/ACWP gives a ratio. For example, 0.6
SPI-schedule performance index= BCWP/BCWS also gives a ratio. For example,
0.7
I think a value <1 (less than 1) is adverse.

The trick is in calculating your BCWP. If you have a cost schedule for an
activity, for $1500 and you think the activity is 2/3 completed(66.67%) the
cost would be (1500)(66.67%) =$1000.
Derrick

 

[Buster]

So then my calculation would be;

BCWS = (rate*estimated Work)
BCWP = ((rate*estimated Work)*%Complete)
ACWP= ((rate*Actual Work)*%Complete)

Please understand that I'm new to Earned value and I want the calculations I
build into my spreadsheet are acurate and that I'm pulling the right fields
from Project.

JEff

 

[Steve House [MVP]]

Each resource has a cost table where you can specify up to5 different rate
schemes for him and each of them can reflect things like raises etc that
have effective dates as well. Is that sufficient for the variations in
internal rates?

I'm wondering - the rates used by Project for costs and earned value
computations are not rates billed to clients but rather your internal costs
of using the resource. Are you saying that you pay Joe Engineer, say, $50k
a year when working on project X but $65k when working on project Y? I can
see different rates depending on the nature of the task performed - an
example I use in classes is a member of a film crew who can act as both an
AD and a camera operator, because each job is covered by a different
collective agreement the hourly rate is different depending on what she
does - but it seems unsual that more than 5 rates schedules would be
required to take such things into account.

 

[Buster]

Steve you have it in a nutshell. I get my daily rates from a spreadsheet
which contains the different rates based upon roles.

I want substitute those rates intol my earned value calculations that I need
to compile on another sheet and send back to Finance Dept. The problem is
that it was assumed that I had the equations in my left pocket and could
produce viable ones. My assumptions in general are sumized by;
BCWS = (rate*estimated Work)
BCWP = ((rate*estimated Work)*%Complete)
ACWP= ((rate*Actual Work)*%Complete)

I am looking to verify if these equation s are valid and useable. My further
question is do i need to add to the calculations a percentage of total task
time expended based upon the baseline start and a chosen date?

Jeff

 

[Brian K - Project MVP]

I am using a resource pool that does not allow for the use of an
hourly rate, ie we have different rate internally for resources on
different projects.

As a result I export the project data to excel where I insert the
rates using vlookup against our rate sheet.

My superiors are asking for earned value statistics. As a result I
need to know the calculatioons required to calculate
BCWS
BCWP
ACWP
CPI
SPI

THanks for the help in advance

Jeff

I would just have a $1 rate in Project and then use the EV calcs in
project and forget the different rates. They are only really important
for billing, which is not your problem. You are concerned with status.
A $1 rate will give you all your EV calcs without the hassle.

Just a thought

--
___
Brian K
Project MVP
http://www.projectified.com

Project Server Consultant
http://www.quantumpm.com

 

[Steve House [MVP]]

You have some major problems with your calculations

Firstl, the estimated work in the BCWS must come from the Baseline Work
field and not the simple Work field for the task. And equally a problem is
that the earned value is a timephased calculation using cumulative values as
of a certain status date and NOT based on the total value. Your formulas
appear to be using totals, disregarding the status date component. If we
have a task that is due to take 10 days and has a total work of 80
man-hours, after we save the baseline and prior to starting work the
estimated cost and baseline cost of that task are equal and are
rate*estimated work. But if the estimated work changes later, either before
or during the task, the Baseline Cost remains constant while the Cost will
change. Assuming no changes to work for the moment, if the status date is
set to a date halfway through the originally scheduled time frame and
duration, the Work field will still show 80 hours, the Baseline Work field
will also show 80 hours, but the BCWS will show 40 * rate because that is
the portion of the Baseline Work that is supposed to have been done prior to
that date.

BCWP is not rate*estimated work*%complete nor is the formula for ACWP
accurate either. % Complete refers to duration, not work and it is quite
easily possible for the % of the total work to be performed prior to a
certain date to be quite different from the projected % complete on that
date. For example, we are painting a room and we need to put several coats
on with time to dry between each one. Our painter comes in Monday and puts
on the first coat which takes him 1 hour, then he goes away. Tuesday he
comes back for the 2nd coat, Wednesday the 3rd, and Thursday the 4th. Then
Friday he comes in for the entire day and does all the finishing work and
trim. Duration is the elapsed working time between start and end,
regardless of whether all of it is filled with work or not, so total
duration = Mon 8am to Fri 5pm or 40 hours. Total work = 12 man-hours. It's
now Thursday at 5pm and everything is going according to plan. The work is
4/12 or 33% complete. But the duration is 32/40 or 80% complete and it is
the duration that Project is talking about in the % Complete field. Assume
our painter gets $1/hr for discussion. Thursday at 5pm the BCWS is $4 but
your formula says it's $40. The BCWP and ACWP are equal and are also $4
each but your formula gives them $32. SPI is really 1.0 but your formula
says it's .80.

ACWP and BCWP will be equal unless you have substituted resources with a
different rate, use more or less materials than planned, move some of the
scheduled work hours in overtime instead of straight time, or revise the
duration of the task. For example, @ $1/hr a task estimated at 80 hours has
a $80 BCWP when it is completed regardless of how long it actually takes.
If it took us an extra week of work to get it done, total actual work would
be 120 hours and ACWP would be $120 while the BCWP would remain $80. If we
really got it done in 5 days instead of 10, the ACWP would be $40 but again
the BCWP remains at $80.


Our original painter falls ill on Wed and Thur and we substitute another
guy. But this fellow gets $1.50/hr. BCWP = $4 ACWP = $5 (1+1+1.5+1.5) CPI
= 0.80

Now consider that this task was scheduled to start Monday but in fact
because the paint was delivered late didn't get started until Wednesday.
Once again it's Thursday at 5pm. The % Complete is now 40% (still 5
workdays/40 work hours between start and finish and 2 workdays/16 hours have
elapsed since work began). The BCWS is still $4. The BCWP is $2. The ACWP
is $2. SPI = 0.5 CPI = 1.0 Again the original painter was sick on Wed and
Thur so we had a substitute fill-in. But the substitute gets $1.50/hr.
BCWP = $2 ACWP = $3 CPI = .66

Had we picked Wed evening or Friday evening instead of Thur for our status
date the numbers would have been different. Likewise if we started early or
later or took less or more time than originally planned the number also
would be different. In a nutshell Earned Value is the comparison of where
we expected to be on a certain date and how much we planned to spend to get
there versus where we have actually done and spent up to that date. "That
Date" is a crucial operative term.

 

[Derrick Robinson]

Steve- You are right about the BCWS coming from the base line. After all
overall project performance is the crux of the matter and that's what earned
value is about. One thing that must be taken into consideration is that
making an overall estimate of the percent completion of a project without
careful study of each of its tasks and work units is not very wise--some
managers make such estimates nonetheless. At any time during the life of a
project some of the following conditions can exist:
Some work units have been finished and are 100% complete
Some have not yet been started and are 0% complete
Other units have been started and not yet finished and for this latter group
we may have to estimate percentage completion.
Coming up with a reliable method of completion estimate is not that all
easy. What methods other than your example do you teach or recommend to
address the estimation problem? Your method seems to address the
proportionality rule. I may be wrong here but I don't think these guides to
percentage completion are meant to be applied to the project as a whole,
even though sometimes they are, but rather to individual activities.
Derrick

 

[Steve House [MVP]]

It is a muddle all right. Project makes the distinction between "%
Complete", "% Work Complete", and "% Physical Complete." On top of that, if
I show that the 5-day task is 80% complete, it shows progress complete
through day 4 and Work as complete that was scheduled for days 1, 2, 3, and
4 whether or not that is the same percentage of work. But I can break that
link in the options page and post duration and work progress completely
independently of each other. Then to top it off, one can choose to use %
Complete or % Physical Complete for the Earned Value calculations.

One of the major issues that I think PM's need to keep in mind is when you
go to the resource working on a task and ask him "How's it going?" and he
responds "We're halfway done" what does he really mean? Is he referring to
the time he's spent so far compared to the total time it's expected to take,
is it the man-hours of work that have been put in compared to the total
man-hours required (quite different from duration time requirements but a
distinction that is almost always overlooked by resources, as well as too
often overlooked by PM's), or the actual physical progress on the
deliverable? And if we say the deliverable is 50% complete, just what do we
mean. If we have to lay a wall requiring 100 bricks and we've put 50 in
place it's pretty straight forward but what does "50% Physical Complete"
really mean when designing an engine or writing a program?

There are three common ways of estimating the percentage progress:
1: Tasks that are complete are rated 100%, tasks not started or
in-progress are rated 0%.
2: Tasks that are complete are rated 100%, tasks in-progress are rated
50%, tasks unstarted are 0%.
3: Tasks are rated with an estimated completion reflecting the actual
(perceived) percentage progress.

The last method is particulary loosey goosey if one tries to approach it by
entering percentages. A far better way, if one needs that level of
precision (and I really have my doubts how often it is really needed - what
data actually helps you manage the project and a bunch of numbers that look
impressive in a presentation by your boss to the board of directors are
often two entirely different critters) is to forget estimating percentages
altogether and instead ask the resources "How much have you done" and "When
do you think it'll be finished, ie, Actual Duration and a revised estimate
of Remaining Duration, and let Project massage that into a percentage. "We
worked on it all 5 days this week and if we can continue on it every day, it
looks like it'll be another 2 weeks before it's finished" translates into
33% done, regardless of what the original estimated duration was or what the
anticipated work contours might be.


--
Steve House [MVP]
MS Project Trainer/Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

 

[Derrick Robinson]

Quite a muddle Steve. However I follow the logics of Project. Stick to it
and I guess there is a form or rationality. That's the reason why the
various rules were created. Some people use the 50-50 rule- 50% at the start
of the project and the next 50% at the end to simplify the process. Other
conventions include 0-100 percent rule which allows no credit for work until
task is completed, or assignment of credit according to the amount of a
critical input used. I do like the proportionality rule you described.

I am not a project expert because my knowledge in operational management
which involves project planning is limited. I am more a marketing and
finance person. I am trying to learn how to use MS Project. As time goes by
I will seek the advice of this wonderful forum.
Derrick