How to Create a Risk Assessment Template for Excel

How to Create a Risk Assessment Template for Excel
Page content

Risk Assessment in Project Planning

In project planning, project mangers usually don’t tackle their risk assessments with the same enthusiasm as they do in developing the project charter, scope statement, work breakdown structure and project schedule. Why is this? It is just part of human nature to avoid uncertainties and place them on the back burner. This article will help you use a risk assessment template for Excel to identify, highlight, and assess the potential risks in your project. The template can help make those uncertainties more tangible and thereby eliminate the “real” risk in not properly addressing them from the start of the project. [caption id=“attachment_132762” align=“aligncenter” width=“640”]Use this free risk assessment template for Excel to track project risks! Don’t slip up… be sure to track risk![/caption] Just looking for the downloads?

Your download should automatically begin by clicking on the links. If it does not, you may have to adjust the security settings on your browser.

The Risk Assessment Template

[caption id="" align=“alignnone” width=“600”]Use this risk analysis template for Excel to help track project risks. The final product - Risk Assessment Template[/caption]

This risk assessment template created in the steps below uses a scatter plot and gradient shading to highlight the comparative risks associated with undertaking different projects or activities. Before constructing the risk assessment template, you will first need to decide upon the nomenclature and scale to express the probability and magnitude of the possible loss that could be encountered if the risk materializes. This template uses a 1 to 100 scale, breaking down the magnitude into 5 discernible levels and the probability into six possible ranges as follows: Magnitude of the Consequence

  • Insignificant - Easily handled within the normal course of operations with no additional costs. (Impact level <10.)
  • Minor - Some disruption within the normal functions. Manageable risk with minimum estimated cost. (Impact level between 11 and 25.)
  • Moderate - Immediate time/resource reallocation will be necessary with a moderate estimated cost. (Impact level between 26 and 50.)
  • Major - Operations are severely disrupted and significant risk of failure to part of the business is possible. (Impact level between 51 and 75.)
  • Critical - Significant going concerns exists with the business and the risk is classified as critical. (Impact level >75.)

Probability of the Consequence

  • Remote - Probability of less than 10%.
  • Highly Unlikely - Probability between 10% and 35%.
  • Possible - Probability between 36% to 50%.
  • Probable - Probability between 51% to 60%.
  • Highly Likely - Probability 61% to 90%.
  • Certain - Probability above 90%.

Step by Step Instructions for Creating the Risk Assessment Template for Excel

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 on 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 in the chart image on the right and then click on the cell with the data.)

[caption id="" align=“alignnone” width=“600”]Use this risk assessment template for Excel to track risks to your project! Risk Assessment Template - Outline[/caption]

Your skeleton template will now look like this, and you can proceed with formatting the legend, data points, axes, and plot area. 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

Downloads

You now have a working risk analysis 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, click here to download. Another version of the risk assessment form can be downloaded here. Screenshots by Author Image by Steve Buissinne from Pixabay

This post is part of the series: Risk Register: Templates, Examples, and Tips

Preparing an initial risk register and keeping it updated throughout the entire life cycle of a project is crucial for project success. In this series, you’ll find templates and examples to help you build your own risk register - and helpful tips to make sure you get the most out of the document.

  1. Creating a Risk Register: A Free Excel Template
  2. Using Excel to Make a Risk Assessment Template
  3. Creating a Risk Management Checklist
  4. Free PMP Test Questions - Risk Management
  5. Reacting to Negative Risks in Risk Management Strategies