ATC: Business Analytics for Monitoring Financial Indicators of Credit Products in Peruvian Banking

Abstract

Keywords: Analytics, Banking, Bank Credit, Business Intelligence. 1.Introduction In Peru, at the end of 2019, the placements of credit products in the financial system were valued at 87,000 million of dollars and grew by 6.2% compared to the previous year (Fernández, Loo, Nureña & Roca, 2019), which highlights the importance of these in the economy of each banking institution. [...]it is important to effectively monitor credit product indicators and there is a need for information for strategic decisionmaking in the medium and long term (SPP Perú, 2017). [...]the conclusions follow in section 5. There are different entities that track monthly and/or annual credit placements using public information provided by banks, one of the reports is the Financial Stability Report, which shows the evolution of placements over the past 12 months using the disbursed amount and the interest rate as variables, and also shows the delinquency ratio and the credit/ gross domestic product ratio, known as the credit monetization ratio (BCRP, 2019). [...]the senior management visualizes the interactive dashboard with the generated report. 3.2.Model components Dashboard Management This module allows the administrator to configure the dashboard, that is, to be able to add new financial indicators to be measured, as well as publish new charts that show the evolution of such indicators over a period of time.

Headnote

Abstract: Monitoring credit product indicators in Peruvian banking is, generally, carried out semi-automatically, therefore its process can generate delays and induce errors in the financial analysis and, as a result, lead to untimely decision-making. In this work, a business analytics model is proposed to automate this process in Peruvian banks, which considers 4 components: Dashboard Management, Data Management, Visualizer and Information Display. The model is applicable to any bank for its simplicity and for the various existing technologies that facilitate its implementation. In addition, ATC (Analysis & Tracking of Credits) a web application that uses BI&A tools supported in the Cloud, is developed, showing 12 financial variables and 4 indicators of profitability and financial strength. Test with real data from a bank and expert judgement show a reduction in time from 29.7 hours to 4.3 seconds, and a positive impact on aspects such as usability and user experience.

Keywords: Analytics, Banking, Bank Credit, Business Intelligence.

1.Introduction

In Peru, at the end of 2019, the placements of credit products in the financial system were valued at 87,000 million of dollars and grew by 6.2% compared to the previous year (Fernández, Loo, Nureña & Roca, 2019), which highlights the importance of these in the economy of each banking institution. Therefore, it is important to effectively monitor credit product indicators and there is a need for information for strategic decisionmaking in the medium and long term (SPP Perú, 2017). In addition, it can be found that, in banking entities, data loading and extraction processes are carried out semimanually and informally and the information is available in different formats, including text files, which is why the analysis of credit indicators is incomplete and sometimes not appropriate.

Faced with such difficulties, business intelligence and analytics (BI&A) is an alternative that provides summary information and indicators to facilitate analysis and is highly supportive in decision-making. However, traditional BI&A solutions demand high deployment and maintenance costs and, given large volumes of data, can take considerable amount of time to process it. Given these restrictions, Cloud Computing technology and its on-demand services-oriented business model provides access to the resources needed for a low-cost BI&A solution (López Inga & Guerrero Huaranga, 2018).

There are studies of BI&A with Cloud Computing for banks in European (Šćekić, Gazivoda, Šćepanović & Nikolić, 2018) and Asian countries (Massardi, Suharjito & Utama, 2018) for the monitoring of credit products through financial indicators; however, in Peru, there is no precedent for such implementations in banks. This paper proposes a BI&A application on Cloud Computing oriented to the banking sector that allows it to process credit product placement data in real time and generate indicators that help the analysis and decision-making regarding the monitoring of credit products.

The article is distributed in 5 sections. In section 2, works related to credit and monitoring in banking and BI&A in the banking sector are reviewed. In section 3, the proposed BI&A model and implementation is described. The respective validation by expert judgement is presented in section 4. Finally, the conclusions follow in section 5.

2.Related Works

2.1. Credits and Monitoring in the Banking Sector

