Operational Risk Scoring
Scores operational risks by likelihood and impact on a configurable scale, computes a composite risk priority number, and generates a heat map. Supports risk categorization, mitigation tracking, and residual risk assessment.
Operations - Operational Risk Scoring.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Risks identified informally but never scored or prioritized systematically
- No standard framework for comparing risks across categories
- Mitigation efforts not tracked against their effect on residual risk
- Stakeholders lack a visual summary of the organization's risk profile
- High-impact low-probability risks being overlooked in favor of frequent minor issues
Who This Is For
- Operations managers building risk registers
- Risk and compliance officers conducting assessments
- Project managers identifying and mitigating project risks
- Executive teams reviewing organizational risk dashboards
Inputs
- textRisk description
- #Likelihood score (1-5)
- #Impact score (1-5)
- textRisk category
- textMitigation action
- %Mitigation effectiveness
Outputs
- Risk priority number (likelihood x impact)
- Risk ranking by priority
- Heat map (likelihood vs. impact matrix)
- Residual risk score after mitigation
- Category-level risk summary
How Calculations Work
Each risk is scored on likelihood (1-5) and impact (1-5). The product gives the risk priority number (RPN) on a 1-25 scale. Risks are ranked by RPN. A heat map plots all risks on a 5x5 grid. When mitigation actions are entered, the effectiveness percentage reduces the RPN to produce a residual risk score. Category-level aggregation shows which areas carry the most total risk.
Example Use Case
Scenario: An operations team identifies 12 risks across supply chain, IT, and personnel categories. The top risk is 'single supplier dependency' with likelihood 4 and impact 5.
Result: Single supplier dependency: RPN 20 (critical). After 60% effective mitigation (dual-sourcing plan): residual RPN 8 (moderate). Category summary: supply chain carries 45% of total organizational risk.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
How do I estimate likelihood on a 1-5 scale?
1 = rare (less than 5% chance per year), 2 = unlikely (5-20%), 3 = possible (20-50%), 4 = likely (50-80%), 5 = almost certain (above 80%). Calibrate to your organization's experience.
What RPN threshold is considered critical?
Default: 1-5 low, 6-10 moderate, 11-15 high, 16-25 critical. Adjust thresholds in CONFIG based on your risk appetite.
How do I estimate mitigation effectiveness?
Estimate the percentage reduction in either likelihood or impact. A backup generator mitigates 80% of power outage impact but does not change likelihood.
Should I score inherent or residual risk first?
Score inherent risk (without mitigations) first. Then add mitigations to see residual risk. This shows the value of your mitigation investments.
How often should the risk register be updated?
Quarterly for stable operations. Monthly during major projects or organizational changes. After any significant incident.
Download Operational Risk Scoring
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.