| Home - White
Paper Index |
Status Stoplights with Microsoft Project
|
First published Sept 07
|
Concept designed and introduced by Brian Thompson
|
Rating |
|
|
|
Overview
 Ever
wondered how to use project monitoring tools as pro-active aids instead of
reactive reporting tools? Project stoplights can provide an easy guide to
taking pro-active action on tasks or milestones with potential delay within
a one, two or three week window to the future. Selection of the window horizon
depends on the size and duration of the project. After setting up stoplights,
the process is simple and hassle-free; green stoplights are good; white stoplights
are future tasks with start dates beyond stoplight horizon; red stoplights
require immediate attention; yellow stoplights require pro-active action
to mitigate risk associated with late completion of future tasks. Read on
to find out how…
Typically, stoplights are used during execution phase of a project. Microsoft
Project is used in this example, illustrating how to set up stoplights and
the underlying logic applied to drive the plan in a pro-active manner to
help mitigate execution risk and provide a consistent early warning process
to facilitate on-time task completion.
Setup of Stoplights
 |
Step 1: Customize one of the available
fields in MS Project (I selected the “Number 2” field
for this purpose).
Navigation: Tools>Customize>Fields |
Step 2: Add a formula to support the required stoplight
logic
Navigation: Click the “Formula” button
This formula supports the logic set out below:
IIf([Duration]=0,(IIf([% Complete]=100,2,IIf([Finish]<Now()+ 7 And
[% Complete]<100 And [% Complete]>=80,3,IIf([Finish]<Now()+ 7 And
[% Complete]<80,4,1)))),IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))
 |
Note: For a short duration project (typically 1 to 3
months) a value of 7 (days) is recommended for the stoplight
window (see red text
in formula above) – for a longer duration project
a value of 14 (days) is recommended – this provides
a longer evaluation window with more time to take pro-active
action. The status stoplight underlying logic is discussed
in the last section.
Click to “OK” button to save the formula. This
logic will place the values 1 through 4 on tasks and milestones
in the column. (4 = Red, 3 = Yellow, 2 = Green, 1 = White) |
 |
Step 3: Use averages to roll up scores
to group summary rows and substitute numbers with graphical
indicators.
Navigation: Check the radio button “Rollup” and
select “Average” from the dropdown box for
group summary rows.
The logic for image selection is the first test which meets
the logical argument will prevail (if the value in the column
is between 4 and 3, red will prevail etc). This is useful
for the summary group rollup values.
To set the flag colours, click the “Graphical
Indicators” button |
 |
