I’ve seen many pretty risk assessment maps over time. The issue I see with most of them is that they’re more of an illustration than a method meaning, they’re customized visuals with manual graphics that don’t scale well for different projects. In the post, I’ll share my approach…it’s based on applying basic statistical concept, development and quality factors, and built in a way that’s easily scalable to any projects: software, hardware, buildings, etc. And the graphics I’ll present are easily customizable to scale with minimal work in Excel. No graphics design prowess required.
Let’s start with a fictional software project…
Once we thoughtfully identify the risk factors, write them in a table. Then as a team, we should assign the Impact and Likelihood of each. This feedback must involve inputs from Development, Test, Operations, UX leads (more or less depending on the nature of the project and functional disciplines involved) for example. Based on that we can create a score for each factor, which is product of Impact and Likelihood. A sample table for my fictional project is below:
Now we can easily pull the top risks from the table by using a function (no need to re-read the entire table!) such as: FILTER(). If my table’s name is Table1, then the formula would be: =FILTER(Table1[Topic],D7:D18>=$C$21,””) where C21 contains the risk threshold value.
The formula gives this output for high risk items:
So, how do we get this risk threshold value? We apply the concept of quartiles to our table. For example, we want to quickly point out factors that are of high risk to the project that scored in the 75% percentile. So, we need the 3rd quartile and to get the actual value, we can use the statistical formula: =QUARTILE.INC(Table1[Score],$B$20) and the result is in cell C21 in this example. So, in this case, cell B20 holds the value 3…because we wanted 3rd quartile. Depending on the project and situation, we can easily adjust the risk threshold by changing the quartile range in this method without changing anything else downstream (e.g. charts and visuals, which we’ll see in a moment). If we wanted the 50% percentile to be the cutoff, we can easily change 3 to 2 and everything will be instantly recomputed.
We have all information we need. Now let’s move to the next phase: presentation
We can visually summarize our findings with charts. I’ll show a couple of ideas and then conclude with a quadrant chart (which is my custom visual).
One quick way to present this can be a combo chart as follows with the top 3 risks in dark red, and close-next in red:
Another way is see them is a Pareto chart…it shows where the top risk items fall and what % of them. The area is highlighted with an overlay in a translucent box:
Pretty good…but we can do even better. This is where I created this quadrant chart. It’s a customized scatter chart. It lays all factors neatly into an oft-beloved quadrant layout. For better impact, I find the intersection point of Impact and Likelihood from the table values and plot that as a separate series. Then to split the plot into quadrants, the green lines can go through this intersection point horizontally and vertically. Anything in the top-right box will be the high risk factors as set by the agreed-upon risk threshold above and can be easily boxed in with a translucent shape as a focal point.
So, there you have it. Hope you found it interesting. Note that all of the charts above use the same, just one table as discussed above. We can add new factors, impacts, likelihoods, and even change our risk threshold without having to change a single formula or chart used in this method.
▛ This post is not meant to be a formal tutorial, instead it is to offer key concepts, and approaches to problem-solving. Basic->Intermediate technical/coding knowledge is assumed. If you like more info or source file to learn/use, or need more basic assistance, you may contact me at tanman1129 at hotmail dot com. To support this voluntary effort, you can donate via Paypal from the button in my Home page, or become a Patron via my Patreon site. A supporter/patron gets direct answers on any of my blogs including code/data/source files whenever possible. At the very least, it’ll encourage me to share more posts/tips/knowledge in the future. ▟
