Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
by Mouna Belaid (@mouna_belaid) and Mokhtar Bichiou (@messi007)
Overview of the use case
The internal rating of counterparties is a crucial criterion in banks’ internal measures of credit risk. Banks should have the ability to identify, measure, and control credit risk. In addition, they should master how to hold adequate capital against these risks and how they are adequately compensated for risks that could happen.
An Internal Rating system corresponds to all the processes, methods, and controls that allow a more rational assessment of the risks incurred by the bank, which is through the assignment of ratings to the receivables constituting the loan portfolio. The validation of the risk rating assignment process by adopting supervisory practices is also essential for credit risk monitoring.
The committees responsible for the risk rating and its validation at the major European bank believe that the best way to achieve these objectives is through developing an analytical solution using Alteryx and Power BI. Alteryx has made it possible to analyze the ongoing evolution of credit risk ratings of counterparties.
Business challenges and problems to solve
The assessment of the financial and credit strength of the borrowers implied calculations of the Expected Credit Losses (ECL), which are based on several relevant numbers of financial ratios used in the internal credit risk rating system.
Three key elements enter into the calculation of ECL, i.e., the Probability of Default (PD) of the asset, which is estimated, the Loss Given Default (LGD), and the Exposure At Default (EAD).
In order to calculate the capital necessary to cover the unexpected credit losses, the Basel II risk-weighted asset (RWA) formula is calculated by applying internal ratings-based approaches.
We look to monitor the deterioration of ECL calculations due to the absence or expiry of the internal rating by making a TBQ-N, i.e., a dashboard for monitoring the quality of ratings, available to those who are responsible for the rating (commercial management team) and validation (Risks Cells) in order to prioritize rating and validation actions on every defined scope.
If some internal ratings are downgraded, that will define a deterioration in credit quality. Therefore, it would be highly required to check whether the credit rating system can discriminate between a good and a bad borrower and whether ratings have been calibrated to the expected probability of default. The validation committee should periodically validate the credit ratings based on various methods with the aim of maintaining the accuracy of the attribution of the internal credit ratings.
Working solution
The Alteryx workflow aims to monitor the deterioration of ECL calculations by monitoring the calculation of RWA. Input data should be first extracted from the storage scoring system of the major European bank and stored in the appropriate folder in SharePoint. Input files are a set of .csv files which contain information about the factors that serve to credit losses. Internal control practices should be applied by analyzing available data.
The analytical process includes data cleaning, data manipulation, and data aggregation operations in order to analyze each key borrower's characteristics as well as the evolution of RWA calculations. The process requires the bank to collect and store substantial historical data on borrower defaults, rating decisions, rating histories, rating migration, information used to assign the ratings, the model that assigns the ratings, PD estimate histories, key borrower characteristics, and facility information.
Handling large amounts of data can sometimes be challenging. Alteryx coped well with the big datasets we had. Once we had our output files generated and stored in SharePoint, our Power BI dashboard was automatically updated on our Power BI Report Server by getting our data sources refreshed.
Benefits achieved
Alteryx allows us to be equipped with a robust supervision solution of ECL calculations that reflect the bank's credit quality. Internal rating systems are a key component in banks’ credit risk models. The bank now has the appropriate capabilities to prioritize rating and validation actions through an automated solution that mirrors the ongoing evolution of credit risk management. The development of the interactive Power BI dashboard enhances the analytical power of the solution.
