FinanceDev.com LLC United States>
Case Comprehensive Capital Analysis and Review (CCAR)
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.
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.
Technologies used: MS Access, Excel, SQL, VBA, Essbase