1 of 17

BANK LOAN CASE STUDY�

Final Project - 2

2 of 17

AGENDA

  • Project Description
  • Approach
  • ​Tech-Stack Used
  • Insights
  • ​Results

​

3 of 17

PROJECT DESCRIPTION

  • We have been provided with the Bank loan case study database which describes the data of the clients with payment difficulties and all the other cases in which the payment is paid on time.
  • In this case study, we use EDA to understand how consumer attributes and loan attributes influence the tendency of default.
  • In this project we will have to analyse the data, clean the junk and use only required data given to get the results.
  • Through this project we were able to research about the ‘Risk Analytics’ factor

​

3

4 of 17

APPROACH

  • DATABASE:

�          The given database was downloaded, all the junk data have been cleaned and then created a database to work on.

​

  • ANALYSIS:

I used MS Excel to get results for the questions asked.

�

​

4

5 of 17

TECH-STACK USED

  • I used MS Excel to analyse all the data because it is more convenient to get the results which were asked.

​

5

6 of 17

INSIGHTS

  • Applying EDA in a real business scenario
  • Basic understanding of risk analytics in banking and financial services 
  • Understanding how data is used to minimize the risk of losing money while lending to customers.
  • The explanation of the variables in the form of graph gives us a quick grasp about how well the existing clients are in their bank. Through all these results it’d much helpful for the banks to analyse whether their future applicants are capable of repaying the loan are not.

6

7 of 17

RESULTS

  • The usage of Excel in big projects makes us to get a fulfilled knowledge in it.
  • Research of Risk Analytics in banking and Financial services was challenging.

7

8 of 17

RESULTS FOR THE DATA SET

9 of 17

OVERALL APPROACH

The given data was downloaded and opened in MS Excel. By analysing the questions asked the junk data were cleared and used only the required fields to get the results.

Identification of applicants who has difficulties in paying their installments using EDA is the aim of this case study.

We performed analysis through pivot tables in MS Excel to get the results and summarized it with graphs which explain the numerical/categorical variables.

​

9

10 of 17

OUTLIERS FOR AMOUNT CREDIT IN PREVIOUS_APPLICATION

10

11 of 17

OUTILIERS FOR AMOUNT APPLICATION IN PREVIOUS_APPLICATION

11

12 of 17

UNIVARIATE IN COUNT OF CHILDREN

12

Row Labels

Count of CNT_CHILDREN

0

215371

1

61119

2

26749

3

3717

4

429

5

84

6

21

7

7

8

2

9

2

10

2

11

1

12

2

14

3

19

2

13 of 17

UNIVARIATE IN COUNT OF EDUCATION TYPE

13

Row Labels

Count of NAME_EDUCATION_TYPE

Academic degree

164

Higher education

74863

Incomplete higher

10277

Lower secondary

3816

Secondary / secondary special

218391

14 of 17

BIVARIATE FOR CASH LOANS AND REVOLVING LOANS

14

Row Labels

0

1

Cash loans

255011

23221

Revolving loans

27675

1604

15 of 17

BIVARIATE FOR OCCUPATION TYPE

15

Row Labels

0

1

Accountants

9339

474

Cleaning staff

4206

447

Cooking staff

5325

621

Core staff

25832

1738

Drivers

16496

2107

High skill tech staff

10679

701

HR staff

527

36

IT staff

492

34

Laborers

49348

5838

Low-skill Laborers

1734

359

Managers

20043

1328

Medicine staff

7965

572

Private service staff

2477

175

Realty agents

692

59

Sales staff

29010

3092

Secretaries

1213

92

Security staff

5999

722

Waiters/barmen staff

1196

152

16 of 17

CORRELATION

16

​

CNT_CHILDREN

AMT_INCOME_TOTAL

AMT_CREDIT

AMT_ANNUITY

REG_REGION_NOT_LIVE_REGION

REG_REGION_NOT_WORK_REGION

LIVE_REGION_NOT_WORK_REGION

REG_CITY_NOT_LIVE_CITY

REG_CITY_NOT_WORK_CITY

LIVE_CITY_NOT_WORK_CITY

CNT_CHILDREN

1

