KYC Risk Rating & Transaction Monitoring: Customer Scores, Alert Rules & Case Backlogs

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.

• Score eight new customers of a fictional bank on five risk factors, combine the points into one weighted risk score with SUMPRODUCT and find the highest-risk customer.
• Turn risk scores into Low / Medium / High tiers with IFS and a PEP override, assign simplified, standard or enhanced due diligence, schedule the next periodic review with EDATE and count the overdue reviews.
• Run a simple threshold rule on a week of transactions: flag single cash transactions at or above the limit, add up each customer's cash per day with SUMIFS, flag the customer-days that reach the limit and count what the daily view adds.
• Build a structuring rule: mark deposits just below the reporting threshold, count them per customer in a rolling 7-day window with COUNTIFS on date ranges, total the window's cash with SUMIFS and raise an alert only when both tests are met.
• Clean customer names with UPPER, TRIM and SUBSTITUTE, test them for exact matches against a fictional watchlist, count partial key-term hits with SEARCH and ISNUMBER, and count the names an analyst must review.
• Summarise a month of closed alerts per monitoring rule: number of alerts, false-positive rate, escalation rate and how many escalations turned into reports – the numbers that show which rule works and which only creates work.
• Back-test one rule at six thresholds: alerts, true hits, precision and coverage, then pick the highest threshold that still catches the target share of suspicious cases.
• Age open investigation cases against the report date, flag service-level breaches by priority and calculate how many analysts it takes to handle the inflow and clear the backlog in a target time.