Risk Assessment - Excel help

[Billt007]

Not sure this is as much of an excel question as much as it is a statistical questions but thought I would try here with all the big brains. I need to calculate the risk rating for an audit. Here are the four factors:

1. Risk level 1-3 with one being highest risk
2. Volume of items.. this could be 1000 to 100000 thousand items
3. Fail percentage.. This is how bad we failed in the past audit of this

What I am trying to do is to give a rating to an item that factors in the three above criteria as an example

Category	Risk Level	Volume	Fail Percentage	Percentage of total Risk or Risk Rating
Sky diving	High 1	1000	25%
Driving	Medium 2	100000	87%
Airplane passenger	Low 3	25	10%

I need to find a way to factor in those three items to get at a risk rating that shows others while this is a high risk when have less examples of this so therefore for the risk rating is X
Something like Risk = 1*1000(volume) *25% (fail percentage) Looking to create a formula that would help me calculate this
thanks in advance for any help on this

 

[Candyman8019]

There are likely a ton of ways to slice and dice this. I would think you need to define metrics for what constitutes risk levels 1-3 since those are ambiguous terms. For example, is the risk monetary loss? If so, what are the potential or average losses for each of high/medium/low?

Second, what is the importance of each factor?

Once you have that info you should be able to do a weighted average of sorts to give you an overall risk rating.

 

[Billt007]

The three levels of initial risk have criteria that justifies the 1 High 2 medium and 3 Low.. Volume is just that .. how many instances we have in a give year. The percentage fail is how well or bad we did last time we reviewed compliance in that area
Guess what I am trying to get at is say something is a high risk but low volume and low fail rate then it would be ranked lower that something that is say medium risk, high volume and high fail rate as I would overall have more examples of things that could go wrong..

 

[Candyman8019]

Okay, I'm not sure I'm getting exactly what you're looking for, but this may be food for thought. Assign arbitrary values for risk level...in this case I used High 10, Medium 5, Low 3. Calculated the product of the three factors in each activity and then ranked them from high to low in column F.

Book2 (version 1).xlsb
	A	B	C	D	E	F
1	risk	volume	failue			Rank
2	10	1000	25%		2500	2.00
3	5	100000	87%		435000	1.00
4	3	25	10%		7.5	3.00
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=+A2*B2*C2
F2:F4	F2	=RANK.EQ(E2,E$2:E$4,0)