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.
A fraudster often will post a questionable sales entry in the fourth quarter because he knows that the company isn't likely to announce a performance target for that time of the year. There are many ways to detect such a scheme; one of my favorites is to relate fourth quarter sales to the rest of the year. Most entities do this with a high-level test such as an analysis of revenue by quarter. But to identify the real root of fraud, we need to incorporate a new dimension - the customer dimension. This column will present a data analysis process in Excel to relate, at a customer level, the fourth-quarter invoice sales to the first three quarters for enhanced trending purposes.
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 menau 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
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.