Detecting Fraud, Errors and Analyzing Data Using IDEA

1
Detecting Fraud, Errors and Analyzing Data Using
IDEA

• Mary G. Elizondo, Comptroller, South Texas
  College
• Myriam Lopez, System Analyst, South Texas College
• Don Sparks, Audimation Services, Inc.

2
South Texas College

• 19,973 Headcount-Fall
• 1,600 Employees
• 14 Years Old
• 96,054,313 Unrestricted Fund Budget

3
Overview and Purpose of IDEA

• Fraud Detection
• Errors
• Irregularities
• Auditors recommendation

• Analyzing of Data
• Report on Data

4
Importing Data Into IDEA

• Excel worksheets
• Text files
• Databases

• PDF
• Flat files, Formatted files
• Other

5
Importing Data into IDEAFlat Files
6
Importing Data into IDEAFlat Files
7
Importing Data into IDEAFormatted Files
Requires template setup to capture data
8
Functions of IDEA

• Indexing
• Sorting
• Grouping
• Filtering

• Summarization
• Stratification
• Sampling
• Other

9
Functions of IDEAIndexing
Index allows you to display the database in a
specified order
10
Functions of IDEASorting
Sorting makes a copy of the database physically
sorted in the specified order
11
Functions of IDEAGrouping
Grouping is used to organize records, making
expandable and collapsible sorted groups
12
Functions of IDEAFiltering
Filtering allows you to display specific records
based on a criteria
13
Functions of IDEASummarization
Summarization creates a total of numeric fields
for all records with the same value in the key
field(s)
14
Functions of IDEAStratification
Stratification allows you to categorize records
into bands.
15
Functions of IDEASystematic Sampling

• Generates at specific intervals
• (for example every 100th record)

16
Functions of IDEARandom Sampling

• Generates a list of random records based on
  random number seed

17
Functions of IDEAStratified Random Sampling

• Generates a random sample for a specified number
  of records for each group. Requires the database
  to be stratified.

18
STC IDEA Uses

• Reconciliations
• Join Files for Analysis of Data
• Financial Ratios
• Graphing Financial Data

• Aging of Accounts Receivables
• Trend Analysis
• G/L Balances
• Expense Totals
• Utility Payments

19
Analyzing Data for Fraud Detection

• Duplicate payments
• Purchases from employees (conflict of interest)
• Employees also employed as independent contractors

• Comparing W-2 data to final check stubs
• Material changes in key data from period to
  period
• Lapping of receipts
• Benfords Law digit occurrence analysis

20
Fraud DetectionDuplicate Payments

• Import file to IDEA
• Clean the data (Data Extractions)
• Example Remove canceled invoices
• Create new field to remove special characters
• Function _at_upper(_at_strip(vendor invoice)
• Duplicate key detection analysis

21
Fraud DetectionDuplicate Payments

• 5. Review results - output table includes all
  invoices with duplicate vendor invoice number

22
Fraud DetectionCheck Gap Detection

• Analysis / Gap Detection
• Select field to use to detect gaps name results

23
Fraud DetectionCheck Gap Detection

• 3. Review results identifying gaps in document
  selected

24
Trend AnalysisPayments by Month

• Analyze material changes from period to period

25
Benfords Law

• First digit analysis
• Second digit analysis
• First two digits analysis
• First three digits analysis

26
Benfords Analysis
27
Current Errors/Fraud Events(Would Your Audits
Find?)

• Billion Dollar Electric Bills
• Dummy Vendor Account
• Audit Finds Ghost Employee
• Audit Detects 1 Million Missing
• Top Executive Takes 1.6 Million
• 6 Year Employee Skims 860,000
• School Superintendent to Serve 8 Years

28
What to Look for at Your Institution
29
Questions??

• Mary G. Elizondo, STC
• Myriam Lopez, STC
• Don Sparks, Audimation Services, Inc.
• Thank You!