Menu
Home
Log in / Register
 
Home arrow Business & Finance arrow Fraud and fraud detection
< Prev   CONTENTS   Next >

GEL-1 AND GEL-2

The purpose of the GEL tests (GEL-1 and GEL-2) is to detect the relationship or link within the data file as potential indicators of fraud.

The GEL tests establish the link between two selected fields with the first field being the key field and the second field being the element factor over the entire data set.

GEL is short for the gestalt element link. Gestalt is defined by the online Merriam-Webster dictionary as: a structure, configuration, or pattern of physical, biological, or psychological phenomena so integrated as to constitute a functional unit with properties not derivable by summation of its parts. 1

An example for using GEL tests is to detect bribery or improper relationships. Since most entities do not have access to the records of the payer company, their own data can be analyzed to detect improper relationship patterns.

The following GEL-1 example depicts testing of potential links between the sales representatives and their customers. A high GEL factor may be an indicator of an improper relationship or merely that certain sales representatives are assigned certain customers. A good understanding of the business practices and procedures is necessary to properly interpret the results.

GEL-1

The example uses the "Sales Transactions" database that contains the fields SALESREP (sales representative) and CLIENT_NO (client or customer number). We want to know the number of transactions each sales representative had with each client expressed as a ratio. The higher the GEL-1 ratio, the more transactions were done with the particular client that may indicate a special relationship.

Summarize the Transactions of the Potential Relationship Fields

FIGURE 6.11 Summarize the Transactions of the Potential Relationship Fields

Step 1. Summarize by SALESREP and then CLIENT_NO and name the new file "Summarization G1-1." Note that the file name contains the step identification to keep tracking simpler as shown in Figure 6.11. This step creates a file that shows how many records or transactions there are for each sales representative by client.

Step 2. Append a field named REC_NO using the @Recno( ) function shown in Figure 6.12. Associating the record number is necessary to properly join the ifile together later.

Step 3. Obtain the highest number of records or transactions by sales representative. Use the Top Records Extraction feature to obtain the top-most record for NO_OF_ RECS grouped by SALESREP as in Figure 6.13. Name the file "Top Records G1-3."

Create a Record Number Field

FIGURE 6.12 Create a Record Number Field

Obtain the Highest Number of Transactions for Each Sales Representative

FIGURE 6.13 Obtain the Highest Number of Transactions for Each Sales Representative

Create a File That Excludes the Top-Most Transactions for Sales Representatives

FIGURE 6.14 Create a File That Excludes the Top-Most Transactions for Sales Representatives

Step 4. Join the "Summarization G1-1" database with the "Top Records G1-3" database as the primary and secondary database respectively, as in Figure 6.14. The Match Key Fields are REC_NO for both the primary and secondary files. Use the match option of "Records with no secondary match." Name the file "Join G1-4." The result is those records that are not top transactions.

Step 5. Summarize the "Join G1-4" ifile by SALESREP using NO_OF_RECS as the numeric field to total as shown in Figure 6.15. This obtains the total number of records or transactions by SALESREP, excluding the top transactions. Name the file "Summarization G1-5."

Step 6. Summarize original database of "Sales Transactions" by SALESREP to obtain total number of transactions for each sales representative as displayed in Figure 6.16. Name this file "Summarization G1-6."

Step 7. Join as primary database, "Summarization G1-5" with the "Top Records G1-3" database. Join using the "Matches only" option and the Match Key Fields as SALESREP, as shown in Figure 6.17. We do not need all the fields from both the primary and secondary files. In fact, retaining all the fields would create confusion. By selecting

Obtain the Total Number of Transactions by Sales Representatives Excluding the Top-Most Transactions

FIGURE 6.15 Obtain the Total Number of Transactions by Sales Representatives Excluding the Top-Most Transactions

Obtain the Total Number of Transactions for Each Sales Representative from the Original Sales File

FIGURE 6.16 Obtain the Total Number of Transactions for Each Sales Representative from the Original Sales File

the Fields button for the primary file, choose the SALESREP field. Fields to include from the secondary file are NO_OF_RECS and CLIENT_NO. This results in a file that has the top transactions for each client. Name this file "Join G1-7."

Obtain the Total Number of Transactions for Each Sales Representative from the Original Sales File

FIGURE 6.17 Create a File with the Top Transactions for Each Client

Step 8. Rename the field NO_OF_RECS to TRAN_PER_FREQ_CLIENT_NO to display the number of transactions with the sales representatives' most frequent clients. Refer to Figure 6.18.

Identify the Number of Client Transactions by Renaming the Number of Records Field

FIGURE 6.18 Identify the Number of Client Transactions by Renaming the Number of Records Field

Step 9. Make "Join G1-7" as the primary database and join it with "Summarization G1-6" as the secondary file using the "Matches only" option shown in Figure 6.19. The Match Key Fields is SALESREP. Include from the primary database all fields and only the NO_OF_RECS field from the secondary file. Name the file "Join G1-9." The resulting file will have total transactions and top-items transactions.

Put All the Transactions Together

FIGURE 6.19 Put All the Transactions Together

Step 10. Rename the NO_OF_RECS field to TOTAL_FOR_SALESREP as in Figure 6.20.

Rename the Number of Records Field to Identify the Total Transactions for Each Sales Representative

FIGURE 6.20 Rename the Number of Records Field to Identify the Total Transactions for Each Sales Representative

Step 11. There is one final step to obtain the ratio that we are looking for. Append or create a field called GEL_1 with four decimal places, using the equation of TRAN_ PER_FREO_CUSTOMER_NO/TOTAL_FOR_SALESREP as shown in Figure 6.21.

Create the GEL-1 Field and Perform the Calculation

FIGURE 6.21 Create the GEL-1 Field and Perform the Calculation

The final file is shown in Figure 6.22 with the GEL_1 ratio indexed by descending order to display the highest to the lowest. A high GEL_1 ratio shows an overview of the link between the sales representative and clients.

Resulting File with the GEL-1 Ratio Indexed by Descending Order

FIGURE 6.22 Resulting File with the GEL-1 Ratio Indexed by Descending Order

The auditor may decide to further review transactions and relationships for those with a GEL_1 ratio of 0.6000 or more.

For instance, SALESREP 105 falls into this criterion with a GEL_1 factor of 0.7500. He had a total of 160 transactions but 120 of them were with CLIENT_NO 30608. In other words, 75 percent of SALESREP 105 transactions or sales were with one customer. He only had 40 transactions with other customers. Further analysis is needed.

 
Found a mistake? Please highlight the word and press Shift + Enter  
< Prev   CONTENTS   Next >
 
Subjects
Accounting
Business & Finance
Communication
Computer Science
Economics
Education
Engineering
Environment
Geography
Health
History
Language & Literature
Law
Management
Marketing
Philosophy
Political science
Psychology
Religion
Sociology
Travel