KYC Verification & AML Risk Detection
Business Scenario
You have joined ABC Bank as a Compliance Analyst.
Every day, the bank processes many customer transactions. Before approving a transaction, the Compliance Team must verify the customer's Know Your Customer (KYC) information and check whether the transaction shows any signs of Money Laundering (AML).
In this lab, you will retrieve customer KYC details, identify large transactions, and recommend the appropriate AML action before the transaction is approved.
Pre-Lab Preparation
Lab File
Topic : Client Onboarding & AML
1) Client onboarding workflow
2) KYC documentation
3) Beneficial ownership
4) AML typologies
5) Transaction monitoring alerts
6) Escalation and reporting
KYC_AML_Lab_Blank.xlsx : Link
KYC_AML_Lab_Completed.xlsx : Link
Task 1: Understand Your Spreadsheets
Before checking customer transactions, review the worksheets in the workbook.
Open KYC_AML_Lab.xlsx.
1
Open the Client Database worksheet
2
Review the following information:
-
Client ID
-
Customer Name
-
KYC Status
-
Risk Rating
-
Country
This worksheet contains the KYC details of all customers.
Open the Transaction Log worksheet
3
To see today's raw customer transactions, then open the AML Risk Analysis worksheet, which is where you will perform your compliance checks."
Task 2: Retrieve Customer KYC Details
Use the Client ID to retrieve the customer's KYC information.
Open the AML Risk Analysis worksheet
1
Click Cell D2 under Client Risk Rating.
Enter the following formula
2
=VLOOKUP(B2,'Client Database'!A:F,5,FALSE)
Press Enter.
Copy the formula down.
Click Cell E2 under KYC Status
3
Enter the following formula: =VLOOKUP(B2,'Client Database'!A:F,4,FALSE)
Press Enter.
Copy the formula down.
How the Formula Works
The formula searches for the Client ID in the Client Database and returns the required information
| Information Retrieved | Purpose |
|---|---|
| Client Risk Rating | Shows whether the customer is Low, Medium, or High Risk. |
| KYC Status | Shows whether the customer's KYC is Active, Pending, or Expired. |
Task 3: Identify Large Transactions
Banks monitor large transactions because they may require additional compliance checks.
Click Cell F2 under Large Transaction Alert
1
Enter the following formula
2
=IF(C2>10000,"Flag","Clear")
Press Enter.
Copy the formula down.
How the Formula Works
The formula checks the transaction amount.
| Transaction Amount | Result |
|---|---|
| More than $10,000 | Flag |
| $10,000 or less | Clear |
Task 4: Recommend the AML Action
Based on the customer's KYC status, risk rating, and transaction amount, determine the appropriate AML action.
Click Cell G2 under AML Action
1
Enter the following formula
2
=IF(OR(E2="Pending",E2="Expired"),"Freeze Account",IF(AND(F2="Flag",D2="High"),"Manual Review","Approve"))
Press Enter.
Copy the formula down.
How the Formula Works
The formula follows the bank's AML policy.
| Condition | AML Action |
|---|---|
| KYC Status is Pending or Expired | Freeze Account |
| Large Transaction and High Risk Customer | Manual Review |
| All other transactions | Approve |
Example
| KYC Status | Risk Rating | Large Transaction | AML Action |
|---|---|---|---|
| Active | Low | Clear | Approve |
| Active | High | Flag | Manual Review |
| Expired | Medium | Clear | Freeze Account |
| Pending | High | Flag | Freeze Account |
Why Is This Important?
Banks must verify customer identity and monitor transactions to prevent fraud, money laundering, and financial crimes.
By using Excel formulas, the Compliance Team can quickly identify high-risk transactions and take the appropriate action before processing customer payments.
After completing this lab, you will be able to:
-
Retrieve customer KYC information automatically.
-
Identify large transactions that require additional review.
-
Recommend the correct AML action based on the customer's risk profile and KYC status.
-
Prepare an automated AML risk analysis report using Microsoft Excel.
Next-Lab Preparation
Topic : Middle Office Controls
1) Economic and booking validation
2) Confirmation status checks
3) Operational risk events
4) Preventive and detective controls
5) Audit and regulatory interaction
6) MIS reporting