One of the core activities performed on all projects we deliver is risk planning. This important exercise helps a project get ahead of potential pitfalls the could at worst completely stop a project. To facilitate risk planning, someone developed an Excel spreadsheet. I’ve had this for years – and unfortunately, I don’t know who the original author(s) are. Click project risk log template to get a copy of the spreadsheet.
Before we jump in to the template, I’d like to extend thanks to a friend, Dawna Covey, who reviewed and improved on my post. I’ve got many years of experience delivering IT projects, but she’s got the credentials to make sure I didn’t say something incorrect.
The follow graphic is probably a little hard to read, it is a snip of all of the column headers – we will go through each column below.
I’ll break it into 3 parts – risk meta-data, risk impact, and risk mitigation.
1. Risk meta-data
- ID – Pretty self-explanatory; this is a unique number so when someone needs to refer to the risk – it’s not dependent on an Excel row number (e.g. someone inserts a row, this would move the risk around).
- Risk type – Used for categorization and grouping. You will have to define what types of risk make sense for your project. Just remember to keep things consistent and maybe publish the definition of each risk type for clarity.
- Risk name – The key here is short. Pick something meaningful, but only a couple of key words.
- Description – This is one people tend to get wrong. The description defines what the risk is. Do not define the outcome, just the risk itself. Really getting to the heart of what the risk is can be tricky.
- If we don’t have resources available to do the configuration due to freeze dates
- Because we cannot control the key dependency on the Security Team
- If the change control board doesn’t approve our exception
- Consequence – Use this to define the consequence of the risk (description) – in other words, what will happen.
Some examples (continued from above examples):
- Then we will miss the targeted deployment date and the overall timeline will slip
- Then we will not be able to deploy the feature the business requires
- Then the project will not be able to continue
- Date Opened – This is important for time tracking (e.g. how long has this been open, etc.).
- Due Date – When you expect the risk to be realized or know when it may be safe to no longer track it.
- Risk Owner – The primary contact – avoid the temptation to list multiple people. Many people may work on the risk, but its best to identify a single person who can speak to it and coordinate across the rest of the teams. In RACI terms, this person is ‘accountable’.
2. Risk impact
The next section is for determining the risk exposure. It’s a very simple equation – the impact multiplied by the probability equals the exposure (I x P = E). How do we come up with the impact and probability?
There are a few methods I use, keeping in mind these are for the most part subjective and arbitrary. I find its helpful when explaining this to others is to forget about the inputs for a moment and understand the outcome of the calculation (how will the exposure rating be used).
The rating will fall into one of three buckets – low, medium, and high. Again, these are somewhat arbitrary, but have a relative importance. It may sound like common sense, but high means things that are very risky. They have a high probability of being realized, and a high impact – so when ranking exposure – these are the ones you really want to focus on. Medium is in the middle, and low (not surprisingly) means its very unlikely to happen and if it does, doesn’t really have much impact.
Armed with this knowledge, you can start to assign impacts and probabilities using a consensus model. A group of people can usually come to an agreement and everyone in the end can see if the result passes the ‘sniff test’. If in the end the exposure doesn’t feel right, its fine to go back and adjust the inputs.
Another method is to ask a subject matter expert (SME). A SME has domain knowledge and may be able to better assign the values based on previous experience.
Finally, it may be possible to use an empirical approach, at least with probability. You may have data that shows a failure rate and use a measurement to determine the value.
For me, the most important result is that all the stakeholders agree on the exposure ratings and rankings versus being ‘right’. After you complete this for all risks, it’s a good idea to review the list a few times. If you find you have many high risks – it’s possible some of the inputs were overvalued. Generally, you want a bell curve distribution, but don’t force it. It’s possible there are a lot of high risk, high impact exposures if the project is a risky one.
3. Risk mitigation
The last section is often overlooked, but equally important to fully document.
Items 3-5 are mainly for tracking if you intend to use the spreadsheet for this purpose. The key item are 1 and 2.
- Mitigation Plan – If mitigating, how to reduce the impact or probability of the risk. Notice the ‘if’. There are other options – such as accept the risk, avoid the risk, and transfer the risk.
- Contingency Plan – Oh no, it happened. We realized the risk – what do we do? Its best to try to think of this way ahead of time instead of after the fact when you are knee deep in damage control. The mitigation and contingency plans should be commensurate with the threat. An example is what if we lose our data center, the contingency may be to have a secondary one. Depending on the cost of being down, this could be hot, warm or cold.
- Status – just a tracking column – the default choices are open, closed, and reopened. There is a hidden sheet that you can edit to customize. One common addition to add is ‘canceled’. This is different than closed as you can cancel as the risk level was reduced or removed. In other words, closed infers there was action upon it, but canceled connotes the risk disappeared (no mitigation).
- Resolution – The defaults are mitigated, accepted, and escalated. As mentioned in #1, you could add other resolutions that make sense for your situation.
- Resolution Detail – Used for tracking, a textual description of the resolution.
- Date Closed – Again, just used for tracking purposes.
- Notes, Comments – Anything else relevant to capture.
One other possible column you may wish to add is ‘triggers’. You can identify criteria for determining when to initiate the contingency plans. That’s it, hopefully it will help you understand at least one way to understand and document project risks.
— Bonus —
Here are some Q&A that didn’t quite fit above:
Q. Do I need to do this on every project?
A. I think it’s the right thing to do and is the correct way to manage your risks. It does not take a lot of time, and has the potential to avoid embarrassing or damaging events. Its like the old saying “if you can’t measure it, you can’t improve it.” The risks will be there whether or not you plan for them. You need a mechanism that clearly defines and explains the risk and the process to status.
Q. How many risks should we have?
A. Enough to feel comfortable that you have identified the most relevant exposures. I have a spreadsheet entitled ‘Every Risk Imaginable”, consisting of over 1,000 risks. This is most certainly overkill. Interestingly, one example is “valued staff member quits” – and I’ve been on a project where we lost half the project team within a week..this wasn’t on the risk list. While overkill is a possibility, if done using something like the template risk log, you can filter and sort the risks. As they close or move to lower priority, they will go to the bottom and will not increase the burden during recurring risk review.
Q. What is the difference between a risk and an issue?
A. A risk is a potential for something to happen. An issue is a risk that has been realized. I like to track these separately, to keep people from getting confused. I’ve seen this trip up many people, so I may expand upon this topic in the future.