Set up four logic “tests for Stoplight” as
depicted in the screen shot.
Click “OK” when done. |
|
|
Step 4: Insert a column into the Project
Gantt view and rename the column.
Navigation: View>Gantt Chart. Right-Click column
header at the position for column insertion. Insert Column>Select
Field name from dropdown box (“Number 2”).
Enter a Title for the Column (“Status”). Click
OK. |
The column will now be in the Gantt View. See screen shot below.
Practical application of the stoplight enhancement
In simple terms a quick scan down the stoplight column will draw immediate
attention to red and yellow stoplights, being the alert to take pro-active
steps to re-assign resources, re-evaluate effort or update progress to facilitate
on-time task completion. From the Gantt chart above, tasks may be filtered
on the numeric value in the stoplight (status) column, keeping in mind that
white (not started tasks) is less than 1, green is between 1 and 2, yellow
between 2 and 3, red between 3 and 4, or simply filter tasks by date range
and address tasks in priority sequence (red, yellow, green, white).
Import stoplights to other project plans
Once the stoplight column has been set up in an initial project plan, it
is easily imported into others by opening the plan containing the stoplights,
opening a new plan, inserting a numeric column, right-clicking the header,
select “Customize Fields”, then click the “Import Custom
Field” button to select the initial project and stoplight field. Rename
the column by clicking the “Rename” button and entering “Stop
Light” or any other suitable name after the import and your stoplight
will be embedded into the new project.
Formula Logic
Logic used to drive status stoplights has two different drivers. First driver
is the logic to apply to milestones to evaluate if any action is required
on a zero duration milestone. Second driver is logic to drive actions on
each task in the plan.
Milestones:
If milestone is 100% then green.
IIf([Duration]=0,(IIf([% Complete]=100,2 ,IIf([Finish]<Now()+ 7 And
[% Complete]<100 And [% Complete]>=80,3,IIf([Finish]<Now()+ 7 And
[% Complete]<80,4,1)))),IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))
If milestone is due within 7 days and the progress >= 80% then Yellow
(This is a signal to check if milestones are still on target for completion).
80% is a good general guideline for the next two sets of logic, this value
can be changed to suit your situation and/or project duration. The lower
the value, the sooner the yellow alert will pop up.
IIf([Duration]=0 ,(IIf([% Complete]=100,2,IIf([Finish]<Now()+ 7 And [% Complete]<100 And [% Complete]>=80,3 ,IIf([Finish]<Now()+ 7 And
[% Complete]<80,4,1)))),IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))
If milestone is due within 7 days and the progress < 80% then Red (This
is a signal to check on milestone status and potential problems which could
mean moving a milestone).
IIf([Duration]=0 ,(IIf([% Complete]=100,2,IIf([Finish]<Now()+ 7 And
[% Complete]<100 And [% Complete]>=80,3,IIf([Finish]<Now()+ 7 And [% Complete]<80,4,1 )))),IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))
Tasks:
If task is 100% complete or if ratio of days since start divided by task
duration is less than “% complete” then Green to indicate complete
task.
IIf([Duration]=0,(IIf([% Complete]=100,2,IIf([Finish]<Now()+ 7 And
[% Complete]<100 And [% Complete]>=80,3,IIf([Finish]<Now()+ 7 And
[% Complete]<80,4,1)))),IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))
If today (date) is past the end date of the task and task is not complete,
irrespective of progress reported, then Red to indicate an overdue task.
IIf([Duration]=0,(IIf([% Complete]=100,2,IIf([Finish]<Now()+ 7 And
[% Complete]<100 And [% Complete]>=80,3,IIf([Finish]<Now()+ 7 And
[% Complete]<80,4,1)))),IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))
If today (date) is past the start date of the task and progress is reported < (days
since start/duration) then Yellow to indicate a task with insufficient progress.
IIf([Duration]=0,(IIf([% Complete]=100,2,IIf([Finish]<Now()+ 7 And
[% Complete]<100 And [% Complete]>=80,3,IIf([Finish]<Now()+ 7 And
[% Complete]<80,4,1)))),IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))
 If today (date) is before start date of task then White to indicate a future
task.
IIf([Duration]=0,(IIf([% Complete]=100,2,IIf([Finish]<Now()+ 7 And
[% Complete]<100 And [% Complete]>=80,3,IIf([Finish]<Now()+ 7 And
[% Complete]<80,4,1)))),IIf(([% Complete]=100 Or [% Complete]>100*(Abs((Now()-[Start])/([Finish]-[Start])))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3),4))))
About the Author
Brian Thompson is a Senior Manager with Capgemini based in Massachusetts,
USA. He was born in South Africa, educated at Rhodes University and Stellenbosch
University where he earned an engineering degree in aeronautics. He worked
in aerospace for 12 Years managing aerospace programs, projects and global
projects. Re-located to North America in 1998 where he lives with his wife
Tracey and two sons Brad and Matthew. Brian is a certified Project Management
Professional (PMP) and an active member of the Project Management Institute
(PMI). Program and project management experience in USA include managing
projects and implementing Enterprise Resource Planning (ERP) solutions for
a number of aerospace companies and advanced supply chain planning systems
across a number of industries including financial, aerospace, automotive,
consumer products, paper, gaming, leasing and government.
To date,
42 people have rated this article. The average rating is
4.57 - Add your rating. Just select a rating and click the button. No other information required.
Only one rating per person is allowed.
|
Stoplights in Microsoft Project
|
Return to the top
|
When you subscribe you can download a free eBook
