Risk Assessment Tool Advanced Version

Quick Summary: Identify and then assess the risks the company may experience and 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 numerical nature of the technique helps to 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 the capturing of as many potential business risks as possible.  Once captured, grading the likelihood of the risk occurring and its potential severity impact can provide the organization with a prioritized list of issues to monitor and address.

There are two versions of the Tool, which is a Microsoft Excel™ application.  This article links to the most complex version of the Tool.  It allows the user to quickly manipulate the rows and columns of the worksheet using “one-click” Visual Basic routines (“Macros”).  All of the “one-click” features of the Advanced version can also be performed manually, using standard Excel techniques in the Basic version.  Both versions of the Tool provide the same results which are shown on a separate Summary worksheet.

In addition to allowing the user to assess the risk likelihood and potential impact for each issue listed, the Tool also allows the user to enter addition relevant data associated with each issue.  All of the data is entered using dropdown menus.  With the exception of the issue Category, the other dropdown menu choices each include numerical values ranging from one to five, with five representing the “worst” situation associated with the risk.  Below is a list of the categories included in the Tool.

 

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

 

 As an example, an identified issue could be summarized as being related to the company’s Application, that has High Likelihood of occurring, which would have 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.  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).  Sorting the resulting Weightings for all of the identified issues result in a rank-order listing that the company should consider monitoring or addressing.

The Tool presents the Weighting information as a numerical result as well as in 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.  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.

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 Tool provides the ability to set 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 on the Risk Assumptions worksheet duplicates the symbol bar charts 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 data on the Risk Assumptions worksheet in both 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 row 5, the 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 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 then be re-numbered so that the “old” row 23, now number 3.1, is numbered as 4 after the sort.
 

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 a wide variety of 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.  To the contrary, this is NOT a negative exercise; instead, it is a pro-active exercise to help the company remain successful.

 

The Tool file referenced above is: Risk Analysis Tool v1_Advanced.xlsm

Article Number : 8.020104   

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

9 Volumes 40 Chapters ~598 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.