How to Make a Risk Assessment Matrix in Excel

Summary

This video script introduces an Excel-based risk assessment template that simplifies the process of risk management. It covers key elements like risk description, probability, impact, and ownership. The template automatically calculates risk ratings using probability multiplied by impact. It also includes a risk matrix for visual representation and a section for residual risk after implementing controls. The script guides viewers on how to set up the template, including using data validation, INDEX-MATCH functions, and conditional formatting for a comprehensive risk management tool.

Takeaways

• 📊 The video introduces a comprehensive risk assessment template that is highly effective for daily use in various business contexts.
• 🔑 Key components of the risk assessment include risk descriptions, causes, consequences, risk owners, probability, impact, and risk rating calculations.
• 📐 The template automatically calculates risk ratings by multiplying the probability by the impact, streamlining the assessment process.
• 📈 A risk matrix is central to the template, helping to categorize risks into sustainable, moderate, severe, and critical levels based on probability and impact.
• 🎛️ Controls and control owners are identified to mitigate risks, with residual risk assessment post-implementation.
• 📋 The template allows for dynamic updates, with changes in the risk matrix automatically reflected throughout the assessment.
• 🖋️ Text formatting and color-coding are used extensively to enhance readability and visual organization of the risk assessment.
• 🔄 The use of data validation and drop-down lists for probability and impact selections ensures consistency and ease of use.
• 📊 Conditional formatting is applied to risk ratings to visually differentiate between risk levels at a glance.
• 📈 The template includes a dynamic count of risks using the COUNTIFS function, providing a quick overview of risk distribution.
• 🔗 All elements of the template are interconnected, allowing for comprehensive risk management and easy updates.

Q & A

What is the primary purpose of the risk assessment template discussed in the script?

The primary purpose of the risk assessment template is to measure and manage risks in various fields such as projects, businesses, or any entity that requires risk evaluation.

What are the key components of a risk assessment according to the script?

Key components of a risk assessment include risk descriptions, causes, consequences, risk owners, probability, impact, risk rating, controls, control owners, and residual risk.

How is the risk rating calculated in the template?

The risk rating is calculated by multiplying the probability by the impact of a risk.

What is a risk matrix and how is it used in the template?

A risk matrix is a tool used to prioritize risks based on their probability and impact. It is used in the template to automatically update the risk rating based on the selected probability and impact.

How does the template handle changes to the probability and impact levels?

The template allows users to change the probability and impact levels through data validation lists, and these changes are automatically reflected in the risk rating and the risk matrix.

What is meant by 'residual risk' in the context of the script?

Residual risk refers to the leftover risk after the implementation of controls or mitigants.

How does the template visually represent different risk levels?

The template uses conditional formatting to color-code risk levels, such as sustainable (green), moderate (yellow), critical (red), and severe (dark orange).

What is the significance of the inherent risk section in the template?

The inherent risk section represents the initial risk before any controls are put in place, providing a baseline for risk assessment.

How does the template count and display the number of risks at each level?

The template uses the COUNTIFS function to count and display the number of risks at each level based on the risk assessment data.

What are the steps to create the risk matrix in the template?

The steps to create the risk matrix include merging cells, formatting, adding borders, and aligning text. The matrix is then populated with probability and impact levels, which are used to categorize risks as sustainable, moderate, severe, or critical.

How can users customize the risk assessment template to fit their specific needs?

Users can customize the risk assessment template by adjusting the probability and impact levels, changing the risk matrix criteria, and adding or removing rows and columns to suit their specific risk assessment requirements.

Outlines

00:00

📊 Introduction to Risk Assessment Template

The speaker introduces a comprehensive risk assessment template that is highly useful for those working in risk management, project management, or any business that requires risk measurement. The template is praised for its daily usability and its ability to cover various aspects of risk assessment. It includes sections for risk descriptions, causes, consequences, risk owners, and the crucial elements of probability and impact. The template automatically calculates risk ratings by multiplying probability by impact, using a risk matrix that can be customized with different probability and impact levels. The speaker also mentions the sections for controls and control owners, which are essential for mitigating risks, and residual risk, which represents the leftover risk after controls are applied.

