Overview and Instructions
Assignment: Data Analytics: Helping to Solve Financial Fraud
The following Course Outcome is assessed in this assignment:
Appraise investigative techniques including interviewing and inquiry, in solving financial crimes.
Introduction:
In this assignment, you apply what you learned concerning investigative techniques, including interviewing and inquiry to resolve a financial fraud. Read the case study and then respond to the checklist items in a substantive manner.
Chapter 4: Case 4: Using Data Analytics: Analyzing and Summarizing Data with Excel Pivot
Case Adapted from the Casebook 4-1 Anderson Internal Medicine (Data Analytics): Using Data Analytics — Analyzing And Summarizing Data with Excel Pivot
History Behind The Fraud Case
The president of an unlicensed securities firm was suspected of securities fraud (i.e., selling bogus stock warrants to unsuspecting investors). The contents of 12 monthly bank statements, canceled checks, and deposit tickets of the business were scheduled (over 50 pages of transactions). How could the transactions be easily summarized? What does the schedule of transactions reveal? Were there any significant transactions? Excel Pivot came to the rescue.
In a half hour and several clicks on Excel Pivot, the Pivot tables of deposits and disbursements showed that the president used the business checking account as if it were his personal piggy bank. He primarily used debit cards to pay for two to three meals a day, every day travel and entertainment, an automobile for a girlfriend, and payments for household utilities, cell phones, and “strip clubs.” Based on the timing of receipts from customers, he celebrated at “strip clubs.” Soon after he was indicted and arrested, he read the fraud examination report and accompanying schedules. Shortly thereafter, he agreed to plead guilty to racketeering. The judge sentenced him to 20 years for racketeering.
Background
Tonya Larsen was the sole person in charge of the practice’s finances, including accounting during the period from September 1, 2015, to March 31, 2016.
After completing the examination steps in Anderson Internal Medicine cases (Cases 2 and 3), you decided to schedule the bank transactions shown on the bank statements for the six months preceding March 2016 (the month you examined in Cases 2 and 3).
Before proceeding, go to the companion website and download the Excel spreadsheet titled, Anderson Bank Transactions (Sep Oct Nov Dec Jan Feb). It is also available in a lengthy hardcopy format in Chapter 7, section 7‐4, “Using Data Analytics: Analyzing and Summarizing Data with Excel Pivot.” The downloadable version contains drop-down categories, which helps simplify the process of adding categories, whereas the hardcopy version does not. This spreadsheet contains the result of your efforts for September 1, 2015, through February 29, 2016. Based on the initial review of the Excel spreadsheet, you decide that you need to add categories to the withdrawal transactions so you can better summarize the data.
You decide to use the following categories commonly used by medical practices:
Answering Service
Automobile
Bank Charges
Basement
Books/Subscriptions
Business
Cash
Cash Deposit
Coding
Contributions
Food
Hardware
Health Insurance
Insurance
Janitorial Services
Legal & Accounting
Linen
Marketing/Promotions|
Medical Supplies/Pharmaceuticals
Medical Waste
Miscellaneous
Mortgage
Office Supplies
Parking
Printing
Repairs & Maintenance
Shipping
Taxes
Telephone
Travel
Utilities
In scanning the spreadsheet, you notice unusual names that do not fall under the common categories used by medical practices. Accordingly, you will use the following categories for those unusual transactions.
People
Unusual Transactions
To facilitate categorization, a drop‐down menu has been provided in the detail of transactions column. To use the menu, select the cell you want to use and click the down arrow that will appear to the right. Then select the category you feel is appropriate for the transaction.
Note: There are intentional spelling errors in the spreadsheet that you will identify and then sanitize (scrub) before completing the Pivot.
Summary Requirements:
Your firm has been contracted to perform the fraud examination. The attorney for Southern Appalachian Insurance Company added an additional assignment: analyze the bank transactions from September 1, 2015, through February 29, 2016.
Access the Study section 4-2, How to Use Excel Pivot to Analyze and Summarize Data, and download the Excel Data sheet provided before conducting the specific requirements of this case.
The results of your assignment will be used as a summary schedule in your report, a schedule attached to the report, and possibly a courtroom audiovisual should you be called to testify as an expert witness. Your assignment (in general) is, as follows:
Assign categories (using the drop‐down choices provided) to the disbursement transactions to assist in summarizing transactions.
Insert a PivotTable in a new worksheet and obtain the total quantities (count) of transactions and total dollars (sum) for each category. Make at least three new PivotTables (Exercises 1, 2, and 3):
Deposits (Amount Deposit and Deposit Category)
Check and Debit Card Disbursements (Check Category, Payee per Check/Debit, Amount Check/Debit) (un‐scrubbed)
Check and Debit Card Disbursements (Check Category, Payee per Check/Debit, Amount Check/Debit) (scrubbed)
Sort the total dollars per category in descending order.
Enhance the scrubbed PivotTables for report and courtroom presentation purposes: Add an appropriate title and use proper “$” signs, decimal format, etc.
Checklist: Make sure to address all of the following in your response:
Provide the 7 irregularities noted in the first pivot table you developed.
Provide the pivot table from the first exercise.
Assess the irregularities in the data.
Determine what inquiries you would make of Anderson Internal Medicine to learn more about these irregularities.
Recommend what questions you would ask in an interview to gain more insight into these irregularities.
Provide the anomaly you determined in the deposits pivot table.
Determine the source of the anomaly.
Determine if this check is a normal deposit for Anderson Internal Medicine.
Provide the pivot table from the first exercise.
Provide the Final Excel Pivot Tables that would be included in your final fraud report to serve as an exhibit during your expert witness testimony.
This is an individual assignment. In particular, all students are to perform the various fraud examination steps and submit their Excel PivotTables for grades. NOTE: No Excel template is provided. You are to create your own Excel PivotTables by following the step‐by‐step instructions.
