1 of 33

Bank Loan Case Study

2 of 33

INTRODUCTION

A bank loan case study typically explores a scenario where a borrower seeks financing from a bank to meet their financial needs, such as starting a business, purchasing a property, or funding a project.

The bank then evaluates the borrower's creditworthiness based on various factors, such as their credit history, income, assets, and liabilities, to determine whether to approve the loan application and under what terms.

The case study focus on the bank's lending policies, the borrower's financial situation, the loan application process, the loan agreement terms and conditions, and the outcome of the loan, among other factors.

It also examine the risks and benefits for both the borrower and the bank, as well as the broader economic and regulatory context.

3 of 33

SOFTWARE AND TOOLS USED

  • Microsoft Excel
    • Microsoft Excel is a spreadsheet software developed by Microsoft that allows users to organize, analyze, and manipulate data in a tabular format. It provides a range of features for creating spreadsheets, calculating formulas, and generating charts and graphs to visualize data.
    • Some of the key features of Excel include:
      1. Cell-based calculation: Users can enter data into individual cells, and Excel can calculate formulas based on that data.
      2. Formatting options: Users can format the cells and data in a variety of ways, including changing the font, color, size, and alignment.
      3. Charts and graphs: Excel allows users to create charts and graphs to visually represent the data.

​

4 of 33

APPROACH

  • Before commencing data analysis, it is essential to conduct a thorough examination and understanding of the different columns present in the provided dataset.
  • This initial step helps to identify the various variables, their types, and their significance in the overall data analysis process.
  • By carefully analyzing the data, it is possible to distinguish between continuous and categorical variables, and gain insight into the relationships between different variables and their impact on the data analysis process.

5 of 33

PROBLEM STATEMENT

  • When a company receives a loan application, it must evaluate the applicant's profile to decide whether to approve the loan. The company faces two types of risks when making this decision.
    1. The first risk is that if the applicant is likely to repay the loan but the loan is not approved, the company loses potential business.
    2. The second risk is that if the applicant is unlikely to repay the loan and defaults, approving the loan can result in a financial loss for the company.
    3. Therefore, the company must carefully consider all relevant factors before making a decision to approve or reject a loan application.

​

6 of 33

DATA UNDERSTANDING

  • The provided data pertains to loan applications and includes two scenarios.
    1. The first scenario involves clients who have experienced payment difficulties, as evidenced by a late payment of more than X days on at least one of the first Y instalments of the loan within the sample.
    2. The second scenario encompasses all other cases where the payment has been made on time.
  • The analysis was conducted using three CSV files.
    • application_data.csv contains all the information of the client at the time of application. The data is about whether a client has payment difficulties.
    • previous_application.csv contains information about the client’s previous loan data. It contains the data whether the previous application had been Approved, Cancelled, Refused or Unused offer.
    • columns_descrption.csv is data dictionary which describes the meaning of the variables.

�

7 of 33

ANALYSIS

  • To prepare the data for analysis, the first step was to remove columns that were either irrelevant or had a significant number of missing values.
  • Additionally, the number of blank or missing values within the columns was counted, and the percentage of such values was calculated.
  • Next, for columns that were important for analysis but contained blank values, a decision was made to replace these with 0, which was accomplished using an "if" formula.
  • By taking these measures, the completeness of the dataset could be assessed, and strategies could be developed to address missing data in a meaningful and appropriate way. This process is an essential part of data cleaning, and it helps to ensure that the results of the analysis are accurate and reliable.

8 of 33

9 of 33

10 of 33

OUTLIERS

11 of 33

12 of 33

13 of 33

DATA IMBALANCE

14 of 33

15 of 33

16 of 33

17 of 33

18 of 33

19 of 33

TYPES OF ANAYSIS

  • Univariate analysis: Univariate analysis involves analyzing a single variable to understand its distribution, central tendency, and dispersion.
  • Segmented univariate analysis: Segmented univariate analysis involves analyzing a single variable based on different segments or groups.
  • Bivariate analysis: Bivariate analysis involves analyzing the relationship between two variables.

20 of 33

UNIVARIATE ANALYSIS

21 of 33

DEFAULTERS ON THE BASIS OF GENDER

22 of 33

�DEFAULTERS ON THE BASIS OF EDUCATION

23 of 33

24 of 33

25 of 33

26 of 33

27 of 33

RISK FACTOR ON THE BASIS OF REGION RATING

28 of 33

AMOUNT CREDITED IN DIFFERENT TYPES OF LOANS

29 of 33

30 of 33

31 of 33

TOP CORRELATIONS

32 of 33

33 of 33