End of Chapter Questions, Exercises, and Problems for by

Version 1.00 dated March 21, 2012.

This document is a supplement to Forensic Analytics and has been prepared for purchasers of the book.

Table of Contents

These analytic skills are not easy to acquire. No specialized vocation is easy. Fraud investigation requires some special skills because we are trying to detect activities that the fraudster purposely sought to conceal. On top of this, we operate in an adversarial system of justice. This means that errors and omissions on the part of the investigator will be used defendants and their attorneys. A quick skim through the chapters isn't going to develop much of a useful skill. This would be like reading a small English-Spanish translation dictionary and then expecting to be able to talk Spanish in your travels abroad. What is needed here is a careful reading of the book and lots of practice. For each chapter the end-of-chapter materials include:

• 
  At least five discussion questions, five short answer questions, five multiple choice questions, and
  
• 
  At least two cases
  

The discussion questions are meant to be a bit thought provoking. Some of these questions will require you to think "outside the box" or "outside the chapter." You won't find the exact answer to the question in the chapter. This was done on purpose. The short answer and the multiple choice questions call for the best answer to each question. The cases will be hard work. You will be asked to use four different data sets for the cases together with an Excel template and a real-world forensic report. The cases will assume a reasonable level of competence with Excel and Access. If you get stuck please look at your course syllabus for the procedures and rules related to asking your instructor or a class friend for help. Lots of help is available on the Microsoft website. You don't have to guess what VLOOKUP is (Case 18-16). The Microsoft site has an explanation together with an example.

There are also PowerPoint slides that outline the main points in the chapter. The PowerPoint slides are not a substitute for reading the chapter.

Please thank your instructor for making your course a highly relevant course from a personal and a social perspective. I welcome feedback from users.

Pennington, New Jersey, USA

March 20, 2012


CHAPTER 1
USING ACCESS IN FORENSIC INVESTIGATIONS

Discussion Questions

1. 
   Forensic analytics is the procurement and analysis of electronic data to reconstruct, detect, or otherwise support a claim of financial fraud. List the four main steps in forensic analytics.
   
2. 
   List the four Access objects of most use in forensic analytics with a brief description of each.
   
3. 
   Is importing data into Access a challenging task?
   
4. 
   List four features that show that Access was made to, amongst other things, perform calculations and to manipulate and query data.
   
5. 
   Why do Excel tables usually need some work before they can be used in Access?
   

6. 
   If you make a serious error in Access you can always "exit without saving" to undo the error (true/false).
   
7. 
   In Access it is quite easy to export tables or the results of queries to Excel (true/false).
   
8. 
   Numeric amounts made up of dollars and cents or numeric amounts, or where the user does not want any rounding errors should be formatted as text (true/false).
   
9. 
   It is good practice to include a primary key in Access tables (true/false).
   
10. 
    Access does not have a password encryption feature (true/false).
    

1.An Access database (.accdb) file size is limited to,

a.1,048,576 rows

b.10 gigabytes

c.2 megabytes

d.2 gigabytes

2.To get Access to start working you need to,

a.Open an existing file or create a new blank database

b.Run the Query Wizard "start new database"

c.Have a table that takes up more than 2 GB

d.Click "Run" from the VBA module

3.The order of the records in an Access table,

a.Make the execution of a query faster when the results are sorted from smallest to largest

b.Should have no effect on the results of any query

c.Can help to keep a file size below the Access file size limit

d.Can cause innocent people to be found guilty of fraud

4.The form most often used in forensic analytics is called a

a.Dashboard

b.Billboard

c.Signboard

d.Switchboard

5.A query that requires Access to use the data in two or more tables needs to include a,

a.Coin

b.Filter

d.Parameter

Cole Forester is Elaine’s nephew and a senior majoring in statistics. Both Elaine and Cole shared a common interest – solving brain-teasers. Elaine anticipated that Cole would try to stump her with challenging problems that he had come across since the family’s last get-together.

Sure enough, Cole had a lot of interesting mind-twisting puzzles. They tested each other for a while. To introduce the next problem, Cole took an Almanac from the book shelf.

Cole said, “Aunt Elaine, this is a question about the length of rivers. What do you think is the most common first digit of a river’s length? Do most river lengths start with a 5 as in 50 miles or 500 miles, or do river lengths mostly start with some other digit?”

“Wow, this is a good one!” said Elaine. Thinking aloud, she wondered, “It is more likely that the first digit of a river’s length is a 5, or a 6, or a 2?” “Wait, this is a trick question. Is the river’s length in miles or kilometers,” asked Elaine.

“The answer is the same whether length is measured in miles or kilometers,” answered Cole. “In fact, the answer is the same no matter how you measure it. You could measure it in beer can lengths if you wanted,” Cole stated.

After a few moments, Elaine said, “I’m stumped. I would think that each digit would be equally likely, especially if the measurement doesn’t matter.”

“The most common first digit for river lengths is 1,” replied Cole. “Look here in the almanac.” Cole opened the book to show Elaine a list of rivers and their lengths. Indeed, 1 was the most common first digit, followed by 2, then 3, and so on.

