Project Risk Register Template in Excel

Project Risk Register Template in Excel

Project Risk Register Template in Excel
Project Risk Register Template in Excel

Project Risk Register Template Excel Free Download

The Project Risk Register template used as a basis to create a project specific register. This template has example risks, which can easily be deleted. The Risk Register is a Microsoft Excel spreadsheet containing two separate spreadsheet tabs: 
  1. Development and procurement risks, which occur prior to contract execution
  2. Implementation and operations risks, which occur after contract execution.

Risk Register Format:
A title block is provided for the user to enter the project title and the name of the VAP3 project manager. Headings and footers describe the phase, page number and date of printing. The title block and header also allows the user to define the stage of assessment. The Risk Register is divided into “risk categories” for the major types of risk found in P3 projects. See the following tabs for examples.

Risk Register Content: 
The following is a description of the content in the Risk Register for both tabs described above. The tabs vary slightly and these differences are discussed below in more detail.

Risk Information:
Each risk has its own risk number (column 1). The risks provided in the template are described by risk category (column 2), risk topic (column 3) and impact phase (column 4). The impact phase is any one of the five risk phases, excluding the screening phase, and relates to the timing of the impact of the risk event should it occur. If a risk has potential impact in more than one phase, a new line has to be added with identical category and topic, but with a different impact phase selected. Each risk has a risk description (column 5), which is an elaboration of the category/topic to describe the risk event. It should contain sufficient detail to be understood by any reader.

Risk analysis report template:
Each risk has a consequence (column 6), which should be a brief description of the potential consequence if the event should occur and provide a logical link between the event and its impacts. The consequence typically impacts cost or schedule but can be as specific as a safety, environmental, or reputation impact. 

The Red-Amber-Green (RAG) risk analysis involves the selection of a probability range in column 7 and cost and schedule consequence ranges from columns 8a and 8b respectively. At this stage of analysis, the assessment is limited to percentage ranges provided in drop-down menus in the Risk Register. The cost impact is a percentage of total project costs. Implementation phase schedule impacts are defined as a range of months delaying completion. Development phase cost impacts are defined as a percentage of project funds to reach financial close and schedule impacts are defined as a range of months delaying financial close. 

The spreadsheet contains a formula that calculates cost and schedule risk impact. The Risk Register contains a macro to automatically color the risk impact column based on the RAG Analysis described in the guidance. Colors may assist in the communication of risk results.

An Expected Value Analysis requires the estimation of a range of dollar values for cost and schedule impact. The range is defined by three values: a minimum (Min), most likely (ML) and maximum (Max). Columns 9, 10a and 10b allow the quantification of probability, cost consequence and schedule consequence in a similar manner to that described above but without the restriction of drop down menus. The Risk Register contains a simplified formula that combines the input values for probability and impact to calculate the risk value for use in this Risk Analysis. 

This formula is discussed in the guidance. As with the qualitative risk analysis, a total value for cost and schedule impact can be obtained by summing the “risk value” columns. Also, filters can be applied to the Risk Register to calculate separate amounts for the different Risk Response methods, including retained and transferred risks.

Schedule impact may not be applicable for risks during the operations phase. Instead, an additional column 9.1 is provided as a free text box to describe the frequency of the risk impact. Typical frequency options include: every year, once over the whole analysis period, every 10 years, in the first 3 years of the analysis period, and in the last three years of the analysis period. 

This determination has been found to be useful during Risk Workshops to help attendees quantify risk impacts without getting confused about whether the event is annual or occurs at longer intervals through the operations period. Further analysis can be performed to annualize the risk values, or adjust different parts of the operations phase cost/revenue estimate.

A Monte Carlo Simulation uses the same quantitative input data as the Expected Value Analysis, but instead of using a simple formula to approximate a weighted distribution of impact values, specialist software is used to perform a Monte Carlo Simulation (see Appendix E for discussion on the Monte Carlo Simulation), resulting in a range of probabilities and impacts. 

Consequentially, columns 9, 10a and 10b can be ignored. Instead column 10c is used if a Monte Carlo Simulation is performed as the definition of assumption curve replaces the simplified formula used in the Expected Value Analysis. Column 10c defines the assumption curve for each individual risk and any important modeling notes.

Risk Response, Assignment and Allocation:
Having identified and assessed the risks, the VAP3 Project Manager and project team determine what the Agency’s initial response is likely to be. Column 11 allows the user to define an overall Risk Response strategy from a drop-down menu of five options (1) avoidance; (2) transference; (3) acceptance; (4) mitigation; and (5) sharing. These options are described in Section 3.1.2 – Step 3.

Column 12 allows the user to select a party that is to be allocated the risk. The menu of options includes: (1) the Agency (for retained risks); (2) the contractor (for transferred risks under a design-bid-build or design-build approach); (3) the concessionaire (for transferred risks under a design-build-finance-operate-maintain approach); and (4) shared. A table of typical allocations for different methods of procurement for transportation highway projects is provided in the guidance. Risk Allocation notes to explain key assumptions and rationale for the allocation should be added to column 13. 

As the project progresses, more information is likely to be available for a determination of Risk Response and allocation. These columns should be updated and notes added.

Risk Tracking: 
The column 14 provides the user with an opportunity to track risks via a record of risk owner, a more detailed description of response/mitigation strategy, estimated mitigation cost (if known), risk review date, status/comments and whether the risk is on the critical path or not. Critical path risks may require a more detailed analysis of schedule risk impacts.

If risks are closed during later stages of analysis this can be shown in column 15 and the entire risk row can be “grayed out”. This approach is better than deleting the risk row entirely so that you have a record of having considered the issue, which is important for capturing and feeding back lessons learned. The reason for closure should be added to column 15. Notes may be added to column 16 to state any lessons learned regarding a specific risk or the Risk Management Framework and/or the relevant section in the contract documents, which discusses the Risk Allocation.

Project risk register examples

Download also:

Post a Comment

Previous Post Next Post