Risk Assessment Calculator

[craigwarren85]

So I'm completely stuck - I've made a worksheet with a few drop down lists, and I need for the end user to be able to select any combination of list, and for the sheet to output an overall risk level, based on those selections (be referring to the included matrix).

To add an additional level of complexity, I'd also like to add in a third variable, whereby whether or not it's an internal risk, or external risk would effectively act as a multiplier (Internal = no multiplier, external = X2)

I'm not entirely sure where to even start, or what tool/commands would be best to achieve this.

Any help would be appreciated!!

Transition Risk Assessment.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
6			Transition Risk Assessment
7
8
9
10	#	Risk	Description	Probability	Severity	Actions to minimise Risk	Risk Level		Probability	Certain	Low	Medium	High	Very High	Unacceptable	Unacceptable	Unacceptable
11	1	Internal/External Risk								V. Likely	Negligible	Low	Medium	High	Very High	Unacceptable	Unacceptable
12	1.1	Schedule Overrun		Remote	Insignificant	Divide Transition into different Tracks and phases to bring focus to execution. Any schedule slippage should be isolated at track/phase level and impacted track/phase should be replanned without impacting othert tracks/phases much.				Likely	Negligible	Negligible	Low	Medium	High	Very High	Unacceptable
13										Moderate	Negligible	Negligible	Negligible	Low	Medium	High	Very High
14										Unlikely	Negligible	Negligible	Negligible	Negligible	Low	Medium	High
15										V. Unlikely	Negligible	Negligible	Negligible	Negligible	Negligible	Low	Medium
16										Remote	Negligible	Negligible	Negligible	Negligible	Negligible	Negligible	Low
17											Insignificant	V. Minor	Minor	Moderate	Significant	Major	Catastrophic
18											Impact
19
20	2	Internal/External Risk
21	2.1	Organisational Change Management		Remote	Insignificant	Organisation change should not be underestimated specially in case of in-house to vendor transition scenario. Engage a professional team to manage this in case required capability not available interrnally
22
23
24
25
26
27
28
29	3	Internal/External Risk
30	3.1	Timely Access to Systems		Remote	Insignificant	Access to the IT environment for the incoming team is vert critical. Plan adequate time for this activity as arganisation procedure may require certain minimum time before access is granted. The risk is more porminent if the incumbent service provider is an external vendor
31
32
33
34
35
36
37
38	4	Internal/External Risk
39	4.1	Process and System disparity		Remote	Insignificant	Dedicated process Transition track is critical for uniformed processes to be ready in time for the new transitioned environment.
40
41
42
43
44
45
46
47	5	Internal/External Risk
48	5.1	Lack of Documentation		Remote	Insignificant	Use tools to assess and create documentation during Transition. Tools like CAST can help create good amount nof documentation from scratch.
49
50
51
52
53
54
55
56	6	Internal/External Risk
57	6.1			Remote	Insignificant
58
59
60
61
62
63
64
65	7	Internal/External Risk
66	7.1			Remote	Insignificant
67
68
69
70
71
72
73
74	8	Internal/External Risk
75	8.1			Remote	Insignificant
76
77
78
79
80
81
82
83	9	Internal/External Risk
84	9.1			Remote	Insignificant
85
86
87
88
89
90
91
92	10	Internal/External Risk
93	10.1			Remote	Insignificant
94
95
96
97
98
99
100
101	11	Internal/External Risk
102	11.1			Remote	Insignificant
103
104
105
106
107
108
109
110	12	Internal/External Risk
111	12.1			Remote	Insignificant
112
113
114
115
116
117
118
119	13	Internal/External Risk
120	13.1			Remote	Insignificant
121
122
123
124
125
126
127
128	14	Internal/External Risk
129	14.1			Remote	Insignificant
130
131
132
133
134
135
136
137	15	Internal/External Risk
138	15.1			Remote	Insignificant
139
140
141
142
143
144
145
Risk Assessment

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G12:G19	Expression	=$G$12	text	NO
D12:D19,D21:D28,D30:D37,D39:D46,D48:D55,D57:D64,D66:D73,D75:D82,D84:D91,D93:D100,D102:D109,D111:D118,D120:D127,D129:D136,D138:D145	Cell Value	ending with ""	text	NO
D138:D145	Cell Value	ending with ""	text	NO
D129:D136	Cell Value	ending with ""	text	NO
D120:D127	Cell Value	ending with ""	text	NO
D111:D118	Cell Value	ending with ""	text	NO
D102:D109	Cell Value	ending with ""	text	NO
D93:D100	Cell Value	ending with ""	text	NO
D84:D91	Cell Value	ending with ""	text	NO
D75:D82	Cell Value	ending with ""	text	NO
D66:D73	Cell Value	ending with ""	text	NO
D57:D64,D48:D55,D39:D46,D30:D37,D21:D28,D12:D19	Cell Value	ending with ""	text	NO
D12:D19	Cell Value	ending with ""	text	NO
D12:D19,D21:D28,D30:D37,D39:D46,D48:D55,D57:D64,D66:D73,D75:D82,D84:D91,D93:D100,D102:D109,D111:D118,D120:D127,D129:D136,D138:D145	Cell Value	ending with ""	text	NO
D12:D19,D21:D28,D30:D37,D39:D46,D48:D55,D57:D64,D66:D73,D75:D82,D84:D91,D93:D100,D102:D109,D111:D118,D120:D127,D129:D136,D138:D145	Cell Value	ending with ""	text	NO
D12:D19,D21:D28,D30:D37,D39:D46,D48:D55,D57:D64,D66:D73,D75:D82,D84:D91,D93:D100,D102:D109,D111:D118,D120:D127,D129:D136,D138:D145	Cell Value	ending with ""	text	NO
D12:D19,D21:D28,D30:D37,D39:D46,D48:D55,D57:D64,D66:D73,D75:D82,D84:D91,D93:D100,D102:D109,D111:D118,D120:D127,D129:D136,D138:D145	Cell Value	ending with ""	text	NO
E57:E64,E48:E55,E39:E46,E30:E37,E21:E28,E12:E19,E66:E73,E75:E82,E84:E91,E93:E100,E102:E109,E111:E118,E120:E127,E129:E136,E138:E145	Cell Value	ending with ""	text	NO
B11,B128,B119,B110,B101,B92,B83,B74,B65,B56,B47,B38,B29,B20,B137	Cell Value	ending with ""	text	NO
B11,B20,B29,B38,B47,B56,B65,B74,B83,B92,B101,B110,B119,B128,B137	Cell Value	ending with ""	text	NO
B11,B20,B29,B38,B47,B56,B65,B74,B83,B92,B101,B110,B119,B128,B137	Cell Value	ending with ""	text	NO
E12:E19,E21:E28,E30:E37,E39:E46,E48:E55,E57:E64,E66:E73,E75:E82,E84:E91,E93:E100,E102:E109,E111:E118,E120:E127,E129:E136,E138:E145	Cell Value	ending with ""	text	NO
E12:E19,E21:E28,E30:E37,E39:E46,E48:E55,E57:E64,E66:E73,E75:E82,E84:E91,E93:E100,E102:E109,E111:E118,E120:E127,E129:E136,E138:E145	Cell Value	ending with ""	text	NO
E12:E19,E21:E28,E30:E37,E39:E46,E48:E55,E57:E64,E66:E73,E75:E82,E84:E91,E93:E100,E102:E109,E111:E118,E120:E127,E129:E136,E138:E145	Cell Value	ending with ""	text	NO
E12:E19,E21:E28,E30:E37,E39:E46,E48:E55,E57:E64,E66:E73,E75:E82,E84:E91,E93:E100,E102:E109,E111:E118,E120:E127,E129:E136,E138:E145	Cell Value	ending with ""	text	NO

 

