The questions might look long but it's fairly simple. Exercise 1: Excel Pivot (Checks and Debit Card Disbursements)—Individual Assignment Using the Excel spreadsheet (small database) titled “Ander

The questions might look long but it's fairly simple. 

Exercise 1: Excel Pivot (Checks and Debit Card Disbursements)—Individual Assignment Using the Excel spreadsheet (small database) titled “Anderson Bank Transactions (Sep Oct Nov Dec Jan),” which is found in the companion website for this course, develop two Excel PivotTables to analyze and summarize the data. Exercise 1 will involve checks and debit card disbursements and Exercise 2 will involve deposits. Your starting point is the tab at the bottom of “Anderson Bank Transactions (Sep Oct Nov Dec Jan)” labeled ORG SCH. NOTE 1: Save frequently to avoid loss of your work! NOTE 2: Be very careful when assigning categories, as there could be surprises revealed by the names and/or dollar amounts! Not all of the receipts and disbursements may be those of Anderson Internal Medicine—one of the purposes of Excel Pivot (or any other data analysis) is to detect anomalies for further examination. Also, not all names are what they appear to be. In real life, check the names on the Internet​ to find out more information. For example, Strip TZ Grille is a credit‐card/debit‐card front for Strip TZ, a strip club that uses the name to hide charges from spouses and significant others. NOTE 3: When using Pivot, it will automatically create a new tab called “Sheet2,” “Sheet3,” etc. with each iteration. Use the instructions below to rename the Pivots to something more recognizable, like “DISB” for Checks and Debit Card Disbursement and “DEP” for Deposits. Save the spreadsheet ORG SCH to a copy for analysis and audit trail purposes (this maintains the original file and starts a new file). You do this by putting the cursor over the tab called ORG SCH; right click; and then select “Create a copy” and “(move to end).” That will create a new tab called “ORG SCH (2).” Put your cursor over that new tab; right click; and then rename it something that will reflect it as a working copy, say “Working Sch.” Then open “Working Sch” and use the drop‐down labels in the column Check Category to assign the checks and debit card transactions to categories for subsequent analysis. These are the disbursement categories provided in the case scenario. Prepare the FIRST of two Excel PivotTables (Checks and Debit Card Disbursements a/k/a DISB) using the steps below. For DISB, performing the following steps: Highlight the entire schedule except Ending Balance (note that the results will include the Opening Balance that will have to be removed from the ultimate results). Click the “Insert” tab at the top of the spreadsheet, followed by clicking “PivotTable.” The pop up called “Create PivotTable” will give choices. One of the choices is “Select a table or range,” which should give the highlighted range in Step b. Another choice is “Choose where you want the PivotTable to be placed,” which should already be marked “New Worksheet.” Make sure you put it in a “New Worksheet” to avoid future confusion. This places the PivotTable in a new tab labeled “Sheet5” at the bottom of the worksheet. On the left side is the blank PivotTable and on the right is the PivotTable Field List, which allows you to click on the boxes in “Choose field to add to report.” Start building the PivotTable by experimenting with the “Choose field to add to report.” Changes help you to immediately modify the PivotTable. For example, check​ “Payee of Check/Debit,” “Amount Check/Debit,” and “Check Category.” That gives you all of the individual checks for each payee. Note that the selections are listed under “Row Labels” at the bottom of the “PivotTable Field List.” Experiment with this example by moving “Amount Check/Debit” from “Row Labels” to “Σ Values” by dragging and dropping the label. This changes the Pivot results and gives a Count of the amount of checks/debits. Experiment further with this example by changing “Σ Values” from a Count to a Sum of the amount of checks/debits. Click on the down arrow for Count of Amount Checks/Debits under “Σ Values” and select the option “Value Field Setting” and then click on Sum. This changes the summary results to Sum of Amount Check/Debit. Now you have the sum total of all checks/debits made payable to particular payees. What if you want both the count and sum of checks/debits. Add a second selection to “Σ Values.” Drop and drag “Amount Check/Debit” from the “Choose field to add to report” down to “Σ Values.” This adds both Count of Amount Checks/Debits and Sum of Amount Check/Debit under “Σ Values.” Now you have the quantity and sum of the checks and debits. Reverse the order “Row Labels” from “Payee of Check/Debit” followed by “Check Category” by dragging and dropping “Check Category” to the top of the list. Change the name from Sheet5 to DISB to provide a better title for future identification. Highlight the rows you want to hide (e.g., Beginning Balance and Blanks that contain no quantities) and then right click and select “Hide.” Review the results for the “Check Category” Unusual Transactions and look for anomalies. There are at a minimum seven irregularities. Put those in your memory bank for the next fraud case. ​

Exercise 3: Excel Pivot (Scrubbing)—Individual Assignment You are not done yet. You have to scrub the data to ensure uniform results. In the DISB PivotTable for Working Sch, for example, look at the Row Label “Automobile.” It has KIA Finance Company and KIA Southwest. Both are the same company. Look at the Row Label “Business.” There are three Kinko's. First, save Working Sch to a new name. You will be using Search and Replace inside Working Sch to scrub the category descriptions. If you do not save Working Sch to a new name, you will lose the results of the PivotTable from Exercise 2 when you click Refresh. For example, scrub KIA Southwest by changing all of those transactions to KIA Finance Company. Similarly, change the three different sets of Kinko's transactions to a single Kinko's—and so on, until you make all category titles uniform. Use scrubbing instructions above in the “How‐To” section. Using Working Sch from Exercises 1 and 2, clean up the input errors and refresh the DISB and DEP using the following steps: Save the spreadsheet “Working Sch” to a new name like “Scrub Sch.” Now you have the ORG SCH, Working Sch, and Scrub Sch. This maintains your audit trail should you need it in court. Scrub the remaining data. Once the scrub is complete, perform the Pivots again and make sure that the names under the Row Label are uniform. This will ensure your final results. Change the formats of the numbers and totals. Get the Excel Pivot ready for inclusion in the report and/or as a courtroom‐ready audiovisual. ​