Scenario

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.

Lab Components and Instructions

Deliverables

The deliverable for this lab is a Microsoft Excel file including all steps below from this case study.

Required Software

This lab will use the following Lab Resources.

Virtual Lab Citrix $($MS Excel $2016$ or $2019).$ If you have MS Excel on your personal computer, it will work for this lab.

Use a personal copy of the software or access the Lab Resources located in the Course Resources page.

Step $1$: Insurance Policy Analysis

Download the Excel File Download Excel FileOpen this document with ReadSpeaker docReaderand save the file as Week$1\_$Lab$\_$YourLastName$\_$YourFirstName.xlsx$.$

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.

W$1\_$Lab$\_$Step$\_1.$jpg

Image Description

Save your Excel file.

Step $2$: Complete Analysis of Car Loan Customer Without Insurance

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.

W$1\_$Lab$\_$Step$\_2.$jpg

Image Description

Save your work.

Step $3$: Exploring Other Opportunities

Create a new worksheet at the end and name it Step $3.$

In cell A$1,$ enter "Number of customers without any mortgages" and calculate the number of customers without any mortgage accounts in cell B$1.$ Hint: you will want to utilize the count command in cell B$1.$

Calculate the percentage of in C$1$ based on the $50$ customers.

In cell A$2,$ enter "Number of customers without any car loans" and calculate the number of customers without any mortgage accounts in cell B$2.$ Hint: you will want to utilize the count command in cell B$2.$

Calculate the percentage of in C$2$ based on the $50$ customers.

In cell A$3,$ enter "Number of customers without any investment accounts" and calculate the number of customers without any mortgage accounts in cell B$3.$ Hint: you will want to utilize the count command in cell B$3.$

Calculate the percentage of in C$3$ 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.

Step $4$: Summarize Your Findings

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.