[JamesCanale]

I did it the old 2-dimensional way in I12. In H12, I added the int/ext and a numeric system. They colors don't match up perfectly. If that is important it will get a bit more complicated.

MrExcelPlayground15.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
10	Risk	Description	Probability	Severity	Actions to minimise Risk			Risk Level		Probability	Certain	Low	Medium	High	Very High	Unacceptable	Unacceptable	Unacceptable		1	Negligible
11	Internal/External Risk										V. Likely	Negligible	Low	Medium	High	Very High	Unacceptable	Unacceptable		8	Low
12	Schedule Overrun		Remote	Insignificant	Divide Transition into different Tracks and phases to bring focus to execution. Any schedule slippage should be isolated at track/phase level and impacted track/phase should be replanned without impacting othert tracks/phases much.	Internal	Low	Negligible			Likely	Negligible	Negligible	Low	Medium	High	Very High	Unacceptable		18	Medium
13											Moderate	Negligible	Negligible	Negligible	Low	Medium	High	Very High		24	High
14											Unlikely	Negligible	Negligible	Negligible	Negligible	Low	Medium	High		30	Very High
15											V. Unlikely	Negligible	Negligible	Negligible	Negligible	Negligible	Low	Medium		35	Unacceptable
16											Remote	Negligible	Negligible	Negligible	Negligible	Negligible	Negligible	Low
17												Insignificant	V. Minor	Minor	Moderate	Significant	Major	Catastrophic
18												Impact
19
Sheet16

Cell Formulas
Range	Formula
H12	H12	=XLOOKUP(MATCH(D21,$L$10:$L$16,0)*MATCH(E21,$M$17:$S$17,0)*IF(G12="Internal",1,2),U10:U15,V10:V15,,-1)
I12	I12	=INDEX($M$10:$S$16,MATCH(D12,$L$10:$L$16,0),MATCH(E12,$M$17:$S$17,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H12:I19	Expression	=H12="Medium"	text	NO
H12:I19	Expression	=H12="Low"	text	NO
H12:I19	Expression	=H12="Negligible"	text	NO