The Central Reserve Bank of Peru (BCRP) defines credit as "the economic transaction in which there is a promise of payment with some good, service or money in the future, in which the delivery of resources from one institutional unit (the creditor) to another unit (the debtor) is involved" (BCRP, 2011). Credits are classified into commercial credits, which are granted to natural and legal persons for the purpose of financing any trade activity; mortgage loans, intended for natural persons who want to acquire, build or remodel a property; consumer credit, granted to natural persons for any purpose (Resolución S.B.S. No 1343-2003, 2003). In 2019, the placement of credit in Peruvian banking was distributed as 52.69% for commercial credit, 21.13% for mortgages and 26.18% for consumption, totaling $6,840,838,695 (SBS, 2019).

On the other hand, it is stated that "any credit assessment process to a company or business is based on the analysis of the main financial indicators of the company (management ratios, indebtedness, liquidity and profitability)" (Huertas, 2015). There are different entities that track monthly and/or annual credit placements using public information provided by banks, one of the reports is the Financial Stability Report, which shows the evolution of placements over the past 12 months using the disbursed amount and the interest rate as variables, and also shows the delinquency ratio and the credit/ gross domestic product ratio, known as the credit monetization ratio (BCRP, 2019).

2.2. BI&A in the Banking Sector

Due to the synthesized information and indicators that can be obtained by BI&A, applied studies have been developed in banking. For example, in Indian banks, its application is explored using focused interviews and analysis of case studies, such is the case of HDFC Bank, which has pioneered the implementation of BI, thus managing to track the financial habits of customers and then use this information to promote their service offering (Mishra, 2016).

Given the categorization of a "bad bank" in Indonesia's financial system, when the 5% threshold of non-performing loans is exceeded, it is proposed to implement BI using the Kimball lifecycle to process all loan data stored in Excel sheets and finally present it on a loan collection dashboard (Susena et al., 2018). On the other hand, the main areas in a bank to apply BI are the risk area, the asset and liability management area and the compliance area (Ubiparipović & D strok signurković, 2011). Also, different BI&A technologies were investigated and a sales analysis process was simulated as a test in order to monitor indicators and increase the profitability of products (Bijakšić et al., 2017).

3.ATC Web Solution

3.1.ATC model

ATC (Analysis & Tracking of Credits), a BI&A model is proposed to automate the process of monitoring financial indicators of credit products in Peruvian banking, which is based on the following 4 components: Dashboard Management, Data Management, Visualizer and Information display (see figure 1).

The administrator has access to Dashboard Management, where he can add and remove Key Performance Indicators (KPIs), as well as modify the design of the charts for credit monitoring, which is done in coordination with the financial analysts (ANF). They interact with the "Financial Data" process to obtain the data of the credit placements in the established format and, through Data Management, such data is loaded and validated using business rules. If the data has inconsistencies, adjustments must be made, otherwise an Extraction, Transformation and Load (ETL) process will be performed in order to have a repository ready to be executed by the visualizer, a component that generates the dashboard according to the KPIs and graphs defined in Dashboard Management. This dashboard is submitted by the Information display module to the ANF to make a corporate report regarding the tracking of credit products. Finally, the senior management visualizes the interactive dashboard with the generated report.

3.2.Model components

Dashboard Management

This module allows the administrator to configure the dashboard, that is, to be able to add new financial indicators to be measured, as well as publish new charts that show the evolution of such indicators over a period of time. It is suggested to consider the different KPIs for the follow-up ofthe credits, such as amount disbursed, interest rate (TASOPE), spread (SPRNOR), funding rate (TASFON), regulatory capital (CR), cash available for distribution (CAD), opportunity cost of capital (COK), expected loss (PE), probability of default (PD), loss given default (lGd), return on regulatory capital (RORC), earnings before taxes (BAI), financial margin and income, return on economic capital (RAROEC), profit after tax (BDI) and the Profit and Loss Statement (P&L) report. These indicators should be defined according to the bank's needs and in coordination with financial analysts, taking into account the processes involved in credit monitoring, which are the following: budget planning, price management, credit product placement management, profitability management and income assurance.

