Part 3 of a series on customising fields in Microsoft® Project® to make working with project schedules more useful.
So you’ve created a useful programme schedule, and you are now working on keeping it on the right track. You’ve set up fields to calculate RAG status, but what if you want some schedule management information to go with it, in the form of some compact RAG status explanation to go with it, without having to study the baseline vs. forecast dates and % complete, working it out in your head line by line?
This post describes how to customise a Text field to display a very compact schedule management commentary to explain why a project schedule item should be Red or Amber.
Part 2 of a series on customising fields in Microsoft® Project® to make working with project schedules more useful.
So you’ve created a useful programme schedule, and you are now working on keeping it on the right track, reviewing the plan regularly with the Programme Manager and Project Managers. You will probably review the programme schedule regularly – focussing on on items with Red or Amber RAG status – but how do you use MS-Project to ensure that you don’t skip items that probably should have been marked amber or red?
This post describes how to introduce some schedule automation by customising a text field to display RAG status (based on performing some simple tests and calculations with dates).
So why would you want to use this approach, how do you do it, and what’s in it for you as a result?
RAG status is often allocated to project tasks based on the intuition or “gut feel” of the project manager. But too much subjectivity in status reporting can lead to watermelon or green-side-up reporting, in which project status is reported more favourably than it probably should be. The more objective approach presented here introduces some schedule automation, assigning a RAG rating to individual project tasks based on calculation.
This approach can’t account for subjective things like project risks (that takes professional judgement), but it can give a useful “starter for ten” RAG status assessment onto which the PM can layer factors such as risk, or to act as the starting point of a discussion.
Approach: Logic tests
Devise a set of criteria that you will use to determine RAG status, based on the rules or criteria in place for your project or programme. Your central PMO may have some criteria, or you may devise your own. They may look something like this (this assumes the plan has been baselined):
Red = Item that should be complete by now but isn’t
Amber = Item forecast to finish later than baseline finish date
Green = Item forecast to finish on baseline finish date or earlier
You need to formulate the tests in such a way that they can be expressed as a mathematical expression that can be tested and return a TRUE or FALSE result. For example, to test whether an item should have finished by now, test for [Forecast finish date] < [Today’s date]. If the answer is TRUE, the item should have finished by now.
These tests can be made as simple or complex as you like, for example you could enhance the Red test so that Red returns true if the item (is forecast to finish earlier than today’s date) OR (is on the Critical path, and is forecast to finish later than baselined) OR (is on the critical path and has not yet been baselined).
After testing for item completion (because if the item is complete then none of the other tests apply) these tests will be applied in order of decreasing severity (because an item that triggers a Red condition would probably trigger all the Amber conditions too).
Implementing the tests
Pick an empty custom task text field (in MS-Project 2016, select Project, Custom Fields), rename it something like “Auto RAG” and click on Formula (see figure 1).
The MS-Project Switch() function applies a number of tests and performs an action for the first test it comes to that returns a TRUE result. The format is:
The TestThatAlwaysReturnsTrue at the end “catches” the condition where none of the previous tests return a True result. Seeing the “ERROR!” result should prompt you to check your tests (or add some more conditions to catch other scenarios).
In “coded” format, such a series of tests would look like this:
I have formatted this to make it more readable. Be aware that MS-Project will strip out the spaces and returns, so the formula will look a lot less readable if you ever have to come back to edit it.
Put this in the “formula box” (see figure 2).
Once you have a text RAG value, you will probably want to display that as a graphical indicator to make reds and ambers easier to pick out in a long plan. This is done from the same Customise Fields dialogue box as before (figure 1), but this time using the Graphical Indicators option, which gets you here:
The approach is to test whether the value in the field “equals” each of the RAG status values and to assign an appropriate graphical symbol to each.
The What(’s in it for you)
Congratulations! You have now introduced some schedule automation and given MS-Project the ability to tell you what the RAG status of an item should be, based on a rigid interpretation of whatever rules you have given it, ready to have overlaid upon it any other factors the PM knows about. What approaches do you use to calculate RAG status in MS-Project?
This approach comes to you from my experience of getting it wrong and coming up with a better way. If you would like to have the benefits of an approach like this but prefer not to have to worry about this sort of techy stuff, why not talk to us about taking care of it all for you on your next big project or programme?
Part 1 of a series on customising in Microsoft® Project® to make working with project schedules more useful.
So, you’ve inherited a set of project schedules that you need to combine into a useful programme schedule. How do you set up a framework to enable you to quickly find and diagnose schedule setup problems, without having to deduce and apply complex combinations of filters? Here is an approach I have used successfully on several programmes.
High risk rapid delivery or lower risk slower delivery – which would you go for?
Until recently, my morning commute into work comprised a bus journey to the station, a train into London, and another bus to the office. One morning a few weeks ago I was ready about ten minutes earlier than usual so instead of waiting about 15 minutes to catch my usual bus, I took the earlier bus from near my house. I had already bought my train ticket so I didn’t have to queue at the station, instead waiting just a few minutes to get on a train about 25 minutes earlier than my usual one. Once in London I went to the bus stop, and joy of joys, the bus arrived straight away.
All in all, by leaving my house about 25 minutes earlier than usual, I managed to arrive at my desk at least 45 minutes earlier. I had made a significant time “profit”!
There were some unexpected but very welcome fringe benefits too – the earlier trains and buses are less crowded and so I have more chance of getting a seat (making the journey more pleasant) and often enough elbow room to do some work on the way (making the journey also more productive).
It occurred to me that what had happened here was akin to what I do at work with schedule tuning – I had experienced a journey with less slack time (waiting) between the fixed duration events (journey stages) to enable an earlier final delivery (my arrival at my desk).
I now regularly use this approach to get to my desk earlier. Having less slack in the plan introduces risk of course; if any of my journey stages runs even a few minutes late then it all falls apart and the rest of the journey reverts to the (considerably slower) Plan B. But it works enough of the time to make it worth persisting.
Are you able to use schedule optimisation like this to cut the overall duration of your projects? Are you more concerned with earlier delivery, regardless of overall project duration? Or do you go for a less risky plan with more slack?
His presentation contained several key points that resonated with me so I thought were worth restating in the light of my own experience.
Estimates need to have a solid foundation. No, a finger in the air won’t do here. Base your estimate on a similar project that was done recently. Base it on productivity figures and lines of code. Base it on PERT or three-time estimating if you like, but be comfortable with how you derived it and have at least some idea of the potential error margin. I have used all of these estimating techniques at some time or other, and knowing the likely accuracy at least gives you a guide for how much contingency to build in, which brings me to…
Build in some contingency. A knotty one, this! In most plans where I have explicitly built in contingency, I have been required to remove it to reduce timescales. In most cases, of course, something has then happened to increase the project timescales, and the final delivery has ended up where it would have been with contingency in place, except that the project is now Late. No wonder project managers are tempted to build in “secret” contingency, and deliver on time?
Update the plan with actuals (durations, costs, etc,). This is essential for keeping your schedule on the right track. If a task was late due to a delay, add in a task showing this delay and label it accordingly. The schedule then acts as an audit trail for delivery. If any of the tasks are repeated, use the actuals from iteration 1 to refine the estimates for iteration n. Follow through and see what the impact is on delivery. I have found it is very difficult to get people to care about activities once they’re complete, but this approach does mean that you can apply your learning from the past to the future, and best of all this happens while the project is still running.
Manage the Critical path.Make sure you make good use of dependencies, and as little use of time constraints as possible. I always do this as much as possible, as it enable you to see the effect of any changes on the critical path at the touch of a button. The plan should be a useful mathematical modelof the project, not just a pretty picture on the wall of some coloured bars linked together.
The act of developing the plan is as important as the plan itself. Or perhaps more important! Thinking through the steps in detail, rehearsing the project in the team’s collective mind, is a great way of identifying potential pitfalls and managing them while they are still risks, if appropriate.
What are your experiences? Do you agree with Andrew and me? Do you have any planning tips you would like to share?
Scheduling, Monitoring & Control: The Practical Project Management of Time, Cost and Risk
Association for Project Management (Princes Risborough, Buckinghamshire) 2015
£50 RRP (electronic review copy provided free of charge)
This book is a good reference guide to Planning, Scheduling, Monitoring and Control, with most of the topics covered at introductory to intermediate level in relatively informal jargon-free language with plenty of helpful diagrams.
The guide is aimed at students and practitioners, so I’m a bit puzzled why it begins with a chunky explanation of how projects are defined and the documents used. At 20 pages this section is too hefty for the completely uninitiated, but has nowhere near enough detail to be useful an already-practicing project manager (who would be better off referring to one of the BoKs or methodologies). I guess however that novice Project Planners may find it useful for context and orientation, and it signposts topics for further reading.
The book gives a useful and succinct description of the differences between planning and scheduling:
Planning is the art of deciding on the best way to do the project, defining scope and deliverables, and getting agreement from the stakeholders. It is broader than scheduling and logically happens earlier.
Scheduling is the science of estimating how long it will take and how much it will cost to deliver tasks, and sequencing them to create a logical model of the future that can predict when project outputs can be delivered. This model progressively becomes fact as estimates are revised and activities completed.
Next there are tips on planning and scheduling, including some suggested approaches for creating and quality-checking the documents. It describes how to set a schedule baseline, how to evaluate the impact of any changes requested (using offline, static copies of the live schedule), and how to update the baseline to take account of approved changes.
Monitoring approaches are then described, with a warning that no one of these gives a complete picture of the project’s status. Earned value analysis (EVA) comes closest, but requires significant effort
The book recommends regular schedule co-ordination meetings, to review recent progress (recording reasons behind any slippage for analysis later) and future tasks within an agreed time window, allowing team leaders to plan the near future in detail.
There is a good introduction to quantitative schedule risk analysis (a.k.a. Monte Carlo analysis) and the value it can bring to both schedule and dimensions by giving the PM statistically-derived forecasts of final delivery date and cost. Some of the diagrams here could be clearer, but I am told these will be reviewed ahead of the next print run.
The book also outlines how forensic schedule analysis can be used to establish the cause of delay(s) in litigation scenarios.
At the end there is a helpful glossary and a list of abbreviations.
Some example tips
Schedules should have an appropriate density (amount of detail) for their purpose: Low density for communication e.g. to executives; Medium density for the reference plan; High density for day-to-day management of delivery teams’ work. A schedule with too much detail is just as bad as a schedule with not enough detail, as it is difficult to manage and won’t be used.
Schedules should record a baseline (when we agreed it would be delivered, updated with any approved change requests), a forecast (when we now believe it will be delivered), and “as built” (when we actually delivered it, including any unexpected extra events). These may be in the same scheduling document but will not usually be displayed at the same time.
Where a team is producing a string of broadly similar deliverables and can be thought of ticking these off a list, it may be more helpful and easier to manage using a spreadsheet tracker rather than scheduling software.
Schedule item names should be understandable without reference to WBS headings (which may not be visible if a filter is applied) – task names should start with a verb (lay bricks; paint walls) to indicate activity, and milestone names should express a state in the past tense (walls built; plumbing complete)
First sequence tasks in logical order (using mostly easy-to-understand Finish-to-Start relationships), then allocate resources. Then apply resource levelling by first delaying tasks with free float (so delaying them does not delay successor tasks), and only then delaying tasks with no free float but some total float (so delaying them delays successor tasks but not the overall finish date)
Identify dependencies between projects, label these clearly in the schedule including whether these are inbound or outbound. Meet regularly with the PM at the other end of the dependency link; review whether the current forecast date works for both parties and agree any remedial action required. Any changes in delivery date to be the subject of change control.
A budget is a planned upper limit on resource to be consumed for a given task or tasks. It is often expressed financially but not always; it could be expressed as terms of days of effort, quantity of materials, etc.
I recommend this book for early and mid-career PMs and PMOs. I would have scored it higher if not for the somewhat incongruous section on project definition (which one can simply overlook) and a few mathematically doubtful diagrams (harder to ignore). I expect that my review copy will become dog-eared through use!
During the final delivery stage of a multi-year relocation and Change Management project, with a few weeks to go before moving ~800 people and their equipment between sites, I was managing the integrated programme schedule to confirm we were in a position to move on the planned dates (or not!).
The Steering Committee (SteerCo) and Internal Audit wanted reassurance (via a series of pre-move readiness meetings) that we would be able to move on the planned dates, to know that the Business and Stakeholders would be happy to move on those dates; and for the programme to take any appropriate action to enable this to happen.
To enable this, I created a suite of intermediate “collector” milestones on the programme schedule:
“People ready” to move (reflecting the completion of Change Management activity, both “Must have” and “Should have”)
“Systems & Building ready” to receive the People ( both “Must have” and “Should have”)
I worked on the basis that in order to be in the “Must have” category, the absence of the deliverable had to be big enough to stop the move; otherwise it wasn’t really a “must have” but a “should have”. I made these intermediate milestones immediate predecessors of the first move (with any slack available between these and the first move date being explicitly shown as schedule contingency). I obtained current information on forecasts and actual start/finish dates through weekly Workstream Lead (WSL) reports and regular 1:1 schedule review sessions, and updated the schedule with this information.
I monitored the plan (and especially the critical path leading up to the first move) for the effect on the final deliverables of any updates. Each time I updated the plan with new information, I checked to confirm we were still on track to deliver the final deliverables on the planned dates, adjusting the contingency buffer if necessary.
In this way I spotted anomalies – for example, having updated the schedule with revised dates from weekly WSL reports, I noticed final delivery milestone had moved 2 weeks later than planned. Each WSL’s detail plan appeared to be on track in isolation, but taken as a whole programme (taking dependencies into account) the final deliverables were late.
My typical approach to finding the cause of an anomaly such as this is to trace the predecessors of the slipped deliverable, working backwards in time to find where the delay is ultimately coming from (usually this is from missing or outdated inter-workstream or inter-project dependencies, raising the need for greater detail or revised dependencies).
In the case of the example, I isolated the problem to the start of a technology Pilot, which in turn was dependent on an IT system becoming available following Technical Testing and User Acceptance Testing (UAT).
Re-plan, Recommend, Resolve
I then analyse the situation and propose some remedial options, in the case of the example these included:
Removing the IT system from the Pilot scope (introducing a quality risk)
Shortening the duration of the Tech testing before the UAT and Pilot (also introducing a quality risk)
Combining the Pilot with UAT by selecting UAT users for the Pilot, killing two birds with one stone (recommended approach)
The programme team opted to implement the combined UAT / Pilot approach I recommended, bringing the programme back onto schedule. The approach for Change Requests is similar:
Create a new version of the schedule (or insert new activities in such a way that they don’t affect the “real”, working schedule) to use as a “what-if?”schedule.
Make the changes as detailed in the Change Request, making or breaking dependencies as appropriate.
Assess the impact on the delivery dates, resource utilisation, financial forecasts, and make a recommendation to the programme director and ultimately the SteerCo to accept or reject the Change Request.
As a result of using this approach:
The programme was kept on schedule, to deliver on time;
SteerCo and Internal Audit were reassured that the run-up to moving was in hand, as they were kept up to date on an ever-diminishing list of pre-move “must have” deliverables;
The Go/NoGo decision on moving was taken in the full knowledge that: all “must have” deliverables would be in place on day 1; most “should have” items would be in place on day 1, and the missing “should have” items would become available on specific dates post-move.
In this way, the schedule was transformed from a collection of dates and coloured sticky notes on the wall into a dynamic tool with which to proactively manage programme delivery.
So that’s my approach to keeping a programme schedule on track, learned through training and experience. It this approach useful to you? How do you keep your programme schedules on track?
If you would like to have the benefits of an approach like this but prefer not to have to worry about this sort of techy stuff, why not talk to us about taking care of it all for you on your next big project or programme?
How do you integrate the schedules of multiple projects into a single programme schedule that concisely conveys time-related information? This is the approach I have successfully used for several programmes.
I was brought into a programme which had only rudimentary programme spreadsheet-based plans, and which was therefore unable to forecast how delays in one part of the programme would affect other parts of the programme, or to assess the potential impact of change requests. Senior stakeholders needed a programme “road map” (to assess overall progress towards delivery deadlines, and to obtain some guideline forecasts for the annual budgeting cycle), and the programme director needed a detailed view of the activities leading up to delivery.
Purpose of the schedule
A programme schedule should not just be a pictorial representation of what we think will happen, or a rigid list of events to which the programme should slavishly adhere. Instead I believe it can and should be a flexible model of the programme’s activities that can forecast potential problems, be adapted to overcome those problems, and be used to enable on-time delivery.
Where they already existed, I used task lists and brainstorming session outputs as a starting point. I developed these by meeting with project managers (PMs) and workstream leads (WSLs), capturing their thoughts on the key activities of their projects directly into Microsoft® Project® (MS-Project), challenging duration estimates and dependency relationships as we worked. I refined the resultant draft schedules using stakeholder input before getting them agreed by all parties and baselining them.
Where there were no plans at all, I either held planning sessions or translated process flow diagrams prepared by Business Analysts into MS-Project plans, cross-linking these with dependences into an integrated and resourced programme schedule, with the critical path identified and highlighted.
I also worked with contributing PMs / WSLs to standardise elements of their plans (e.g. use of custom MS-Project fields) for easier integration into a programme schedule.
From this programme schedule, I prepared resource and financial plans for budgeting as required, custom filtered views (missed milestones, items due to start or finish in the next four weeks, etc.) for progress tracking, and highly summarised “road maps” for senior stakeholders.
I kept the schedule updated with progress and with the outcomes of approved Change Requests; I regularly reviewed forecasts against the schedule baseline to identify potential slippage; and prepared “what-if” plans showing the impact of proposed changes.
In this way, the schedule was transformed from a collection of dates and coloured sticky notes on the wall into into a dynamic tool with which to proactively manage programme delivery.
Under this approach, the Programme Manager has a good forward view of the critical path to delivery (and the potential pitfalls along the way) and the Programme Board has a good overview of progress achieved so far, and the activities still to come.
But what about when things change? What do you do then? More about that in a separate post.
So that’s my approach to creating a useful programme schedule, learned through training and experience. It this approach useful to you? How do you keep your programme schedules on track?
If you would like to have the benefits of an approach like this but prefer not to have to worry about this sort of techy stuff, why not talk to us about taking care of it all for you on your next big project or programme?
® Microsoft and Project are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.