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

$1.$ Download the Excel File Download Excel Fileand save the file as Week$1\_$Lab$\_$YourLastName$\_$YourFirstName.xlsx$.$

$2.$ Add a worksheet and rename it Step $1.$

$3.$ 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.

$4.$ Format the data to look professional.

$5.$ Sort the data by the total deposit and investment balance in descending order.

$6.$ Filter those customers without an insurance policy with the bank. It should look like the following.

Image Description

$7.$ Save your Excel file.

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

$1.$ Copy the primary sheet $($Sheet $1)$ and move it to the end. Rename it to Step $2.$

$2.$ Filter the data to show those customers with a car loan balance that do not have any car insurance policy with the bank.

$3.$ Sort the filtered data by the car loan balance in descending order. The worksheet should look like the following.

Image Description

$4.$ Save your work.

Step $3$: Exploring Other Opportunities

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

$2.$ 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.$

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

$4.$ 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.$

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

$6.$ 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.$

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

$8.$ 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.$

$9.$ Make sure that you utilize formulas to calculate each number.

$10.$ Save your work.

Step $4$: Summarize Your Findings

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

$2.$ 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.$

$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.

Step $5$: Complete and Submit Work

Save and click the Submit Assignment button located at the top of your page to submit your MS Excel file.

Title First Name Surname Employer Number of Bank Deposit Accounts Total Balance of Bank Deposit Accounts Number of Credit Cards Total Balance of Credit Cards Mortgage Loan Balance Car Loan Balance Life Insurance Policy? Car Insurance Policy? Number of Financial Investment Accounts Total Balance of Financial Investment Accounts ATM Card? Direct Deposit?

Mr Ryan Kilroy Kilroy Electricals Ltd $4$ $$852,0251$ $$0$ $$0$ $$0$ No No $1$ $$639,019$ Yes Yes

Miss Susan Cathcart Cathcart Off Road Vehicles Ltd $2$ $$25,2420$ $$0$ $$520,213$ $$5,469$ No Yes $1$ $$18,932$ Yes No

Mr Conrad Coyer Coyer Instruments Ltd $1$ $$1,0020$ $$0$ $$159,821$ $$0$ No Yes $0$ $$0$ Yes No

Mrs Diane Cromwell Cromwell Costume Hire Ltd $3$ $$9,8722$ $$5,854$ $$269,456$ $$0$ No No $0$ $$0$ No No

Mr Jacob Zanetti Zanetti Worktops Ltd $3$ $$425,8503$ $$62,500$ $$356,712$ $$0$ Yes No $2$ $$319,388$ Yes Yes

Ms Emilia Harding Harding Technologies Ltd $2$ $$25,7891$ $$1,954$ $$0$ $$0$ Yes No $0$ $$0$ Yes Yes

Mr Ryan Jarvis Classic Nursing Supplies Ltd $5$ $$125,4621$ $$258$ $$0$ $$25,321$ No No $0$ $$0$ No Yes

Ms Dawn Desborough Eagle Caterers Ltd $4$ $$256,7212$ $$967$ $$0$ $$33,251$ Yes Yes $2$ $$192,541$ No No

Mr Jerry Sampson Sampson Cars Ltd $6$ $$202,1200$ $$0$ $$0$ $$0$ Yes No $2$ $$151,590$ Yes No

Miss Melissa Grant Grant Office Furniture Ltd $3$ $$29,8560$ $$0$ $$249,331$ $$0$ Yes No $1$ $$22,392$ Yes Yes

Mr Harvey Chapel Chapel Cleaners Ltd $2$ $$69,8311$ $$0$ $$132,525$ $$0$ No Yes $1$ $$52,373$ Yes Yes

Mrs Kathy Bates Bates Marketing Ltd $1$ $$12,0210$ $$0$ $$279,581$ $$3,352$ Yes Yes $0$ $$0$ Yes Yes

Mr Albert Gimley Jewel Promotions Ltd $1$ $$5691$ $$12,502$ $$332,154$ $$45,258$ Yes No $0$ $$0$ No Yes

Ms Sarah Handley Handley Antiques Ltd $1$ $$7,5642$