Risk Assessment Tool: All Versions

Quick Summary: Identify and then assess the risks the company may experience and then plan accordingly.

Abstract:

Every organization is constantly exposed to risks – some are under their control, and some are controlled by others.  Listing those risks and then assessing their likelihood fr occurrence and the severity impact they could have allows the company to prepare responses to either avoid them or develop strategies to minimize their impacts.  A simple numerical rating system can help to prioritize the identified issues.

The articles 3.020506, “The Good, The Bad, and The Ugly” and 5.060601, “What Just Happened,” describe a numeric method that can be used to assess various risks and issues that can occur that could have a negative impact on the company.  The technique's numerical nature helps eliminate any positive or negative biases that can creep into the analysis.  The tool can be used by companies at any stage, from single entrepreneur startups to large, publicly-traded corporations.  The key to its successful implementation is capturing as many potential business risks as possible.  Once captured, the prioritized list of issues should be monitored and addressed.

The article 4.040404, “Negative Thinking to Avoid Negative Results,” provides a link to a PowerPoint™ presentation of the same name.  That presentation describes the need and process to follow to develop an effective risk identification and management process.  The presentation uses a basic assessment tool that can be used as a step-by-step training tool.  The link to it (Sample Risk Assessment Tool) is included below.

There are four versions of the tool, which are Microsoft Excel™ applications.  All versions are fundamentally the same and yield the same results.  The differences are in some of the captured data and the tool automation level.  The versions are:

Risk Assessment Tool_Simple:  As its name implies, this version contains only the most basic information allowing the entry of an issue description and the associated likelihood, the impact, and its status.  A worksheet with twenty-six sample issues is included to guide the user as they enter their own risks on the Risk Template worksheet.

Risk Assessment Tool_Basic:  This version adds several fields that help describe each risk and its impacts.  The use of Excel’s basic AutoFiltering capabilities allow columns to be sorted and certain selections hidden from view.

Risk Assessment Tool_Advanced:  This version adds two bar charts that show the sum of the risk and likelihood factors.  Visual Basic routines (Macros) were included to sort the data by most columns.  A Utility Menu is available that allows access to eight other routines that can further manipulate the data.  One of the menu options allows any number of columns to be hidden from view by placing an “H” in Row 2 of the column to be hidden.  A more complete description is included at the end of this article.

Sample Risk Assessment Tool: This version is intended to be a training tool that helps a user become familiar with any other tool versions.  It consists of multiple worksheets that sequentially add column data to the identified risks. 

This article provides links to all of the versions of the tool.  The table below lists the six data fields associated with each identified risk.  The Simple version does not include the Category, Impact, or Control fields.  The values shown in each table can be changed on the Range worksheet for each version of the tool.  Except for the Likelihood and Severity entries, the user may choose to change any of the other entries to suit their needs. 

Issue Category

Risk Likelihood

Risk Severity

Application

5 Unknown

5 Fatal

Business

4 High

4 Unknown

Product

3 Medium

3 High

Technology

2 Low

2 Medium

Distribution

1 None

1 Low

Competition

 

 

Business Impact

Entity that has Control

Current Status

5  Reduced Viability

5 Competition

5 An Issue

4 Reduced Mkt Size

4 Market

4 Open

3 Delayed Revenue

3 Customer

3 Unknown

2 Reduced Margins

2 Technology

2 Resolved

1 Increased Costs

1 Company

1 Not Applicable

 

For example, an identified issue could be summarized as being related to the company’s Application, which has a High Likelihood of occurring, which would have a High level of Severity, which, in turn, could result in Reduced Business Viability.  Further, the issue is controlled by the Competition, and currently, the issue is viewed as Open.

The tool automatically provides a Weighting factor that consists of the product of the Risk Likelihood and the Impact Severity.  Sorting the resulting Weightings for all identified issues results in a rank-order listing that the company should prioritize.  Using the numerical values associated with each option, the worst-case product would be 5 times 5, or 25 associated with the Unknown Likelihood of Occurrence (5) and Severity Impact (5).

The tool presents the weighting information as a numerical result.  With grading scales varying between 1 and 5 for both the Likelihood and Severity variables, possible weighting products are 25, 20, 16, 15, 12, 10, 9, 8, 6, 5, 4, 3, and 2.  Obviously, the lower the value, the better. 

The data on the Risk Assumptions worksheet in all versions of the tool can be manipulated using the standard Excel AutoFilter capability (found under the Excel Data menu option).  To use this feature, select all columns in column title row, and then click on the Filter option under the Data menu.  With AutoFilter, a user can sort the worksheet by the column selected or chose to hide any rows with values shown in the AutoFilter dropdown menu displayed in the title row for each column.

The tool has been populated with some general, common risks as examples.  The user should edit or remove these examples and add company-specific risks as appropriate.  In one past implementation, a company identified fifty-six different risks across various business areas.  This exercise is best performed in a group setting with members from each functional unit present.  In most organizations, there is a general feeling of expressing only positive, optimistic outcomes.  It must be emphasized to the group that the specific purpose of this exercise is to identify what could happen in order to plan to either avoid the issue occurrence or begin to develop plans to respond (not react) to its occurrence.  On the contrary, this is NOT a negative exercise; instead, it is a proactive exercise to help the company remain successful.

