The GEL-2 test can provide additional information on specific sales representatives and their clients. In this example, we will continue to focus on sales representative 105.
Step 1. First, extract all records from the "Sales Transactions" database for sales representative 105. Use the equation or criterion of SALESREP = = 105, as in Figure 6.23. Name this file "G2-1."
FIGURE 6.23 Create a File with All Transactions for Sales Representative Number 105
Step 2. Summarize by CLIENT_NO, in Figure 6.24 , to obtain number of unique clients for this particular sales representative. Name the file "Summarization G2-2"; it should have the results shown in Figure 6.25 .
FIGURE 6.24 Obtain Transactions by Clients with Sales Representative 105
FIGURE 6.25 Resulting File of Transactions by Clients with Sales Representative 105
Step 3. Summarize the "Sales Transactions" database by CLIENT_NO to obtain the total transactions for each client as in Figure 6.26. Name this file "Summarization G2-3."
FIGURE 6.26 Create a File with Transactions by Clients from the Sales Transaction Database with All Sales Representatives
Step 4. Join the "Summarization G2-2" database as the primary database to the "Summarization G2-3" database as in Figure 6.27. The "Summarization G2-3" is the secondary database. Use the "Matches only" option and CLIENT_NO as the Match Key Fields to attach the total transactions per customers to the file. Name this file "GEL 2 Salesrep 105 G2-4." Having the sales representative number in the filename allows you to easily identify the sales representative you were applying the GEL-2 test to. Since the auditor decided that the GEL-1 ratio cut-off was 0.6000, individual GEL-2 tests of sales representatives 108, 110, 117, and 119 would be needed as they all exceeded the cut-off ratio as shown in Figure 6.22 .
Step 5. Rename the NO_OF_RECS1 field to TOTAL_NO_OF_RECS as in Figure 6.28.
Step 6. Append the GEL_2 field with four decimal places using the equation of NO_OF_RECS/TOTAL_NO_OF_RECS to produce the GEL_2 ratio as seen in Figure 6.29.
FIGURE 6.27 Put All the Information Together
FIGURE 6.28 Identifying the Total Number of Records by Renaming the Additional Number of Records Field
FIGURE 6.29 Create the GEL_2 Field and Perform the Calculation
The final file is shown in Figure 6.30, with the GEL_2 ratio indexed by descending order to display the highest to the lowest. This ratio provides specific links between the sales representative and his or her clients.
FIGURE 6.30 Resulting File with the GEL_2 Ratio Indexed by Descending Order
The resulting file shows that sales representative 105 dealt with client number 30608 120 times out of 131, while the client purchased from all other sales representatives only 11 times. In addition, this sales representative sold 40 times to client number 30501, while the same client purchased from all other sales representatives only 14 times.
FIGURE 6.31 Final Result of the GEL-2 Test
Clearly, there is a strong business relationship between this sales representative and clients 30608 and 30501. Additional investigation is needed to determine whether this relationship is of benefit to the company or of benefit to the sales representative and the clients to the detriment of the company.
Additional steps can be added to this GEL-2 example that provide more information, such as gross sales amounts totals. The objective was to ensure that the reader understands the concept and steps without added complexities. An IDEAScript has been developed to automate the GEL-1 and GEL-2 processes that provide the additional relevant information shown in Figure 6.31. The IDEAScript is available on the companion website.
Applying the two GEL tests against an accounts payable file and analyzing the Check Authorized By field and the Vendor field can indicate favorable status given to some vendors by the authorizing personnel. It may also provide an indication of a money-laundering or a disbursement scheme.
Applying the GEL-1 test to a corporate credit card file may reveal some red flags when the following field combinations are selected.
1. Employee ID versus general ledger account number.
2. Employee ID versus vendor ID (business name).
3. Employee ID versus vendor location (city, province/state, or country). Low GEL ratios should be reviewed.
4. Vendor ID versus employee ID.
The GEL-2 test can be then be run against the selected results above.
Using the GEL-1 test on a sales file from a point-of-sales system for EMPLOYEEID (or SERVERID) field with the order date field may indicate the suppression of sales invoices for certain employees. A low GEL ratio, where the number of transactions is low compared to total transactions for the day, should be analyzed by employing GEL-2 on specific EMPLOYEEID. Was the employee on a short shift, and did not serve many tables, or were there other unexplained anomalies that require investigation?
This chapter outlines some advanced data analytical tests that provide relationship information. Correlation calculates how well or poorly two variables or data set fields move together in relationship to each other. Trend analysis shows how the trend line its with the actual data and can make predictions based on the analysis. These two procedures are simple to apply as they are built into the IDEA software.
The two GEL tests were developed by Gee, Element, and Luciani to establish a relationship link between two fields within a data set. While the steps may be numerous, especially for the GEL-1 test, they are well worth doing as there is a potential for ferreting out previously unseen ties or relationships. The step-by-step procedures outlined allow the reader to understand what can be accomplished with the IDEA software and understand exactly how the GEL procedures work. An IDEAScript has been created to allow all these procedures to be processed automatically just by applying the script. Well-written scripts can be applied effectively even by the most novice of users. Further IDEAScript information is provided in Chapter 17.
Having outlined data analytical tests in detail in this chapter and in Chapter 5, we move on to specific fraud schemes and how data analytics can detect them.
1. Merriam-Webster Dictionary, "Gestalt," accessed April 19, 2014, merriam-webster .com/dictionary/gestalt.