Fraud Detection & Data Analytics

Course Topics

• Overview of Data Analytics • Accounts Receivable • Overtime Audit • Inventory / Obsolescence • Commissions • Payroll • Sales • Purchasing • Telephone Charges • Regression Analysis • Credit Card Purchases • No Vacancy p 4-8 p 9-11 p 12-13 p 14-17 p 18-20 p 21-23 p 24 p 25 p 26-33 p 34 p 35-36 p 37-39

Course Topics

• Time and Expenses • Reference Material • Appendix p 40 p 41-42 p 43 p 44-45 p 46-47 p 48-50 p 51 p 52 p 53-61

Importance of Data Requirements Request

• Incorrect statement of audit data requirements can result in information received not being what was needed

• Failure to identify important fields requiring the auditors to ask for a second and third data extraction

• Obtaining the information in a format that is not conducive to further electronic use (Monarch)

• Failing to tell the client the file format for the requested data file

Incorrect Identification of Audit Population

• Important to pull all necessary accounts

Data Analytics….

Risk of Making an Error

• When extracting data it is critical to ensure the decimal place

is in the correct place

• Risk: potential incorrect definition of the format file

• The incorrect use of the join function when combining two or more files

• Auditors have used duplicate files

Fraud Application

• Analyze the control framework to identify areas of greatest risk

• Examine possible risks including those in Consideration of Fraud in a Financial Statement, SAS#82 and Deterrence, Detection,

Investigation and Reporting of Fraud, SAS #3 • Identify the symptoms related to these risks

Proactive Analysis – Batch jobs

• Develop batch jobs to test for

symptoms

of fraud

• Proactively looking for fraud symptoms means auditors

can detect and even prevent the fraud

• i.e. payroll batch test example

• It is possible to perform 100% examination of data

• Determine a frequency for running the jobs

• Run the jobs and review the results

Accounts Receivable

• To compensate for her “meager” salary, an A/R clerk started cashing customer payments

• When customers paid by check, she used one of two methods:

• 1) Take the customer check, cash it, and don’t record the payment.

• In this case, she would create a new customer monthly statement to make it look like the payment had been credited to the account.

• The account balance would still show an account balance higher than the

Accounts Receivable (Cont’d)

• 2) Cash the customer’s check and hide the theft by crediting the customer account, meanwhile debiting a fictitious account

she had set-up.

• This meant that her receipts balanced, and the valid customer accounts were correct. Later, she would write-off the fictitious account as bad debt

• The company had established a voice system that customers could call to get their balances, and the customers were

Accounts Receivable

• An auditor had looked at trends in the A/R department and

determined that the amount written off as bad debt had increased significantly in the last year

• An aging on the accounts that had been written off was performed • The results showed that some accounts had been written off even

though the balance had only been outstanding for 3 months

• The usual period was one year and only after three reminders had been sent out

Overtime Audit

• Identify Managers with overtime expenditures greater than 1.5 times last year’s overtime totals

• Highlight all managers with overtime expenditures greater than or equal to 10% of their regular pay budget

• Identify all employees with total overtime payments equal to more than 25% of their salary

Overtime Audit

• The overtime can be analyzed by type:

• Regular overtime, 1st _{day of rest -Saturday}

• 2nd _{day of rest – Sunday}

• By matching the overtime records to the attendance system, audit can determine if individuals are consistently taking the next 2 days off with or without pay

• Total overtime paid by type of overtime was calculated to determine

instances where individuals were working more overtime on the second day of rest

• at double time rates vs. regular overtime (at time and a half)

Using ACL to Identify Obsolete Inventory Items

• Raw materials / sub-components should be deemed obsolete when the parent finished good product is declared obsolete (in some cases)

• Where used reports can be utilized to determine where a raw material is used within finished good products

• Items used to maintain the obsolete item should be declared obsolete; unless items supported another piece of equipment

Obsolete Identification

Example:

• Auditors obtained a file of obsolete items for the past three years; compared those items to an active inventory listing and subsequently….

• Flagged those items that supported obsolete pieces of equipment

• Secondarily, they flagged those items marked obsolete to see if they supported other non-obsolete pieces of equipment

Inventory Levels

• Identify inventory items with stock levels greater than maximum stock levels

• Stock levels on hand that would satisfy several years worth of use

