Compliance Monitoring Plan in Excel: Convert the Risk Assessment Into a Defensible Test Universe

TL;DR

• A compliance monitoring plan template in Excel should start with a complete test universe, then show why each review was selected, deferred, or excluded.
• Every scheduled review needs a traceable obligation, risk, control, population, procedure, evidence source, owner, exception rule, and escalation path.
• The plan is not proof that monitoring happened. Link each calendar row to the completed workpaper, exceptions, issue record, and validated closure.

“Test Reg E in Q3” is not a compliance monitoring plan. It is a calendar reminder.

A defensible plan explains which Reg E obligation and product are in scope, what risk drove selection, which population will be reviewed, what evidence will be obtained, how exceptions will be judged, and what happens if the result fails. That detail matters most when you are the compliance hire who inherited a recycled spreadsheet and an examiner asks, “What led to this year’s monitoring plan?”

The answer should be in the workbook.

Start with the examiner request, not the calendar

The CFPB’s Compliance Management Review Examination Procedures, updated August 30, 2017 gives unusually concrete design requirements. In Module 2, the monitoring and audit procedures tell examiners to request the monitoring schedule, review applicable risk assessments that led to the plan, determine coverage of service providers, and evaluate whether monitoring covers tools, disclosures, notices, marketing materials, and scripts. The procedures also ask whether findings are escalated to management and the board as appropriate.

The same document distinguishes monitoring from audit: monitoring is generally more frequent and less formal and may be performed by the business; audit is generally less frequent, more formal, and independent. Small or low-complexity institutions may not have both functions, but examiners evaluate whether coverage is commensurate with size, complexity, and risk profile.

The FDIC Consumer Compliance Examination Manual, Section II-1 says its examination approach directs resources toward operational areas where compliance errors present the greatest potential for consumer harm. It also states that transaction count and sampling type should be relative to perceived consumer-harm risk and the need to assess compliance.

The OCC’s Compliance Management Systems Comptroller’s Handbook likewise reflects risk-based supervision. None of these sources gives you a magic “25 files quarterly” rule. They point toward a documented chain from risk to coverage.

That chain starts with the test universe.

Build a test universe before choosing the annual plan

A test universe is the complete inventory of things the organization may need to monitor or test. It is deliberately broader than this year’s calendar.

Use one row per testable unit, not one row per regulation. “Reg E” is too broad. Better units might include:

• Reg E initial disclosures for the consumer deposit product
• Unauthorized-EFT intake and provisional-credit timing
• Error-resolution investigation notices
• ATM and one-time debit overdraft opt-in
• Remittance transfer disclosures, cancellation, and error resolution
• Third-party dispute processor quality assurance

The universe should combine several inputs:

The practical aside: ownership gets ugly here. Legal may own the obligation inventory, product owns the catalog, operations owns procedures, vendor management owns third parties, and compliance owns none of the source systems. Assign a named role to each feed and a certification date. Otherwise “complete universe” becomes an annual scavenger hunt.

For the broader program design, use the risk-based compliance monitoring and testing guide. This article focuses on the workbook that makes the selection reproducible.

The workbook structure: universe, plan, execution, issues

A lean workbook can use six tabs:

1. Methodology — purpose, scope, roles, frequency logic, change triggers, and approval rules.
2. Test universe — every testable unit and its risk inputs.
3. Annual plan — selected reviews, rationale, timing, owner, and dependencies.
4. Review register — actual start/end dates and links to completed workpapers.
5. Exceptions & issues — exception disposition, escalation, remediation, and validation.
6. Change log & dashboard — additions, deferrals, overdue work, themes, and coverage gaps.

Keep detailed testing workpapers outside the planning workbook. Link to them with stable review IDs. The plan needs to remain readable; it should not become a landfill of transaction-level data.

Score selection transparently—without pretending it is science

A small team needs a repeatable way to decide what gets tested. Use weighted factors only as prioritization aids, not as automatic truth.

A workable starting model:

These ranges are not regulatory benchmarks. Calibrate them against the prior three to six months of internal plan decisions, then check whether the model would have prioritized known problem areas. If not, change the model—not the history.

Add a mandatory override field. A moderate numeric score may still require review because a new legal requirement takes effect, a board commitment exists, or a regulator required validation. Conversely, resource dependencies may force a deferral. Both judgments need documented rationale and approval.

