AML Risk Assessment in Excel: Make Every Inherent-Risk Score Traceable to Source Data

TL;DR

• A BSA/AML risk assessment that can’t trace a score back to source data isn’t defensible — it’s a guess with a rating attached.
• The workbook structure matters: separate tabs for source data, segmentation, weighting, overrides, control effectiveness, and residual scoring give you an audit trail examiners can follow.
• Override logs and challenge documentation are the fields most institutions skip — and the first ones examiners look for when a score seems inconsistent with the portfolio.
• Control effectiveness ratings should be grounded in test results, not policy existence.

In a BSA/AML exam, the question isn’t just “what is your residual risk rating?” It’s “show me how you got there.” An institution that produces a risk rating without being able to demonstrate the underlying data, the segmentation logic, the control effectiveness rationale, and who reviewed and challenged the assessment is going to have a harder conversation with examiners than one that walks them through each step.

The difference is usually workbook structure. A risk assessment built in a single Excel tab — or worse, as a Word document narrative — can’t show its work. One built with separate tabs for each stage of the methodology can. This post covers the workbook architecture and the field-level requirements that make every AML inherent risk score traceable from rating back to source data.

Why the Excel Structure Is the Documentation

The FFIEC BSA/AML Examination Manual describes what the risk assessment must accomplish: a comprehensive analysis of money laundering, terrorist financing, and other illicit finance risk across customers, products/services, geographies, and channels. It doesn’t prescribe a specific format. That leaves institutions to design their own — and the format they choose determines whether the assessment is defensible when examined.

A narrative risk assessment describes the methodology and conclusion. An Excel workbook with a documented calculation chain shows the methodology, the inputs, the calculations, and the conclusion — and lets an examiner (or your auditor, or your BSA independent tester) follow each step. When a score looks wrong or inconsistent with portfolio trends, the examiner can look at the source data tab, the segmentation logic, and the weighting applied instead of asking you to re-explain it verbally.

The workbook structure is the audit trail. Build it before you have to explain yourself.

The FFIEC Risk Categories: Four Tabs of Source Data

The FFIEC BSA/AML framework organizes inherent risk into four categories. Each one needs its own source data section in the workbook — not combined, not summarized from memory.

Customer risk captures the money laundering risk posed by the institution’s customer base. Source data fields: count and percentage of each high-risk customer type — money services businesses, politically exposed persons, cash-intensive businesses, non-resident aliens, high-net-worth customers with complex ownership structures, correspondent banking relationships, third-party payment processor customers. Each count should tie to a core system extract with the extraction date.

Product and service risk captures the risk posed by what the institution offers. Source data fields: volume and count of wire transfers (domestic and international), private banking accounts, international ACH, correspondent banking accounts, cryptocurrency-related services, prepaid card programs, and any other products with elevated money laundering typologies. Volume matters here — not just the presence of the product.

Geographic risk captures where the institution operates and where funds flow. Source data fields: branch and ATM locations, customer addresses by jurisdiction, wire transfer counterparty jurisdictions, correspondent banking locations. Flag jurisdictions on the FATF high-risk or monitored list, active FinCEN Geographic Targeting Orders, and HIDTA designations relevant to the service area.

Channel risk captures how customers access products. Source data fields: percentage of accounts opened online without face-to-face interaction, percentage of transactions processed through third-party payment processors, number of customers with no physical interaction in the prior 12 months, use of digital wallet or peer-to-peer payment integrations.

Each source data tab should have: extraction date, data source (system name and query or report name), and the analyst who ran the extract. This creates the first link in the audit trail.

The Segmentation Tab: Translating Data Into Risk Tiers

Source data tells you what you have. The segmentation tab tells you what it means for risk.

Segmentation applies a risk tier — typically low, moderate, high — to each data element. The segmentation logic should be documented on the tab itself, not assumed. If your institution classifies MSB customers as high risk, say so — and reference the regulatory basis (FinCEN guidance, FFIEC manual, internal policy). If your wire transfer volume threshold for “elevated” geographic risk is 10% of total wire value originating from FATF high-risk jurisdictions, document that threshold and who approved it.

