World Bank Financial Analysis using SQL

Published By: Madeeha Umar

The World Bank is an international organization owned by 187 countries. It plays an important role by lending money to the governments of its members to improve their economies and raise the standard of living of their people. In 1960 IDA, International Development Association was established aiming to reduce the poverty by providing zero to low-interest rate loans called as credits and grants. The aim of this programs is to boost economic growth, reduce inequalities, and improve people's living conditions.

As a data analyst I am always curious to analyze different types of datasets. Recently I was hired as a data analyst to analyze the World Bank financial data consisting of information about borrowing countries. Our goal is to provide a comprehensive overview of countries borrowing loans from IDA and to help World Bank make informed decisions that how they can enhance their ability to empower these countries in different ways.

Key Findings:

• The World Bank's total number of transactions are 11,09,994.
• Total amount of IDA loans is about $20 trillion given to 137 countries from the World Bank.
• ?India has the largest amount of loans which is approximately $793 million.
• India, Kenya, Nigeria, Pakistan and Ethiopia are the top 5 countries in the world to whom IDA has lent the most loans.
• Ghana has made 30,849 total transactions with IDA and so far has paid back the loan amount of $1,93,19,001.41 to IDA.
• Ghana has taken the most loans for GH-EGPRC(fast-track) projects while other projects include Power Distribution, Accra Tema water Sup, Fisheries, Cocoa Development and Power Distribution ECA III.

Lets start with the data:

For this project we are using dataset from The World Bank. You can find the data here The World Bank Dataset. This data is kept up to date quarterly by the World Bank. It is a huge dataset, there are 1.18M rows and 30 columns in this dataset. Each row represents a credit or grant.

Exploration:

Since this is a huge dataset so I will be using SQL to process and analyze the data. For this project I am using a tool CSVFiddle.io ?to explore and share insights from CSV files using SQL. It runs in-browser and allows you to import data, write SQL queries and share it with anyone.

First I explored this dataset and found that this dataset has information about different countries who borrowed loans form the World Bank through IDA International Development Association. The data dictionary shows there are many columns some of which are:

• Credit numbers
• Region
• Country
• Country code
• Borrower
• Credit status
• Service charge rate
• Due to IDA
• Project name
• Project ID

Here is the table preview of this dataset.

Business Questions:

• Which countries are getting loans from IDA and how many total transactions have been made?
• What is the total amount due to IDA and which top 5 countries have the largest number of loans owed?
• How many total transaction were made by Ghana?
• What is the average amount Ghana pays back to IDA?
• What are the top 10 projects Ghana has taken loans for?

Analysis:

I started by exploring that how many total transactions have been made by World Bank so far. By using COUNT we can see total number of transactions are 11,09,994.

To see which countries are getting loans from IDA, I ran a query to see how many transactions per country have been made. I used the?GROUP BY?statement to total the number of transactions for each country.

The query returned 137 results. This means there are?137 countries that have borrowed from the World Bank.

To see the total amount owed to IDA I added the?SUM?statement in my query. It shows that total amount of IDA loans is about $20 trillion!!!

Now we want to find about top 5 countries which have the largest number of loans owed, I ran the MAX statement and used LIMIT statement in descending order to see the top 5 countries who owe the maximum amount to IDA.

?The data returned displays that India is the country with the highest amount of loans owed to IDA. Other countries include Kenya, Nigeria, Pakistan and Ethiopia. All these countries have been continuously borrowing loans from IDA for completing different projects.

Analyzing deep on India's loans that who is the main borrowers of loan, on which projects India is spending this money and is there any cancelled amount from IDA. What is the total disbursed amount for now. I ran a SQL query to get the answers of these questions and rename the column Disbursed Amount by using Alias to make it easy to find.

Query returns these results that shows the main borrower is Controller of aid accounts & audit has taken loans for different projects like for maintaining Highways, Irrigation department and Punjab flood control. As we can see that these loans are fully paid by IDA, the highest total disbursed amount is $7,15,82,713.47 and still an amount of $5,30,000 has been cancelled by IDA for Highways project.

Next business question to answer is that how many total transactions were made by Ghana. I simply used COUNT function and found Ghana in the dataset showing that it had 30,849 total transactions made with IDA.

I am curious to know where Ghana is spending most of the borrowed money, so I spent some time more closely analyzing Ghana's loans. I ran a query to see?which are top 10 projects Ghana has taken loans for.

It seems that Ghana in the region of Africa, has taken loans for GH-EGPRC(fast-track) projects. A quick research shows that it is Economic Governance and Poverty Reduction Credit?provided by IDA to Ghana. This program for Ghana aims to support the authorities efforts, in the midst of the current global crisis, to bring their fiscal stance to a sound and sustainable track and protect the development objectives in Ghana's Growth and Poverty Reduction Strategy. The EGPRS emphasizes the importance of private-led growth by some organizations to reduce poverty in the region.

My next business questions to analyze using SQL is that what is the average amount Ghana pays back to the World Bank. As we can see that Ghana has made average returns of loans amount is $19,319,001.41 that is around $20 M.

I have used aggregations to get average of loans paying back to IDA, I used Alias to rename the column to make it easy to find.

To find out what are other 5 projects of Ghana for which country has taken the loans I ran a SQL query and by using operators 'AND' and 'NOT'. We can see that here are 5 more projects other than GH-EGPRC(fast-track). These includes Power Distribution, Accra Tema water Sup, Fisheries, Cocoa Development and Power Distribution ECA III.

Insights and Recommendations:

As we can see that?total amount owed?to the IDA in loans is?$20 trillion. IDA has lent out money to 137 different countries.?We have analyzed that?largest amount?that the IDA lent out was to?India?for approximately?$793 million.

The top 5 countries that the?IDA has lent the most money?to are?India, Kenya, Nigeria, Pakistan and Ethiopia.

Doing a quick calculation shows that?Ghana has 30849 total transactions with IDA and slowly paying back the loan amount to IDA, so far has paid back $19,319,001.41.

Ghana has taken the most loans for GH-EGPRC, Power Distribution, Accra Tema water Sup, Fisheries, Cocoa Development and Power Distribution ECA III.

Knowing this information, IDA can more?closely monitor the amount borrowed and how often credits are requested. IDA can periodically monitor the progress of undergoing projects in lending countries to ensure transparency of funds used. Especially the countries who owe the largest amount. World Bank may consider to apply some conditions like holding to issue additional loans or increase their service charge fees for a certain amount of loan that has been exceeded a set amount of time. World Bank needs to monitor percentage of revenue earned?from these programs by borrowing countries in order to see IDA’s return on these investments (ROI).

Conclusion:

Thank you for reading my project article of World Bank Financial Analysis using SQL. If you have any feedback or questions about my insights and analysis, feel free to comment below. You can reach out to me at [email protected]?You can connect with me on?LinkedIn. You can find more articles written by me in my Portfolio. I would love to have your suggestions.