Data Management

After obtaining the data of credit product placements from the "Financial Data" process, responsible for managing the information of the bank's servers, this module allows the loading of the data into the system and its validation, the latter is based on established business rules. The validation rules are conditions of consistency that the financial data must meet, some rules are given in table 2. After the data is validated, an ETL process is performed to deposit the data into a repository of dimension and measure tables.

Visualizer

This module receives the information generated by Data Management, the KPIs and charts defined in Dashboard Management, calculates such KPIs and updates the graphics for disposal by the Visualizer.

Information display

This module allows to visualize the charts with the information of the financial indicators of the credit products for their analysis by the ANF, who elaborates a corporate report. This module is also accessed by the Senior Management, who will be able to view the dashboard, as well as the corporate report, in order to make strategic decisions about the sales of credit products.

3.3.Implementation

In order to show the application of the proposed model, the processes of credit product placement management and credit indicators monitoring are considered, which provide data on credit and information placements for the Senior Management. ATC's physicallogical architecture was designed using the ArchiMate modeling language and features an application layer, a network layer and the following components: web application, Datamart, ETL process, and Power BI; it also presents the processes of data collection, preparing data for visualization and finally information display (see figure 2).

Application Layer

1.Data collection

We proceeded with the construction of the web application that will receive and validate the input data. For this, the programming language TypeScript was used, which is open source, object oriented and developed by Microsoft (Cherny, 2019). In addition, a logic was implemented that allows to locate the positions of the variables in the file, instantiate them and validate them. Once correctly validated, the data is uploaded to the DataMart through a connection to the database.

2.Preparing data for visualization

For the implementation of the application processes pertaining to the preparation of data for the process of tracking credit products (construction of the DataMart, ETL and pivot tables), the methodology of the Kimball lifecycle (Kimball et al., 2008), was followed.

* Definition of Variables

According to Choy et al. (2015) and in interviews with the ANFs, the following 12 variables were defined to help determine the cost of credit: interest rate, funding rate, spread, amount disbursed, expected loss (PE), term, commission, percentage discount, initial fee, balance, capital regulatory and economic capital. In addition, 4 financial ratios of profitability and financial strength were identified: return on regulatory capital (RORC), financial income, financial margin and earnings before taxes (BAI).

* Establishing the Level of Granularity, Dimensions and Measures

To determine the level of detail of the data to be stored in the Datamart, the level of granularity was established. In figure 3, on the right side are the quantitative measures, which represent indicators or variables, the dimensions and their attributes are observed on the left side. Then, the dimensional model was implemented in a star scheme, using Microsoft SQL Server.

* Implementation of the ETL

SQL Server Integration Services (SSIS) was used for the construction of the ETLs. As for ETLi, the first step is to complete the content of the dimension tables. For this purpose, data about the dimensions (Segment, Channel, Product, Client, Office, Agent and Contract) are extracted from text files shared by the bank daily and inserted into each table. Then, the Credit Placement Fact Table is processed and completed with the data obtained from the file loaded and validated in the system. Finally, the calculations of the BAI, RORC, Financial Margin and Financial Income indicators are made, which are also stored in the Fact Table. As for ETL2, it creates a pivot table for each customer type from the unified Datamart.

3.Information Display

Using the Microsoft Power BI tool, the charts for the monitoring of credit product indicators were designed. Various filters were added, such as period, area, product, currency, segment and territory. Billing amounts for new contracts, existing contracts and cancellations are shown, as well as the spread percentage, interest rate and DI over time (see figure 4).

In addition, profitability and financial strength indicators were included (see figure 5). Financial strength indicators show margin and financial income over time, and profitability indicators show the evolution over time of the Return on Regulatory Capital (RORC) and earnings before taxes (BAI) indicator.

Network Layer

The application was deployed using Git which features a fully automated deployment system and is able to ensure that the uploaded project is on the remote server (Chacon & Straub, 2014). Microsoft Azure cloud services were used for Datamart and dashboard storage, due to the benefits it offers such as scalability, pay-as-you-go, and ease of integration with Power BI and Microsoft SQL Server (Crump & Luijbregts, 2019).

