Case Study 13.2: Rafael's Reporting Transformation — From Quarterly Chaos to Automated Pipeline

The Situation

Organization: Meridian Capital (fictional US broker-dealer and bank holding company subsidiary) Rafael's challenge: Transforming a fragile, manual regulatory reporting process into a resilient, automated pipeline — and doing so under regulatory scrutiny Timeline: 2022–2024 Regulatory backdrop: SEC (10-K/10-Q iXBRL), Federal Reserve (FR Y-9C, FR Y-14), FINRA (broker-dealer reports)

Background: The Crisis State

When Rafael Torres was appointed VP Compliance Technology at Meridian Capital in 2021, he inherited a regulatory reporting function that was nominally functional but operationally unsustainable.

Meridian's regulatory reporting obligations:

The process supporting this reporting:

Data extraction: Three analysts — Amelia, Devon, and Prashant — spent 3 weeks each quarter manually extracting data from seven source systems: the GL (Sage Intacct), loan management system (nCino), trade settlement system (FIS), brokerage platform (Pershing), treasury management (Kyriba), credit risk system (internal Python scripts), and a collateral management system (Excel-based).

Reconciliation: 14 Excel workbooks. Amelia was the only person who fully understood the logic of the most complex workbook — the one that reconciled GL classifications to FR Y-14Q credit risk categories. She had built it in 2019.

XBRL production: SEC iXBRL filings were produced by an external accounting firm (Deloitte) for an annual fee of $180,000. Deloitte received Meridian's Excel-populated financial statement templates and converted them to iXBRL. The turnaround was 5 business days.

Filing timeline: Q3 2022 had been filed with 4 hours to spare before the FR Y-9C deadline. During Q4 2022 filing preparation, Devon left Meridian — his departure, mid-filing cycle, required Amelia and Prashant to absorb his work in real time.

The Near-Miss That Triggered Change

Q4 2022 came dangerously close to a missed deadline. Meridian's FR Y-9C was due January 30, 2023. Devon's departure on January 10 required Amelia and Prashant to cover his data extraction and reconciliation responsibilities. They missed two reconciliation items that were caught only after the filing was transmitted — and the discrepancies were minor (£85,000 rounding between the GL and the regulatory data extract). But the experience shook Rafael.

He modeled the consequence of a missed Federal Reserve filing: a supervisory finding, potential civil money penalty, and regulatory reporting failure appearing in the Bank Holding Company Performance Report. "We were one person departure away from a regulatory enforcement action," he told the CFO.

The CFO approved a transformation budget of $2.4 million over 18 months.

The Transformation Program

Phase 1 (Months 1–6): Data Foundation

Rafael hired a data governance consultant (Sophie, from a Big 4 firm) and assigned Amelia to a 6-month project: document the complete data lineage for every regulatory data point across all six reports.

The output: a data dictionary of 847 distinct regulatory data elements, each with: - Authoritative definition (referencing the regulatory source document — specific form instruction, Basel text, or accounting standard) - Source system and source field - Transformation rules (any calculations, classifications, or mappings applied) - All reports in which the element appears

