Excel Risk Register, Lecture notes of MS Microsoft Excel skills

Download Excel Risk Register and more Lecture notes MS Microsoft Excel skills in PDF only on Docsity!

Excel Risk Register

HSE

Instructions for the use of the HSE Excel Risk

Register

Quality Assurance and Verification Division 6/22/

A. Table of Contents

• A. Table of Contents
• B. Preparing the Risk Register:
• C. General Information
• D. Changing the Branding
• E. Adding New Risk Owner..................................................................................................................
• F. Adding New Risk Co-ordinator
• G. Entering New Risks:.........................................................................................................................
• H. Editing a Risk
• I. Adding an Existing Control
• J. Initially Rating a Risk
• K. Risk Review Date
• L. Adding New Action Owner............................................................................................................
• M. Adding an Action
• N. Current Risk Rating........................................................................................................................
• O. Deleting a Control
• P. Deleting an Action
• Q. Preparing Sub-registers to send to Risk Co-ordinators
• R. Removing Risk Co-ordinators
• S. Receiving Updated Sub-register(s)................................................................................................
• T. Spellcheck
• U. Generating a Report......................................................................................................................
• V. Removing Risk Owners..................................................................................................................
• W. Removing Action Owners..............................................................................................................
• X. Closing a Risk

4. With the file saved, select the “Basic Details” worksheet by clicking it (red arrow).
5. Navigate to cell ‘B7’ to the ‘Division’ field (red arrow), and input the Division for which the Risk Register represents.
6. If the Risk Register is to be used at the next level down, select cell ‘A8’ and use the dropdown menu to select from the dropdown (red arrow). Then select cell ‘B8’, and input the organisation the Risk Register will represent (yellow arrow).

7. If the Risk Register is to be used at a further level down, select cell ‘A9’ and use the dropdown menu to select from the dropdown (red arrow). Then select cell ‘B9’, and input the organisation the Risk Register will represent (yellow arrow).
8. Navigate to cell ‘B10’ (red arrow) and input the name of the individual who is ultimately responsible for the Risk Register.
9. Each risk will be assigned an alphanumeric ID. The alpha component of the ID is generated combining the initial letters of the Division and other fields above i.e. in the above example the alphanumeric ID will be AHDSHGUHG1, AHDSHGUHG2 etc.
10. Preparation of the Risk Register is now complete.

3. Locate and click the “Unlock” button (red arrow).
4. Now you can select the HSE logo, delete as required, and replace with a new logo. You can also select cells A6:A11 and change the colour to better reflect departmental branding. The colour of header cells in sheets “Summary”, “Risk Register”, “Risk Action Log” and “Closed Risks” can also be changed to reflect departmental branding.
5. Once all changes have been made, go to “Basic Details” sheet.
6. Locate and press the “Lock” button.

E. Adding New Risk Owner

1. Select the “Risk Register” worksheet by clicking it (red arrow).

2. Above the ‘Risk Owner’ heading, locate and click the ‘+’ button (red arrow) and this will open the ‘New risk owner’ window.
3. In the ‘New risk owner’ window, input the name of the possible owner of risks in the field provided (red arrow) and click ok (yellow arrow).
4. The inputted name will now appear on the ‘Risk owner’ dropdown.

F. Adding New Risk Co-ordinator

1. Select the “Risk Register” worksheet by clicking it (red arrow).
2. Above the ‘Risk Co-ordinator’ heading, locate and click the ‘+’ button (red arrow) and this will open the ‘New risk co-ordinator’ window.
3. In the ‘New risk co-ordinator’ window, input the name of the possible co-ordinator of risks in the field provided (red arrow) and click ok (yellow arrow).
4. The inputted name will now appear on the ‘Risk co-ordinator’ dropdown.

4. Select the risk co-ordinator from the dropdown menu in the ‘Risk Co-ordinator’ field (red arrow). If the adequate person is not included in the dropdown, click the ‘Cancel’ button and go to section F: Adding New Risk Co-ordinator on page 7.
5. Select the risk type from the dropdown menu in the ‘Risk Type’ field (red arrow).

