Paste B I U. : Me E15 X fx 3550 B D E F G 1 Date Day of Week 2. 1-Aug Friday 3 1-Aug Friday 4 1.Aug Friday 5 1-Aug Friday 6 1-Aug Friday 7 1-Aug Friday 8 1-Aug Friday 9 1-Aug Friday 10 1-Aug Friday 11 1.Aug Friday 12 1-Aug Friday 13 1-Aug Friday 14 1-Aug Friday 15 1-Aug Friday 16 1-Aug Friday 17 1.Aug Friday 18 1-Aug Friday 19 1 Aug Friday 20 1 Aug Friday 21 1-Aug Friday 22 1-Aug Friday 23 1. Aug Friday 24 1-Aug Friday 25 1 Aug Friday 25 1 Aug Friday 27 1. Aug Friday 28 1-Aug Friday 29 1-Aug Friday 30 1-Aug Friday 31 1 Aug Friday 32 2 Aug Saturday 33 2 Aug Saturday 34 35 2. Aug Saturday 16 2-Aug Saturday 32 Account Savings Checking Checking Checking Checking Certificate of Deposit Certificate of Deposit Certificate of Deposit Savings Savings Savings IRA Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Certificate of Deposit Certificate of Deposit Certificate of Deposit Type New Account New Account New Account New Account New Account New Account New Account New Account Deposit Deposit Deposit Current Contribution withdrawal Deposit Deposit Deposit Deposit Withdrawal Deposit Deposit withdrawal Deposit Withdrawal withdrawal withdrawal Deposit Deposit Amount Customer Branch 3000 New Anderson 380 New Blue Ash 3070 New Anderson Hi 3560 New Blue Ash 620 New Anderson Hill 39700 New Blue Ash 49300 New Delhi 19600 New Blue Ash 3000 Current Delhi 1100 Current Anderson Hills 1500 Current Anderson Hills 1100 Current Anderson Hills 3800 Current Anderson Hills 3550 Current Anderson Hills 940 Current Blue Ash 250 Current Delhi 3090 Current Anderson Hills 890 Current Anderson Hills 760 Current Blue Ash 750 Current Anderson Hills 420 Current Anderson Hills 140 Current Anderson Hills 930 Current Blue Ash 330 Current Anderson Hills 130 Current Anderson Hills 170 Current Anderson Hills 90 Current Delhi 57800 Current Anderson Hills 59400 Current Anderson Hills 25200 Current Anderson Hills 2200 New Anderson Hills 1100 New Delhi 130 New Delhi 940 New Anderson Hills Delhi New 2 Aug Saturday Savings Checking Checking Certificate of Deposit New New New Account New Account New Account New Account New Account PeoplesChoiceBankett + Read 16 A- A A be X, X? A AaBbcode Abbe de AaBb Normal No Spacing Make sure you complete each of the tasks listed in your eText for People's Choice Bank. Here are some details you need to complete the file. 1. You will need to add a column and create a function that calculates Cash Flow based on criteria from Type. Create an IF statement that returns a negative cash flow (looks like ($500.00) for example) if the type was a Withdrawal. For all other types, the cash flow should be positive. 2. In running your pivot tables, some suggestions are to look at things like branch, account type, customer, etc. These are suggestions only. You will probably run more than just those types. Remember to run more than just SUMs in your pivot tables. You can also run MIN, MAX, COUNT, PERCENT and more. Run at least different 3 pivot tables, saving each as a separate sheet with each tab descriptively labeled (For example: Pivot 1, Pivot 2, etc.). Include those pivot tables OR charts generated from the pivot tables as images in your Word document report. (One final tip, don't forget about time such as dates and days of the week in your analysis and data set.) 3. Place your name in each worksheet in cell A200 and your student number in A201 to receive credit for your Excel worksheets for this assignment. You must submit both your Excel file and Word document to the Dropbox. Your Case Study Assignment is to be submitted to the appropriate Dropbox by 11:59 pm CT Sunday of Week 7. Engh (United States S 3 4. % 5 & 7 6 8. 9 0 Paste B I U. : Me E15 X fx 3550 B D E F G 1 Date Day of Week 2. 1-Aug Friday 3 1-Aug Friday 4 1.Aug Friday 5 1-Aug Friday 6 1-Aug Friday 7 1-Aug Friday 8 1-Aug Friday 9 1-Aug Friday 10 1-Aug Friday 11 1.Aug Friday 12 1-Aug Friday 13 1-Aug Friday 14 1-Aug Friday 15 1-Aug Friday 16 1-Aug Friday 17 1.Aug Friday 18 1-Aug Friday 19 1 Aug Friday 20 1 Aug Friday 21 1-Aug Friday 22 1-Aug Friday 23 1. Aug Friday 24 1-Aug Friday 25 1 Aug Friday 25 1 Aug Friday 27 1. Aug Friday 28 1-Aug Friday 29 1-Aug Friday 30 1-Aug Friday 31 1 Aug Friday 32 2 Aug Saturday 33 2 Aug Saturday 34 35 2. Aug Saturday 16 2-Aug Saturday 32 Account Savings Checking Checking Checking Checking Certificate of Deposit Certificate of Deposit Certificate of Deposit Savings Savings Savings IRA Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Certificate of Deposit Certificate of Deposit Certificate of Deposit Type New Account New Account New Account New Account New Account New Account New Account New Account Deposit Deposit Deposit Current Contribution withdrawal Deposit Deposit Deposit Deposit Withdrawal Deposit Deposit withdrawal Deposit Withdrawal withdrawal withdrawal Deposit Deposit Amount Customer Branch 3000 New Anderson 380 New Blue Ash 3070 New Anderson Hi 3560 New Blue Ash 620 New Anderson Hill 39700 New Blue Ash 49300 New Delhi 19600 New Blue Ash 3000 Current Delhi 1100 Current Anderson Hills 1500 Current Anderson Hills 1100 Current Anderson Hills 3800 Current Anderson Hills 3550 Current Anderson Hills 940 Current Blue Ash 250 Current Delhi 3090 Current Anderson Hills 890 Current Anderson Hills 760 Current Blue Ash 750 Current Anderson Hills 420 Current Anderson Hills 140 Current Anderson Hills 930 Current Blue Ash 330 Current Anderson Hills 130 Current Anderson Hills 170 Current Anderson Hills 90 Current Delhi 57800 Current Anderson Hills 59400 Current Anderson Hills 25200 Current Anderson Hills 2200 New Anderson Hills 1100 New Delhi 130 New Delhi 940 New Anderson Hills Delhi New 2 Aug Saturday Savings Checking Checking Certificate of Deposit New New New Account New Account New Account New Account New Account PeoplesChoiceBankett + Read 16 A- A A be X, X? A AaBbcode Abbe de AaBb Normal No Spacing Make sure you complete each of the tasks listed in your eText for People's Choice Bank. Here are some details you need to complete the file. 1. You will need to add a column and create a function that calculates Cash Flow based on criteria from Type. Create an IF statement that returns a negative cash flow (looks like ($500.00) for example) if the type was a Withdrawal. For all other types, the cash flow should be positive. 2. In running your pivot tables, some suggestions are to look at things like branch, account type, customer, etc. These are suggestions only. You will probably run more than just those types. Remember to run more than just SUMs in your pivot tables. You can also run MIN, MAX, COUNT, PERCENT and more. Run at least different 3 pivot tables, saving each as a separate sheet with each tab descriptively labeled (For example: Pivot 1, Pivot 2, etc.). Include those pivot tables OR charts generated from the pivot tables as images in your Word document report. (One final tip, don't forget about time such as dates and days of the week in your analysis and data set.) 3. Place your name in each worksheet in cell A200 and your student number in A201 to receive credit for your Excel worksheets for this assignment. You must submit both your Excel file and Word document to the Dropbox. Your Case Study Assignment is to be submitted to the appropriate Dropbox by 11:59 pm CT Sunday of Week 7. Engh (United States S 3 4. % 5 & 7 6 8. 9 0