Situation
Article 100 of CRR mandates the EBA to develop reporting templates for all forms of asset encumbrance which will provide supervisory authorities with the necessary information on the level of asset encumbrance in institutions. The first part consists of the legal text to be incorporated into the full reporting framework, which already consists of COREP, FINREP, large exposures liquidity and leverage ratio reporting. The second part consists of reporting templates and instructions for the templates. Asset encumbrance reporting was implemented on 31 December 2014.
The customer was searching for an iterim solution and prototype for asset encumbrance reporting to the banking authorities. The solution shall be used for the first year of reporting, be adaptable to new sources of encumbrance quickly and serve as a prototype for final implementation of an own reporting solution. As the bank was currently building a central data pool and data source systems would be shut down and switch, the solution has to be flexible in terms of data import as well.
As a prototype for future implementation the solution must be transparent in terms of business logic. Every calculation step or data manipulation must be visible to the end user. At the same time, it has to comply with internal and external audit requirements. Especially manual corrections to the reporting templates must be logged and made transparent.
Project
Two months before the first reporting period, we started to develop an Excel prototype for importing different data sources (SAP, Kondor+ etc.), built a data model to link these data sources coming from several systems and process the data according to the implementing technical standards for asset encumbrance reporting.
With flexible import routines we were able to quickly switch from one data source to another as well as between different data containers (CSV, xls, Access or SQL data base). For each data source specific filters, mappings and up front calculations could be applied. For filtering and aggregation SQL like logic could be applied with simple Excel tables.
With regards to the data model we were able to enrich data with static data and even connect different data sources with different primary keys, which is essential to put assets and liabilities into a context. At the same time the solutions was able to produce output, which could again be imported to the primary systems of the customer and sent to national bank or European banking authority respectively.
All calculations were performed on worksheet level to safeguard readability for future implementation. More demanding calculations were coded in Visual Basic for Applications (VBA) and documented for further usage.
Audit requirements were fulfilled by restricting access to the Excel Tool and the data in the Tool by an active directory user list controlled by a central SQL data base. Different user roles were applied with respect to data access, visibility and editing. All triggered automatic procedures and manual data manipulations were logged and stored for audit purposes.
The final Excel Tool, program code and documentation was handed over to the customer to be implemented in the target systems.