Segmentation is where institutions have the most flexibility and where examiners focus the most scrutiny. If your segmentation logic produces a “low” rating for a customer category that examiners generally consider elevated risk, the segmentation documentation needs to explain why that’s a reasonable conclusion for your specific portfolio — not just that you made a different judgment.

Fields for each data element in the segmentation tab:

• Data element name
• Source data value (linked to source data tab)
• Segmentation threshold used
• Resulting tier (low/moderate/high)
• Policy or regulatory reference for the threshold
• Date the threshold was last reviewed

The Weighting and Scoring Tab: Inherent Risk by Category

Segmentation produces tier ratings for individual data elements. Weighting combines them into a category-level inherent risk score.

This is where most workbooks get opaque. “Customer risk: HIGH” with no visible calculation is not an inherent risk score — it’s a conclusion. The weighting tab should show: which data elements are included, the weight assigned to each, the tier score converted to a numeric value, the weighted average, and the resulting category risk level.

The weighting assumptions — why MSB customers get 25% weight and NRAs get 15% — should be documented in a methodology note on the tab. The conversion from numeric score to risk tier (e.g., 2.0–2.4 = Moderate, 2.5–3.0 = High) should be defined explicitly, not assumed.

This is the calculation chain. An examiner who sees “customer risk: Moderate-High” can open the weighting tab and follow every number back to the source data.

The Override Log: Document Every Score Adjustment

No mechanical scoring model perfectly captures actual risk. Management will sometimes need to adjust a score — upward or downward — based on qualitative factors the formula didn’t capture. That’s legitimate. Undocumented adjustments are not.

The override log is a separate tab that captures every instance where the final inherent risk score differs from the mechanically calculated score. Required fields:

Without an override log, examiners have no way to distinguish between a score that accurately reflects the data, a score that was adjusted down to produce a favorable overall rating, and a score that was adjusted up based on qualitative risk factors the formula missed. The log creates the transparency.

A well-documented override log is not a weakness — it demonstrates that the risk assessment went through real challenge. An absence of any overrides on a complex portfolio is sometimes a flag that the process was mechanical without genuine judgment.

The Control Effectiveness Tab: Rating Based on Evidence

Inherent risk tells you what risk exists before controls. Control effectiveness ratings should reflect what you actually know about how controls perform — not what policies say they should do.

For each control area (transaction monitoring, KYC/CIP procedures, EDD program, SAR filing, OFAC screening, independent testing), document:

A transaction monitoring system with no documented alert disposition review isn’t “strong” because it flags alerts — it’s at best “adequate pending validation.” An EDD program that policy describes as comprehensive but hasn’t been audited in three years can’t support a “strong” effectiveness rating.

The BSA/AML independent testing program is the primary source of objective control effectiveness data. If your last independent test identified gaps in SAR documentation completeness and those findings are still open, the SAR filing control effectiveness should reflect that — regardless of what the policy says the process looks like.

The Residual Risk Tab: Combining Inherent Risk and Control Effectiveness

Residual risk is the net risk after controls. The residual risk tab applies control effectiveness ratings to inherent risk scores to produce category-level and overall residual risk.

The calculation logic should be explicit. One common approach: assign numeric values to control effectiveness ratings (Strong = 1, Adequate = 2, Weak = 3), subtract a control offset from the inherent risk score, and convert the result to a residual tier. Whatever method the institution uses, document it in the tab.

The residual risk tab should produce:

• Category residual risk (Customer / Product / Geographic / Channel)
• Overall institution residual risk
• Comparison to prior period (is residual risk increasing, decreasing, or stable?)
• Narrative summary of drivers

Examiners compare residual risk assessments over time. An institution whose residual risk has been “Low” for five years despite growth in high-risk customer categories and product expansion will get questions about whether the assessment is genuinely re-run each year or just carried forward.

Challenge Documentation: The Sign-Off That Proves Someone Looked

The last tab most institutions forget is challenge documentation — a record of who reviewed each section, what questions were raised, and how they were resolved.

A risk assessment that goes from analyst to BSA Officer to approval with no documented challenge process looks like it was rubber-stamped. Examiners want evidence that the assessment went through genuine review: that someone questioned whether the geographic risk segmentation was appropriate given new business in a flagged MSA, or whether the control effectiveness rating for transaction monitoring should have been downgraded after the last audit finding.

The challenge documentation tab should include:

• Section reviewed
• Reviewer name and title
• Date reviewed
• Questions or challenges raised
• Resolution or rationale for maintaining original position
• Final approval signature

The BSA Officer challenge of the assessment, the ALCO or senior management review, and the board-level approval should all appear here with dates. This documentation also supports your exam response when examiners ask about governance over the risk assessment process.

Connecting the Workbook to the Ongoing BSA Program

An annual risk assessment that’s disconnected from the day-to-day BSA program is a compliance document, not a risk management tool. The workbook structure should support ongoing program management:

The KRI and SAR filing metrics tracked month-to-month should feed into the risk assessment update — if SAR filing volume has increased 40% in a category, that should surface in the next risk assessment update as a potential driver of elevated residual risk. The source data tabs should reference the same data extraction processes your compliance team runs monthly, not one-off pulls.

When the assessment is updated, run a comparison to the prior version and document what changed and why. This creates a longitudinal view examiners can use to assess whether your risk management is responsive to changing conditions.

For community banks and credit unions new to this level of documentation, the AML risk assessment methodology for banks and fintechs covers the foundational framework; this post covers the workbook architecture for making that framework auditable.

What Examiners Ask For

When examiners review a BSA/AML risk assessment, they’re tracing a specific path: from the overall risk rating, back through control effectiveness, back through inherent risk, back through the source data. The questions they ask follow that path:

• “What data did you use to determine that customer risk is high?” — Source data tab with extraction date and system reference.
• “Why is geographic risk only moderate given your volume of wires to this jurisdiction?” — Segmentation thresholds and any overrides applied.
• “Your transaction monitoring effectiveness is rated ‘strong’ — what’s that based on?” — Test results from independent testing, not the policy document.
• “The overall risk rating is lower than last year — what changed?” — Comparison to prior period in the residual risk tab.
• “Who reviewed and approved this assessment?” — Challenge documentation tab with dates.

Each of those questions has an answer in a well-structured workbook. Without the workbook structure, each one requires a verbal explanation that may or may not hold up under follow-up questions.

The Evidence Gap in Most Assessments

The most common BSA/AML risk assessment gap isn’t methodology — it’s evidence. Institutions understand the four FFIEC risk categories. Most have a process for scoring them. The gap is usually one of three things:

Source data without extraction metadata. A customer count used in the risk assessment that can’t be tied to a specific core system report run on a specific date isn’t auditable. Six months later, you can’t confirm whether the number was accurate or how it was derived.

Control effectiveness ratings without testing support. “Strong” for transaction monitoring based on the fact that the system is tuned twice a year — without test results showing what the tuning produced — isn’t grounded in evidence. When independent testing results are available, they should be cited. When they aren’t, the rating should reflect that uncertainty.

No documentation of management review. The BSA Officer certification is not the same as challenge documentation. Certification says “I approve this.” Challenge documentation says “I reviewed the geographic risk segmentation and questioned whether the MSA designation justified the moderate rating given recent FinCEN guidance — the team’s rationale is X.” Examiners know the difference.

So What?

An AML risk assessment in Excel is only as strong as the audit trail behind it. The rating tells an examiner — or your board — what the institution’s BSA/AML risk exposure looks like. The workbook structure tells them whether to believe the rating.

Separate tabs for source data, segmentation, weighting, overrides, control effectiveness, residual scoring, and challenge documentation make every score traceable. They make the assessment reviewable by people who weren’t in the room when it was built. And they make the update process disciplined — instead of revising a narrative, you’re updating specific source data fields and running the calculation chain again.

The AML/BSA Risk Assessment Template includes a pre-structured workbook with all the tabs, fields, and formula logic described here — along with the segmentation thresholds and weighting methodology aligned to the FFIEC BSA/AML Examination Manual. Build the audit trail before the exam, not during it.