Risk Bubble Charts – Part 1

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 here simply 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) is not 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.  It is often used to complement financial modelling.

We’ll build up the chart in stages. 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 the LU_Matrix (i.e.  the “Look Up Matrix”), using Insert -> Table (CTRL + T) viz.

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

The next input we need is the risk category table which determines the likelihood and consequence of a risk.  Therefore, we will have the following table:

We name this table Risk_Category.  

In this table, we can enter the Risk Category, Consequence and Likelihood here.  This input will be used to make the Risk Bubble chart but we going to refine this data for it to be usable.

With all 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 employ the COUNTA, the SEQUENCE and the INDEX functions.  What the COUNTA do is, as you guessed it, “count” every cell that is not blank.  Therefore, we will count the number of the Likelihood categories:

=COUNTA(LU_Likelihood)

The output for this function will be five [5] as we have five likelihood categories in our example.  Next, we need to generate a sequence of numbers which we will use the SEQUENCE function here.  Then, we will wrap up this formula inside the SEQUENCE functions:

=SEQUENCE(COUNTA(LU_Likelihood))

What this will do is it will generate an array from one [1] to five [5]:

However, we want this array to generate in reverse order which is five to one.  Hence, assuming you have dynamic arrays available in your version of Excel, we alter our formula as follows:

=SEQUENCE(COUNTA(LU_Likelihood),,COUNTA(LU_Likelihood),-1)

The third argument of the SEQUENCE function will specify the location we will start at and the fourth argument will specify the amount of increment for each subsequent value.  This will give us the following (as before):

Alternative formulae may generate the same desired result.

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

=INDEX(LU_Likelihood,SEQUENCE(COUNTA(LU_Likelihood),,COUNTA(LU_Likelihood),-1))

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.  After entering this formula into a cell in Excel this will have the following visual:

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

=LU_Consequences

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

We applied the format here for a better looking (autofitting both the column widths and the row heights):

Assuming the grid is positioned as in the graphic (above), in cell L75, we will enter the following formula and populate this formula to the entire table:

=INDEX(LU_Matrix[[Minimal]:[Catastrophic]], MATCH($K75,LU_Likelihood,0), MATCH(L$80,LU_Consequences, 0))

These MATCH functions will try to 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 formulate we will have the following visual:

To colour the inner region of the chart we will use conditional formatting.  First, we select all the inner regions of the chart and select Conditional Formatting -> Highlight Cells Rules -> ‘Equal To…’:

After selecting ‘Equal To…’ the following box will appear:

In the first box, we will put Low and in the drop-down box we will select ‘Custom Format…’:

For the Custom Format, we choose the colour green for the font and the cell colour

We repeat this process for ‘Medium’, ‘High’ and ‘Extreme’.  For ‘Medium’, we use the colour yellow, ‘High’, we’ll use the colour orange and for ‘Extreme’, we will use the colour red.  After applying the conditional formatting, we will have the following visual:

We then move this table and then resize this table to 100 pixels for the height and the width, we will have the following visual:

We then add two [2] axis arrows here for visual effect:

And that’s it, we have a colourful risk table (i.e.  the more the likelihood increases and the consequence becomes risky, so will the colour become “more dangerous”). 

Next time, we will create the bubbles to put on this chart and you’ll see the finished product in action.