Which risks stay red after controls?
Every quarterly risk deck has the same 5x5 heat map, and the same argument about which squares are truly red. This model uses the consulting-toolkit residual formula behind that map: inherent risk is likelihood times impact, controls scale it down, and a 20% irreducible floor means mitigation never reaches zero. Sweep control effectiveness and see exactly which maturity level, if any, takes your top risk below appetite.
Operations Intermediate Data Table free
After you install, this is the model to open.
IT Risk: Still Red After Controls?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
One click sweeps control effectiveness from 1 to 5 and recomputes the residual score and the gap to appetite at every maturity level.
- Inherent risk score
- 20 of 25, from 4 likelihood x 5 impact
- Residual at current controls
- 12 level-3 controls, double the appetite of 6
- Control level needed to go green
- 5 residual 4; level 4 still lands at 8
- Irreducible floor
- 4 20% of inherent survives even perfect controls
At today's level-3 controls this risk carries a residual score of 12, double the appetite ceiling of 6. Stepping up to level-4 controls still leaves it red at 8. Only level-5 controls go green, and barely, because the 20% floor locks in 4 points of residual risk no matter what you spend. If the committee will not fund top-tier controls, this risk has to be transferred or accepted, not just mitigated.
The model
The flagship risk is a ransomware-driven outage scored on 5-point scales. Residual = Inherent x (1 - Control/5) + 0.2 x Inherent, so 20% of inherent risk survives even perfect controls.
| Likelihood score | 4 of 5 (expected within 1 to 2 years) |
| Impact score | 5 of 5 (over 25% of EBIT at stake) |
| Control effectiveness | 3 of 5 today (swept 1 to 5) |
| Risk appetite ceiling | 6 points |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Where should your warehouse actually be?Warehouse Location Optimizer
- Which rows in these two lists are the same company?Match Two Customer Lists That Don't Agree
- What do the next four quarters of demand actually look like?Seasonal Demand Forecast
- What does website downtime really cost us per year?What Does an Hour of Website Downtime Cost?
- Is the feasibility study worth it before you bid?Should We Pay for a Feasibility Study Before Bidding?
- Should we pay the overtime or take the late penalty?Overtime vs Late Penalty Scheduler
Every model like this one, and the method behind them: What-if analysis in Google Sheets.