4.Validation

The model is validated through its implementation with real data on monitoring a bank's credit products and expert judgment.

4.1. Case Study

The web application was validated in a bank with presence in more than 30 countries and is considered one of the largest banks in Peru. Currently, it has more than 4 million customers to whom it offers various types of loans such as mortgage loans, leasing and commercial loans, with a return of more than 20 billion dollars in 2019. As for the monitoring of credit product indicators, the bank does it semi-manually. Analysts collect data on credit placements from the Financial Data area and then establish the financial indicators and variables they will show. Finally, using excel sheets, they calculate the indicators and prepare statistical graphs with the results to present them in Microsoft Power Point along with a descriptive report of them, which are sent to the bank's senior management.

4.2. Experiment

The ATC model and application were presented to the executives of the Finance and Commercial Management area, who defined the indicators, variables, business rules and reports according to their needs. After the respective adjustments, training on the use of the application was given to 3 finance experts who work in the monitoring of credit products and 1 expert in BI technology, to whom access was provided (see table 3).

Then, each expert was assigned a one-month credit product monitoring task through the traditional bank process and via ATC. The transactions for the months of December 2019, March, April and May 2020 were assigned to experts E1, E2, E3 and E4, respectively (see table 3). For the follow-up, each expert loads the transaction file in ATC, which automatically performs the validation and then, through the dashboard, the corporate credit monitoring reports are obtained.

4.3.Metrics

We measured the time to obtain the financial reports with and without the application. Besides, a survey was conducted to measure the experts' perception of the usability, user experience, truthfulness and accuracy of the information provided by ATC (see table 4). The answers to the questions were rated according to the Likert scale (1: strongly disagree; 2: disagree; 3: neither agree nor disagree; 4: agree; 5: strongly agree).

Table 4 - Questionnaire

4.4.Results

Table 5 shows the times for the completion of the tasks assigned to each expert, following the usual form given at the bank and through ATC. A very significant reduction is observed in the time needed to perform the monitoring of credit products, reducing the average time to perform the four tasks assigned from 29.7 hours to 4.27 seconds.

The results of the ATC survey (see table 6) show that the experts' perception of the system's usability is rated at an average of 4.81, i.e. they strongly agree that access and navigation through the application is easy and uncomplicated. Likewise, the experts rate the user experience at 4.75, which means that they are very much in agreement with the satisfaction of the application and are willing to recommend it. In relation to the truthfulness of the information, it is qualified as high quality and reliable (rating of 5), however, it does not consider all the categories of the bank, so it is qualified with 4.75. With respect to the accuracy of the information, all the experts are in full agreement with the results presented by the application.

The results confirm that the application presents a dashboard that is easy to understand and a business analysis that provides enough indicators of profitability and financial strength. In addition, it was possible to determine that the information displayed satisfies the requests of the finance and business management in the banking sector.

5.Conclusions

In this work, a business analysis model has been proposed to automate the process of monitoring financial indicators of credit products in Peruvian banks, which considers 4 components: Dashboard Management, Data Management, Visualizer and Information Display. The model is applicable to any banking entity due to its simplicity and the various existing technologies that facilitate its rapid implementation. In addition, the model has been implemented through a web application called ATC, which uses BI&A tools supported in the Cloud, and which shows profitability and financial strength indicators that will allow financial analysts to quickly and accurately prepare financial analysis and strategies, and will enable senior management to make appropriate and timely decisions.

Application tests with real data from a bank and expert judgement show a very high rating for usability, user experience, truthfulness and accuracy of information. In addition, the results of the questionnaire show that the experts are totally willing to recommend ATC because of its friendly navigation and structure, as well as showing information that is reliable and of high quality. On the other hand, the time required by the web application to process the data and generate the credit monitoring reports is reduced from 29.7 hours, which the manual process had, to 4.3 seconds.

