How to Create a RAG (Red, Amber, Green) Report for your Gantt Chart


A RAG status report is frequently using in project management to provide a visual indication of project performance. The actual definition of the thresholds used to determine the status can vary from project to project, however a typical interpretation for each condition is:

  • If a status is Red, then some form of escalation is immediately required byond the level of the project delivery team
  • If a status is Amber, a problem may have a negative effect on project performance, but can be dealt with by the project team
  • If a status is Green, there are no significant issues that will impact performance

In the following example I am going to use a combination of built-in GanttDiva features and excel macros to update the RAG status for each task based on whether the task finish date has deviated from the baseline planned date.

The sample project represents a construction project where some equipment is installed roadside and in equipment rooms. There are three sections, each with the same sequence of tasks with the start time for each section offset by a set number of days after the start of the previous section. In this example, after creating the plan, I baseline it using the ‘Set Baseline – all tasks’ command. This takes a snapshot of the start date, finish date and budget for each task.

The baseline finish date field will be compared to the finish date by the excel macro to set the RAG status for the affected task.

 

The first step will be to set up the task colour coding scheme so that we will have a colour available to represent each status.

Step 1 – setting up colours for tasks

It’s actually quite easy to set colour coding for tasks, so that you can set a field (in this example I will use the WPM field) to a status value and update the task to reflect a colour related to the status.

In this case I have defined three new names in the WPM field and set the colour code for each of the new names accordingly.  For simplicity I am going to assume that this project is not using Resource name field (which would have a higher priority for colour overrides)

setting-rag-colours

Step 2 – Preparing a macro to set the value of the WPM field

In my example I am just going to create a macros for each hammock task that I created to represent the total duration of that activities covered for each section. In this case it means that I will use a macro on rows 8, 22 and 36.

I am going to set up the following criteria for my macro:

  • If the finish date is more than 15 days after the baseline finish date for this task, then the status of the task will be Red
  • If the finish date is 1 – 15 days after the baseline finish date, then the status will be Amber
  • If the finish date is less than or equal to the baseline date, the status is green

In my example I define the macro in the WPM field as the following formula

=IF(I22>V22+15,”R”,(IF(I22>V22,”A”,”G”)))

Basically this is a simplistic macro that only takes actual days rather than working days into account but it should be enough to show how you can define your own macro.

In this case the macro first checks if the finish date is more than 15 days > the baseline finish date

If it is then the contents of the WPM field are set to the value “R”

If the difference is 15 days or less, a nested if statement is called, which first checked if the current finish is > the baseline finish and if it is the contents of the WPM field are set to “A”

If this condition is not met then the contents of the WPM field are set to “G”

This macro can now be copied to each of the hammock rows.

Step 3 – Verifying the correct behaviour occurs

In the same project I updated the duration of the planning update task for section 2 and verified that the rules described above are followed and the appropriate RAG status is now written into the WPM field.

Updating the Colour of the Tasks to reflect the RAG status

So if you have been following this example with your own project, you will notice that even though the contents of the WPM field change as expected, the colour of the task does not automatically change.

This is due to one of the limitations of VBA in that there is no event triggered by the spreadsheet for a calculated change to the WPM field which is triggered by a change in the task duration.

To get all tasks to update their colours to reflect any RAG status changes, you can use the “Redraw Gantt Page” command which is effectively like a recalculate command for Gantt chart. After using this command you should see any RAG status changes reflected in colour changes for the affected tasks.

rag-status-green

Conclusion

You can combine Excel macros with the built-in GanttDiva features to further extend the capabilities of GanttDiva to meet your needs. I have implemented a rather rudimentary way of defining RAG status, but you could also further enrich your macro to look at other factors such as EAC vs budget, a combination of schedule and budget thresholds, etc.

No comments yet.

Leave a Reply