DATA ANALYTICS A CORE COMPETENCY IN THE

DATA ANALYTICS – A CORE COMPETENCY IN THE NEW MILLENNIUM Donald E Sparks, CIA, CISA, CRMA, ARM Christopher Peters, CFE, MBA

Session Objective Interrogating data to gain insights into actions, good or bad, has progressed to an everyday task of professional auditors and fraud investigators. By looking at events occurring in other organizations as reported in the news, this session will help you answer the base point “would your analytical skills have found similar issues in your agency/company right now”.

Agenda What are we talking about? Definitions What is in your tool box? Demonstrations of common audit terminology from real cases from the presenters & news • Wrap – up: Answer Questions; take-aways from this session - “what is next? ” • •

Data Analytics Defined Involves processes and activities designed to obtain and evaluate data to identify areas of key risk, fraud, errors or misuse; improve business efficiencies; verify process effectiveness; and influence business decisions. ISACA White Paper 2011

Key Data Analytics Terminology • Stratification • Summarization • @ Functions • Join Files • Append Files • Field Statistics • Search • Direct Extraction • Duplicate Key • Sampling Techniques • Judgmental Sampling • Indexing • Project Overview • Scripting • Repeatable • PRN and PDF • Importing • ODBC

What about Data Analytics? World Class Good Average Source: July 2011 ISACA

What about Data Analysis?

What’s In Your Tool Box?

Auditors & DA: Where did it start? Stratification - involves creating bands based on ranges of values (normally from the minimum to the maximum values of one or more fields) and accumulating the records from the database into the appropriate bands. By totaling the number of records and value of each band, you can gain a profile of the data in the database.

Credit Balances in Accounts Receivables Summarization - accumulates the values of fields for each unique key. For example, summarizing an Accounts Payable database by account number (the key) and totaling invoice amounts produces a database or Results output of outstanding liabilities by supplier.

Payroll Issues in USA Federal Government Search - accumulates the values of fields for each unique key. For example, summarizing an Accounts Payable database by account number (the key) and totaling invoice amounts produces a database or Results output of outstanding liabilities by supplier. Ghost employee search. Former employee sentenced to 32 months in prison after admitting perpetrating multiple frauds under the guise of performing undercover work as a CIA agent. Misleading and false statements dating back to hiring by the agency in 1988, followed by payment of retention incentive bonuses amounting to 25% of his salary authorized for a maximum of six years that continued for 22 years. In 1994, as an employee, began falsely claiming to be a CIA agent, a lie that became the core of his scheme to justify frequent absences from the office between 2000 and 2013. Additionally, he cited malaria contracted during Army service in Vietnam – although he neither had malaria nor served in Vietnam – as the basis for obtaining subsidized parking. Employee also committed travel fraud involving vouchers, lodging, first-class air accommodations and misuse of a government passport. The frauds were discovered after he was honored at a retirement party in September 2011 but remained on the agency’s payroll until April 2013, when he officially retired. The agency’s Office of General Counsel referred the case to the Office of Inspector General in February 2013.

Employee Data Analysis Techniques • Ghost Employees Copyright 2013 - GPS Consultants, LLC

Integrated Data Mining Field Statistics are good if auto-generated for every data set imported into your software. The user can customize but is presented with a lot of information to improve client understanding. Easy to find transactions posted on weekends or before or after normal work hours without writing a query. Demo –Theme park $1 mil Fraud

Summarization Accumulates the values of Numeric fields for each unique key. For example, summarizing an Accounts Payable database by account number (the key) and totaling invoice amounts produces a database output of outstanding liabilities by supplier. You may select up to eight fields to summarize. Demo- AP $860, 000 fraud/also with benford’s

Import PDF Files An east coast manufacturing company had persistent rumors of AP fraud but no one could catch the “unsub”. The CEO and CFO called on a CFE to take a look. The CFE asked three questions: 1. How many employees in AP? 2. How many years have they been in AP? 3. Can You Export 6 years of AS 400 AP ledger?

Find Employees in Vendor Files County clerk hires son’s 28 year old live-in girlfriend; then promotes to second in command INSERT News Clipping here

Join Tables Combine two databases into a single database. Each data file must contain at least one common field (referred to as match key fields). The match key field (a maximum of eight) does not need to have the same name or length but must be of identical field type. You can use the data manipulation feature to change field types. Demo – Winter Park Fraud

Keyword Search Find text in fields of a database. Enter the text you are looking for, Search looks for matches within the specified fields of one or more databases. Search enables keyword searches, the use of wildcards, and proximity searches. Results are displayed the results window or in an extraction database. Demo – finding T&E fraud

Keyword Search features can assist in locating abnormal records in large data sets. A law force official resigns when admitting she used her company vehicle and company issued purchasing card to pay for non-company related travel and personal shopping trips. Stores such as TJ Maxx, Ross, . Walmart and Piggy BQ restaurants. Manager resigns admitting she misused company credit cards

Credit Balances Rarely is there a valid reason to hold on to a credit balance in areas such as accounts receivables. Before presenting your findings, don’t overlook the potential for a valid business reason to justify holding a credit balance. Sample some of the transactions and pull the support files to see whether patterns emerge. Demo – Don’s biggest fraud ever. Insert aging. .

Looking for Duplicates Use Duplicate Key Detection to find records with or without duplicates in one field or up eight fields (key fields). You can use Duplicate Key Detection to identify Duplicate items, such as duplicate invoice numbers and items without a duplicate, such as testing for matching debits and credits. Work Force Fraud splitting contracts.

Employees as Vendors Cross-matching data in the customer data to the employee data can identify special issues. One approach is to distill address data to just numbers to avoid variances in punctuation and spelling and return a higher portion of true positives. Demo - Son’s girlfriend lives in same house as county manager

False Billing Scheme – Distributor • Identifying phantom goods and services Purchased from Third Source Data Sold from Distributor Data Copyright 2013 - GPS Consultants, LLC

Prepare to Dominate 1. Create a Script to run at desktop 2. Arrange for consistent flow of data 3. Create/run a Script using a task scheduler Demo Project Overview – fastest way to script and maximizes working paper documentation

The End!! Audimation Services, Inc. Founded in 1992, Audimation Services, Inc. is a leader in providing data analysis technology to help companies understand their data, assess risks, test controls, and fight fraud. As the U. S. distributor of IDEA, Case. Ware Monitor and Smart. Exporter for SAP, we help our clients maximize their technology investments by providing support, training and other valuable resources. Christopher Peters, CFE, MBA Christopher is the co-founder of GPS Consultants, LLC, a boutique management consulting and forensic accounting firm. Prior to starting GPS in May 2011, Peters was the Chief Financial Officer for one of the largest home remodeling companies in the U. S. Prior to his CFO position, Chris worked for a regional CPA firm where he focused on advising clients with improving business operations and solving problems through forensic accounting, internal auditing, litigation support, and data analysis.