Overview

The International Bank of Reconstruction and Development (IBRD), a lending arm of the World Bank Group, offers loans to middle-income developing countries. For this case study, we will assume that the IBRD is a private company seeking acquisition. The task is to clean, process, and analyze the underlying loan portfolio to provide insights and visualizations.

Data Provided

1. Data Dictionary: A list of columns and their definitions.
2. IBRD-Statement-of-Loans-Latest-Available-Snapshot: A point-in-time snapshot of the historical loan portfolio.

Dataset Description

The dataset contains the following columns:

1. End of Period: The reporting period’s end date for the loan data.
2. Loan Number: A unique identifier for each loan.
3. Region: The geographic region where the loan was issued.
4. Country: The country where the borrower is located.
5. Borrower: The entity that received the loan.
6. Guarantor: The entity guaranteeing the loan.
7. Loan Type: The type of loan issued (e.g., commercial, personal).
8. Loan Status: The current status of the loan (e.g., active, repaid, defaulted).
9. Interest Rate: The interest rate applied to the loan.
10. Project ID: A unique identifier for the project associated with the loan.
11. Project Name: The name of the project funded by the loan.
12. Original Principal Amount: The initial amount of the loan.
13. Canceled Amount: The portion of the loan that was canceled.
14. Undisbursed Amount: The amount of the loan that has not yet been disbursed.
15. Disbursed Amount: The amount of the loan that has been disbursed.
16. Repaid to IBRD: The amount repaid to the International Bank for Reconstruction and Development.
17. Due to IBRD: The amount is still due to the International Bank for Reconstruction and Development.
18. Exchange Adjustment: Adjustments made due to exchange rate fluctuations.
19. Borrower’s Obligation: The total obligation of the borrower, including principal and interest.
20. Sold to 3rd Party: The portion of the loan sold to a third party.
21. Repaid to 3rd Party: The amount repaid to the third party.
22. Due to 3rd Party: The amount is still due to the third party.
23. Loans Held: The current loans held by the company.
24. First Repayment Date: The date of the first repayment.
25. Last Repayment Date: The date of the last repayment.
26. Agreement Signing Date: The date the loan agreement was signed.
27. Board Approval Date: The date the loan was approved by the board.
28. Effective Date (Most Recent): The most recent effective date of the loan.
29. Closed Date (Most Recent): The most recent closed date of the loan.
30. Last Disbursement Date: The date of the last disbursement.

Click here to access the dataset

IBRD statements  dataset

Objectives

1. Data Cleaning and Preparation: Identify and address data issues, clean fields, and convert them to appropriate data types.
2. Borrower Concentration Analysis: Determine the percentage of borrowers that account for 80% of the loan volume and create a Pareto chart.
3. Annual Cohort Analysis: Analyze loan cohorts from 2010-2020 based on the “Effective Date (Most Recent)” and determine the percentage of loans open for years 1-10.
4. Seasonality Analysis: Identify instances of seasonality and adjust the data for seasonality. Create visualizations to compare the Original Principal Amount before and after adjustment.
5. Interest Rate Analysis: Identify factors influencing a loan’s interest rate and analyze specific loan characteristics associated with higher interest rates using statistical methods.

Tools Required

• Alteryx: This is for data cleaning, processing, and analysis workflow.
• Tableau: For creating interactive visualizations.
• Excel: As an alternative for data analysis and visualization if Alteryx/Tableau is not available.

Task Breakdown

1. Data Cleaning and Preparation:

       Questions to ask about the dataset:

• Are there any missing or inconsistent data entries?
• Are all fields correctly formatted?
• Are there any outliers or anomalies in the data?

         Clean the dataset:

• Address missing values and inconsistencies.
• Convert fields to appropriate data types (e.g., dates, numerical values).
• Standardize text entries for consistency.

         Summary of findings:

• Highlight any data issues discovered and describe the steps taken to clean the data.

     2. Borrower Concentration Analysis:

• Determine the concentration of borrowers.
• Calculate the percentage of borrowers accounting for 80% of the loan volume.
• Create a Pareto chart to visualize borrower concentration.

        Summary of findings:

• Describe the borrower concentration and the implications for the loan portfolio.

     3. Annual Cohort Analysis:

• Define loan cohorts based on the “Effective Date (Most Recent)”.
• Determine the percentage of loans open for each year from 2010-2020 using the “Closed Date (Most Recent)”.
• Present the results in a table.

          Summary of findings:

• Discuss the trends observed in loan cohorts and the longevity of loans.

      4. Seasonality Analysis:

• Identify instances of seasonality in the loan data.
• Adjust the Original Principal Amount for seasonality.

        Create two visualizations:

• One shows the Original Principal Amount unadjusted for seasonality.
• One showing the Original Principal Amount adjusted for seasonality.

       Summary of findings:

• Explain the impact of seasonality on the loan data and how adjustments were made.

     5. Interest Rate Analysis:

• Analyze factors influencing loan interest rates.
• Identify specific loan characteristics associated with higher interest rates.
• Use statistical methods to determine significant factors.

        Summary of findings:

• Describe the key factors influencing interest rates and their implications for loan pricing.

Conclusion

By conducting a comprehensive analysis of the IBRD loan portfolio using the provided dataset, this case study aims to provide actionable insights to assist in the potential acquisition of IBRD. The analysis includes data cleaning, borrower concentration, cohort analysis, seasonality adjustments, and interest rate factors. The findings will be presented through visualizations created in Tableau or Excel, offering a clear and insightful view of the loan portfolio’s performance and trends.

Click here to access the dataset.

IBRD statements  dataset