BUS 210 CASE STUDY 2 Instructions: Read over the Peoples Choice Bank case study in Chapter 2 and use the Peoples Choice Bank data file provided for the analyses needed below. Neatly present your results within Excel and Word. (No need to address the two problems presented in the textbook related to this case.) MS Excel Portion Part 1: Create a copy of the Data worksheet and rename it to your full name. A) In column I, create a data field next to Branch and give it the name Flow Type. Next, use the IF function to determine if the Flow Type for each transaction is an Inflow or an Outflow based on the following condition: if a bank transaction Type is a Withdrawal, then the Flow Type value is an Inflow, otherwise it is an Outflow. B) In column J, create another data field next to Flow Type and give it the name Cash Flow Amount. Use the IF function to format each bank transaction Amount as a positive value or negative value depending on the transaction Flow Type. For example, if the bank transaction Flow Type is an Inflow of cash, then the transaction Amount will be positive value for the Cash Flow Amount. However, if the transaction Flow type is considered an Outflow of cash, then the transaction Amount will be a negative value for the Cash Flow Amount. Below is a sample of how the output would look like for parts A and B. Part 2: Within the workbook, create a new worksheet named My Pivot Tables. Within this worksheet, create the necessary pivot tables that clearly answers each of the following: A) Find the total number of each Flow Type for the month of August. B) Find the total dollar amounts for inflows and outflows. C) Display each branchs total cash inflow amounts and total cash outflow amounts. D) Which day of the week generates the most net cashflow? E) What is the total number of new customers for each branch? Carefully format each of the five pivot tables as needed. MS Word Portion Based on your analyses above, provide a written report on each of your findings in 1 to 2 pages maximum. Also provide information on the total net cash flow amount for month of August. Your welcome to provide additional analyses on the data for the report.