Cole continued, “And it’s not just river lengths, it could be the stream flow numbers, the populations of cities, the market capitalizations of listed companies, or almost anything in which numbers occur naturally.”

“Pretty interesting stuff, where did you find this one?” asked Elaine.

“We were talking about Benford’s Law in one of my statistics classes. Frank Benford, back in the 1930’s, saw that the first pages of his logarithm tables were more worn than the last pages. The first pages show the logs of numbers with low first digits. He did some analysis of real-world numbers and a little integral calculus and voila – Benford’s Law. Benford found that the probability of digits occurring is mathematically determinable and that large differences between the expected probabilities of the low and high digits exist. For example, the likelihood of a 1 being the first digit is more than 30% and the likelihood of a 9 being the first digit is less than 5%,” said Cole.

“Okay, you got me on that one. Now, I have one for you,” replied Elaine. For the next half-hour, Elaine and Cole challenged each other.

On her way home Elaine mulled over what Cole had said about Benford’s Law. Maybe she could use Benford’s Law to help identify which client accounts to audit. Her thought was that authentic numbers would follow Benford’s Law, while fictitious numbers would not follow Benford’s Law. If a fraud has been, or is being, perpetrated, the principles of Benford’s Law might help her identify the fraudulent (invented) numbers. She decided to call Cole in the morning to ask for some more information on Benford's Law.

Cole wrote a report that described the fundamental elements of Benford’s Law. Cole’s professor gave him somel references to recent academic and professional articles. After reading the report, Elaine felt that forensic analytics was appropriate as part of the analytical procedure process and as a fraud detection tool.

Elaine felt that Benford’s Law provided a valid benchmark against which to test the actual digit frequencies against the expected digit frequencies. Excesses of any specific digit, digit combination, or specific number, might point to a fraud (where the scheme causes the same numbers to be used over and over again), or a bias in the numbers (where employees duplicate transactions just below internal control limits). Another possibility was that the abnormal duplications were something innocently inherent in the way that the client did their business.

Elaine’s believed that a tool that could help detect fictitious numbers might assist in the audit of management’s existence or occurrence assertion. Detection of processing inefficiencies or biases might be offered to clients as a consulting service linked to the audit. She considered Benford's Law to be a new analytical procedure. The auditing standards require auditors to use analytical procedures in planning the nature, timing, and extent of other auditing procedures. Forensic analytics is a series of high-level data overview tests together with tests that drill down to look for small sets of data anomalies.

Elaine also knew that auditors were required to specifically assess the risk of material misstatement due to fraud. Benford's Law, because of its detective abilities, is one useful tool for assessing fraud risk. By identifying data that does not conform to the expected digit frequencies, the auditor could direct additional audit effort to these high-risk areas. For example, if an accounts receivable list showed significant deviations from Benford’s Law, the auditor could increase the level of confirmations, do additional scanning of transactions, and perform a more thorough examination of supporting documents.

Elaine decided that her newest client, Page Software, would be a good candidate for some Benford's Law and forensic analytics tests. Page Software was a NASDAQ listed company with their head office in Cincinnati, Ohio. In her discussions with CFO Roland Highman he noted a concern about the efficiency of the accounts payable system. With thousands of invoices totaling around $100 million, Roland did not have the resources to investigate the issue. Elaine wanted to make a good impression with the firm’s management. She believed that forensic analytics would be a good starting point and would also help with writing the formal audit program. Roland provided Elaine with an Excel file with last year’s accounts payable invoice data.

1. 
   Create an Access database with a file name PageSoftware.accdb.
   
2. 
   Import the PageSoftware_Invoices.xlsx file into your Access database. Name the table Invoices. Format the Amount field as Currency. Show a screenshot of your Access database with the Invoices table opened.
   
3. 
   Give the number of records do you have in your Access Invoices table?
   

1. 
   Use the Table Properties dialog box and enter a description for your Invoices table as is done in Figure 1.24. Show a screenshot similar to Figure 1.24 in your answer.
   
