Imagine that you are hired as a data analyst for a bank. The bank would like to learn more about its customers’ spending and banking habits to identify areas of improvement. You have been asked to review the bank’s income statements over the last five years and identify trends that will allow them to understand their customers better.
chosen bank’s annual income statements from the last five years from the Mergent Market AtlasLinks to an external site. database in the University of Arizona Global Campus University Library. Review the Mergent Market Atlas Tip SheetLinks to an external site. resource for tips on accessing and searching the database. Use the “Company Financials” tab in Mergent Market Atlas to access the income statements.
In your paper,
Identify an area of the bank’s income statement related to customer spending.
Describe the data points or variables that give a complete picture of the customers’ spending pattern for the last six months.
In addition to the income statement, explain which other data sources you might use to understand the customers’ spending patterns.
List the steps you will take to prepare all these data sources such that they afford clear and accurate information.
Build a frequency table, a bar chart, and a pie chart using Excel for the identified variables that explain the customers’ spending patterns. Review the Microsoft Excel Help: HomeLinks to an external site. webpage, available for resources to help utilize Microsoft Excel. Use the same three variables in each table and chart to display the same data in three different formats.
Sample Answer
A bank's income statement primarily reflects its own financial performance, not the granular spending of its customers. To understand customer spending habits, an analyst must look at the bank's revenue sources that are directly tied to customer activity, such as Service Charges on Deposit Accounts or Credit Card Fees. These income statement items act as a high-level proxy for customer behavior, but they must be supplemented with transaction-level data from other sources.
Analysis of Customer Spending and Banking Habits
1. Identifying an Area of the Income Statement Related to Customer Spending
The most direct area of a bank's income statement that reflects customer spending and banking activity is typically found within the Non-Interest Income section:
Service Charges on Deposit Accounts: This line item is generated from fees customers pay for various services, such as overdraft fees, ATM fees, insufficient funds (NSF) fees, monthly maintenance fees (if minimum balances aren't met), and wire transfer fees.
Data Preparation Steps
Before any analysis or charting can be done, the raw data must be cleaned, transformed, and integrated. The following steps ensure the data affords clear and accurate information:
Data Extraction and Consolidation:
Extract the last six months of raw transaction data from the Core Banking System.
Extract corresponding customer identifiers and demographic data from the CRM system.
Store all data in a unified, secure analytical database or a structured spreadsheet.
Data Cleaning and Validation:
Remove Duplicates: Identify and eliminate any duplicated transaction entries.
Handle Missing Values: For the Transaction Category variable, identify transactions without an assigned category. Use a rule-based engine (based on Merchant ID) or manual review to assign categories. If necessary, flag as "Unknown" rather than discarding.
Standardize Formats: Ensure all date fields are in a consistent format (e.g., YYYY-MM-DD) and all currency fields are numerical.
Data Transformation and Feature Engineering: