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.
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.
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.
