Compliance Risk Scoring: Obligations, Inherent vs Residual Risk & Heat Maps

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Turn a fictional distributor's list of compliance obligations into a working register: months per frequency, next due dates with EDATE, an overdue status against the report date and a count of what falls due in the next 30 days.
• Translate likelihood and impact words into scores, multiply them into an inherent risk score, give each score a rating band with an approximate XLOOKUP and count the risks that need attention.
• Apply control-effectiveness ratings to the inherent scores, compute residual scores and ratings, and measure how much risk the controls remove across the whole register.
• Build the classic 5×5 heat map: COUNTIFS places every risk in its likelihood/impact cell, a formula counts the red-zone risks and two formula rules colour the red and amber zones.
• Compare each residual risk with the appetite set for its category, flag what must be escalated, pull the escalation list out with FILTER and report the share of risks outside appetite.
• Compute due dates for data-subject requests with a one-month rule and an extension for complex cases, measure the days to spare, and run a 72-hour breach clock with date-time arithmetic.
• Age every compliance issue against the report date, sort open issues into 0-30 / 31-60 / 61-90 / 90+ buckets with IFS, and report the closure rate and the open issues older than 60 days.
• Turn twelve months of key risk indicators into a board view: 3-month averages, Breach / Warning / OK status against limits, sparklines for the trend and a count of KRIs above their limit.