Excel: Bubble Charts for Risk Management
Published on 6th November 2006
Welcome to the latest Hot Tip from Black Swan Training Solutions. This time we are going to look at using a special chart type in Excel to help with analysing risks.
Risk management is a discipline taught in our Project Management courses, and is important in order that your plans anticipate factors that might force a change. ‘It’s the one you don’t see that gets you!’ is a saying that justifies risk management in all forms of planning and management.
It involves the following steps (amongst others):
- Identify the risks
After you have spent some time listing the risks that you can think of, you need to evaluate them to see which demand some counteraction either now or in the future.
Evaluating a Risk
When prioritising which risks to deal with, you should consider at least these three aspects:
- Probability (how likely is it that the risk will happen)
- Impact (how serious will it be, if it does happen)
- Urgency (how soon it will happen)
Risks that are high impact, high probability and high urgency need to be dealt with before the unlikely, low impact risks that won’t happen in the next couple of years. For example, recently President Bush was having to deal with the risk of the Democrats doing well in the mid-term elections yesterday (Tuesday 7th Nov) with much more urgency than the Labour Party needs to worry about the Conservatives winning the next general election (you can make your own evaluation of that).
Plotting the Risk on a Chart
Traditionally, probability and impact have been plotted on a scatter graph, such as the one below.
On this graph the risks near the top right-hand corner need to have countermeasures taken to reduce them.
However, this does not take into account the urgency.
Excel’s bubble charts enable us to plot three aspects on a two dimensional graph.
Creating a Bubble Chart for Risks
- List your risks in an Excel spreadsheet
- Add columns for Urgency, Probability and Impact in this order. The order is important – the first column (Urgency) is plotted along the horizontal axis and the last is the size of the bubble (to show the size of the impact).
- Put your evaluations in (eg they could all be on a scale of 1 to 10, where 10 is high), so that each risk has three values.
- Highlight only the numbers
- Start Chart Wizard (Insert – Chart or click the Chart Wizard button)
- In step 1 of the Chart Wizard select Bubble and click Next.
- Make sure the data series are in Columns and click Next
- In the Titles tab of Chart Options enter the Chart Title (Risks), X-axis title (Urgency), Y-axis title (Probability).
- Turn off options such as Legend and Gridlines.
- Click Next and choose where you want the chart, then click Finish.
It is still the Risks near the top right we need to worry about, but it’s the circumference of the bubble that we need to watch not the point in the middle.
Of course bubble charts can be used for any scatter-type data that has three values to it. Remember to highlight only the numbers and that the first column is plotted against the horizontal axis (X), the second against the vertical axis (Y) and the third is the size of the bubble.