Project Risk Register in Excel: Heat Map, Expected Value and Response Plan

No construction project is free of risk: late supply, design changes, rain, a change-order dispute. The difference between a project that stumbles and one that survives is that the second logged its risks early, estimated their effect, gave someone the response and set a date.

This sheet gives you a ready register for that: each risk has a category, owner and probability and impact from 1 to 5, and the sheet computes score, rating (low to critical) and expected cost, draws the heat map of open risks, shows the top five and compares value before and after mitigation.

Who is it for?

• Project managers and planning engineers
• Quality, safety and contracts engineers
• Subcontractors and small firms
• Owners reviewing their contractors risks

What is inside the sheet?

• A 25-risk register with category, owner, probability, impact and response
• Editable rating limits and probability percentages per level
• Score, coloured rating and expected value for each risk
• A 5×5 heat map counting open risks in each cell
• The top five open risks with their mitigation actions
• A residual assessment after mitigation and a residual value
• Overdue action alerts, grey for closed risks and a guide

How to use it

1. Enter the report date and score limits.
2. Log each risk with description, category, owner, probability and impact.
3. Enter the estimated cost impact and the mitigation action and date.
4. Re-score after mitigation and watch the heat map and residual value.

Formulas used

• Score = probability × impact
• Rating: low ≤ 4, medium ≤ 9, high ≤ 15, above that critical
• Expected value = cost impact × level probability (10% to 90%)
• Residual value = cost impact × post-mitigation probability
• Suggested contingency = expected value of open risks

Worked example from the sheet itself

12 risks on a villa project, 11 open and 7 high or critical.

• The top two are critical at 16: steel delay and price rise (4 × 4) and owner drawing changes.
• Expected value of open risks is 321,000, falling to 183,000 once mitigation actions are done.
• That suggests a risk contingency of 183 to 321 thousand depending on mitigation success, 4 to 7% of a 5 million project.

Why expected value

A score of 16 does not tell you how much budget to set aside. Expected value multiplies cost impact by probability, giving an amount comparable to your contingency. It is an indicator, not a guarantee, because a single risk either happens in full or not.

The heat map

Each cell counts open risks at that probability and impact, coloured by the limits you set. The red cells at upper right are your first priority. When a risk is closed it leaves the count automatically.

Responding to risk

Four known responses: avoid by changing the plan, mitigate by reducing probability or impact, transfer to another party such as insurance or a subcontractor, and accept knowingly with a contingency. Pick the response from the list and write a specific action with an owner and a date.

Residual assessment

After an action, probability or impact changes, so record them in the residual columns to see how effective your plan is. If the score stays the same the action is ineffective or not done.

Tips for best results

• Review the register in the weekly project meeting.
• Write a risk as a specific event, not a vague worry.
• Tie each high risk to one owner.
• Close a risk when its cause is gone; do not delete it.

Frequently asked questions

Can I change the probability percentages?

Yes, in the right-hand list for each level from 1 to 5.

How many risks fit?

25 risks on one page.

Is expected value precise?

It is a rough indicator for contingency, not a guarantee.

Does it suit non-construction projects?

Yes, by changing the categories in the list.

Download the sheet

Press the download button at the top of the page to get the Excel file. It includes a full guide sheet, yellow input cells and formulas protected from accidental edits. The sheet is also available in Arabic from the language switcher.

A simple risk management tool; financial estimates are editable examples.