1. Note that it is difficult to determine whether each transaction in the Amount column represents a positive cash inflow or a negative cash outflow without information in other columns. Modify the database as appropriate to more easily analyze the data.
Case S Formulas Data Review View Home Insert Page Layout Calibri (Body). 11 A- A+ Wrap Text Paste B I U A. Merge & Center E15 XV fx 3550 c. E F G H A 8 1 Date Day of Week 2 1.Aug Friday 3 1-Aug Friday 4 1-Aug Friday 1-Aug Friday 6 1-Aug Friday 1-Aug Friday 1-Aug Friday 9 1-Aug Friday 10 1-Aug Friday 11 1.Aug Friday 12 1-Aug Friday 13 1.Aug Friday 1-Aug Friday 15 1 Aug Friday 16 1-Aug Friday 17 1-Aug Friday 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 1.Aug Friday 1. Friday 27 1 Aug Friday 28 1-Aug Friday 29 1-Ag Friday 30 31 1 Aug Friday 32 Account Savings Checking Checking Checking Checking Certificate of Deposit Certificate of Deposit Certificate of Deposit Saving Saving Savings TRA Checking Checking D 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 Depost Deposit withdrawal Deposit Withdrawal withdrawal withdrawal Depot Deposit New New New New Account New Account New Account New Account Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Certificate of Depost Certificate of Deposit Certificate of Depot Amount Customer Branch 3000 New Anderson Hills 380 New Blue Ash 3070 New Anderson Hills 3560 New Blue Ash 620 New Anderson Hills 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 420 Current Anderson Hills 140 Current Anderson Hills 930 Current Blue Ash 330 Current Anderson Hills 110 Current Anderson 170 Current Anderson Hills 90 Current Delhi 57800 Current Anderson Hills 59400 Current Anderson Hills 25200 Current Anderson 2200 N Anderson Hills 1100 New Delhi Delhi Anderson Hills 16300 New 34 36 Cartie Peoplesch Bantu 16 A- A- AS AaBb Ccode Abbe de AaBb be X, X? A Normal No Spacing Header 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. English (United States Focus S 3 % 5 4 & 7 6 8 9 0 Case S Formulas Data Review View Home Insert Page Layout Calibri (Body). 11 A- A+ Wrap Text Paste B I U A. Merge & Center E15 XV fx 3550 c. E F G H A 8 1 Date Day of Week 2 1.Aug Friday 3 1-Aug Friday 4 1-Aug Friday 1-Aug Friday 6 1-Aug Friday 1-Aug Friday 1-Aug Friday 9 1-Aug Friday 10 1-Aug Friday 11 1.Aug Friday 12 1-Aug Friday 13 1.Aug Friday 1-Aug Friday 15 1 Aug Friday 16 1-Aug Friday 17 1-Aug Friday 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 1.Aug Friday 1. Friday 27 1 Aug Friday 28 1-Aug Friday 29 1-Ag Friday 30 31 1 Aug Friday 32 Account Savings Checking Checking Checking Checking Certificate of Deposit Certificate of Deposit Certificate of Deposit Saving Saving Savings TRA Checking Checking D 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 Depost Deposit withdrawal Deposit Withdrawal withdrawal withdrawal Depot Deposit New New New New Account New Account New Account New Account Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Checking Certificate of Depost Certificate of Deposit Certificate of Depot Amount Customer Branch 3000 New Anderson Hills 380 New Blue Ash 3070 New Anderson Hills 3560 New Blue Ash 620 New Anderson Hills 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 420 Current Anderson Hills 140 Current Anderson Hills 930 Current Blue Ash 330 Current Anderson Hills 110 Current Anderson 170 Current Anderson Hills 90 Current Delhi 57800 Current Anderson Hills 59400 Current Anderson Hills 25200 Current Anderson 2200 N Anderson Hills 1100 New Delhi Delhi Anderson Hills 16300 New 34 36 Cartie Peoplesch Bantu 16 A- A- AS AaBb Ccode Abbe de AaBb be X, X? A Normal No Spacing Header 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. English (United States Focus S 3 % 5 4 & 7 6 8 9 0
