Please thoroughly read instructions provided
Assignment 1 – Analysis and Report of the Unbanked and Underbanked in the US
Introduction
The Federal Deposit Insurance Corporation (FDIC) conducts frequent surveys to understand the unbanked and underbanked situation in the U.S. In this assignment, we will use the 2023 FDIC National Survey of Unbanked and Underbanked Households. It is the most recent survey conducted by the FDIC focusing on the unbanked and underbanked situation in the U.S.
The data has been cleaned and prepared in Excel format for our analysis. Each row in the spreadsheet contains information collected from one household. There are a total of 29,483 households included in the dataset. It is important to note that not all households carry the same weight in our analysis. Due to the sampling design, each household has a weight, and the weight (as opposed to count) will be used to calculate the proportions and statistics.
To keep the scope of the assignment manageable, not all data from the FDIC survey have been included. The original data, dictionary, and survey questions can be found on the FDIC official website. Survey data from earlier years are also available.
Assignment Questions
What is the proportion of unbanked and underbanked households in the U.S.? (6 points)
How do the unbanked and underbanked situations compare across different states? To answer this question, please create two separate visualizations for: (20 points)
a. The proportion of the unbanked population in different states
b. The proportion of the underbanked population in different states
What proportion of the unbanked individuals were previously banked? (6 points)
What are the main reasons people become or choose to be unbanked? (6 points)
How does the unbanked and underbanked proportion differ for different: (6 points each, total 42 points)
a. Household type
b. Employment status
c. Education
d. Citizenship and place of birth
e. Disability status
f. Income
g. Race/Ethnicity
How do the unbanked, underbanked, and banked differ in their use of the following alternative financial services (AFS)? (6 points each, total 48 points)
a. Nonbank check cashing
b. Nonbank money order
c. Payday loan
d. Pawnshop loan
e. Refund anticipation loan
f. Rent-to-own service
g. Auto title loan
h. Prepaid card
7. How do the unbanked, underbanked, and banked differ in their home ownership? (6 points)
8. In question 6 above, we looked at how each AFS is used. In this question, let’s explore what proportion of unbanked, banked, and underbanked households used at least one of the AFS services. (10 points)
9. What are the takeaway messages from your analyses above? Please describe the current landscape of unbanked and underbanked. (6 points)
Deliverables
There are two deliverables for this assignment:
1. The completed Microsoft Excel spreadsheet (based on the template with finished calculations)
– For each question and sub-question except for questions 2 and 9, please include a pivot table.
– For question 5, you will need one pivot table for each category (e.g., household type, employment status, etc.).
– For question 2, creating one pivot table is sufficient, but you must include two separate visualizations for unbanked and underbanked proportions across different states.
– Question 9 asks for your takeaway messages and does not need to be reflected in your spreadsheet.
2. A professionally organized 3–6 page report that answers the questions above.
– Organize your report as responses to each of the questions.
– Focus on findings and insights; do not include technical details or calculation steps.
– For questions 1–8, include:
• The calculation results (percentages, tables, or visualizations).
• Your interpretation of the results (e.g., whether it is high/low, implications for households, organizations, and governments).
– For question 9, you’ll be graded on the quality of your summary.
Please adhere to the page limit and refrain from including unnecessary details. For example, referencing the percentage value is sufficient — there’s no need to paste entire pivot tables since your Excel file will include them.
File Naming:
• Excel: [FirstName] [LastName] Assignment 1 Analysis.xlsx
• Report (PDF): [FirstName] [LastName] Assignment 1 Report.pdf
Please note that you are required to use Microsoft Excel to complete this assignment. Google Sheets, Apple Numbers, or other spreadsheet applications are not acceptable.
Download the Template
Download the Excel Template: “Assignment 1 FDIC2023 Clean Unbanked Underbanked Banked Template.xlsx” as the basis for your assignment.
Each row in the sheet represents one household. The columns describe household information, including unbanked status, state, family income, etc.
Some columns have empty cells. Empty cells represent missing or non-applicable values (e.g., the “main reason unbanked” field for fully banked participants). Consider and exclude these when appropriate during your analysis.
Instruction and Demonstration Video
Please visit the course website for the Assignment 1 demonstration video. The analysis will primarily use Pivot Tables.
IMPORTANT: Dummy data is used in the demonstration video. Your actual analysis will produce different numbers.
The data provided to you is the real, official FDIC dataset — your conclusions will reflect the most up-to-date understanding of the unbanked and underbanked landscape in the U.S.
