Loan Applicant Analysis Dashboard

Loan Applicant Analysis Dashboard Documentation

Introduction:

The Loan Applicant Analysis Dashboard is a tool developed by the Business Analyst at IJS Bank Limited to analyze and present data on loan applicants for the current quarter. This documentation provides an overview of the dashboard's features, the steps involved in creating it, and additional deliverables such as data summaries and VBA code.

Dashboard Features:

The Loan Applicant Analysis Dashboard includes the following features:

a. Gender Ratio of Applicants: This feature displays the ratio of male to female loan applicants.

b. Car Owner/Non-Owner Ratio: This feature shows the ratio of loan applicants who own a car to those who don't.

c. House Owner/Non-Owner Ratio: This feature presents the ratio of loan applicants who own a house to those who don't.

d. Client Income Type: This feature provides insights into the different income types of loan applicants.

e. Client's Level of Education: This feature presents the educational backgrounds of loan applicants.

f. Client's Housing Type: This feature displays the types of housing loan applicants reside in.

g. Client's Family Status: This feature provides information on the family statuses of loan applicants.

h. Client's Occupation Type: This feature presents the various occupation types of loan applicants.

Separate Worksheets:

To enhance data analysis and reporting, the following separate worksheets are included in the workbook:

a. Data Summary of All Female Clients: This worksheet summarizes the income and loan amount for all female loan applicants using new functions.

b. Data Summary of All Male Clients: This worksheet summarizes the income and loan amount for all male loan applicants using list functions.

c. Data Summary for Flag Columns: This worksheet provides a summary of all flag columns (e.g., FLAG_DOCUMENT_2, FLAG_DOCUMENT_3) using different variants of the DCOUNT function.

Other Deliverables: In addition to the dashboard, the following deliverables have been included:

a. Macro for Data Formatting: This macro automates data formatting tasks such as conditional formatting and freezing panes to maintain consistency across future quarters' data.

b. VBA Code for Converting Data to PDF: This VBA code allows users to select specific data and convert it into a PDF file for easy sharing and printing.

c. VBA Code for Spell Checking: This VBA code checks for spelling mistakes in the workbook to ensure an accurate and professional presentation.

Conclusion:

The Loan Applicant Analysis Dashboard provides a comprehensive view of loan applicants' data and facilitates data analysis and reporting. By using the features, worksheets, and additional deliverables, users can gain insights into various aspects of loan applicants and make informed decisions.