The inventory revealed: - 23 data points with inconsistent definitions between source systems (e.g., "commercial real estate" defined differently in the GL and the loan management system) - 11 data points with undocumented calculations (in Amelia's Excel workbook, only she knew the logic) - 6 data points appearing in multiple reports with inconsistent values (the same loan balance appearing with different amounts in FR Y-9C and FR Y-14Q)

Rafael established a Regulatory Data Committee — meeting monthly — with representatives from Finance, Risk, Operations, and Technology. The committee owned the data dictionary and resolved definition conflicts.

Technical implementation: A regulatory data store (RDS) was built on Snowflake. Automated daily ETL feeds from six source systems via API (replacing CSV downloads for four of seven systems; two legacy systems required flat-file feeds that were automated through scheduled batch processes).

Daily reconciliation: RDS balance sheet balances reconciled to the GL at close of business each day. Discrepancies above $10,000 generated an automated alert to Amelia.

Phase 2 (Months 7–12): Calculation Engine and Platform Selection

Rafael evaluated three regulatory reporting platforms:

Rafael selected Wolters Kluwer FRR. Key decision factors: pre-built FR Y-14Q credit risk schedule logic (saving estimated 6 months of custom development); Bank of England and EBA taxonomy support for future expansion; dedicated US regulatory team for annual update cycles.

Configuration required (4 months): - Mapping Meridian's organizational structure — three legal entities, two consolidation perimeters - Configuring Meridian's IRB credit risk model output integration (proprietary risk ratings feeding FR Y-14Q credit schedules) - Building FINRA FOCUS Report template (not pre-built in FRR; required custom template development) - Configuring SEC iXBRL output for financial statements (FRR's iXBRL module, replacing Deloitte)

Phase 3 (Months 13–18): Parallel Running and Go-Live

Two quarterly cycles ran in parallel — automated system and manual process simultaneously. The purpose: identify any discrepancy between the two outputs and investigate root cause.

Q2 2023 parallel run: 12 discrepancies identified. Root causes: - 7: Definition differences (the automated system used the authoritative definition; the manual process used an approximation) — the automated system was correct - 3: Legacy Excel formulas using rounded intermediate values — the automated system used unrounded values throughout (improved accuracy) - 2: Timing differences in how two source systems posted end-of-period transactions — resolved by standardizing the data cut-off time across all source feeds

Q3 2023 parallel run: 3 discrepancies — all minor timing differences. No material errors.

Go-live: Q4 2023 filing was the first live automated submission. The FR Y-9C was filed on January 22, 2024 — 8 days before the January 30 deadline. Analyst time: 1.2 analyst-weeks (vs. 9 analyst-weeks manually).

Regulatory Examination Interaction

In April 2024, Federal Reserve examiners conducted a targeted review of Meridian's regulatory reporting controls — unrelated to Rafael's transformation but coinciding with its completion.

The examiners reviewed: - Data lineage documentation (RDS to FR Y-9C/Y-14Q) - Calculation methodology documentation - Control environment (reconciliation, exception management) - System change management (how the new reporting platform was tested before go-live)

Examiner feedback: "The data dictionary and lineage documentation represent best practice for an institution of this size. The parallel running methodology and the Q2/Q3 discrepancy resolution documentation demonstrate appropriate validation rigor."

One finding: the FINRA FOCUS Report custom template had not been independently validated — it was configured by the implementation team and reviewed only by Amelia. The examiners expected at least one independent review of the template logic.

Remediation: Wolters Kluwer FRR's professional services team conducted an independent review of the FOCUS Report configuration. One mapping error was found and corrected (the "aggregate indebtedness" calculation had included a line item that should have been excluded under the FOCUS Report instructions). The correction reduced Meridian's reported aggregate indebtedness by $2.1 million — below all applicable thresholds.

Cost-Benefit Summary

Rafael's presentation to the board: "The financial payback is five years on a direct cost basis. But the risk-adjusted case is compelling: we were one analyst departure from a missed Federal Reserve filing. The reputational and regulatory cost of that outcome would have dwarfed the investment."

Discussion Questions

1. The data dictionary revealed that 23 data points had inconsistent definitions between source systems. For a regulatory reporting context, why is definitional consistency more important than it might be for internal management reporting? What is the consequence of submitting a regulatory report where "commercial real estate" means different things in two contributing source systems?

2. Rafael established a Regulatory Data Committee to own the data dictionary and resolve definition conflicts. What is the appropriate governance structure for this committee? Who should chair it? What decision rights should it have — can it override the Finance team's preferred definition if the regulatory definition is different?

3. The parallel running methodology identified 12 discrepancies in the Q2 2023 cycle, 7 of which revealed that the manual process had been using approximations rather than authoritative definitions. What does this finding imply about the accuracy of prior regulatory submissions? At what point does the organization have an obligation to notify the regulator of prior inaccuracies?

4. The Federal Reserve examiner found that the FOCUS Report custom template had not been independently validated. For a custom-built regulatory template (not pre-built by the vendor), what validation steps should be required? Who should perform the validation — internal staff, the vendor, or an independent third party?

5. Rafael's three-year platform license cost was $720,000 ($240,000/year). His analysis showed the direct payback period was approximately 5 years — longer than the license term. A CFO skeptic might argue the financial case is weak. How would you construct a more complete business case, including risk-adjusted benefits and qualitative factors?