Can this 5-attribute 2D risk map be built in excel?

Question

So there is this risk map (or heat map otherwise) below (*):

What is interesting is that in the a 2D image there the following attributes are mapped:

Attribute -------- Drawing component
1. Likelihood -------- y-axis value
2. Impact -------- x-axis value
3. Onset speed -------- dot size
4. Vulnerability -------- dot color
5. classification - underlying grid square color

My questions are
1. whether this map can be implemented in Excel; where the user fills the table shown in the right part of the above image and excel plots the corresponding map shown.
2. if it cannot be done in Excel,what other options are there?
3. In which program do you think the map in the above diagram was made?

---

(*) Ref: Deloitte & Touche LLP,. Risk Assessment In Practice. Committee of Sponsoring Organizations of the Treadway Commission (COSO), 2012. Web. 26 June 2015.

Answer 1

I believe the color and size are both the Speed of Onset. Vulnerability corresponds to the color of the area under the dot (Green, Yellow, Red). Higher values of V trend towards red squares.

---

Partial Answer:

This is as far as I could get before I had to leave for the day:

It's a bubble chart. I typed up the data in the sample for points 1 - 12. Next, I created a different column for each Vulnerability. Those columns are all formulas that check if V is within a certain range and, if it is, the formula returns the I value. If it is not, the formula returns an error. There are five series in the chart. Each series is set to use one of these formulaic columns for its X-axis data. If it's an error, then nothing will be graphed. This allows you to have a different color for each Vulnerability group.

For the ID numbers, I added another data series with X & Y values equal to I & L and the bubble size = ID. I added data labels to this series and set them to display the bubble size in the center. Then, I formatted the series to have no edge and no fill. The net effect is the ID number in the correct location for every item.

All I'm missing now are the background squares. Could this be just drawn in? That's not very neat but you can't mix bar charts and bubble charts. You could set the chart to be transparent and fill the cells behind it. That works so long as the chart doesn't get moved or copied anywhere else. You could also make an image and set that to the background of the chart. That would go with it. I tried it and results were promising. (I also setup both horizontal and vertical gridlines as 6pts white on the chart to overlay my solid color background.)

(You would obviously have to play with it to get this to work.)

---

EDIT FOR CLARITY

Here are the basic steps I followed with more clear explanations.

1. Transcribe the data from the picture into Excel
2. Add the columns for Very Low, Low, etc.
3. Add formulas for those added columns of the general format =IF(AND([V]>=0.8,[V]<1.6),[I],NA()) where [V] is the data in the Vulnerability column, [I] is the data in the Impact column, and 0.8 & 1.6 are just numbers I made up as breaking points. The picture didn't show at what points each color was assigned and I couldn't decipher it as there appears to be a contradiction between points 11 and 8. (I later noticed that the color is actually related to Speed so the formula should actually be =IF([V]=1,[I],NA()) for "Very Low" and so on for the rest. That simplifies it.)
4. Insert a blank bubble chart
5. Select Data for that chart and add each series one at a time. The names are "Very Low", "Low", etc. The X values are the corresponding columns from the formulas we added. The "Very Low" series gets the "Very Low" X values, etc. The #N/A! error will prevent the values not in the series from being graphed. The Y values are [L]. The bubble size is [S].
6. Add another series with the same X and Y values but whose bubble size is [ID]. Add data labels to that series to show bubble size and then format the series to have no border and no fill, effectively just leaving the label, which will be [ID].
7. Create a background through your preferred means: Group the chart with a bar chart, fill in cells and set the chart to transparent, draw a shape and set it as the chart background, whatever.