United Savings Bank (USB) is a large bank with branches spanning the United States. They are already collecting information on their customers in the following systems.
- Core Banking System
- Online Banking
- Lending System (Auto/Personal Loans)
- Credit Card System
- Mortgage Lending System
- Insurance Policy System
- Financial Investment System
They are interested in understanding how they can leverage this data, in addition to data gathered from other potential sources, to provide a competitive advantage in their market. You are to analyze some of the data to see if you can provide a recommendation for USB to achieve a strategic advantage.
The following is the necessary information to complete this lab assignment.
- Add a worksheet and rename it Step 1.
- Create a table that includes customer first and last name, total balance of deposit and investment accounts, and whether the customer has a life insurance policy with the bank or not.
- Format the data to look professional.
- Sort the data by the total deposit and investment balance in descending order.
- Filter those customers without an insurance policy with the bank. It should look like the following.
- Copy the primary sheet (Sheet 1) and move it to the end. Rename it to Step 2.
- Filter the data to show those customers with a car loan balance that do not have any car insurance policy with the bank.
- Sort the filtered data by the car loan balance in descending order. The worksheet should look like the following.Create a new worksheet at the end and name it Step 3.
- In cell A1, enter “Number of customers without any mortgages” and calculate the number of customers without any mortgage accounts in cell B1. Hint: you will want to utilize the count command in cell B1.
- Calculate the percentage of in C1 based on the 50 customers.
- In cell A2, enter “Number of customers without any car loans” and calculate the number of customers without any mortgage accounts in cell B2. Hint: you will want to utilize the count command in cell B2.
- Calculate the percentage of in C2 based on the 50 customers.
- In cell A3, enter “Number of customers without any investment accounts” and calculate the number of customers without any mortgage accounts in cell B3. Hint: you will want to utilize the count command in cell B3.
- Calculate the percentage of in C3 based on the 50 customers.
- In rows 4 through 6, repeat the previous two items (item 6 and 7) to determine the number of customers without direct deposit, number of customers with a deposit balance greater than $200,000, and number of customers with a deposit balance less than $10,000.
- Make sure that you utilize formulas to calculate each number.
- Save your work.
- Create a new worksheet at the end and name it Step 4.
- Write a 100- to 200-word analysis of what you could recommend to the board regarding possible opportunities to maximize the banks performance in all financial areas based on Steps 1 to 3.
- Below the analysis from item 2, please indicate at least three other analyses that could be performed to determine how this data might be used to generate a competitive advantage for the bank.
