Tracking EV with a formula

[J.Allman]

I have a question regarding the statusing of a project using EV.
Specifically, I am trying to create a series of green/yellow/red lights in a
new column to reflect when individual tasks in the project start to fall
behind on total work performed per task over the duration of the project.

My current formula is:
IIf([Work]=0 And [Baseline Work]=0,11,IIf([Work]=0 And [Baseline
Work]>0,10,IIf([Actual Work]=0 And [SV%]=0,0,IIf([% Complete]=100 And [Finish
Variance]>0,5,IIf([% Complete]=100 And [Finish
Variance]<=0,2,IIf([SV%]>-10,1,IIf([SV%]>-20,3,IIf([SV%]<=-20,8))))))))

The majority of the formula is tracking other events that happen in the
project but the part of the formula that focuses on the
green(1)/yellow(3)/red lights(8) are:

,IIf([SV%]>-10,1,IIf([SV%]>-20,3,IIf([SV%]<=-20,8))))))))

My problem occurs when I have a task that is currently being worked that has
a <baseline work> of 20 hrs a <work> of 20 hrs and an <actual work> of 18
hours..in this scenario the light is yellow as it falls between that 10% -
20% behind schedule.
HOWEVER, this task has baseline start and finish dates that should have
occured 6 weeks ago. This task even has a start date that occured 4 weeks ago
and the resource has not been working the task quickly.
I also have tasks similar to this one that are green because the actual work
is almost 100% complete even though the task is running 2 months behind
baseline schedule.


Does this have something to do with unfinished predecessors?
Should there always be a status date assigned or can it be left at NA?
Why is this happening?

 

[Jim Aksel]

The SV%, etc are calculated in relation to the baseline dates in the original
plan. This has nothing per se do to with unfinished predecessors. Without
getting off on a tangent, perhaps my best opinions are listed on my blog
(link below).

I have three white papers on the subject of defining %complete, and Stop
light charts. See if what you find there is useful. I specifically address
the issue that a task starts late compared to its baseline.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

 

[MrAlNather]

The SV%, etc are calculated in relation to the baseline dates in the original
plan.  This has nothing per se do to with unfinished predecessors.  Without
getting off on a tangent, perhaps my best opinions are listed on my blog
(link below).

I have three white papers on the subject of defining %complete, and Stop
light charts.  See if what you find there is useful.  I specifically address
the issue that a task starts late compared to its baseline.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:http://www.msprojectblog.com

I have a question regarding the statusing of a project using EV.
Specifically, I am trying to create a series of green/yellow/red lightsin a
new column to reflect when individual tasks in the project start to fall
behind on total work performed per task over the duration of the project.

My current formula is:
IIf([Work]=0 And [Baseline Work]=0,11,IIf([Work]=0 And [Baseline
Work]>0,10,IIf([Actual Work]=0 And [SV%]=0,0,IIf([% Complete]=100And [Finish
Variance]>0,5,IIf([% Complete]=100 And [Finish
Variance]<=0,2,IIf([SV%]>-10,1,IIf([SV%]>-20,3,IIf([SV%]<=-20,8))))))))

The majority of the formula is tracking other events that happen in the
project but the part of the formula that focuses on the
green(1)/yellow(3)/red lights(8) are:

,IIf([SV%]>-10,1,IIf([SV%]>-20,3,IIf([SV%]<=-20,8))))))))

My problem occurs when I have a task that is currently being worked that has
a <baseline work> of 20 hrs a <work> of 20 hrs and an <actual work> of 18
hours..in this scenario the light is yellow as it falls between that 10% -
20% behind schedule.
HOWEVER, this task has baseline start and finish dates that should have
occured 6 weeks ago. This task even has a start date that occured 4 weeks ago
and the resource has not been working the task quickly.
I also have tasks similar to this one that are green because the actualwork
is almost 100% complete even though the task is running 2 months behind
baseline schedule.

Does this have something to do with unfinished predecessors?
Should there always be a status date assigned or can it be left at NA?
Why is this happening?- Hide quoted text -

- Show quoted text -

Jim,

I had a look at the white papers on the strength of the above. What I
read was really enlightening. Thanks for this. Only thing to point out
is that the "DEFINING %COMPLETE IN
MICROSOFT PROJECT" white paper kind of tails off...might be worth a
check.

Great stuff...

Paul

 

[J.Allman]

You write:

Schedule Status
Marching through a schedule plugging in a %Complete will not accurately
status your schedule. As a matter of fact, it will more than likely damage
the schedule logic. Think about the status date in relation to the task time
line. You need to concern yourself with the Actual Start date, remaining
duration and remaining work. They are the important parameters.

The four shalls:


There shall be no task with a start date left of status date with 0%
Complete, establish a new start date if necessary.
There shall be no task with a finish date left of the status date that is
not 100% complete, establish a new finish date if necessary.
There shall be no task with a %Complete>0 with a start date to the right of
the status date, you did not do the work in the future.
There shall be no task claiming 100% Complete with a finish date to the
right of the status date, the latest this task can finish is the status date.
If you do those four things, you probably have 80% of it covered.
____________________________

This makes me even more confused. I have a schedule that has a primary
purpose of tracking work performed to detect any deltas such as schedule
variance. I believe that I am basing my formulas to calculate changes in work
not duration. It is common in my environment (software coding) that even
though <work> starts with the same initial value as <baseline work> when I
initially baseline the project, that eventaully <work> will be altered to
reflect the most recent reality. When <Actual work> = <work> then the task
is complete.

Ex:
Test manger originally BOEs a tesk at 200 hrs of work which is baselined.
Two weeks later as we discover more about this code the <work> is changed
from 200 hrs to 250 hrs.

Something else that is common and in conflict with what you wrote is that,
unfortunately, it is not uncommon for tasks start dates to have already
occured even though no work as started against the task.

Ex.
Test manager has a Task A with a start date of 6/6/09 and the current date
is 6/10/09 and no work has been done on this task yet.

Are you purposing that I continually change all unstarted tasks to reflect a
<start> date of the current day?

Let me add one more example:
Task B has a <baseline start> of 7/21/09 and a <start>/<actual start> of
8/27/09.
The <baseline finish> was suppose to be 7/22/09.
<Baseline work> was originally 10 hours but has balloned up to <work> = 50 hrs
<Actual work> is 42 hrs.
What I cant figure out is why the RAG light is still showing yellow at the
current date of today, 10/13/09. All I can figure is that the formula thinks
that is 85% done and thus is in the yellow zone even though the task is 2
months overdue. In my mind it should show red to reflect that it is more than
20% behind.

Am I coding the RAG light with an incorrect formula or is something else the
issue?
I am currently basing the RAG on SV%..is that my problem?
BTW, I realize that SV is based on BCWS and BCWP--are related at all to
%complete or %work complete?

The SV%, etc are calculated in relation to the baseline dates in the original
plan. This has nothing per se do to with unfinished predecessors. Without
getting off on a tangent, perhaps my best opinions are listed on my blog
(link below).

I have three white papers on the subject of defining %complete, and Stop
light charts. See if what you find there is useful. I specifically address
the issue that a task starts late compared to its baseline.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

I have a question regarding the statusing of a project using EV.
Specifically, I am trying to create a series of green/yellow/red lights in a
new column to reflect when individual tasks in the project start to fall
behind on total work performed per task over the duration of the project.

My current formula is:
IIf([Work]=0 And [Baseline Work]=0,11,IIf([Work]=0 And [Baseline
Work]>0,10,IIf([Actual Work]=0 And [SV%]=0,0,IIf([% Complete]=100 And [Finish
Variance]>0,5,IIf([% Complete]=100 And [Finish
Variance]<=0,2,IIf([SV%]>-10,1,IIf([SV%]>-20,3,IIf([SV%]<=-20,8))))))))

The majority of the formula is tracking other events that happen in the
project but the part of the formula that focuses on the
green(1)/yellow(3)/red lights(8) are:

,IIf([SV%]>-10,1,IIf([SV%]>-20,3,IIf([SV%]<=-20,8))))))))

My problem occurs when I have a task that is currently being worked that has
a <baseline work> of 20 hrs a <work> of 20 hrs and an <actual work> of 18
hours..in this scenario the light is yellow as it falls between that 10% -
20% behind schedule.
HOWEVER, this task has baseline start and finish dates that should have
occured 6 weeks ago. This task even has a start date that occured 4 weeks ago
and the resource has not been working the task quickly.
I also have tasks similar to this one that are green because the actual work
is almost 100% complete even though the task is running 2 months behind
baseline schedule.


Does this have something to do with unfinished predecessors?
Should there always be a status date assigned or can it be left at NA?
Why is this happening?