05:01

🔍 Setting Up the Risk Matrix and Assessment

The speaker details the process of setting up the risk matrix and the risk assessment template. The risk matrix is created with scales for probability and impact, which are used to categorize risks as sustainable, moderate, severe, or critical. The speaker explains how to use data validation to allow users to select from predefined lists of probability and impact levels. The use of the INDEX and MATCH functions is highlighted to automatically generate risk ratings based on the selected probability and impact levels. The speaker also demonstrates how to use conditional formatting to color-code risk ratings according to their severity.

10:02

🎨 Customizing and Automating Risk Assessment

The speaker continues to guide through the customization and automation of the risk assessment template. They show how to use Excel's COUNTIFS function to count and categorize risks based on their inherent and residual probabilities and impacts. The process involves creating formulas that reference the risk matrix to tally up the number of risks in each category. The speaker also explains how to copy and paste these formulas across the spreadsheet while ensuring that certain cells reference static values and others update dynamically. The result is an automated system that updates the risk count on a one-pager as new risks are added or existing ones are modified.

15:02

🚀 Finalizing the Risk Assessment Template

In the final part of the script, the speaker wraps up the process of creating the risk assessment template. They review the steps taken to set up the risk matrix and the automated counting of risks. The speaker emphasizes the flexibility of the template, allowing users to add more details or adjust it to fit their specific needs. The template is presented as a ready-to-use tool for managing risks in any professional setting. The speaker concludes by encouraging viewers to adopt and adapt the template for their own use and looks forward to sharing more in the next video.

Keywords

💡Risk Assessment

Risk Assessment is a systematic process of identifying, analyzing, and evaluating risk. In the context of the video, it refers to a method used to measure and manage risk in business, projects, or any area that requires risk evaluation. The script mentions using a 'risk template' that helps in daily risk assessment tasks, emphasizing its importance in the workflow.

💡Risk Description

A Risk Description is a detailed account of potential risks, including what could go wrong and the circumstances that might cause it. In the video, risk descriptions are part of the risk assessment template where each risk is described, helping to understand and manage it effectively.

💡Probability

Probability refers to the likelihood of a risk occurring. The script discusses assigning a probability to each risk, which could be represented as a percentage, and is a key factor in calculating the risk rating.

💡Impact

Impact is the consequence or effect that a risk, if realized, would have on the project or business. The video script explains how impact is considered in the risk assessment process, with different levels of impact affecting the overall risk rating.

💡Risk Rating

Risk Rating is a measure that combines the probability and impact of a risk to determine its severity. The script describes how the risk assessment template automatically calculates risk ratings based on the selected probability and impact.

💡Risk Matrix

A Risk Matrix is a tool used to prioritize risks by plotting the probability of occurrence against the impact. The video script highlights creating a risk matrix to categorize risks as sustainable, moderate, severe, or critical, which aids in visualizing and managing risk levels.

💡Controls

Controls are measures put in place to mitigate or manage risks. The script discusses how controls are listed in the risk assessment template, along with the control owner, to show how risks are being actively managed.

💡Residual Risk

Residual Risk is the remaining risk after controls have been implemented. The video script explains calculating residual risk by assessing what's left over after applying mitigating controls, indicating whether the risk has been adequately managed.

💡Data Validation

Data Validation in Excel restricts input to a predefined set of values, ensuring accuracy and consistency. The script uses data validation to limit the selection of probability and impact to a set list, streamlining the risk assessment process.

💡Conditional Formatting

Conditional Formatting is an Excel feature that formats cells based on their values. The script describes using conditional formatting to color-code risk ratings, making it easier to visually identify risk levels at a glance.

💡COUNTIFS Function

The COUNTIFS function in Excel counts the number of cells that meet multiple criteria. The script uses this function to count different types of risks based on their probability and impact, providing a quick summary of the risk landscape.

Highlights

Introduction to a fantastic risk assessment template that is highly useful for those working in risk management, projects, or businesses needing to measure risk.

The template is used daily and covers various aspects of risk assessment, including risk descriptions, causes, consequences, and risk owners.