2. 
   Use the Access Documenter feature to document your database. Select only the Invoices table to be documented (you don't really have anything else at this stage). Show a screenshot of the first page of the documentation as your answer. Using a right click will magnify the page so that the top half of the first page is readable in a screenshot.
   

1. 
   Why is it important to do data cleansing before the data is analyzed?
   
2. 
   What sorts of things can one put into a cell in an Excel worksheet?
   
3. 
   What is the difference between using the Copy and Paste sequence in Excel, or using the Data→Get External Data commands?
   
4. 
   How can we use Excel's conditional formatting options in forensic analytics?
   
5. 
   Would a forensic report include a recommendations section?
   

6. 
   Data cleansing involves the detection and correction (or removal) of corrupt or inaccurate records from data tables (true/false).
   
7. 
   The column references in Excel start with Roman i, and increase by 1 as we move to the right (that is, ii, iii, iv, v, and so on) (true/false).
   
8. 
   An Excel file can be password protected (true/false).
   
9. 
   Excel results can be sent to Word documents reasonably easily (true/false).
   
10. 
    Excel's error indicator is a little red triangle in the bottom right corner of a cell (true/false).
    

1. 
   Excel's row limit is,
   

f.2 GB

h.There is no limit

6.The procedure to convert an Excel table to a normal range consists of a right click in the table followed by,

a.Clear Contents

b.Sort→Custom Sort

c.Insert Comment

d.Table→Convert to Range

7.Which phase in a forensic investigation is likely to be most relevant to Excel?

a.Data collection

b.Data preparation

c.Data analysis

d.Data entry

8.The risk of faulty decisions being made on the basis of errors that have crept into spreadsheets is known as,

a.Risk management

b.Market risk

c.Systematic risk

d.Spreadsheet risk

9.To make it so that some rows or columns are not viewable we use the ______ command.

a.Paste Special

b.Hide

d.Hyperlink

1. 
   Import the data in the Invoices table of PageSoftware.accdb. into Excel using the Data→Get External Data series of steps. Show a screenshot of the first 10 records of your Excel worksheet.
   
2. 
   Remove the connection to the Access database and convert the data to a normal range. Format the Amount field as currency with two decimal places. Show a screenshot of the first 10 records of your Excel worksheet.
   
3. 
   Add some conditional formatting to the Amount field using purple data bars. Follow the method used for Figure 2.7. Show a screenshot of the first 10 records of your Excel worksheet.
   
4. 
   Remove the formatting in (c) above. Save the Excel file as PageSoftware.xlsx when you are done.
   

1. 
   Password protect the file using the password Forensic to both open and to modify the file. Close the file. Open the file and show the opening dialog box for your password protected file. See Figure 2.10.
   
2. 
   Open Word 2007. Copy and paste the first ten rows of the Excel worksheet into your Word document. Use the method of pasting that does not keep the updating link with the Excel file. Show a screenshot of the Word page.
   
3. 
   Put your cursor in cell H2. Calculate the sum of the amounts in column E. Your answer should be $93,136,553.14. Now put your cursor in cell H4. Calculate the average invoice amount using the formula =H2/Count(E:E). Format cell H4 as currency with two decimals. Keep your cursor in cell H4. Click Formulas→Formula Auditing→Trace Precedents. Show a screenshot of the first 10 records of your Excel worksheet which should include column H. Clear the formula auditing arrows using Remove Arrows. Do not save the file after parts (b) or (c).
   

1. 
   In what forms are the PowerPoint support available on the Microsoft website?
   
2. 
   Give the five main sections of a PowerPoint presentation dealing with a forensic-related matter.
   
3. 
   A PowerPoint presentation on a forensic matter should only be made at the end of the investigation. There should be no interim report. Discuss.
   
4. 
   Who would be the audience for a presentation concerning a fraud investigation that dealt with members of senior management?
   
5. 
   Would it be a good idea to password protect a sensitive forensic-related PowerPoint presentation?
   

6. 
   The "divided attention" issue is made more acute the ______ (greater/closer) the distance between the presenter and the screen.
   
7. 
   Avoid using white as a background color in forensic presentations (true/false).
   
8. 
   A forensic report should be _____ (biased/unbiased).
   
9. 
   In addition to _____ (actual fallacy/factual accuracy) a good report should be well-written with no grammatical, typing, or spelling errors.
   
10. 
    It is best to read from PowerPoint slides so as not to make any mistakes (true/false).
    

11. 
    PowerPoint slides can include,
    

1. 
   Text
   
2. 
   Diagrams
   
3. 
   Images
   
4. 
   All of the above
   

12. 
    The largest section of the PowerPoint window is the,
    

1. 
   Slides tab
   
2. 
   Outline tab
   
3. 
   Slide pane
   
4. 
   Notes pane
   

13. 
    When planning the length of a presentation,
    

1. 
   Don't leave any time for audience questions
   
2. 
   Take into account audience questions
   
3. 
   It's better to have too much to say that to have open time at the end
   
4. 
   Include plenty of jokes, especially ones that poke fun at political views and moral beliefs.
   

14. 
    To copy a table from Word to PowerPoint,
    

1. 
   The user needs to retype the table from scratch in PowerPoint
   
2. 
   The table can be selected in Word followed by Copy and Paste
   
3. 
   The table needs to be copied to Excel and then from Excel to Word
   
4. 
   Is only possible for tables with eight or fewer rows
   

15. 
    To copy a screenshot from your computer screen to PowerPoint one can use,
    

1. 
   Alt+F4
   
2. 
   Alt+Enter
   
3. 
   Alt+PrintScreen
   
4. 
   Alt+Shift
   

http://www.wcpss.net/audit/strengths.html Click on (The full audit) to open the pdf file.

1. 
   Prepare a PowerPoint presentation on the background, engagement team, the approach and the general findings in the report. These sections refer to pages 4 to 16 in the report.
   
2. 
   Prepare a PowerPoint presentation on the areas of noted weakness in the report. This section is in pages 16 to 26 in the report.
   
3. 
   Prepare a PowerPoint presentation on the fraud prevention and detection section in the report. This section is in pages 27 to 34 in the report.