Elaine Ashby had come to enjoy the family holiday celebrations. While year-end was normally a busy time with Elaine preparing for her upcoming audits, the holiday events provided a welcome break from her clients. Being promoted to partner this past year at Alexander, Mitchell & Co. meant new audit responsibilities and several new clients. Elaine needed, and looked forward to, a relaxing evening with her extended family.

Cole Forester is Elaines 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 familys 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 rivers 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 rivers length is a 5, or a 6, or a 2? Wait, this is a trick question. Is the rivers 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, Im stumped. I would think that each digit would be equally likely, especially if the measurement doesnt 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 its 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 Benfords Law in one of my statistics classes. Frank Benford, back in the 1930s, 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 Benfords 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 Benfords Law. Maybe she could use Benfords Law to help identify which client accounts to audit. Her thought was that authentic numbers would follow Benfords Law, while fictitious numbers would not follow Benfords Law. If a fraud has been, or is being, perpetrated, the principles of Benfords 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 Benfords Law. Coles 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 Benfords 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.

Elaines believed that a tool that could help detect fictitious numbers might assist in the audit of managements 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 Benfords 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 firms 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 years accounts payable invoice data.

Required: Note: All answers and screenshots can be prepared in one Word document.

1.

1. Create an Access database with a file name PageSoftware.accdb.
2. Import the PageSoftware_Invoices.xlsx file below 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.

2. Refer to your Access database PageSoftware.accdb.

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.

3. Open Excel. For this case we will import the data from our PageSoftware.accdb Access database.

1. Import the data in the Invoices table of PageSoftware.accdb. into Excel using the DataGet 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.

4. Open Excel. For this case we will use the PageSoftware.xlsx Excel file created in Case 2-16 above.

Required:

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 FormulasFormula AuditingTrace 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).