• Low turnover rates

• Check for shelf life flags …

• extract items which have been stored for periods longer than stated shelf life

• Compare turnover rates with reorder levels

• (Example: Does it make sense to set reorder qty at 100 if it took five years to order 25 items)

Inventory Levels (cont’d)

• Compare turnover rate by item by location

• Run statistics to determine negative inventory reporting

• Timing – Shipping reports product shipped before manufacturing floor reports product as produced

• Partial shipments – risk of backflushing full order

• Trend analysis on inventory shrinkage (avg. price, yearly loss, etc.)

• Search for inventory at zero standard cost

• Search for finished goods standard cost that does not equal sum of components

Commissions / Bonus

• Review policies and procedures to understand commission schedules and conditions for bonuses.

• Extract data from commission system for past five years to determine commission and bonus payment pattern

• Reviewing the total of these payments for the past five years by region allow auditors to identify trends and determine regions with largest total payments for commissions / payments

Commissions

• Identify all employees by geographical area who had received commissions amounting to 20% or more of their regular

salary figures

• Identify all locations with significant increases over last year’s budgets for commissions / bonuses

• Totals by location, sales area, and other criteria can be reviewed

Commissions

• Select the job title, work location, supervisor and rate of pay from the personnel system

• The compensation system is used to determine total

commissions and bonuses paid to each employee in the sample (for current year and past three years)

• The sales system was used to identify the annual sales volume for these employees for the past three years

• Note: An analysis of returns after quarter-end by responsible salesperson could be performed as well

Payroll Fraud Symptoms

• Risk of continuing to pay ex-employees

• Risk of paying new employees prior to their actual start date • Risk increases with rapid growth, high turnover

• Compare the personnel system to the payroll system • This batch job can be run before checks are released

• Thereby, erroneous checks can be identified

Payroll

• Test for Duplicate Direct Deposit Numbers

• Test for Duplicate Employee Addresses

• Example: Developer responsible for the benefits system was able to create three ghost employees and direct their pay to his bank account

• Compare payroll disbursements to employee master file to identify discrepancies

Payroll Analytics

• Direct deposit number – duplicate • No medical deductions

• No tax deductions

• Employee name duplicate • Employee address duplicate

• Employee / vendor – addresses match • Overtime pay greater than X amount • SSN – blank

Sales

• Numerous transactions at multiple store locations requires data analytics to track

• Discover improper discount or promotional applications • Detect trends in pricing, inventory by store or region • Trace sales to valid vendors

• Ensure validity of vendors through approval tracking and

duplicate testing on vendor master file

• …this can prevent duplicate, fraudulent, and miscoded transactions

Purchasing Fraud and Detection

• Fictitious vendors

• Filtering P.O accounts, blank fields

• Join function to search matches in vendor and employee addresses, phone numbers

• Duplicate, similar and generic vendor searches

• Identifying knock off vendors through “sounds-like” function

• Sequential invoices

• Gap analysis

Telephone Charges

• Telecommunication charges were increasing steadily

• The data received from the phone company included:

• the originating phone number • phone number called

• date and time of call • length of call in minutes • cost

• ACL was used to run several reports, including:

• All long distance calls longer than three hours

• The results showed a number of calls that were exactly 999 minutes (over 16 hours) in length

• By performing a detailed review of the activity on these lines…

• the auditors found that other calls had been made during this time

period on phones that did not enable multiple calls

• The root cause after checking with the phone company was a faulty communication switch that remained open after the

phone had been hung up

• Failing to register the completion of the call, thereby resulting in an erroneous long distance charge

Telephone Charges (Cont’d)

• The phone company had a maximum call length of 999

minutes or the charges would have been even higher

• All charges were reversed by the company

• Overall result was 17% reduction in the total telecommunications bill

Telephone Charges (Cont’d)

• In some cases, where calls were longer than 180 minutes, large data transfers were being performed between two sites. • The auditors summarized the detailed billing information and

determined that usage was high enough to justify leasing a

dedicated line

• Reducing the overall cost and improving the transmission speeds and reliability of file transfers.

Telephone Charges (Cont’d)

• Another test centered on possible abuse of long distance privileges

• Auditors identified calls after working hours or during holiday

periods

Recommendations:

• Restrict the ability to call long distance after 6pm and on weekends and holidays • A simple change to the company’s telecommunication switch software to block all

