Federal Reserve Regulatory Reporting Suite
The cases below will highlight how a single application done with flexibility and dynamic pattern coding allowed leveraging its design tables and code base to provide a solution to another problem solved manually. Due to the iterative process of gathering requirements and building of logical blocks the application leveraged previously designed modules to build and maintain additional functionality. The approach allowed handling the creation of trend analysis that was not required initially. These cases are a great testament of how building logical blocks allows for quick turnaround when requirements change or unanticipated scenarios occur while developing solutions.
Case FFIEC101 Regulatory Reporting (Phase 1)
Within CIB Treasury controllers were using a set of intricate Excel spreadsheets located in various directories with multiple worksheets, formulae, pivot tables and pasted values to assess and monitor the levels and components of each reporting entity's risk-based capital requirements and adequacy under the Advanced Capital Adequacy Framework and report its findings to senior management as well as to the Federal Reserve. These reporting schedules assist in understanding expectations necessary to validate the rules of the Advanced Capital Adequacy Framework. The process took several iterations to complete due to the lack of referential data integrity and validation and controllers would finish their reporting in 5-7 business days.
Each reporting entitys data was saved in a designated folder accessible by all controllers responsible for providing their cuts with predefined meaningful names indicating the name of the entity and valuation period. Due to constant upstream changes the inputs formats could be different which made it extremely difficult to work with the data. Therefore, all data was imported into a Microsoft Access database using a supporting field management file maintained by the controllers which allowed them to map the changing inputs into a predefined master table indicating the source and including the meaningful financial parameters. Separate user-maintained reference files were also created to facilitate a process that required filtering certain data points in each reporting entity based on constantly changing rules mandated by the Federal Reserve. The elegance of the MS Access solution and its underlying VBA scripting language was that it granted the user unlimited opportunities to edit and save business logic without having to ask the developer to make coding and design changes. The application had a set of 10-12 reference support files that had been set up to allow the controllers to accomplish their task seamlessly and intuitively. The benefit of this approach was that controllers could set up the logic pertaining for each of the Schedules (A, B, C, D, E, F, G, I, J, P, Q, R) per risk entity per valuation/reporting period, add financial parameters (Exposure At Default, Risk-Weighted Average, Balance Sheet Amount, etc..) and run the application for any Schedule and any period at the click of a button. The solution provided transparency of all business rules used for each reporting entity that allowed controllers to answer questions or investigate issues almost immediately. The automated process took about 10-15 minutes to complete and exported all results to a pre-defined pivot tables that allowed drill-through capabilities along for audit purposes.
Case HC-R, RC-R Regulatory Reporting (Phase 2)
As of Q1, 2015 all bank holding companies (BHCs) must complete schedules HC-R and R-CR pertaining to regulatory capital rules mandated by the Federal Reserve. Treasure controllers used a set of manual processes, offline spreadsheets, saved emails, to create the report
Leveraging design and coding work done to automate FFIEC101 Regulatory Reporting it became obvious this solution would follow the same design patterns as it sourced its data from another set of the same reporting entities but saved in different file formats. Since the tool granted controllers the flexibility to maintain file formats in the field management reference file the only work left was to design a filter management process for H-CR and R-CR as it differed fundamentally from FFIEC101. Similar to FFIEC101 a pivot table was created to allow drill-through capability for further analysis.
Case Internal FFIEC101/HC-R, R-CR Reconciliation (Phase 3)
Separate group of controllers existed to provide an independent view of capital requirements that were sent to the Federal Reserve in strict templates set up by the Federal Reserve in order to have a standard reporting platform among all banks. Senior management required the reconciliation of the two sets of reports to avoid misrepresentations in the external reporting. Due to the structure and format of the templates controllers had to manually create literally hundreds and thousands of formulae to accomplish their task and the smallest change would require revising/verifying this large amount of manually set up formulae.
After analyzing the independently produced data, it became clear that a flexible solution was required to minimize the input changes controllers had to make each period and provide them with a predictable and intuitive mapping file they could easily maintain. Definitions to uniformly map the two datasets were set up per schedule, period, financial parameter and other applicable data points. The application seamlessly imported the data provided by the independent group at which point it was very easy to reconcile and provide an exceptions report. Pinpointing the exact error, controllers would be able to quickly investigate and resolve the issue and rerun the process.
Case FFIEC101/HC-R, R-CR Quarter on Quarter Trend Analysis (Phase 4)
Due to tight deadlines to produce the absolute minimum of federally mandated reporting requirements, controllers rarely had the time to further analyze the underlying data and provide more meaningful insights to senior management. Often times, when an urgent question arose, controllers would scurry around and collect what they felt was necessary from the dozens of scattered spreadsheets, emails and notes to come up with a plausible answer that could hardly be substantiated if further questions were asked. It became apparent that the design work to streamline all reporting entities would allow controllers to focus on analyzing the data as opposed to collecting it, mapping it and trying to make sense out of it. Decision was made to create quarter on quarter trending reports.
During the previous development phases al the work to import all relevant reporting entities was completed along with the flexible business logic modules to convert various inputs into a meaningful standard master input. Trending analysis simply became creating some custom reports and charts. It saved controllers days of work and provided them with the piece of mind that they were working with validated, correct, auditable data and could focus on adding value based on economics and not on data clean-up and manipulation.
Case Regulatory Capital Attestation (Phase 5)
Treasury controllers preparing, validating and creating the regulatory capital reports were also responsible for creating cuts of the reports relevant to hundreds of lines of businesses within the bank for attestation purposes. This was another manual task they had to complete and involved creating and saving hundreds of spreadsheets, sending emails with instructions where the spreadsheet is saved, which worksheet is theirs, etc. Lines of businesses would write commentaries and sign-off and send information back in email or save it somewhere on the spreadsheet and then notify the controllers. The controllers would then manually aggregate all attestations for further management reporting and sign-offs. This process would take days, weeks and data would be scattered again in many different places.
Since the application stored all information it was simply a matter of creating an interface to expose it to the lines of businesses and allow them to view their cuts dynamically. To keep everything within this platform the developer designed functionality for the lines of businesses to save commentaries and sign-off. This was immediately accessible to the treasury controllers who would refresh the attestation screen and view the current status. Leveraging work done in blocks and modules allowed the design and implementation of a sleek, flexible solution that kept all documentation and audit trail in the same database.