Fourth Quarter Revenue Trending: Customer dimension for financial statement fraud testing

As the 2005 year-end approaches, fraud examiners need to remember the impact of revenue mistatements. While financial statement fraud only represents 7.9 percent of the number of fraud cases (per the ACFE's 2004 Report to the Nation), in dollar terms the median loss is $1 million per incident. Of these misstatements, fictitious and improperly timed sales are popular favorites as many company health stats are based on their ability to maintain and grow revenue.  

Get the data
First, download the sample Excel data file, Fourth Quarter Testing.xls, from www.auditsoftware.net/community/acfe/. Open it using the File, Open command from the Excel menu bar. Notice that it's an invoice sales file for the 2005 calendar year for two customers comprised of the following data fields: Customer Number, Invoice, Amount, Invoice Date, and Salesperson. Now that we have the necessary data, we'll work the following steps to finish the analysis:
1. Create calculation of the quarter
2. "Pivot Table" the data
3. Select each quarter and paste to a new sheet
4. Combine the quarters and complete the customer analytical
1. Create calculation of the quarter

The result of this step is to create a column that has "1st Qtr" in cells for any sale that took place in the first three quarters of the calendar year and "4th Qtr" for any sale that occurred in the last quarter of the year. Assuming a year-end calendar date of 12/31/2005, Figure 1 (below) shows that three new columns should be added to the Excel sheet.

[Figure 1 is no longer available. — Ed.]

In the first new column (column F), the date 12/31/2005 is entered. In the next column (column G), the formula starting in cell G2 is "F2-E2" to deduct from the year-end date the invoice date thereby resulting in the number of days the invoice is aged from 12/31/2005. In the last column (column H), the formula per the Excel formula bar (=IF(G2>=91,"1st 3Qtr","4th Qtr")) should be entered into H2. With the new data entered into cells F2 to H2, this data/formula should be copied and pasted down for every row with an invoice sale. Please note that the formulas will automatically adjust themselves. Once copied/pasted, the calculation of "1st 3Qtr" and "4th Qtr" will be properly applied depending on the invoice date.

2. 'Pivot table' the data
With the formulas all set, we'll utilize an Excel feature known as Pivot Tables. Some consider this feature advanced but the easy Pivot Table Wizard makes it a cinch to perform. Pivot Table provides exceptional power in summarizing data both in rows and then by columns. For our test, we'll only use it for summarizing sales by customer.

To begin, go to the Data menu and the Pivot Table and Pivot Chart Report selection. Select the Next button at the first presented screen, noting that you'll be working with an Excel table and want to create a Pivot Table. The window (Figure 2) will present the data that will be used in the Pivot Table.

[Figure 2 is no longer available. — Ed.]

At the next presented dialog box, select the Layout button and drag the columns on the right so that they appear in the appropriate rows and columns below. See the before (Figure 3) and after (Figure 4) versions of this window.

[Figures 3 and 4 are no longer available. — Ed.]

Note that in Figure 4 that the Sum of Amount field may start out as the Count of Amount when you first attempt this maneuver. To change it from Count to Sum (so that the total amounts can be calculated) drag it over and then double click on the Count of Amount. From here, simply change it to Sum. To close the Layout window, select OK and select Finish from the previous window. 

If you've done everything correctly, figure 5 will be created in a new worksheet in your Excel workbook.

[Figure 5 is no longer available. — Ed.]

3. Select each quarter and paste to a new sheet
To initiate this step, two new worksheets need to be added to this spreadsheet - one for the first three quarters and the other for the fourth quarter. Do this by selecting Worksheet from the Insert menu bar item in Excel. One sheet can be re-named "1st 3Qtr" and the other "4th Qtr" although it doesn't matter what names you give them for this test.

With the two new worksheets, go back to the pivot table worksheet, which should be named Sheet 1. In this worksheet, the invoice amounts now have been summarized by customer.

By selecting the dropdown arrow next to the word "Quarter," the sales will be summarized by the two quarterly options. First select "1st 3Qtr" which will filter the data for the first three quarters and summarize it by customer. (See Figure 6)

[Figure 6 is no longer available. — Ed.]

Then select cells A5 to B6 (only the customer number and summarized invoice amounts), select Copy from the Edit menu, and place your cursor in cell A1 in the new worksheet "1st 3Qtr." Select Paste Special from the Edit menu and then the option for "Values" (3rd option on the left from the top). Select OK to copy the first three quarters' sales to this worksheet. Now do the same process for the fourth quarter sales but this time select "4th Qtr" from the dropdown menu next to the word Quarter in the pivot table and copy/paste special the fourth quarter information to the "4th Qtr" worksheet.

4. Combine the quarters and complete the customer analytical
With the two summary worksheets set with data, all that's needed is to relate the fourth quarter sales with the first three quarters. To start with a cleanup step, go to the "4th Qtr" worksheet and insert a row at the top of the sheet so that titles can be added for the customer number and fourth-quarter sales. Then, in cell C1, type "First 3 Qtr" and place your cursor in Cell C2. Select Function from the Insert menu and find the Vlookup function. Then complete the function dialog box as in Figure 7.

[Figure 7 is no longer available. — Ed.]

This function will take the Lookup value (customer number in A2), search the table array in the "1st 3Qtr" sheet for a match, and then write the amount in the column next to the customer number found in the "1st 3Qtr" sheet (designated by the "2"). As shown in figure 7, also enter "FALSE" in the range lookup field, which will ensure an exact customer number match before writing the first three-quarter amounts into the fourth quarter sheet. This formula in C2 can be copied down to every customer line in the "4th Qtr" worksheet. By completing this step, the amounts for the first three quarters have been aligned to the fourth quarter. The final results should look like Figure 8.

[Figure 8 is no longer available. — Ed.]

As you can clearly see, customer 35000042 had a large increase in fourth-quarter sales. While this may be the expected business pattern for this customer, it highlights a review consideration in the examination. Fraudsters never expect anyone to complete quarterly analysis of customer sales and it's in this unpredictability that fraud examiners can find their strength and catch the fraud.

Richard B. Lanza, CFE, CPA-CITP, PMP, president of Cash Recovery Partners, L.L.C., in Lake Hopatcong, N.J., provides audit technology and project management assistance to companies. He focuses much of his time in developing computerized audit and fraud tests.