Risk Rating Automation (Advanced)

Problem: As an internal or external auditor, have you ever had to deal with doing the combined risk assessment (CRA) on MS Excel? Have you faced the issue of having to manually enter the combined risk assessment results (Control risk + Inherent risk) and wished that some form of automation would save you this hassle?

Solution: The following is a method to automate the calculation of the combined risk assessment score based on the control and inherent risks inputs:

Assuming the following example:

Risk Assessment Combination (The following table is for illustration purposes only, no need to copy to the sheet)

	Lo	Mod	Hi
Lo	Low	Low	Mod
Mod	Low	Mod	Hi
Hi	Mod	Hi	Hi

 We want to determine the CRA for the following risk assessments (here the exercise starts):

	A	B	C	D
1	Risk ID	Inherent Risk	Control Risk	Combined Risk
2	Risk #1	Low	Mod	?
3	Risk #2	Hi	Mod	?
4	Risk #3	Mod	Low	?
5	Risk #4	Mod	Hi	?

So how to determine the CRA under column D? It might look simple to enter the results manually, but if you have like, 15 risks or even more, the task would become tedious.

So, what we need is to automate the first table (Risk Assessment Combination) into column D. How?

To automate the CRA result, follow the following steps:

1.Define a Name for each rating type (Low, Mod, and Hi). On Excel, Names are defined to facilitate referencing inside formulas, and also to be linked with value (as we will shortly learn in our case) to be able to do calculations easily. In our case, we will assign the values 1,2 and 3 to Low, Mod and Hi, respectively. For that purpose, please enter the 3 values (1 to 3) each in a separate cell on the sheet (preferable away from the tables to ensure that they are not accidentally overwritten).

To define a Name, go to the Formulas tab --> Defined Names section --> Define Name

You should see the following dialogue box:

Under Name, enter the first risk rating, say, Low.

Under Refers to, enter a reference to the cell where the value of 1 has been entered in the beginning of the exercise. Then press OK.

Do the same for Mod and Hi ratings.

2. The second step is to instruct MS Excel with the following logic: If the inherent risk value X the control risk value = 1 then the result is Lo, if 2 then Mod and if 3 then Hi. However, we have a problem here; the combined risk assessment refers to ratings (Lo-Hi) not values. So how to solve this issue?

We should use the INDIRECT function, which returns the reference specified within the cell it is reading from.

Example: Cell A1 contains the value "Lo". As we learned earlier, we have defined a Name as "Lo" that refers to the value of 1. What happens if on cell A2 we enter the following formula: 

=INDIRECT(A1)

The formula reads the content in cell A1 and identifies the value "Lo", which refers to a defined Name within the sheet, "Lo", which refers to the value of 1. So the formula should return 1.

Now that the sheet is able to interpret the risk ratings as values, we can embed it within an IF function to identify the 3 different possibilities of combined risk assessment.

3. The formula should be as follows assuming the selection is on cell D2:

=IF(INDIRECT(B2)*INDIRECT(C2)<3,"Lo",IF(INDIRECT(B2)*INDIRECT(C2)<6,"Mod","Hi"))

However, in case any of the inherent or control risks was not entered for any reason, or is N/A for example, and to maintain the presentation of the sheet in good shape, you may add an IFERROR function accompanied by "" indicating blank cells, as follows:

=IFERROR(IF(INDIRECT(B2)*INDIRECT(C2)<3,"Lo",IF(INDIRECT(B2)*INDIRECT(C2)<6,"Mod","Hi")),"")

And, voila! You have automated the CRA part once and for all!

If you liked the exercise, please don't hesitate to share!