How to create risk analysis matrix formula xls template?

Asked By 0 points N/A Posted on -
qa-featured

Good day to everyone. I would like to ask how to create risk analysis matrix formula xls template effectively. What are the steps needed to make this possible? Please include its overview and key features. I would also like to know about its pros and cons. I'm looking forward for your answers. 

SHARE
Best Answer by Floyd Pitcock
Go To Solution
Best Answer
Best Answer
Answered By 0 points N/A #130527

How to create risk analysis matrix formula xls template?

qa-featured

 

Step by Step Instructions for Creating the Risk Assessment Template

1. Enter the Data in the Excel Sheet

  • Label the first row in Columns A, B, and C as Project Name or Activity, Probability and Consequence and fill in the name each project or activity and your estimated probability and impact values in the subsequent rows.

2. Select the Chart Style

  • Choose from the ribbon the Insert Tab
  • Select Scatter Chart
  • Choose Scatter Chart with only Markers (a blank chart will appear)

3. Sync the Data to the Chart

  • From the Chart Tools on the ribbon, select Design
  • Choose Select Data
  • Select Add to enter the data for the first project or activity
  • Change the Series Name to cell A1
  • Set Series X values to cell B2 and Series Y values to cell C2
  • (To enter cell values click on the chart image on the right and then click on the cell with the data.)
  • Your skeleton template will now look like this, and you can proceed with formatting the legend, data points, axes, and plot areas.

4. Delete the Legend (the legend is not necessary because each data point will be labeled)

  • Right click on the legend
  • Choose delete

5. Label the Data Point

  • Right click on the data point
  • Choose Add Data Label
  • Check the Series Name and uncheck the Y axis and then click Reset Label Text

6. Set Each Axis Range from 0 to 100

  • Right click each axis
  • Choose Format Axis
  • Set Min to 0
  • Set Max to 100

7. Key in the Title and Axis Names

  • Right click over the text
  • Select Text Edit and type
  • Title – Risk Assessment
  • X axis – Remote Probability Certain
  • Y axis – Insignificant Consequence Critical

8. Format the Plot Area

  • Right Click anywhere in the Plot Area
  • Choose Format Plot Area (The selection box to the left will appear.)
  • Click on the Gradient circle
  • On the first stop on the Gradient Bar switch the color to Red
  • Change the Direction to Linear Diagonal

You now have a working risk assessment template that you can modify either by changing the existing data or by adding new projects/activities for evaluation. If you would like to download this template to use or adapt for your own risk analysis, follow this link to its location in the Bright Hub's media gallery where you can also download other project management templates and forms, including this risk assessment form.

 

 

About Floyd Pitcock
Questions
1
Answers
8
Best Answers
2
Answered By 5 points N/A #198362

How to create risk analysis matrix formula xls template?

qa-featured

Risk assessment analysis can be done on Microsoft Excel. They generally look at the project charters, scope statements, work breakdown analysis and the project schedule. However, Risk assessment analysis can implemented on the Excel sheet quite efficiently. There are various ways in which this can be implemented and you need to define the factors that will be defining the risk status on various accounts. Two factors are important related to the consequence. They are magnitude and probability. You need to look at the magnitude as well as the probability. Both of them are important. Let us have a look how we can define them:

  1. Insignificant
  2. Minor
  3. Moderate
  4. Major 
  5. Critical

You can fix a particular percentage for each of them. The stages of probability is being defined as:

  1. Remote: probability of below 10%
  2. Highly Unlikely: probability in between 11% and 35%
  3. Possible: probability in between 36% and 50%
  4. Probable: probability in between 51% and 60%
  5. Highly likely: probability in between 61% and 90%
  6. Certain: probability above 90%

You need to plot them all on the scatter graph in excel sheet. There are many spreadsheet software available for this purpose. However, the excel sheet provides easiest features for this purpose.

Add activity, probability and the consequences in cells A, B and C. Fill some data. Now you need to select the scatter chart without any markup. You will see an empty chart. Now click "select data". Change the series A, B and C to activity, probability and consequences. Now right click on chart and then click on the cells that you need to select. Here, they are three of the above.

You need to delete the legends as they are not required. Change the label of the data point. Set the range of the axis in between 0 and 100. Key in the Title and axis name. Title will be risk assessment, X axis will be probability and the Y axis will be consequences.

Now format the plot area. Click on the gradient circle. From the gradient bar select the color to Red.

Now change the direction to linear diagonal.

You are ready with your risk assessment template. 

 

 

 

 

 

 

About Waters Magner
Questions
0
Answers
199
Best Answers
32

How to install watermark frame program software?

Previous

How to download orb caster web flash live feed online?

Next

Related Questions