6. Select the risk category from the dropdown menu in the ‘Risk Category’ field (red arrow). The options included in the dropdown menu are aligned to the impact table of the HSE Risk Policy.
7. Input the risk description into the ‘Risk Description’ field (red arrow). Ensure to follow the instructions provided for the risk description. Finally, click the ‘OK’ button (yellow arrow)

4. To edit the risk description select the applicable cell in column ‘F’ (red arrow) and edit the cell contents.
5. To update the status of the risk to “Monitor” by selecting the appropriate cell in column ‘M’ (red arrow), clicking the dropdown icon which appears (yellow arrow) and selecting “Monitor” from the dropdown menu (blue arrow).
6. Any edits made to the “Risk Register” worksheet will be replicated to the “Risk Action Log” and “Summary” worksheets.

I. Adding an Existing Control

1. Select the “Risk Register” worksheet by clicking it (red arrow).
2. If inputting the first existing control to the risk, continue to step 3. If inputting an additional existing control to the risk, skip to step 5.
3. Select the first available cell in column ‘H’ (red arrow) in the row related to the applicable risk (yellow arrow).
4. Input the existing control in that cell.

5. If an additional existing control is required, select the cell of the most previous control in the example below ‘H5’ (red arrow). Locate and click the “New Control” button (yellow arrow).
6. This will merge cells in adjacent columns, but will allow 2 rows of cells in column ‘H’ as shown below. If the correct cell was not selected in step 5, and the cells do not merge as shown below, locate and click the ‘ ’ button (red arrow); this will reverse the action and start again at step 5.

J. Initially Rating a Risk

1. Select the “Risk Register” worksheet by clicking it (red arrow).
2. To input the likelihood rating from 1-5 select the appropriate cell in column ‘I’ and click the dropdown icon which appears (yellow arrow).
3. Select and click the adequate rating from the dropdown menu (red arrow).

2. The risk review date is the date the risk was most recently reviewed. To input the date, select the appropriate cell in column ‘L’ and input the date in the dd/mm/yyyy format.

L. Adding New Action Owner

1. Select the “Risk Action Log” worksheet by clicking it (red arrow).
2. Above the ‘Action Owner’ heading, locate and click the ‘+’ button (red arrow) and this will open the ‘New Action Owner’ window.
3. In the ‘New action owner’ window, input the name of the possible owner of actions in the field provided (red arrow) and click ok (yellow arrow).
4. The inputted name will now appear on the ‘Action owner’ dropdown.

M. Adding an Action

1. Select the “Risk Action Log” worksheet by clicking it (red arrow).
2. If inputting the first action to the risk, continue to step 3. If inputting an additional action to the risk, skip to step 8.
3. Select the first available cell in column ‘F’ (red arrow) in the row related to the applicable risk (yellow arrow).
4. Input the action required in that cell.
5. To input the action owner select the associated cell in column ‘G’, and select the action owner from the dropdown menu (red arrow). If the adequate person is not included in the dropdown, go to section L: Adding New Action Owner on page 15.
6. To input the action due date select the associated cell in column ‘H’ (red arrow), and in put the due date in the format dd/mm/yyyy.
7. The Action status will auto-populate based on the action due date, action completion date and today’s date. With no action completion date, and an action due date in the future, the status will be ‘Not due’ (red arrow).

With no action completion date, and an action due date in the past, the status will be ‘Overdue’ (yellow arrow).

3. Select and click the adequate rating from the dropdown menu (red arrow).
4. To input the impact rating from 1-5 select the appropriate cell in column ‘M’ and click the dropdown icon which appears (yellow arrow).
5. Select and click the adequate rating from the dropdown menu (red arrow).
6. The current risk rating will then be calculated and inputted to the appropriate cell in column ‘K’. The colouring will also represent the severity of the risk rating; Green = Low; Amber = Medium; Red = High.

O. Deleting a Control

1. Select the “Risk Register” worksheet by clicking it (red arrow).
2. Locate and select the cell containing the existing control you wish to delete in column ‘H’.
3. Locate and click the ‘Delete’ button (red arrow).
4. This will put a line through the text and add the word ‘deleted’ and the date the control was deleted (red arrow).
5. If the correct control was not selected in step 2, locate and click the ‘ ’ button (red arrow); this will reverse the action and start again at step 2.