A risk matrix is not a spreadsheet

Almost every risk matrix that reaches us arrives as a spreadsheet. That works right up until somebody asks why a hazard scored 600 instead of 400, and the answer is lost somewhere among merged cells and formulas nobody remembers writing.

The problem is not Excel, it is where the calculation lives

Colombia's GTC 45 standard defines risk level as the product of four variables: deficiency, exposure, probability and consequence. The arithmetic is trivial. What is not trivial is that the arithmetic be recorded alongside the data, together with the version of the criteria applied on the day of assessment.

In a spreadsheet the calculation lives in the cell. If somebody changes the scoring table in March, every assessment from January silently recalculates and nobody notices. In an audit that is precisely what cannot happen: the auditor does not ask what the value is today, they ask what it was then and under which criteria.

Separate the fact from the criterion

A model that survives an audit separates three things a spreadsheet mixes together:

• The fact — which hazard was identified, where, when and by whom. It is immutable.
• The assessment — the four levels assigned, stored as values, not as formulas.
• The criterion — the interpretation table in force on that date, versioned.

With that separation, changing the criteria in March does not rewrite history: it creates a new version that applies from March onwards. January's assessments keep theirs.

Why the semantic model matters more than the dashboard

The temptation is to build the dashboard first, because that is the part people see. It is an expensive mistake. A beautiful dashboard on a poorly designed model has to be rebuilt entirely the moment the second unforeseen question appears: “show me this by contractor”, “compare site against site”, “only the risks that dropped after the control”.

If the model has the right dimensions — hazard, location, process, role, contractor, time — those three questions need no new work. If it does not, every question is a project.

The measure almost nobody models correctly

The indicator most often requested and worst delivered is control effectiveness: how much the risk fell after intervening. It requires comparing the same unit of analysis at two points in time, and that only works if the hazard has its own persistent identity. In most spreadsheets each assessment is a new row with no relationship to the previous one, so the comparison is impossible without matching text by hand.

If the hazard has no stable identifier, there is no before and after. And without a before and after, there is no way to demonstrate that the management system achieves anything at all.

What to check in your current matrix

Three questions you can ask yourself today:

• If I change the scoring table, do old assessments recalculate? If the answer is yes, you have a traceability problem.
• Can I answer “which risks dropped a level this year” without opening two files and comparing by hand?
• Who assessed each hazard, and when? Is it recorded in the data, or does somebody have to be asked?

If any answer is uncomfortable, the problem is not the dashboard. It is the model underneath it.