Telephone Charges (Cont’d)

• The audit reviewed the accuracy of the telephone bill and the efficiency and effectiveness of leased lines

• The auditors selected a sample of dedicated leased long distance lines from a number of branch offices

• Automatically generated confirm letters to be sent to the branch offices

• The letter asked the branch managers to verify the accuracy of the charges

by ensuring the lines were still connected • Managers were asked to justify the lines

Telephone Charges (Cont’d)

• In 10% of the cases, the lines were no longer required but the service had never been canceled

• In a further 5% of the cases, the lines were not even physically connected to a phone. Because of office redesigns, telephone

Telephone Charges (Cont’d)

• The use of audit software to analyze thousands of lines of

detailed calling information, and to highlight anomalies or

potential abuses, greatly improved the effectiveness of the audit

• Overall result was 17% reduction in the total telecommunications bill

Regression Analysis

• Regression Analysis is a statistical method for examining a series of records to determine if the values are appropriate

• A mathematical relationship is established between the driver and the dependent variable

• For example, the amount of heating fuel used is dependent on the temperature • Using regression analysis you can estimate how much heating fuel will be used

given a certain temperature

• Auditors and fraud investigators can compare predicted values with actual values

Credit Card Purchases

• The bank was losing millions of dollars due to fraudulent

purchases made using stolen credit cards

• The audit director was asked to identify a way to identify inappropriate purchases as early as possible

• Regression analysis was used to compare each transaction per cardholder to their

normal purchasing pattern

• Anomalies were identified and cardholders called to determine if they had made the purchase

Credit Card Purchases

• A second analysis was to review vendors

• A third analysis was a comparison of the dates and locations of purchases

• Two purchases made on the same day, but in widely separated zip codes would cause a flag to be set

No Vacancy

• The manager of a large apartment complex in the city claimed he had a 5% to 10% vacancy rate

• The rate was not significantly higher than any of the other buildings owned by the company

• The manager was renting out apartments to friends and relatives, without having them sign a lease

• They paid him a nominal sum each month and he kept the cash

• Since they did not sign the lease, the owners of the complex thought the apartments were empty

No Vacancy

• The auditors were reviewing the leases for all the buildings owned by the company

• They had enough data to use regression analysis to predict

the number of people living in a building

• Based on electricity, water, and heat usage

• The predicted number of tenants was then compared with the actual number of people listed on the leases

No Vacancy

• The analysis estimated that there should be 1,203 tenants, but the leases only accounted for 1,147

• A careful review of all apartments found 58 people living in 14 apartments that the manager had rented to his friends and relatives, without a signed lease

Digital Analysis

• A growing area of fraud prevention and detection involves the examination of patterns in the actual data

• The rationale is that unexpected patterns can be symptoms of possible fraud

• A simple example of the application of this technique is the search for duplicate transactions, such as the same invoice number and vendor number

Benford’s Law…

• More advanced techniques take data analysis to another level,

examining the actual frequency of digits in the data

• Benford’s Law developed by Frank Benford in the 1920’s makes predictions on the occurrence of digits in the data

• Benford’s Law predicts that the first digit in a large number of transactions (10,000 plus) will be a ‘1’ more often than a ‘2’; and a ‘2’ more often than a ‘3’

Benford’s Law…

Signing Authority

• Analysis of contracts showed the digits ‘49’ were in the data more often than expected

• Classifying on the Contracting Officer for all contracts with ‘49’ determined that a contracting manager was raising contracts for

$49,999

• Contracts under $50,000 could be sole sourced;

• Contracts $50,000 or higher had to be submitted to a bidding process

• He was raising contracts just under the financial limit and directing them to a company owned by his wife

Ratio Analysis

• A useful fraud detection technique is the calculation of ratios for key numeric fields

• Data analysis ratios point to possible symptoms of fraud

• Commonly used ratios are:

• The ratio of highest value to the lowest value (maximum / minimum) • The ratio of the highest value to the next highest (maximum / 2nd

highest)

Ratio Analysis

• For example, auditors concerned about prices charged for a particular product…

• Calculate the ratio of the maximum sales price to the minimum

sales price

• If the ratio is close to one, they can be sure that there is not much variation between the highest and lowest prices charged to

customers