Explanation of the importance of calculating risk rating by multiplying the probability by the impact of a risk.

The sheet automatically calculates risk ratings based on selected probability and impact levels.

Introduction to creating a risk matrix to categorize risks into sustainable, moderate, severe, and critical based on probability and impact.

Discussion on how controls and control owners are used to mitigate risks and calculate residual risk.

Visualization of risk distribution using a chart that shows the number of risks in each category.

The ability to customize the risk matrix with different probability and impact percentages.

Instructions on how to format the risk assessment sheet with unique IDs, descriptions, and risk owners.

Explanation of how to create a risk matrix with probability scales and how they relate to risk categories.

Use of data validation in Excel to select from a list of predefined probability and impact levels.

Application of the INDEX and MATCH functions to automatically determine risk ratings based on the risk matrix.

Utilization of conditional formatting to color-code risk ratings for quick identification.

Counting and displaying the number of risks in each category using the COUNTIFS function.

How to create additional risk matrices for residual and inherent risks to track the effectiveness of controls.

Final walkthrough of the risk assessment sheet creation process and its practical applications.

Encouragement for viewers to adapt and use the template in their own work for effective risk management.

Transcripts

[Music]

hi everyone this one is something

absolutely fantastic it's a risk

assessment and if you work in risk or

around risk or in projects or even in a

business that needs to measure risk this

is absolute gold and it's the best risk

template that i have ever come across

and i use on a daily basis there's a lot

of different parts to this risk

assessment and we're going to go through

all of them to create it for yourself if

you want to but long story short as part

of any risk and risk assessment you've

got your risk descriptions you've got

your uh what's the what are they caused

by and what are the consequences of them

and who owns that risk but then most

importantly we've got the probability

and impact and for any risk we want to

multiply the probability by the impact

and that will give us our risk rating so

this particular sheet actually does that

automatically for us and what we do as

an example is if we select from a drop

down maybe it's a medium probability or

and you know this could be a percentage

as well and you can change it and we'll

show you how to change it as well but

let's say the impact when it happens is

is really low very low then actually it

changes our risk rating to be

sustainable and it's all based on the

risk matrix which we're going to create

as well just quickly we've got the

controls and the control owner so how

are we mitigating these risks what are

our controls around these risks and then

what's the residual risk what is the

leftover risk

once we've put those controls in place

so have we have we made our risk a lot

better and you know if it's sustainable

then maybe it's going to be sustainable

again hopefully the risk doesn't get

worse that would be a bad thing

but the way we manage this particular

sheet as well is with this beautiful

risk matrix so again you can change this

to be a percentage for example low might

be 20

40 60 80 or 100

probability of happening for example but

i find these these names work really

well at least in the real world everyone

understands that

and same with the impact is it a low

impact is it a high impact

but the best part about this is it shows

us on this beautiful chart here how many

sustainable risks we have how many

moderate risks we have see these

moderate risks and we've currently got

three

before our controls and we've even got

one severe and it counts them all up for

us just based on what we've done on our

risk assessment over here so i mean how

cool is that so it shows us exactly at a

glance what we've got and as you can see

there's our critical or

our critical risk there sorry that's the

severe one and if we just have a look

have we got yep there is our severe one

and we can see exactly why it's severe

it's because it's got a high impact if

it happens

so all of that to say let's get into

this sheet i'll show you how to create

it there's definitely a lot to get into

and you can create your own but i truly

believe this is the absolute best risk

assessment that you will ever find let's

get into it now of course like

everything every time we start i'll just

do the general framing and coloring and

we might speed that up ever so slightly

so that we can get into the good stuff

of creating first the risk matrix and

then our risk assessment

but let's do all of this shading and

framing and coloring first of all then

we can do the formulas and that's the

really good stuff that we really truly

want to see

now the items up the top we're just

going to go through first of all we want

our unique id

uh next we want our risk description and

we want all of these to

to be formatted nicely so let's wrap

that text around and we'll put them in

the center

we want our caused by and consequences

we want our risk owners the name and

role now we want our probability and we

want our impact which will give us our

risk rating and all of this without our

controls is our inherent risk the the

starting risk

next we want the controls that we're

going to be putting in place so what are

the mitigants how are we mitigating

these risks what are we doing about them

basically and of course the control

owner's name and role as well

lastly we want our residual probability

impact and risk rating this is residual

meaning left over so you know what's

left over once we have put our controls

in place

now we've got the bare bones for our for

our sheet and we can put in all the

details here

now before we go any further what we

actually have to do is start creating

our risk matrix over to the right hand

side and on this risk matrix we're going

to put all the probability scales so low

you know medium high very high and we're

going to match that against the critical

risks and or the sustainable risks for

example

and the reason we need to do that is

because we're actually going to use

these two these two tables that we

create here to feed into everything else

we do so a lot of it is automated as

part of our risk assessment this is the

really good stuff when we're creating

our risk assessment

for our probability let's merge this one

we'll say probability

but when we format this cell we actually

want the alignment let's change that

alignment and just make it 90 degrees

and now when we put this in the center

and make it a little bit larger now that

looks really nice let's add the rest of

our borders internal borders here now

that's looking really good now we're

going to see exactly why we needed to

select this this chart first by making

these equal the same levels on this

particular chart all we have to do in

the future if we want to change anything

on this sheet is to change

the the labels on this particular

section over to the right hand side that

will feed into everything and that just

makes it easier for us if we do want to

change those levels later on we're going

to do the same thing for our probability

as well

and then we can cheat a little bit by

copying down just the formulas and excel

will do the rest of us because it will

just go from top to bottom there because

we're going from top to bottom here as

well so that makes it a little bit

easier

now initially for this initial risk

matrix we're actually

making these these items ourselves so

we've got our moderate sections and this

is where they all fit so this is a great

way to do it you may have your own way

to do it and that's fine really if we've

got very low and low and then the

probability of happening is also low

then these are sustainable risks

if we've got probability of happening

you know low to medium but the impact is

getting higher so higher impact up the

top here then we've got moderate we

start to get into severe risks and

lastly we start to get into critical

risks that absolutely need controls or

need to be managed

now that we've got this set up we can

actually go back to our risk assessment

and we can start putting in the um some

of these things for our probability and

impact and adding the risk rating

automatically first of all what we're

going to do is go to our probability

column select the whole thing go to data

and what we want to do is actually uh we

want data validation here so there it is

data validation and we want to use a

list now keep in mind this is for our

probability so

we want to go to the source

go across here and where do we want to

select this from from our master list

how good is this see it's starting to

work already so for probability we want

to select all of these either drag down

or hold shift from top to bottom and as

you can see it appears in our data list

here

if we select ok and go back

now we are able to select from high very

high medium

low or very low and it's the same for

any of these so this starts to be really

really powerful we'll just format that

in the way that we want and now we want

to do the same for our impact as well

data validation selecting the list going

to our source scrolling over and using

the impact section here so that this is

now the list that we get to choose from

everything is connected again let's

format that in the way that we want and

now it's time for us to put in our risk

rating now for our risk rating we're

going to use a nice little excel trick

and that excel trick is index and match

one of the great things to use within

excel and so what we're going to say is

for the index what we're looking for is

c in blue here so all of these different

items so we're looking for the return

we're indexing all of these items from

moderate to severe to critical and

that's what we're going to return but

only if it matches a certain criteria

and the criteria we're looking for is

the match of f6 so the probability of

high and we want to find that in our

probability column over here

and then our match of medium or impact

sorry of g6 our green one here and we

want that to match the impact column

over here so when it matches that then

it will return the appropriate item in

that matrix and if we select we'll just

get rid of the bit on the end there that

will come a little bit later but if we

select enter and we say enter there now

this is automatically returned severe so

we've got probability of high

impact of medium so probability of high

impact of medium and that is a severe

risk and it's returned a severe risk

it's exactly what we wanted now really

quickly if we copy this down you'll see

that it actually gives us an error and

so we just want to use another final

little trick and that is say if error

open our bracket we'll let this formula

run

and then at the end of it

value if error if if it is an error we

just want blank quotes so we want the

cell to be blank now if we select that

again and now if we can copy that and

right click and control

copy the formulas the formulas will just

copy and leave our formatting intact and

now you can see that error disappears

now of course for these particular risk

ratings we actually want to color them

the way we want so we want them to be

colored the same as what we have here

but all we do there is to use

conditional formatting so in in home

conditional formatting we would say a

new rule and basically would say format

only cells that contain specific text

and if it's severe for example

then we format that and the severe one

has a feel has like that that orangy

feel maybe maybe it's a bit a bit darker

orange so i think we have that and then

that'll change automatically but only if

if it is severe if it's a different one

and we haven't set it up then it doesn't

format so let's go ahead and format all

of the different colors for our risk

ratings just using conditional

formatting

once you've done all of that if we go to

manage rules you'll see them all there

you'll have ones that are sustainable

and green

that are moderate and are yellow

critical that are red and severe that

are you know dark orange now of course

we want to do the same trick for our

residual risks and all we do is the same

thing using the same table all the way

over here nice and easy but once we've

done that we're going to start delving

into counting those risks and seeing

that on the risk matrix how many of each

particular risk we have on a one pager

which is just so incredible and it's

really really valuable

isn't that cool now we're making some

really good progress now all we need to

do is create these extra risk matrices

so for our residual risks and counting

those up and our

inherent risks and counting those up as

well first of all let's make sure that

these particular items just have dollar

signs next to the numbers so that they

don't move when we copy them down this

is going to be really important and it's

just going to make our life a little bit

easier now when we select this and

control c and control v

now this will uh will still remain

exactly the same and it'll be

referencing exactly what we want over

here now of course we don't need these

items or these names in here but we do

still want to keep the color so that's a

perfect thing that the color has covered

come across as well now for this

particular section we're going to use

another excel trick and this one is very

very cool and it uses the countifs

function and what we're saying is

countif so we've got

this criteria our probability

matches

x3 so very high

and

if our impact

matches

this one over here which is very low so

we've got very high and very low so if

it matches these then what we actually

want to do is uh or if it doesn't so if

it equals zero then we just want to

return nothing so we want a blank cell

but if it does then we want to count

those up so we're counting up the number

of those items that match that item and

the number of items that match that item

and that will return an actual number of

how many of those risks we have and that

is the absolute beauty of excel and how

we can create this and start creating

this automatically now the rest of this

is just filling out in exactly the same

way the countifs function across all of

these cells and there are a few

different tricks that we can use for

example these dollar signs when we use

when we copy these across and have a

dollar sign we want some of them to move

but some of them not to move and so as

we're copying across just be mindful of

that

then when we copy that and if we select

all of these and if we use our copy

formulas only and copy those across now

all we have to do is just adjust

this ever so slightly so what we're

going to do here is

as you can see in our second one we want

low and very high so impact to be low

that just comes across here comes up

there we go

in our next one

we want our impact to be medium

in our next one we want our impact to be

high

and in our last one we want our impact

to be very high

now that we've done this you'll notice

that we've left the three without a

dollar sign and we've done that because

we actually want that to move when we

copy these all the way down so let's see

if this works we've got our top row we

will copy this and we will select the

rest and select paste formulas

and now as you can see

we've got two risks on our board up here

and it's counting those two risks for us

how cool is that we've got one medium

and another another

moderate so one severe and one moderate

and that's our inherent risk if we add

more risks on our chart it'll count more

risks on our other chart as you can see

filling out those extra ones and now

it's counting them for us this is so

great now all we have to do is use the

exact same method for our risk our

residual risks except in our formula we

want it to be over here in the in the

residual risk and the residual

probability and the residual impact so

when we're selecting our ranges

for our countif function we just want

them to be over here instead of the

inherent risk ones over there and as you

can see it counts them up and we've

currently got two noted there

two noted here and there they are

counted up our residual risk after our

controls are put in place so all in all

there we go it's an amazing thing that

we've created together and of course you

can fill it out in more detail as you

see fit or even add rows and add columns

down here to suit yourself but this is

something that you can take and use

straight away in your own line of work

whenever risk is involved and i hope you

take it and create something great i'll

see you in the next video

[Music]