Bank Loan Case Study

• By:- Pradnyashil A. Lonare

Project work for Data Analytics

• Project Description:-

• This case study aims to give you an idea of applying EDA in a real business scenario. In this case study, apart from applying the techniques that you have learnt in the EDA module, you will also develop a basic understanding of risk analytics in banking and financial services and understand how data is used to minimize the risk of losing money while lending to customers.

​

• Business Understanding:-

• The loan providing companies find it hard to give loans to the people due to their insufficient or non-existent credit history. Because of that, some consumers use it as their advantage by becoming a defaulter. Suppose you work for a consumer finance company which specialises in lending various types of loans to urban customers. You have to use EDA to analyse the patterns present in the data. This will ensure that the applicants capable of repaying the loan are not rejected.
• When the company receives a loan application, the company has to decide for loan approval based on the applicant’s profile. Two types of risks are associated with the bank’s decision:
• If the applicant is likely to repay the loan, then not approving the loan results in a loss of business to the company.
• If the applicant is not likely to repay the loan, i.e. he/she is likely to default, then approving the loan may lead to a financial loss for the company.���

�

• Business Objectives:-

• It aims to identify patterns which indicate if a client has difficulty paying their installments which may be used for taking actions such as denying the loan, reducing the amount of loan, lending (to risky applicants) at a higher interest rate, etc. This will ensure that the consumers capable of repaying the loan are not rejected. Identification of such applicants using EDA is the aim of this case study.
• In other words, the company wants to understand the driving factors (or driver variables) behind loan default, i.e. the variables which are strong indicators of default. The company can utilize this knowledge for its portfolio and risk assessment.
• To develop your understanding of the domain, you are advised to independently research a little about risk analytics – understanding the types of variables and their significance should be enough).

���

• Approach:-

• Get the dataset from the given description.
• Open dataset in Excel.
• Try to understand the problem and given questions.
• Approach to familiar with the attributes and validate given data.
• Create table format and use count if, min, max, avg, sum and other statistical formulas.
• Analyzed data by using pivot table and pivot chart.
• Solve the given questions step by step and create interactive graphs.
• Create excel file in csv format and create report for presentation.
• Finally, I attached to my answers to the file.

​

​

• Tech-Stack Used:-

• Insights and Results:-

• Current Applications:-

Task :- Present the overall approach of the analysis. Mention the problem statement and the analysis approach briefly

• The Bank’s Problem Statement is to identify the major causes of bank loan default. The knowledge will be used for risk assessment by the company. We have provided two datasets here.

1:- Application data.csv contains all of the clients information at the time of application. The information pertains to whether or not a client is having financial issues.

2:-Previous application.csv provides data from the clients previous loans. It indicates if the prior application was accepted, cancelled, refused or unused.

• Both Sets of data contained many undesired columns that will not be used for risk analytics, as well as many blanks. So I cleaned up the data.
• I split columns in the dataset based on two categories of variables-

1:- Categorical Variables- Gender, Name contract type, Income type, Education, Housing type.

2:- Numeric Variables- Age, days Employed, Amount income, Amount Annuity, Amount Credit.

• I completed full EDA on the present application and then on the previous application. Then in this report, I summarized the results of both applications and provided business insights.

Task :-Identify the missing data and use appropriate method to deal with it. (Remove columns/or replace it with an appropriate value)

• The Existing application sheet included 161 Columns.
• I deleted Columns with more than 5% blank data.
• I deleted large number of useless columns.
• To Eliminate blank Values, I used the COUTBLANK function.�

Task :- Identify if there are outliers in the dataset. Also, mention why do you think it is an outlier. Again, remember that for this exercise, it is not necessary to remove any data points.��

• Target vs Amount Credit

• Target vs Total Income:-

• Target vs Income Annuity:-

Task 4 :-Identify if there is data imbalance in the data. Find the ratio of data imbalance.

• Data imbalance occurs when data is disseminated in an unequal manner.

NAME CONTRACT TYPE:-�

• Gender:-

• Education Type:-

• Count Children:-

• Region Rating:-

Task:- Explain the results of univariate, segmented univariate, bivariate analysis, etc. in business terms.

Univariate Analysis:-

INFERENCE

• Individuals with higher incomes are less likely to apply for loans. The Credit amount of a bank loan is typically in the range of 45000 to 1045000. Those with 0 to 8 year of experience are the most likely to seek for loans. Individuals who own homes are more likely to seek for loans. Individuals who own homes are more likely to apply for loans than others. Those who married have taken out more loans. More loans have been requested by working people. Unaccompanied minors have requested for extra loans.

�

• Total Income:-

• Amount Credit:-

• Housing Type:-

• Family Status:-

• Income Type:-

• Name Suite Type:-

Bivariate Analysis:-

INFERENCE

• Customers who live in low-rating areas will have higher defaults. Individuals with lower incomes are more likely to default. Young people are more likely to default, and the trend of defaulters declines with age. Females are less inclined than males to have defaults. More defaults are predicted due to maternity leave and unemployment. Customers with more than five members in family are more likely to default on their bank loan. Customers with fewer educational qualifications are more likely to fail on a bank loan. Customers with hardly work experience are more likely to have defaults.

Task:- Find the top 10 correlation for the Client with payment difficulties and all other cases (Target variable).

• Target vs Region Rating Client:-�

• Target vs Amount Income:-

• Target vs gender:-

• Target vs Name Contract Type:-

• Target vs Flag own car:-

• Target vs Flag Own Reality:-

• Target vs Count Children:-

• Target vs Amount Goods Price:-

• Target vs Name family Status:-

• Target vs Name Housing Type:-

• Previous Application:-

Task:- Finding Outliers

Amount Application:-

​

Task:- Data Imbalance

Below are the Columns where data is unevenly distributed

• Amount Credit:-

• Last Application For previous Contract:-

• Sellerplace Area:-

Task:- EDA

• Univariate Analysis:-
• INFERENCE

Customers have largely chosen cash and consumer loans. The Majority of our clients are repeat customers. The majority of Current loan applicants are individuals who are applied for loans less than 10 month ago. More loans have been requested consumer gadgets.

Contract Type:-

​

• Name Client-

• Seller Industry:-

• Bivariate Analysis:-

INFERENCE:-

Customers who applied for than Rs. 3,50,000 will most likely be denied. The majority of loans sought for through Credit and cash agencies are cancelled. New clients are overjoyed because the majority of their loans were approved. Thus far, car loans have been denied. Loans made to MLM partner clients are likely to be cancelled. Virtually 80% of the loans were authorised, with a steady stream of rejections. Consumers loans have nearly no cancellations and the greatest approval rate. Several loans for the first selling place area group were cancelled. Clients who applied for another loan within 10 months of their previous loan are more likely to have it cancelled. Walk-in loans have a higher refusal rate.�

• Task:- Find the top 10 correlation for the Client with payment difficulties and all other cases (Target variable).
• Amount Credit vs Name Contract type vs Name Contract Status:-

• Amount Credit vs Name Contract status vs Weekday application process start

• Amount Credit Vs Name Contract Status vs Hour Application Process Start

• Amount Credit vs Name Contract Status vs Flag Last Application per Contract

• Amount Credit vs Name Contract Status vs cash Loan Purpose

• Amount Credit vs Name Contract Status vs Payment Type

• Amount Credit vs Contract Status vs Code Reject Reason

• Amount Credit vs Contract Status vs Type Suite

• Amount Credit vs Contract Status vs Client Type

• Amount Credit vs Contract Status vs Goods category

Summary:-

• Loan Highly Recommended Groups
• Previous application approved clients
• Married Clients
• Senior Clients
• More Educated Clients
• Customer with a high income
• Clients with a greater external source
• Females
• Customers with strong work Experience

• Loan High Risk Groups
• Clients that are unemployed
• Youth Clients
• Customers whose prior application were denied
• Customer with low income
• Clients with insufficient external sources
• Customers with little work Experience
• Customers on maternity leave
• Clients with a large number of family members

Thank You