The Comprehensive
Capital Analysis and Review (CCAR) is a regulatory framework mandated by the
Federal Reserve and instituted in response to lessons learned from the
financial crisis. It aims to assess, regulate and supervise Bank Holding
Companies (BHCs). The main objective is to evaluate a large bank's capital
structure. This is done through a set of stress-test scenarios and involves
both qualitative and quantitative measures and intends to ensure each BHC
possesses adequate capital to continue operations throughout cyclical times of
economic ups and downs in the financial markets.

Several financial product controllers were tasked to implement this
and did so by creating a series of spreadsheets with up to a hundred
worksheets. The data was sourced from Essbase cubes and controllers lacked
knowledge on how to query it efficiently and creating reusable query templates
for subsequent periods. It was extremely challenging to navigate through all
workbooks as there was no special indication of what was input, output,
calculation, externally pasted data, etc. Each month certain lines of
businesses would change their place in the corporate hierarchy that drives this
process and controllers would have to manually go and update all relevant
sheet, inadvertently creating errors or inconsistencies they would not be aware
of until later in the process. Various spreadsheet templates of different formats
would be then sent to the lines of businesses, which, in turn, would provide
their projections. Those projections would then be received in email and
manually copied and pasted into the array of CCAR-based spreadsheets. In many
cases formats had changed to account for specifics done on the other end and
managing this process became a burden. This process is done on a quarterly
basis and requires a quick turnaround among the participating groups to meet
tight Federal Reserve report submission deadlines.

Solution

After doing the initial data analysis of the numerous it was
determined that each period controllers were using a template that was not
necessarily optimized for performance and data quality and they ended up
carrying an enormous amount of data throughout all steps of the process.
Reaching out to a few technology groups, the developer was able to get raw
extracts that were fed into an internal system and were therefore validated,
clean, accurate and detailed. These extracts included everything that the CCAR
process needed - reference data, economic data, relevant lines of businesses
cuts, etc. The first step was to standardize all templates sent out to the
lines of business by using scripting code to dynamically create custom data
entry templates for the projected values by each scenario (Base, Adverse, Severe
Adverse, etc. as mandated by the Federal guidelines). Once received, the lines
of businesses would gill in the templates and load them into the application.
The immediate impact was eliminating few steps of emailing non-validated Excel
files with poor data quality. Another benefit was the ability to execute this process
multiple times as it is an integral part of the CCAR process to have several
iterations till all parties of controllers and line of business managers have
agreed and substantiated the numbers. A robust database structure was then
implemented to store all data points. The application also provided the ability
to create ad-hoc reports and export then to Excel and save the report
definitions for further use by other users.