• If the ratio is large, this could be an indication that a customer is being charged too much or too little

Contracting Kickbacks

• A contract officer had devised a scheme…

• The auditors used digital analysis as part of their review

• One analysis calculated the total contract amount by vendor for each of the two past years

• A ratio of current year to previous year was calculated and the STATISTICS command was used to look at the following:

• Minimum • Maximum • Average

• Highest five ratios • Lowest five ratios

Contracting Kickbacks (Cont’d)

• The highest 5 ratios and the lowest 5 ratios showed that some

companies had significant decreases in business, while others had significant increases

• The auditors reviewed the details for all companies with last year to current year ratio of less than 0.7 or greater than 1.3;

• The detailed records were extracted to a file • Totals were calculated by contracting officer

Contracting Kickbacks (Cont’d)

• One contracting officer was responsible for many of the companies that had seen an increase in business;

• However, the same contracting officer had raised no contracts with the companies that had seen a decrease in business;

Doctored Bills

• The auditors reviewed the patient billing system to determine if the

appropriate charges were being assessed to the patient’s healthcare providers

• An initial analysis of the data was performed to calculate the ratio of the highest and lowest charges for each procedure

• The auditing standards required that procedures with a ratio of highest to lowest greater than 1.3 be noted and additional review performed

Doctored Bills (Cont’d)

• A filter was set to identify the records related to the three procedures in question and additional analysis was performed

• This quickly determined that one doctor was charging significantly more than the other doctors for the same procedures

• A comparison of the charges from the billing system with the payments recorded in the accounts receivable system, revealed that the doctor was skimming a portion of the payment received

Doctored Bills (Cont’d)

• The amount recorded in the receivable system was in line with the usual billing amount for the procedures

• The doctor was unable to justify the higher prices or explain the difference in the

Time and Expenses

• In ACL auditors use the MOD function to identify transactions that are multiples of $10 or multiples of $100

• Utilized for standard journal entry testing procedures

• Maximums for meals / hotels can be charged, however actual receipts do not match the maximum

Reference Material

• Fraud Detection

• Using Data Analysis Techniques to Detect Fraud

• By David G. Coderre

• David G. Coderre has over 18 yrs of experience in the informatics field in both the public and private sector. He is a recognized expert on CAATTs development and usage and has written numerous articles for leading international journals. He has also taught the use of CAATTS to auditors in various governments, including Canada, Indonesia and Mexico. Mr. Coderre holds an MBA from the University of Toronto and is a member of the Institute of Internal Auditors

• CAATTs & other BEASTs for Auditors

• Computer Assisted Audit Tools and Techniques (CAATTs) & Beneficial Electronic Audit Support Tools (BEASTs)

APPENDIX

• Common Automated Auditing Techniques

• Payables • Employees

• Receivables & Sales • Inventory & Purchasing • Cash

• Fraud – Vendors • Fraud – A/R & Sales • Fraud - Purchasing

Payables

Tab Area Audit Test Purpose of Test Data Extracts

Required

ACL function Data Extract

Source

Payables & Disb. A/P Aging Verify Aging Test Accuracy of

Client Aging

Invoice #, invoice date, invoice amount, due date

Age A/P subledger

Payables & Disb. A/P Activity Duplicate

Payments Identify Duplicate Payments Check# / wire #, amount, date, vendor/payee #, vendor invoice #, amount Classify/summariz e/sort Check register / payment data

Payables & Disb. Vendors Common vendor

names Verify existence of vendors with common names Vendor master data: name, address, phone, contact name

Index A/P / Purchases

master data

Payables & Disb. Vendors Purchases

fluctuation Identify large fluctuations as compared to PY Purchases by vendor PY and FY

Comparison sort Purchases / A/P payments

Employees

Tab Area Audit Test Purpose of Test Data Extracts

Required

ACL function Data Extract

Source

Employees Human Resources Payroll validity Identify duplicate

addresses, ss#, bank accts HR data: employee name, ss#, address, chk acct#, accrued vacation, vacation taken, overtime billed, phone #

Duplicates search Payroll records

Employees Human Resources Timely assoc.

disposition Verify ex- employees no longer on payroll List of terminated employees: employee name, ss#, address, chk acct#, accrued vacation, vacation taken, overtime billed, phone #

Join / match Payroll records;

HR terminations

Employees Human Resources Match payroll with

