Plotting Impact Vs. Probability in Excelby Sue Smith
In Microsoft Excel, you can implement charting functions for common business and workplace processes such as risk management. By compiling a list of probability and impact values for particular activities, you can calculate the risks involved and plot them on a chart. Viewing such data on a chart can help to prioritize management approaches to potential risks within an organization. Generating a chart for risk management based on such values can be achieved with a few clicks in Excel.
Open Excel and create a spreadsheet. If you do not have a spreadsheet you are working with, create one now. In your worksheet, add column headings in row 1 for "Activity," "Probability," "Impact" and "Risk." You will use the "Activity" column to list the names of activities for which you are calculating risk, the "Probability" and "Impact" columns for a value representing these metrics for each activity, and the "Risk" column for a calculation based on the probability and impact values.
List your activities. List the name of each activity on a new row, in column A. In column B, list the probability for each activity -- for example a value between 1 and 10, or between 1 and 100, with the higher number meaning higher probability. Do the same for impact in column C. In column D, calculate the risk according to your own risk management approach; for instance, by multiplying the impact by the probability for each activity. You can do this by copying the following formula into row 2 column D: =C2*B2 You can then copy this from row 2 column D and paste it into the remaining rows in column D.
Generate a chart. Select the data values from row 2 column B to the last row in which you have an activity listed, column D. In the Insert tab, select "Recommended Charts" and choose the "All Charts" tab. Scroll down until you see the bubble chart option, which takes three column values. The bubble chart should automatically plot your impact (column B) values on the X axis, the probability (column C) values on the Y axis and the risk (column D) values to dictate the bubble size. You can customize display features, for example to include the activity names you listed, by clicking the "Chart Elements" button and selecting "Data Labels."
Check your chart. You should see your values plotted on the chart, with the bigger bubbles plotted to the top right part of the chart, the smaller bubbles to the bottom left. The bigger bubbles in the top right area represent greater risks which would typically be regarded as requiring more urgent attention from a management perspective.
- When you alter the values in your spreadsheet, the chart will automatically update.
- There are many possible ways of categorizing and quantifying risk using impact and probability, as well as additional factors such as urgency.