Advanced Version Capabilities

The Advanced version includes a simple bar chart in which a series of rectangular symbols are used to depict the numerical rating of the likelihood and the severity.  The symbols are “filled in” for the likelihood values and “empty” for the severity values.  The two ratings are added together to show the overall summation which will differ from the product of the two ratings.  If, for example, the Risk was 3 and the Severity was 4, there would be three “filled in” rectangles followed by four “empty” symbols placed in a row for a total of seven rectangles.  The product for the ratings would be 12 (3 times 4).

The Advanced tool allows setting a Weighing Threshold so that any weightings above the Threshold will display their bar chart in red symbols.  To adjust the Weighting Threshold, enter the desired integer value in cell J3 (shown with a yellow background.)  Enter only an integer value; the text shown in the cell will automatically appear along with the numerical value.

The last column duplicates the symbol bar chart described above with one exception; the bars are only shown for issues whose status is listed as:

“An Issue” – meaning that the risk has occurred.

“Open” – meaning that the issue may still occur.

“Unknown” – meaning the status of the issue is unknown.

For the two remaining statuses: “Resolved” and “Not Applicable,” the bars are not shown.

The Advanced version of the tool includes nine different functions intended to simplify the manipulation of the data on the Risk Assumptions worksheet.  The first function consists of nine different instances of the same function.  They are shown in row 3 of the worksheet as small “A over Z” or “Z over A” blue symbols, similar to A/Z and Z/A.  Clicking on any one of these symbols will result in the data being sorted by that column as the primary sort criteria.  Two other second and third-order criteria are also used in each sort.  Cell E4 shows the sort criteria that is used.  For the symbols “A over Z,” the sort is made in ascending order.  For the “Z over A” symbols, the sort is made in descending order.

The other eight functions available in the Advanced version of the Tool are accessed by clicking on the Show Utility Menu box shown in cell B4.  When clicked, a menu will appear with the following options.

Button

Function

Show All Rows

This option will “unhide” all rows that have been hidden by using the AutoFilter function or have been manually hidden using standard Excel techniques.

Auto Row Height

This option will automatically adjust the row height of each row to show all text.  Note that this function is automatically engaged any time the worksheet is sorted by the sort routines described above.

Toggle AutoFilter

This option will change the state of the AutoFilter; if “on” it will be turned “off” and vice versa.  This option is a handy way of removing any AutoFilters that may have been previously set.

Re-Number Shown Rows

This option will sequentially re-number all visible rows in their current order.  Any rows that were previously hidden will have their row number cells set to empty.  This option is useful if the data is sorted, and the user desires to re-number the new listing.

Show/Hide Marked Column Row

This option, along with the next option, allows any of the columns on the Risk Assumptions worksheet to be hidden from view.  To hide a column, click on this option if row 2 is not visible.  If it is visible, enter an “H” into the column in row 2 that is to be hidden.

Hide Marked Columns

In conjunction with the option described above, clicking on this button will result in the columns marked to be hidden (by an “H” in the columns in row 2) to be hidden from view.

Show All Columns

This option unhides any columns that were hidden following the procedure described above or manually hidden by the user using standard Excel techniques.

Add a New Row

This option adds a new row to the top of the data area.  The new row is formatted with all of the same dropdown menus and formatting as the other rows.  Note that it is numbered as row 1 with all other rows numbered sequentially after it.

Close This Menu

As its name implies, this option removes the Utility Menu from view.

 

Although the issues can be sorted using the standard Excel AutoFilter method, or in the Advanced Version, using the “A to Z” option, there is a simple manual method that may be useful from time-to-time.  This method involves the manual assigning of a new number to row.  For example, if it is desired to move row 23 to appear directly below row 3, the value in row 23 could be changed to “3.1”.  Then the list could be sorted manually or using either automatic sorting techniques included with the Basic and Advanced versions accordingly.  Finally, the entire list should be re-numbered so that the “old” row 23, now number 3.1, is numbered 4 after the sort.

The tool files referenced above:

Risk Analysis Tool v1_Simple.xls

Risk Analysis Tool v1_Basic.xlsm

Risk Analysis Tool v1_Advanced.xlsm

Sample Risk Analysis Tool v1.xlsm

 

Article Number : 8.020103   

A Handy Reference Guide for Executives and Managers at All Levels.

9 Volumes 42 Chapters ~689 Articles

Browse Select Read Download

 

 

The weight of your world does not have to be on your shoulders.
The articles in this site will help to lift that weight from your shoulders.
Pick an article similar to how you pick a route on a page of an atlas.
There is no need to look at other articles, just as you ignore other pages in an atlas.
It is easy to start a business but it is hard to run. Bumps and unexpected sharp turns in the road are always present.
Others have traveled the road before you; learn from them. This site may help.