employee master Verify only employees get paid HR data as above; Payroll run

Join / match Payroll records /

Receivables & Sales

Tab Area Audit Test Purpose of Test Data Extracts

Required

ACL function Data Extract

Source

Receivable & Sales

A/R Aging Verify Aging Test Accuracy of

Client Aging

Invoice #, invoice date, open invoice

amount

Age A/R Subledger

Receivable & Sales

A/R Aging Customer

Balances

Identify sig. cust. in AR aging; test accuracy of cust. balances per client; identify cust. with net credit balances

Customer #, customer name, item#, item date,

open amount Classify/ summarize/ sort A/R Subledger Receivable & Sales

A/R Sampling CMA Sampling Perform CMA

sampling of receivables

Customer #, customer name, item#, item date,

open amount

Sample A/R Subledger

Receivable & Sales

A/R Controls Customers who

are employees Identify any customers who are employees Customer #, customer name, item#, item date,

open amount Join / match / merge Customer A/R master data; HR master

Inventory & Purchasing

Tab Area Audit Test Purpose of Test Data Extracts

Required

ACL function Data Extract

Source

Invty & Purch Inventory Aging Verify Aging Test Accuracy of

Client Aging

sku #, description, qty, unit cost, last

purch date, last sale date

Age Inventory

subledger

Invty & Purch Inventory Subledger

Negative / $0 Cost Items

Identify items with negative or zero

cost

sku #, description, qty, unit cost

Sort Inventory

subledger Invty & Purch Inventory

Subledger

Negative Qty Items

Identify items with negative qty

sku #, description, qty, unit cost

Sort Inventory

subledger Invty & Purch Inventory

Subledger Duplicate Sku's, Costs Identify Duplicate Sku #, description or cost sku #, description, qty, unit cost

Sort/sequence Inventory

subledger Invty & Purch Inventory

Sampling

CMA Sampling Perform CMA

sampling of inventory

sku #, description, qty, unit cost

Sample Inventory

subledger Invty & Purch Purchasing

Controls

Purchases under review limit

Identify purchases just under the $ limit that requires

Cash

Tab Area Audit Test Purpose of Test Data Extracts

Required

ACL function Data Extract

Source Cash Bank Reconciliations Cutoff Identify reconciling items after reconciliation cutoff date Reconciling items: date, item#, description, amount

Sort, filter Reconciling items

Fraud - Vendors

Tab Area Audit Test Purpose of Test Data Extracts

Required

ACL function Data Extract

Source

Fraud Vendors Vendors who are

employees

Identify any vendors that are

employees Vendor master and HR master Join / match / merge Vendor and HR Masters

Fraud Vendors Check sequence Identify any

checks out of sequence

FY A/P Payments Sequence analysis A/P check runs

Fraud Vendors Duplicate

payments

Identify any duplicate payments

FY A/P Payments Extract A/P check runs

Fraud Vendors Duplicate POs Extract invoices

posted with duplicate PO's FY A/P Payments with invoice #, PO #, amount, payee, check#

Fraud – A/R & Sales

Tab Area Audit Test Purpose of Test Data Extracts

Required

ACL function Data Extract

Source

Fraud A/R Employee

associate purchases Review associate purchases for large amounts FY Employee purchases; name, date, amount, item, qty, pymt

history

Statistics, index Employee

purchases detail

Fraud A/R Write-offs Determine validity

of write-offs

FY A/R system write-offs; legal

A/R w/offs

Statistics, index All FY write-offs for specific accounts data

Fraud A/R Customers with no

contact phone number

Identify any customers with no

phone number

A/R customer data Index, filter A/R master data

Fraud A/R / Sales Sales / A/R match Identify all

customers in sales that are not in A/R

A/R customer data; Sales master

data

Merge / join A/R and Sales

master

Fraud A/R Duplicate

accounts within the portfolio

Identify any duplicates in A/R

portfolio

A/R customer data / Sales master

data

Extract A/R and Sales

master

Fraud - Purchasing

Tab Area Audit Test Purpose of Test Data Extracts

Required

ACL function Data Extract

Source

Fraud Purchasing Purchases under

review limit

Identify purchases just under the $ limit that requires additional approval

80 City Square Boston, MA 02129

P 617.912.9000 F 617.912.9001 www.vitale.com