An anti-gaming check: map every planned review back to a universe ID, and map every universe item marked “covered” to an actual review ID. This prevents teams from counting a broad thematic review as coverage of obligations the workpaper never tested.

What each annual-plan row must say

The annual-plan tab should contain enough information to hand the review to another qualified person without a reconstruction meeting.

Avoid putting “sample 25” into every row. The official sources above support risk-relative selection, not a universal sample size. Document the population first. Then choose full-population testing, random sampling, targeted judgmental selection, or analytics based on the objective and risk. The control testing techniques guide goes deeper on that decision.

A completed example: unauthorized-EFT provisional credit

Here is a realistic hypothetical plan row. It is not a claim about a real institution and the timing criteria must be checked against the applicable facts and current law before use.

The important move is the reconciliation. A clean sample from an incomplete report proves little. Before testing cases, reconcile the extracted population to an independent system total or case-status rollforward. Record filters, query time, source system, and report owner. “Data received from Ops” is not data lineage.

Define exception handling before the first file is opened

Teams often document test criteria carefully and improvise what happens after a failure. That is backward.

The plan should distinguish:

• Documentation gap: required evidence is missing, so compliance cannot conclude.
• Isolated execution error: the control failed for a case, with no current evidence of a broader cause.
• Systemic exception: common logic, configuration, training, ownership, or data weakness may affect a population.
• Potential violation or consumer harm: requires legal/compliance analysis and prompt corrective action under the institution’s procedures.
• Scope limitation: population or evidence was incomplete; the review conclusion is qualified.

Set escalation based on consequence and pattern, not merely error rate. One severe exception can matter more than ten clerical defects. If starter numeric thresholds are used, label them provisional, calibrate against internal history, and retain management’s ability to escalate based on judgment.

Every exception needs a disposition. Every issue needs an owner and due date. Every closure needs independent or appropriately segregated validation. A finding marked “fixed” because the owner said so is how repeat findings are born.

The issue-management KRI guide explains why reopened items and weak closure validation belong in program reporting.

Manage changes to the plan like decisions, not spreadsheet edits

The annual plan will change. A product launch slips. A regulator announces a new requirement. A monitoring analyst leaves. A complaint spike forces an off-cycle review.

Use a change log with:

• plan or universe ID;
• prior and new scope/date/status;
• reason;
• risk impact;
• substitute coverage, if any;
• requester;
• approver;
• decision date; and
• follow-up trigger.

“Moved to Q4” is incomplete. Better:

Deferred from August to October because the disputes platform migration changes the source population on September 15. Compliance approved the deferral on July 22. Weekly complaint monitoring and a pre-migration exception report remain in place. Review scope will cover both legacy and new platforms.

That note shows the dependency, the risk decision, the compensating activity, and the revised scope.

A practical first-week build

If the regulator asked for the plan you do not have, resist the urge to produce a twelve-month calendar on day one.

Day 1: Gather the current risk assessment, product list, obligation inventory, prior testing, open issues, complaints, and vendor list. Assign an owner to each missing source.

Day 2: Build the universe at a testable-unit level. Reconcile it to products and processes. Flag unknown applicability rather than guessing.

Day 3: Load risk factors and prior coverage. Record source IDs and dates. Run a challenge meeting with Compliance, Legal, Operations, and Product for the top-ranked items.

Day 4: Select the next quarter’s plan. Write objectives, populations, evidence sources, exception criteria, owners, and dependencies for each review.

Day 5: Approve the plan, log exclusions and deferrals, assign review IDs, and publish a dashboard showing selected coverage, high-risk gaps, overdue work, and unresolved data dependencies.

The deliverable is not “Excel completed.” It is a documented selection process and a plan the team can execute.

So what?

Take the first five rows of the current monitoring calendar. For each row, ask:

1. Which universe item does this cover?
2. Which approved risk assessment or trigger selected it?
3. What exact conclusion will the review support?
4. Where will the population come from, and how will completeness be checked?
5. What creates an exception, and who receives it?
6. Where is the completed workpaper and remediation trail?

Any blank answer is a build requirement—not a wording problem.

The GRC Starter Kit provides the connected risk, control, issue, and governance foundation for a small team that needs the monitoring plan to feed a real program rather than another isolated spreadsheet.