Calculating residual risk

[kbrocks]

Hi all, wonder if you can help me with a problem. It may be a little maths an a bit excel. I work in cyber security and I am attempting to calculate residual risk. This is a term used to describe a risk once all the controls have been taken into account. It's quite simple if you only have 2 or less controls but when you have 10 or more it gets more complex. Example below...

-Threat of Phishing. Inherent risk (risk before controls are taken into account) score = 10 (the highest)
- Control effectiveness assessment Cntrl 1 = 4, Cntrl 2 = 2, Cntrl 3 = 8, Cntrl 4 = 6, Cntrl 1 = 3 Total control score = 22
Residual Risk= Inherent risk - control effectiveness but as the number is greater that 10 it obviously doesn't make sense. Even If I use average this doesn't seem to work either. I get the feeling I need a formula or another factor somewhere.

I need an aggregation of the controls relevant to the inherent risk. Each control plays a factor some more than others.

If my logic is flawed which is quite possible please let me know. Phishing residual risk score in a company given controls should be around 20% and the controls should be more than 80% given that one control has a score of 8.

Any help any of you could give would be greatly appreciated....

 

[GraH]

If I understand correctly, you may try a construct like this

Book1
	A	B	C	D	E	F	G	H	I	J	K
1										Weight	sum must be 10
2									ctrl 1	1,5
3	Inherent Risk	ctrl 1	ctrl 2	ctrl 3	ctrl 4	ctrl 5	Residual Risk		ctrl 2	1
4	10	4	2	8	6	3	4,8		ctrl 3	4
5									ctrl 4	0,5
6									ctrl 5	3
7										10
Sheet1

Cell Formulas
Range	Formula
G4	G4	=A4-(SUMPRODUCT(B4:F4,TRANSPOSE(J2:J6))/10)
J7	J7	=SUM(J2:J6)
Press CTRL+SHIFT+ENTER to enter array formulas.