In this video of #engineeringmanagementacademy #excel is used to create #RiskRegisters by #drmehrdadarashpour
❎ Excel workbook to follow along: bit.ly/Excel_Risk_Register
▶ RELATED VIDEOS: kzitem.info/news/bejne/zamh1JmGs2eFeoY
⌛ TIMESTAMPS
0:00 - Introduction to Integrated Risk Registers based on ISO31000
0:21 - Excel’s “Data Validation” feature, “INDEX” and “MATCH” functions & Conditional Formatting
0:44 - Step 1 (Construct a Project-Specific Risk Register)
2:04 - Step 2 (Define Risk Likelihoods & Impacts Based on the Project Risk Matrix)
3:00 - Step 3 (Automate the Calculation of Risk Ratings in the Project Risk Register)
4:13 - Step 4 (Color-code the Project Risk Register)
5:03 - Advantages of the 4-step Approach to make dynamic risk registers for projects
✍ Request Next Tutorial Video: forms.gle/Frz9U9imCouofdoD8
In a previous video, we discussed how to use the framework prescribed in ISO31000 for project risk management. The link to that video can be found here (kzitem.info/news/bejne/zamh1JmGs2eFeoY). Now, we want to create an integrated risk register in Excel. We can use Excel’s “Data Validation” feature to control impact & likelihood entries, “INDEX” and “MATCH” Functions to auto-populate risk ratings & “Conditional Formatting” to colour-code our risk register. You can follow along by getting the Excel workbook here. bit.ly/Excel_Risk_Register
Let’s start with a small project and we can follow only 4 simple steps in Excel to make a dynamic & color-coded risk register for our project.
Step 1 is to Construct a Project-Specific Risk Register
We start with “unique IDs” & “short descriptions” for all risks, which can be identified in a brainstorming session with all stakeholders. Next, we decide on a pre-mitigated mean value for each risk, which is an important component in our integrated risk register to have unified dimensions when setting contingencies. The pre-mitigated probability or inherent likelihood is chosen from our risk matrix. Pre-mitigated Impact or Inherent magnitude describes the size, scale, or significance of the Consequence that a risk event could have. Finally, risk rating or priority level, which is a function of impact & probability, is defined. Mitigation actions are proactive measures taken to reduce the likelihood or impact of identified risks. Post-mitigated probability or residual likelihood is the remaining level of chance for a risk event after mitigation measures. Similar story about Residual magnitude or post-mitigated impact, which can be defined using the risk matrix. Lastly, teams or individuals responsible for overseeing and managing a risk after mitigation actions are identified.
Step 2 is to Define Risk Likelihoods & Impacts Based on the Project Risk Matrix
We start with restricting the type of data that can be entered into the range of cells related to “pre-mitigated probability”. It can be done using “Data Validation” feature of Excel to maintain data accuracy, consistency, and integrity. First, we select range of cells concerning Inherent Likelihood and from “Data” Tab, we click on “Data Validation”. Then, under validation criteria, select “List”. Finally, specify data source by selecting our defined likelihood values on project risk matrix. Each cell now has a dropdown menu from which, likelihood levels based on heatmap can be selected.
Step 3 is to Automate the Calculation of Risk Ratings in the Project Risk Register
We may want to automatically calculate risk ratings based on the fever diagram. A “high priority” risk rating shown in red color is the product of “moderate to high” probability and “High” risk impact. We can use “INDEX” Function of Excel to retrieve risk ratings from our fever diagram or risk matrix. Then, “MATCH” Function in Excel is used to match identified impact of Risk#1 with row headers or impacts in our risk matrix. Similarly, “MATCH” Function is used to match identified probability of Risk#1 with column headers or probabilities. The formula is then duplicated for other risks.
Step 4 is to Color-code the Project Risk Register
Now we can better visualize risk ratings in our dynamic register by clicking on pre-mitigated risk rating for Risk#1 & under “Home” Tab, select “Conditional Formatting”. Then, from dropdown menu, select “New Rule” & in popup window, set rule type to “Format only cells that contain”, which is 2nd option. Next, we select “cell value” as “Equal to” the “Moderate priority” in our risk matrix & by clicking “Format” button, the Fill color is set to Yellow. Then, we choose “Manage Rules” & continue with a new rule of setting “Low Priority” to Green color & “High Priority” to Red Color. The formatting is duplicated for all remaining risks in our integrated risk register. The beauty of this 4-step solution is the automatic update of risk ratings & colour coding, when you change the entries for impact & likelihood, or enter new risks.
Access the Excel workbook via the link: bit.ly/Excel_Risk_Register
Негізгі бет Dynamic Risk Registers by EXCEL (4 Steps) Color-coded Risk Matrix Interactive Heat Map Fever Diagram
Пікірлер: 2