31 2-Aug Saturday 32 2-Aug Saturday 33 2-Aug Saturday 34 2-Aug Saturday 35 2-Aug Saturday 36 2-Aug Saturday 37 2-Aug Saturday 38 4-Aug Monday 39 4-Aug Monday 40 4-Aug Monday 41 4-Aug Monday 42 4-Aug Monday 43 4-Aug Monday 44 4-Aug Monday 45 4-Aug Monday 46 4-Aug Monday 47 4-Aug Monday 48 4-Aug Monday 49 4-Aug Monday 50 4-Aug Monday 51 4-Aug Monday 52 4-Aug Monday 53 4-Aug Monday 54 4-Aug Monday 55 4-Aug Monday 56 4-Aug Monday 57 4-Aug Monday 58 4-Aug Monday Data Savings Withdrawal $1,100.00 Current Savings Withdrawal $200.00 Current Savings Deposit $1,300.00 Current Savings Deposit $800.00 Current Checking Deposit $3.880.00 Current Checking Deposit $910.00 Current Checking Withdrawal $770.00 Current Savings New Account $1,900.00 New Savings New Account $1,400.00 New Checking New Account $320.00 New Checking New Account $3,030.00 New Checking New Account $150.00 New Checking New Account $850.00 New Checking New Account $650.00 New Checking New Account $670.00 New Checking New Account $3,780.00 New Checking New Account $250.00 New Savings Deposit $1,600.00 Current Savings Deposit $700.00 Current Savings Withdrawal $200.00 Current Savings Deposit $1,000.00 Current Savings Deposit $900.00 Current Savings Deposit $700.00 Current Savings Withdrawal $300.00 Current Savings Deposit $400.00 Current Checking Deposit $260.00 Current Checking Deposit $2,820.00 Current Checking Deposit $450.00 Current Anderson Hills Delhi Anderson Hills Delhi Anderson Hills Anderson Hills Blue Ash Anderson Hills Delhi Anderson Hills Blue Ash Blue Ash Delhi Delhi Anderson Hills Anderson Hills Anderson Hills Anderson Hills Blue Ash Anderson Hills Delhi Anderson Hills Anderson Hills Anderson Hills Anderson Hills Blue Ash Delhi Blue Ash + Friday Friday Friday 1 People's Choice Bank - August Account History 2 3 Date Day of Week Account Type Amount Customer Branch 4 1-Aug Friday Savings New Account $3,000.00 New Anderson Hills 5 1-Aug Friday Checking New Account $380.00 New Blue Ash 6 1-Aug Friday Checking New Account $3,070.00 New Anderson Hills 7 1-Aug Friday Checking New Account $3,560.00 New Blue Ash 8 1-Aug Friday Checking New Account $620.00 New Anderson Hills 91-Aug Savings Deposit $3,000.00 Current Delhi 10 1-Aug Friday Savings Deposit $1,100.00 Current Anderson Hills 11 1-Aug Savings Deposit $1,500.00 Current Anderson Hills 12 1-Aug Friday Checking Withdrawal $3,800.00 Current Anderson Hills 13 1-Aug Friday Checking Deposit $3,550.00 Current Anderson Hills 14 1-Aug Checking Deposit $940.00 Current Blue Ash 15 1-Aug Friday Checking Deposit $250.00 Current Delhi 16 1-Aug Friday Checking Deposit $3,090.00 Current Anderson Hills 17 1-Aug Friday Checking Withdrawal $890.00 Current Anderson Hills 18 1-Aug Friday Checking Deposit $760.00 Current Blue Ash 19 1-Aug Friday Checking Deposit $750.00 Current Anderson Hills 20 1-Aug Checking Withdrawal $420.00 Current Anderson Hills 21 1-Aug Friday Checking Deposit $140.00 Current Anderson Hills 22 1-Aug Friday Checking Withdrawal $930.00 Current Blue Ash 23 1-Aug Checking Withdrawal $330.00 Current Anderson Hills 24 1-Aug Friday Checking Withdrawal $130.00 Current Anderson Hills 25 1-Aug Friday Checking Deposit $170.00 Current Anderson Hills 26 1-Aug Friday Checking Deposit $980.00 Current Delhi 27 2-Aug Saturday Savings New Account $2,200.00 New Anderson Hills 28 2-Aug Saturday Savings New Account $1,100.00 New Delhi 29 2-Aug Saturday Checking New Account $130.00 New Delhi 30 2-Aug Saturday Checking New Account $940.00 New Anderson Hills Data Friday Friday AutoSave CF HD The Peoples Choice Bank - Case Study 2 (1) - Protected View O Search Ahmed Khazaal File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable Editing H 1 1 M N P R s T V A1 fx People's Choice Bank - August Account History F 1 People's Choice Bank - August Account History 2 3 Date Day of Week Account Type Amount Customer Branch 4 1-Aug Friday Savings New Account $3,000.00 New Anderson Hills 51-Aug Friday Checking New Account $380.00 New Blue Ash 6 1-Aug Friday Checking New Account $3.070.00 New Anderson Hills 7 1-Aug Friday Checking New Account $3.560.00 New Blue Ash 8 1-Aug Friday Checking New Account S620.00 New Anderson Hills 9 1-Aug Friday Savings Deposit $3,000.00 Current Delhi 10 1-Aug Friday Savings Deposit $1,100.00 Current Anderson Hills 11 1-Aug Friday Savings Deposit $1,500.00 Current Anderson Hills 12 1-Aug Friday Checking Withdrawal $3,800.00 Current Anderson Hills 13 1-Aug Friday Checking Deposit $3,550.00 Current Anderson Hills 14 1-Aug Friday Checking Deposit $940.00 Current Blue Ash 15 1-Aug Friday Checking Deposit S250.00 Current Delhi 16 1-Aug Friday Checking Deposit $3,090.00 Current Anderson Hills 17 1-Aug Friday Checking Withdrawal $890.00 Current Anderson Hills 10 1-Aug Checking Deposit $760.00 Current Blue Ash 19 1-Aug Friday Checking Deposit S750.00 Current Anderson Hills 20 1-Aug Friday Checking Withdrawal $420.00 Current Anderson Hills 21 1-Aug Checking Deposit $140.00 Current Anderson Hills 22 1-Aug Friday Checking Withdrawal $930.00 Current Blue Ash 231-Aug Friday Checking Withdrawal $330.00 Current Anderson Hills 24 1-Aug Friday Checking Withdrawal $130.00 Current Anderson Hills 25 1-Aug Friday Checking Deposit $170.00 Current Anderson Hills 26 1-Aug Friday Checking Deposit $980.00 Current Delhi 27 2-Aug Saturday Savings New Account $2,200.00 New Anderson Hills 28 2-Aug Saturday Savings New Account $1,100.00 New Delhi 29 2-Aug Saturday Checking New Account $130.00 New Delhi 30 2-Aug Saturday Checking New Account $940.00 New Anderson Hills 31 2-Aug Saturday Savings Withdrawal $1,100.00 Current Anderson Hills 32 2-Aug Saturday Savings Withdrawal $200.00 Current Delhi 33 2-Aug Saturday Savings Deposit $1,300.00 Current Anderson Hills 34 2-Aug Saturday Savings Deposit S800.00 Current Delhi Data Friday Friday 31 2-Aug Saturday 32 2-Aug Saturday 33 2-Aug Saturday 34 2-Aug Saturday 35 2-Aug Saturday 36 2-Aug Saturday 37 2-Aug Saturday 38 4-Aug Monday 39 4-Aug Monday 40 4-Aug Monday 41 4-Aug Monday 42 4-Aug Monday 43 4-Aug Monday 44 4-Aug Monday 45 4-Aug Monday 46 4-Aug Monday 47 4-Aug Monday 48 4-Aug Monday 49 4-Aug Monday 50 4-Aug Monday 51 4-Aug Monday 52 4-Aug Monday 53 4-Aug Monday 54 4-Aug Monday 55 4-Aug Monday 56 4-Aug Monday 57 4-Aug Monday 58 4-Aug Monday Data Savings Withdrawal $1,100.00 Current Savings Withdrawal $200.00 Current Savings Deposit $1,300.00 Current Savings Deposit $800.00 Current Checking Deposit $3.880.00 Current Checking Deposit $910.00 Current Checking Withdrawal $770.00 Current Savings New Account $1,900.00 New Savings New Account $1,400.00 New Checking New Account $320.00 New Checking New Account $3,030.00 New Checking New Account $150.00 New Checking New Account $850.00 New Checking New Account $650.00 New Checking New Account $670.00 New Checking New Account $3,780.00 New Checking New Account $250.00 New Savings Deposit $1,600.00 Current Savings Deposit $700.00 Current Savings Withdrawal $200.00 Current Savings Deposit $1,000.00 Current Savings Deposit $900.00 Current Savings Deposit $700.00 Current Savings Withdrawal $300.00 Current Savings Deposit $400.00 Current Checking Deposit $260.00 Current Checking Deposit $2,820.00 Current Checking Deposit $450.00 Current Anderson Hills Delhi Anderson Hills Delhi Anderson Hills Anderson Hills Blue Ash Anderson Hills Delhi Anderson Hills Blue Ash Blue Ash Delhi Delhi Anderson Hills Anderson Hills Anderson Hills Anderson Hills Blue Ash Anderson Hills Delhi Anderson Hills Anderson Hills Anderson Hills Anderson Hills Blue Ash Delhi Blue Ash + Friday Friday Friday 1 People's Choice Bank - August Account History 2 3 Date Day of Week Account Type Amount Customer Branch 4 1-Aug Friday Savings New Account $3,000.00 New Anderson Hills 5 1-Aug Friday Checking New Account $380.00 New Blue Ash 6 1-Aug Friday Checking New Account $3,070.00 New Anderson Hills 7 1-Aug Friday Checking New Account $3,560.00 New Blue Ash 8 1-Aug Friday Checking New Account $620.00 New Anderson Hills 91-Aug Savings Deposit $3,000.00 Current Delhi 10 1-Aug Friday Savings Deposit $1,100.00 Current Anderson Hills 11 1-Aug Savings Deposit $1,500.00 Current Anderson Hills 12 1-Aug Friday Checking Withdrawal $3,800.00 Current Anderson Hills 13 1-Aug Friday Checking Deposit $3,550.00 Current Anderson Hills 14 1-Aug Checking Deposit $940.00 Current Blue Ash 15 1-Aug Friday Checking Deposit $250.00 Current Delhi 16 1-Aug Friday Checking Deposit $3,090.00 Current Anderson Hills 17 1-Aug Friday Checking Withdrawal $890.00 Current Anderson Hills 18 1-Aug Friday Checking Deposit $760.00 Current Blue Ash 19 1-Aug Friday Checking Deposit $750.00 Current Anderson Hills 20 1-Aug Checking Withdrawal $420.00 Current Anderson Hills 21 1-Aug Friday Checking Deposit $140.00 Current Anderson Hills 22 1-Aug Friday Checking Withdrawal $930.00 Current Blue Ash 23 1-Aug Checking Withdrawal $330.00 Current Anderson Hills 24 1-Aug Friday Checking Withdrawal $130.00 Current Anderson Hills 25 1-Aug Friday Checking Deposit $170.00 Current Anderson Hills 26 1-Aug Friday Checking Deposit $980.00 Current Delhi 27 2-Aug Saturday Savings New Account $2,200.00 New Anderson Hills 28 2-Aug Saturday Savings New Account $1,100.00 New Delhi 29 2-Aug Saturday Checking New Account $130.00 New Delhi 30 2-Aug Saturday Checking New Account $940.00 New Anderson Hills Data Friday Friday AutoSave CF HD The Peoples Choice Bank - Case Study 2 (1) - Protected View O Search Ahmed Khazaal File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable Editing H 1 1 M N P R s T V A1 fx People's Choice Bank - August Account History F 1 People's Choice Bank - August Account History 2 3 Date Day of Week Account Type Amount Customer Branch 4 1-Aug Friday Savings New Account $3,000.00 New Anderson Hills 51-Aug Friday Checking New Account $380.00 New Blue Ash 6 1-Aug Friday Checking New Account $3.070.00 New Anderson Hills 7 1-Aug Friday Checking New Account $3.560.00 New Blue Ash 8 1-Aug Friday Checking New Account S620.00 New Anderson Hills 9 1-Aug Friday Savings Deposit $3,000.00 Current Delhi 10 1-Aug Friday Savings Deposit $1,100.00 Current Anderson Hills 11 1-Aug Friday Savings Deposit $1,500.00 Current Anderson Hills 12 1-Aug Friday Checking Withdrawal $3,800.00 Current Anderson Hills 13 1-Aug Friday Checking Deposit $3,550.00 Current Anderson Hills 14 1-Aug Friday Checking Deposit $940.00 Current Blue Ash 15 1-Aug Friday Checking Deposit S250.00 Current Delhi 16 1-Aug Friday Checking Deposit $3,090.00 Current Anderson Hills 17 1-Aug Friday Checking Withdrawal $890.00 Current Anderson Hills 10 1-Aug Checking Deposit $760.00 Current Blue Ash 19 1-Aug Friday Checking Deposit S750.00 Current Anderson Hills 20 1-Aug Friday Checking Withdrawal $420.00 Current Anderson Hills 21 1-Aug Checking Deposit $140.00 Current Anderson Hills 22 1-Aug Friday Checking Withdrawal $930.00 Current Blue Ash 231-Aug Friday Checking Withdrawal $330.00 Current Anderson Hills 24 1-Aug Friday Checking Withdrawal $130.00 Current Anderson Hills 25 1-Aug Friday Checking Deposit $170.00 Current Anderson Hills 26 1-Aug Friday Checking Deposit $980.00 Current Delhi 27 2-Aug Saturday Savings New Account $2,200.00 New Anderson Hills 28 2-Aug Saturday Savings New Account $1,100.00 New Delhi 29 2-Aug Saturday Checking New Account $130.00 New Delhi 30 2-Aug Saturday Checking New Account $940.00 New Anderson Hills 31 2-Aug Saturday Savings Withdrawal $1,100.00 Current Anderson Hills 32 2-Aug Saturday Savings Withdrawal $200.00 Current Delhi 33 2-Aug Saturday Savings Deposit $1,300.00 Current Anderson Hills 34 2-Aug Saturday Savings Deposit S800.00 Current Delhi Data Friday Friday