0.027397188

0.003081225

0.020905318

-0.012342406

0.010856623

0.017326477

0.021586823

0.072192663

0.070987918

AMT_INCOME_TOTAL

0.027397188

1

0.34279945

0.418952886

0.06850953

0.137173505

0.127701208

0.010566688

0.017617898

0.020684033

AMT_CREDIT

0.003081225

0.34279945

1

0.771308946

0.024616818

0.053735255

0.054250263

-0.025035944

-0.015702549

0.00250612

AMT_ANNUITY

0.020905318

0.418952886

0.771308946

1

0.041991896

0.080665424

0.075486712

-0.005674563

0.001519501

0.010581683

REG_REGION_NOT_LIVE_REGION

-0.012342406

0.06850953

0.024616818

0.041991896

1

0.446100857

0.090078763

0.341570626

0.142050766

0.011349543

REG_REGION_NOT_WORK_REGION

0.010856623

0.137173505

0.053735255

0.080665424

0.446100857

1

0.861861361

0.152170279

0.239860165

0.197219848

LIVE_REGION_NOT_WORK_REGION

0.017326477

0.127701208

0.054250263

0.075486712

0.090078763

0.861861361

1

0.023325463

0.186481045

0.235986383

REG_CITY_NOT_LIVE_CITY

0.021586823

0.010566688

-0.025035944

-0.005674563

0.341570626

0.152170279

0.023325463

1

0.43551371

0.031164813

REG_CITY_NOT_WORK_CITY

0.072192663

0.017617898

-0.015702549

0.001519501

0.142050766

0.239860165

0.186481045

0.43551371

1

0.830381135

LIVE_CITY_NOT_WORK_CITY

0.070987918

0.020684033

0.00250612

0.010581683

0.011349543

0.197219848

0.235986383

0.031164813

0.830381135

1

17 of 17

CORRELATION MAP

17

​

CNT_CHILDREN

AMT_INCOME_TOTAL

AMT_CREDIT

AMT_ANNUITY

REG_REGION_NOT_LIVE_REGION

REG_REGION_NOT_WORK_REGION

LIVE_REGION_NOT_WORK_REGION

REG_CITY_NOT_LIVE_CITY

REG_CITY_NOT_WORK_CITY

LIVE_CITY_NOT_WORK_CITY

CNT_CHILDREN

1

0.027397188

0.003081225

0.020905318

-0.012342406

0.010856623

0.017326477

0.021586823

0.072192663

0.070987918

AMT_INCOME_TOTAL

0.027397188

1

0.34279945

0.418952886

0.06850953

0.137173505

0.127701208

0.010566688

0.017617898

0.020684033

AMT_CREDIT

0.003081225

0.34279945

1

0.771308946

0.024616818

0.053735255

0.054250263

-0.025035944

-0.015702549

0.00250612

AMT_ANNUITY

0.020905318

0.418952886

0.771308946

1

0.041991896

0.080665424

0.075486712

-0.005674563

0.001519501

0.010581683

REG_REGION_NOT_LIVE_REGION

-0.012342406

0.06850953

0.024616818

0.041991896

1

0.446100857

0.090078763

0.341570626

0.142050766

0.011349543

REG_REGION_NOT_WORK_REGION

0.010856623

0.137173505

0.053735255

0.080665424

0.446100857

1

0.861861361

0.152170279

0.239860165

0.197219848

LIVE_REGION_NOT_WORK_REGION

0.017326477

0.127701208

0.054250263

0.075486712

0.090078763

0.861861361

1

0.023325463

0.186481045

0.235986383

REG_CITY_NOT_LIVE_CITY

0.021586823

0.010566688

-0.025035944

-0.005674563

0.341570626

0.152170279

0.023325463

1

0.43551371

0.031164813

REG_CITY_NOT_WORK_CITY

0.072192663

0.017617898

-0.015702549

0.001519501

0.142050766

0.239860165

0.186481045

0.43551371

1

0.830381135

LIVE_CITY_NOT_WORK_CITY

0.070987918

0.020684033

0.00250612

0.010581683

0.011349543

0.197219848

0.235986383

0.031164813

0.830381135

1