UB 1

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.

Analysis and Report of the Unbanked and Underbanked in the US

[Insert your full name]

In this assignment, we will use the FDIC 2021 data to understand, as of 2021:

1) What is the proportion of unbanked and underbanked households in the US? (6 points)

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the number) and your interpretation of the result. For example, is it a low/high percentage? What might be the implication for households, organizations, and governments?]

2) 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

[Insert your answer here. Please include the calculation result (no need to include the visualization, you may include just the key numbers) and your interpretation of the result.]

b. the proportion of the underbanked population in different states

[Insert your answer here. Please include the calculation result (no need to include the visualization, you may include just the key numbers) and your interpretation of the result.]

3) What proportion of the unbanked individuals were previously banked? (6 points)

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the number) and your interpretation of the result.]

4) What are the main reasons people become or choose to be unbanked? (6 points)

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.

5) How does the unbanked and underbanked proportion differ for different: (6 points each, total 42 points)

a. household type

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

b. employment status

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

c. education

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

d. citizenship and place of birth

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

e. disability status

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

f. income

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

g. race/ethnicity

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

6) How do the unbanked, underbanked, and banked differ in their use of the following alternative financial service (AFS)? (6 points each, total 48 points)

a. nonbank check cashing

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

b. nonbank money order

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

c. payday loan

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

d. pawnshop loan

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

e. refund anticipation loan

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

f. rent-to-own service

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

g. auto title loan

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

h. prepaid card

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

7) How do the unbanked, underbanked, and banked differ in their home ownership? (6 points)

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

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)

[Insert your answer here. Please include the calculation result (no need to include the pivot table, you may include just the numbers) and your interpretation of the result.]

9) What are the takeaway messages from your analyses above? Please describe the current landscape of unbanked and underbanked. (6 points)

[Insert your answer here.]

References

[Insert references / AI credit here.]

1