Charts and Dashboards: The Risk Bubble Chart – Part 1

Welcome back to our Charts and Dashboards blog series.  This week, we’re going to look at how to make a Risk Bubble chart.

The Risk Bubble Chart

Have you ever wanted to organise your risks and consequences based on the likelihood of occurrence and the potential damage?  Today, we will introduce you to a Risk Bubble chart which helps you to keep track of the likelihood of any given problem, the consequences of the problem, and the size of said problem.

Here, this chart categorises types of risk in two dimensions, i.e. both the impact of the risk and the likelihood of it occurring.  The size of the bubble shows how many categories meet the same criteria, but this could be replaced by a monetary value impact, the number of customers affected, etc.  And yes, I do appreciate that using bubbles (circles) may not be the best way to depict the number of risk types due to the area of a circle being proportional to the square of its radius!  The aim of this article is not to defend such practice, but to show how to create such a chart given they are used for risk assessment / analysis.

A key element of this chart is the risk input table, which is automatically updated based upon your input(s) and the Risk Bubble chart.  To assist us, there are a few key inputs we will be using here.  The first table we have is a table we shall name LU_Matrix (i.e.  the “LookUp Matrix”), viz.

In this table, we will name the row headings (in grey) LU_Likelihood and the column / field headings (also in grey) LU_Consequences.  With these inputs, we can create a risk table that changes colour automatically.

The next input we will need is the risk category table which determines the likelihood and consequence of a risk:

We name this table Risk_Category. 

In this table, we can enter the Risk Category, Likelihood, and Consequence.  These inputs will be used to make the Risk Bubble chart, but we will need to refine this data for it to be usable.

With these inputs available, the question now is: how do we make the Risk Bubble chart?  Well, let’s get started with the risk table…

Risk Table

The first step is to construct the axis for this chart.  For this task, we will need to employ the COUNTA, the SEQUENCE, and the INDEX functions.  COUNTA as you guessed will “count” every cell that is not blank.  Therefore, we will count the number of Likelihood categories:

The output for this function will be five [5] as we have five [5] Likelihood categories in our example.  As we generate a sequence of numbers, we will wrap up the COUNTA formula inside the SEQUENCE function:

This will generate an array from one [1] to five [5]:

However, we want this array to generate in reverse order, i.e., five [5] to one [1].  This can be achieved by setting the third argument of the SEQUENCE function to our number of Likelihood categories and the fourth argument to negative one [-1]:

The third argument of the SEQUENCE function will specify the starting value and the fourth argument will specify the increment for each subsequent value.  This will give us the following:

(If dynamic arrays are not available, alternative formulae can generate the same desired result).

Finally, we apply our INDEX function here to make the y-axis (vertical, or dependent, axis) of the chart:

The INDEX function will take the fifth item of the LU_Likelihood and put it on the top row, the fourth item put on the second row, and we repeat this process until we have the first item in the fifth row:

Then, we will go one cell below and one cell to the right of the word “Rare” here to enter our second, much simpler, formula:

After entering the formula in that cell (again, assuming you have Dynamic Arrays in your version of Excel), we will have the following visual:

Next, we apply some formatting:

We have formatted each of the Likelihood and Consequence values to make them look more like headings, whilst rotating the text in the y-axis.  We have also resized all cells within the graph area to dimensions of 100x100 pixels (a column width of 13.57 and a row height of 75).

Assuming the grid is positioned as in the graphic (above), in cell L89, we will enter the following formula, and then copy this throughout the entire table:

These MATCH functions will match the row position of the item on the left of the LU_Matrix and the column position of the item on the bottom of the LU_Matrix.  Then the INDEX function will return the item in the exact row and column that we match on the LU_Matrix.  After entering the formula and populating the table we will have the following visual:

With the headings and shape of the chart in place, let’s take a break.  Next week, we’ll look at making this a little more colourful.

That’s it for this week, come back next week for more Charts and Dashboards tips.