Finally, future work will be focused on including other financial products, such as savings, and new automatic processes through artificial intelligence, as well as predicting the level of credit placements with machine learning techniques.

References

References

Bijakšić, S., Markić, B., & Bevanda, A. (2017). Business intelligence and analysis of selling in retail. Informatol, 4(45), 222-231.

Chacon, S., & Straub, B. (2014). Pro Git (2nd Ed.). Apress. https://doi.org/10.1007/9781-4842-0076-6

Cherny, B. (2019). Programming TypeScript: Making Your JavaScript Applications Scale (1st Ed.). O'Reilly Media.

Choy, M., Costa, E., & Churata, E. (2015). Radiografía del costo del crédito en el Perú. Revista Estudios Económicos, 30, 25-55. https://ideas.repec.org/a/rbp/esteco/ ree-30-02.html

Crump M. & Luijbregts B. (2019), La guía de Azure para desarrolladores. http://download.microsoft.com/download/3/F/2/3F2FFA90-1B49-478A-919994106D5FB89A/Azure_Developer_Guide_eBook_es-ES.pdf

Fernández, I., Loo, C., Nureña, M., & Roca, R. (2019). Memoria Anual 2019. https://www.bbva.pe/content/dam/public-web/peru/documents/prefooter/ nuestro-banco/memoria-anual-2019.pdf

Huertas, D. (2015). La colocación de créditos mypes y la relación con el nivel de morosidad en el sistema bancario peruano del 2010 al 2014 [Degree Thesis in Economy, Economy and Finance department, Universidad de San Martín de Porres]. Universidad de San Martín de Porres repository. http://repositorio.usmp. edu.pe/handle/usmp/1536

Kimball, R., Ross, M., Thornthwaite, W., Mundy, J., & Becker, B. (2008). The Data Warehouse Lifecycle Toolkit (2nd Ed.). Wiley Publishing Inc.

López Inga, M. E., & Guerrero Huaranga, R. M. (2018). Modelo de inteligencia de negocios y analítica en la nube para pymes del sector retail en Perú. Ingeniería Solidaria, 14(24), 1-17. https://doi.org/10.16925/in.v14i24.2157

Massardi, A. E., Suharjito, & Utama, D. N. (2018). Business Intelligence Design of Rural Bank Performance Assessment Using Financial Ratio Analysis. In Proceedings of 2018 International Conference on Information Management and Technology, 143-148. https://doi.org/10.1109/ICIMTech.2018.8528107

Mishra, R. (2016). Business intelligence and analytics: Paving way for operational excellence in indian banks. International Journal of Management and Social Sciences, 53-58. https://doi.org/10.21013/jmss.v3.n1.p14

Šćekić, M., Gazivoda, M., Šćepanović, S., & Nikolić, J. (2018). Application of DevOps approach in developing business intelligence system in bank. In 2018 7th Mediterranean Conference on Embedded Computing, 1-4. https ://doi. org/10.1109/MEC0.2018.8406047

SPP Perú. (2017). Finanzas: Banco de Crédito de Perú. https://url2.cl/DuUtP

Resolución S.B.S. No 1343-2003 (2003) (testimony of Superintendencia de Banca y Seguros del Perú). https://intranet2.sbs.gob.pe/intranet/INT_CN/DV_INT_ CN/1078/v1.0/Adjuntos/1343-2003.r.pdf

Superintendencia de Banca y Seguros del Perú (2019). Boletín Informativo Mensual 2019. https://www.sbs.gob.pe/app/stats_net/stats/EstadisticaBoletinEstadistico. aspx?p=2#

Susena, K. C., Simanjuntak, D. M., Parwito, Fadillah, W., Yulyardo, & Girsang, A. S. (2018). Business Intelligence for Evaluating Loan Collection Performance at Bank. In 2018 International Conference on Orange Technologies. https://doi. org/10.1109/ICOT.2018.8705829

Ubiparipović, B., & D strok signurković, E. (2011). Application of Business Intelligence in the Banking Industry. Management Information Systems,