Fraud Examination Casebook with Documents

CHAPTER 4Case 4: Using Data Analytics: Analyzing and Summarizing Data with Excel Pivot

4-1 ANDERSON INTERNAL MEDICINE (DATA ANALYTICS): USING DATA ANALYTICS—ANALYZING AND SUMMARIZING DATA WITH EXCEL PIVOT

Learning Objective

After completing this case, you should be able to:

1. Use Excel Pivot to analyze a spreadsheet created by, or small database imported into, Excel.
2. Use Excel Pivot to summarize by preparing easy to understand summary schedules for inclusion in examination reports and/or for courtroom presentation.

 

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