Bank Data Preparation and Visualization
Malawi National Bank KPI Report
Malawi National Bank provides small loans in developing countries of Africa to assist people out of poverty. However, Malawi bank has found out that many of the loans that have been advanced have not been paid back. In order to help them in their situation of crisis, we have conducted an analysis on the bank’s current portfolio and assessed on the potential factors that caused the bank to deteriorate its performance.
In this report, we lay down some key trends and observations that were collected from our analysis to develop a key performance indicator of the portfolio’s performance.
Research and Methodology
We have collected the raw material from the bank, cleared the data by deleting some of the unwanted variables not suitable for our analysis and introduced some new variables which would give us a better understanding of the overall picture of the bank’s performance and performed analysis on the same.
SAS was used to generate some statistical outputs of the bank’s performance which are explained in the enclosed ‘Data Quality Assessment Report’ and thereby followed to generate some graphs using Tableau which gives us a visual representation of the bank’s overall performance.
Data Quality Assessment Report
Some extract of Raw dataset describing several characteristics of customer loan account for small bank are shown in Excel below:
Please note: data in the above analytical file are cleared by deleting some of the unwanted variables (highlighted in red) which are not suitable for our analysis.
Variables removed form Dataset:
· Variables not taken into consideration for analysis are as follows:
· Donor ID — since it contains NULL values which do not help in our analysis.
· CreditOfficerID — since it is not a clean data and contains a mixture of characters and numerical values not useful for our analysis.
· SectorID — since it contains NULL values which do not help in our analysis.
We have provided a descriptive statistics using SAS for each variable in the above mentioned dataset, along with some identification of any data quality concerns.
SAS outputs and interpretations for each variable are as follows —
Below are some statistical measures of remaining variables which we take into consideration for our analytical report.
Summary of Results
Following are some observations and corresponding recommendations to Malawi National Bank to increase their loan performance activity. Graphs below are generated using Tableau.
Malawi banks have a good trend of disbursement amount that a bank should follow.
From the above graph, we notice that general disbursement amount is between 60K and 120K.
Disbursement Amount by Purpose Code
Malawi banks disburses amount to various sectors such as ‘expanding business’, ‘land purchase’ etc. Graph below shows the trend of how much amount it disburses to each sector. Categories of each variable are defined below.
The categories of these variables are ‘1 — Expanding Business‘, ‘2 — Land Purchase‘, ‘3 –House Construction‘, ‘4 — Buying a Car‘, ‘5 — Business‘, ‘6 — School Fees‘, ‘7 — Buying a House‘, ‘8 — Paying School Fees ‘, ‘9 — Paying Medical Fees‘ , ’10 — Building House‘, ’11 — Buying Farm Inputs ‘, ’12 — Purchase of Household Items‘, ’13 — Other Purpose‘.
We observe from the graph that, 1 — Expanding Business takes the maximum loan from the bank about 62 milllion.
Recommendation:
We recommend that the Bank should also focus in distributing loans to other sectors. It is a risk for bank to focus on providing loans to one sector in particular.
Another graph that show various sectors to which Malawi banks have disbursed loan.
We notice that the bank provides maximum loan to Individual Business Loan and payroll secured loan (about 60M)
Recommendation:
To increase the overall revenue, bank can increase the overall loan disbursed to other products as well especially share loans. For example, Woman’s loan can be given more priority. Women applicants can be given priority.
Disbursement Amount by Branches
Malawi banks have various branches. However, some branches have disbursed more amount of money, while some other branches of Malawi have displayed less amount.
Graph tells us that Mandala and Blantrye branches have the highest amount of disbursements and customers. There is a huge gap between the amount disbursed by different branches. Other branches can be given some lucrative offers so that people can take loan from their branches, this in turn increases the overall revenue.
Recommendation:
It can be seen that the amount of loan given is almost 22M. This can be taken into consideration as there is a chance of defaulting the loan. This will lead to fall in revenue. Measures can be put in place to monitor loan applicants and their applications.
Arrears Amount and Disbursement
The main concern for Malawi is that the disbursement is at good rate, whereas, the arrears amount is not keeping the same trend as the disbursement. Graph below shows us the trend of arrears and disbursements.
Above graph shows us that trend from 2006 to 2012 on arrears amount and disbursement amount.
Until 2011, as the disbursement amount increases, the arrears amount also increased.
After 2011, as disbursement amount increases, the arrears decreased. This implies that quality of loan increases after 2011.
Recommendation:
Bank should draw out a plan and a strict discipline of collecting amount from customers, so that they don’t face the issues of arrears. They should emphasize on the interest rate incurred with the loan and the consequences that customers can face if payment of loans are not met on time.
Application Grade
We continue to explore the underlying performance that have to be taken into consideration. Application Grade is a letter grade of loan application quality (A — high quality and D — poor quality)
We can observer here, that we have less high quality loan application and more of poor quality loan observations. Majority of loans disbursed are of C and D grades (by around 70%)
Instalment Performance
The trend of instalment payments to the bank are shown below.
A shows instalment amount less 2750, B shows instalment amount between 2750 and 5500, C shows instalment amount between 5500 and 11000, D shows instalment amount greater than 11000
Paid Ratio Performance
Paid ratio of bank’s performance is shown below. The trend of how much money it collects from its customers. Paid ratio is defined as: (1-(ActualBalance/Disbursement_Amount))
· A shows that Paid_Ratio greater than 0.75
· B shows that Paid_Ratio between 0.75 and 0.5
· C shows that Paid_Ratio between 0.5 and 0.25
· D shows that Paid_Ratio less than 0.25
Recommendation:
Paid Ratio performance is extremely low which indicates that the bank has difficulty to collect money that was disbursed to the customer. Improvements must be done from the bank’s side to collect money on strict deadlines.
Conclusion:
In conclusion, based on the analysis and recommendations stated above, Malawi bank should lay down some strict measures to accommodate the participation of the customers and ensure that they pay back loans at strict deadlines. In this way, the arrears occurring to Malawi bank will be less and they get their payments of loan on time. At the same time, the key performance indicator of Malawi banks will rise up and there will be a better performance of bank’s portfolio.
In this way, we hope that Malawi banks will be happy and contented, at the same time, we ensure they meet customer’s satisfaction too.
