Excel added the new series another set of stacked columns: right-click on the new series and choose Change Series Chart Type from the pop-up menu, and select XY Scatter. This obliterates much of the chart, but that’s only temporary. Starting with the finished background matrix above, copy the X-Y data for the secondary axis points, select the chart, and use Paste Special from the Paste dropdown on Excel’s Home tab, to add the data as a New Series, with Series in Columns, and with Categories in First Column and Series Names in First Row (below left). The risk matrix background is now ready to accept data. Then I deleted the horizontal gridlines (below left), and I hid the axis tick labels by setting the label position to None (below right).įinally I set the gap width of the column chart series to 0% (below). You could probably get away with a lighter shade of gray. Then I formatted both axes so the line color is 50% gray. Next I formatted both axes so the crossing axis crosses at the maximum value or category (below left). You should also investigate the diverging map colors designed by Cynthia Brewer, found at Color Brewer. I made up my own green-yellow-orange color scheme, listed below. I’ve used a set of fill colors described below the charts, with a thin white border (below left). Fix this by clicking the Switch Row/Column button on the Chart Tools > Design tab of Excel’s ribbon (below right).įormat the stacked column series appropriately. Since there are more rows than columns, Excel plots by column, which means the columns aren’t stacked how we want. The first step in construction of the risk matrix is to select the column chart data (see image in above section) and create a stacked column chart (below left). This is a common request, so I’m considering risk matrix charts as an addition to Peltier Tech Charts for Excel. I’ll show you how to construct this background and superimpose the foreground data. Each pair of points has a separate data set. The foreground is a simple scatter chart on the secondary axis with simple data and formatting.įor the paired data, a secondary axis scatter chart is also used, with arrow formatting of the connecting lines. We used to use a 3×3 grid, but 5×5 is also common. The stacked columns are colored appropriately, and the gaps between them are eliminated. The background is a stacked column chart on the primary axis using data like that shown below. The charts are not as complicated as they seem. Sometimes pairs of points will be used, to show the risk-benefit analysis before and after certain mitigation steps are taken, with an arrow connecting the points to show the direction of the mitigation.ĭata and Components of Risk Matrix Charts If the axes have the opposite preferred direction, for example, if the horizontal axes indicated cost of acquisition, then the grid could be produced with green in the top left corner and orange in the bottom right. The green and orange background reminds the viewer that the preferred options are in the top right of the chart. Benefit is usually plotted on the vertical axis, and is a measure of success, such as anticipated revenues or cost savings. Risk is usually placed on the horizontal axis, and is a measure of the cost of an acquisition or the estimated probability of success of a venture or project. When I was working in corporate R&D, we used a risk matrix to decide which projects to allocate our resources on. The actions might be a company’s decision about which market to enter or which other company to acquire. A risk matrix is a graphical way to analyze risks and benefits of a company’s potential actions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |