How to Make a Risk Assessment Matrix in Excel
Summary
TLDRThis 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
📊 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.
🔍 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.
🎨 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.
🚀 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.
Mindmap
Keywords
💡Risk Assessment
💡Risk Description
💡Probability
💡Impact
💡Risk Rating
💡Risk Matrix
💡Controls
💡Residual Risk
💡Data Validation
💡Conditional Formatting
💡COUNTIFS Function
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]
Weitere ähnliche Videos ansehen
PMI Risk Management Professional Exam Free Practice Questions Part 1
Risk Management Basics | Google Project Management Certificate
ISTQB FOUNDATION 4.0 | Tutorial 50 | Risk Identification | Risk Assessment | CTFL Tutorials
CompTIA Security+ SY0-701 Course - 5.2 Explain Elements of the Risk Management Process - PART A
Proses Manajemen Risiko ISO 31000: Penetapan Ruang Lingkup, Konteks, dan Kriteria
Risk Management MindMap (3 of 3) | CISSP Domain 1
5.0 / 5 (0 votes)