Procurement Fraud

1
Procurement Fraud

• Detection and Prevention

November 11, 2008
Mike Blakley
2
Session objectives

• Current trends, techniques and best practices
• Understand statistical basis for analysis
• Procurement cards (p-cards)
• Understand use of Excel

3
Top Six IndicatorsThat you might have a fraud

• 6. System designed to do three way match, but
  only does two way
• 5. Procurement software system doesnt do a match
• 4. When auditors ask to help them out, they point
  to the door
• 3. No procurement software system
• 2. Procurement clerk drives a Porsche
• 1. Clerks kids drive Porsches between mountain
  home and beach home

4
Overview

• Fraud patterns detectable with digital analysis
• Basis for digital analysis approach
• Usage examples
• Using Excel

5
The Why and How
Objective 1

• Two brief examples
• IIA Guidance Paper
• Auditors Top 10
• Process Overview
• Who, What, Why, When Where

6
Example 1School Bus Transportation Fraud
Objective 1

• Supplier Kickback School Bus parts
• 5 million
• Jail sentences
• Period of years

7
Regression Analysis
Objective 1

• Stepwise to find relationships
• Forwards
• Backwards
• Intervals
• Confidence
• Prediction

8
Data outliers
Objective 1

• Sometimes an out and out Liar
• But how do you detect it?

9
Data Outliers
Objective 1

• Plot transportation costs vs. number of buses
• Drill down on costs
• Preventive maintenance
• Fuel
• Inspection

10
Scatter plot with prediction and confidence
intervals
11
Medicare HIV Infusion Costs
Objective 1

• CMS Report for 2005
• South Florida - 2.2 Billion
• Rest of the country combined - .1 Billion

12
Pareto Chart
Objective 1
13
Guidance Paper
Objective 1

• A proposed implementation approach
• Managing the Business Risk of Fraud A Practical
  Guide http//tinyurl.com/3ldfza
• Five Principles
• Fraud Detection
• Coordinated Investigation Approach

14
Managing the Business Risk of Fraud A Practical
Guide
Objective 1

• IIA, AICPA and ACFE
• Report issued 5/2008
• Section 5 Fraud Detection

15
Section 5 Fraud Detection
Objective 1

• Detective Controls
• Process Controls
• Anonymous Reporting
• Internal Auditing
• Proactive Fraud Detection

16
Proactive Fraud Detection
Objective 1

• Data Analysis to identify
• Anomalies
• Trends
• Risk indicators

17
Specific Examples Cited
Objective 1

• Journal entries suspicious transactions
• Identification of relationships
• Benfords Law
• Continuous monitoring

18
Data Analysis enhances ability to detect fraud
Objective 1

• Identify hidden relationships
• Identify suspicious transactions
• Assess effectiveness of internal controls
• Monitor fraud threats
• Analyze millions of transactions

19
Peeling the Onion
Objective 1c
20
Fraud Pattern Detection
Objective 1d
21
Who Uses Analytics
Objective 1e

• Traditionally, IT specialists
• With appropriate tools, audit generalists (CAATs)
• Growing trend of business analytics
• Essential component of continuous monitoring

22
Analytics what is it?
Objective 1e

• Using software to
• Classify
• Quantify
• Compare
• Both numeric and non-numeric data

23
How - Assessing fraud risk
Objective 1e

• Basis is quantification
• Software can do the leg work
• Statistical measures of difference
• Chi square
• Kolmogorov-Smirnov
• D-statistic
• Specific approaches

24
Why - Advantages
Objective 1e

• Automated process
• Handle large data populations
• Objective, quantifiable metrics
• Can be part of continuous monitoring
• Can produce useful business analytics
• 100 testing is possible
• Quantify risk
• Repeatable process

25
Why - Disadvantages
Objective 1e

• Costly (time and software costs)
• Learning curve
• Requires specialized knowledge

26
When to Use Analytics
Objective 1e

• Traditional intermittent (one off)
• Trend is to use it as often as possible
• Continuous monitoring
• Scheduled processing

27
Where Is It Applicable?
Objective 1e

• Any organization with data in digital format, and
  especially if
• Volumes are large
• Data structures are complex
• Potential for fraud exists

28
Objective 1 Summarized
Objective 1

• Two brief examples
• IIA Guidance Paper
• Top 10 Metrics
• Process Overview

29
Objective 1 - Summarized

• Understand why and how
• Understand statistical basis for quantifying
  differences
• Identify ten general tools and techniques

Next is the basis
30
Basis for Pattern Detection
Objective 2

• Analytical review
• Isolate the significant few
• Detection of errors
• Quantified approach

31
Trapping anomalies
Objective 3
Objective 2
32
Understanding the Basis
Objective 2

• Quantified Approach
• Population vs. Groups
• Measuring the Difference
• Stat 101 Counts, Totals, Chi Square and K-S
• The metrics used

33
Quantified Approach
Objective 2a

• Based on measureable differences
• Population vs. Group
• Shotgun technique

34
Detection of Fraud Characteristics
Objective 2a

• Something is different than expected

35
Fraud patterns
Objective 2b

• Common theme something is different
• Groups
• Group pattern is different than overall population

36
Measurement Basis
Objective 2c

• Transaction counts
• Transaction amounts

37
How is digital analysis done?
Objective 2d

• Comparison of group with population as a whole
• Can be based on either counts or amounts
• Difference is measured
• Groups can then be ranked using a selected
  measure
• High difference possible error/fraud

38
Histograms
Objective 2d

• Attributes tallied and categorized into bins
• Counts or sums of amounts

39
Two histograms obtained
Objective 2d

• Population and group

40
Compute Cumulative Amount for each
Objective 2d
41
Are the histograms different?
Objective 2d

• Two statistical measures of difference
• Chi Squared (counts)
• K-S (distribution)
• Both yield a difference metric

42
Chi Squared
Objective 2d

• Classic test on data in a table
• Answers the question are the rows/columns
  different
• Some limitations on when it can be applied

43
Chi Squared
Objective 2d

• Table of Counts
• Degrees of Freedom
• Chi Squared Value
• P-statistic
• Computationally intensive

44
Kolmogorov-Smirnov
Objective 2d

• Two Russian mathematicians
• Comparison of distributions
• Metric is the d-statistic

45
How is K-S test done?
Objective 2d

• Four step process
• For each cluster element determine percentage
• Then calculate cumulative percentage
• Compare the differences in cumulative percentages
• Identify the largest difference

46
Kolmogorov-Smirnov
Objective 2d - KS
47
Classification by metrics
Objective 2e

• Stratification
• Day of week
• Happens on holiday
• Round numbers
• Variability
• Benfords Law
• Trend lines
• Relationships (market basket)
• Gaps
• Duplicates

48
Fraud Pattern Detection
Objective 3
49
What can be detected
Objective 2

• Made up numbers e.g. falsified inventory
  counts, tax return schedules

50
Benfords Law using Excel
Objective 2

• Basic formula is log(1(1/N))
• Workbook with formulae available at
  http//tinyurl.com/4vmcfs
• Obtain leading digits using Left function, e.g.
  left(Cell,1)

51
Made up numbers

• Benfords Law
• Check Chi Square and d-statistic
• First 1,2,3 digits
• Last 1,2 digits
• Second digit
• Sources for more info

52
How is it done?
Objective 2

• Decide type of test (first 1-3 digits, last 1-2
  digit etc)
• For each group, count number of observations for
  each digit pattern
• Prepare histogram
• Based on total count, compute expected values
• For the group, compute Chi Square and d-stat
• Sort descending by metric (chi square/d-stat)

53
Invoice Amounts tested with Benfords law -
Example Results
Objective 2
Store Hi Digit Chi Sq D-stat
324 79 5,234 0.9802
563 89 4,735 0.97023
432 23 476 0.321
217 74 312 0.2189
During tests of invoices by store, two stores,
324 and 563 have significantly more differences
than any other store as measured by Benfords
Law.
54
Next Metric
Objective 2

1. Outliers
2. Stratification
3. Day of Week
4. Round Numbers
5. Made Up Numbers
6. Market basket
7. Trends
8. Gaps
9. Duplicates
10. Dates

55
Duplicates
Objective 2
Why is there more than one?
Same, Same, Same, and
Same, Same, Different
56
Two types of (related) tests
Objective 2

• Same items same vendor, same invoice number,
  same invoice date, same amount
• Different items same employee name, same city,
  different social security number

57
Duplicate Payments
Objective 2

• High payback area
• Fuzzy logic
• Overriding software controls

58
Fuzzy matching with software
Objective 2

• Levenshtein distance
• Soundex
• Like clause in SQL
• Regular expression testing in SQL
• Vendor/employee situations

Russian physicist
59
How is it done?
Objective 2

• First, sort file in sequence for testing
• Compare items in consecutive rows
• Extract exceptions for follow-up

60
Possible Duplicates - Example Results
Objective 2
Vendor Invoice Date Invoice Amount Count
10245 6/15/2007 3,544.78 4
10245 8/31/2007 2,010.37 2
17546 2/12/2007 1,500.00 2
Five invoices may be duplicates.
61
Next Metric
Objective 2

1. Outliers
2. Stratification
3. Day of Week
4. Round Numbers
5. Made Up Numbers
6. Market basket
7. Trends
8. Gaps
9. Duplicates
10. Dates

62
Holiday Date Testing
Objective 2

• Red Flag indicator

63
Typical audit areas
Objective 2

• Invoices
• Receiving reports
• Purchase orders

64
Federal Holidays
Objective 2

• Established by Law
• Ten dates
• Specific date (unless weekend), OR
• Floating holiday

65
Understanding the Basis
Objective 2

• Quantified Approach
• Population vs. Groups
• Measuring the Difference
• Stat 101 Counts, Totals, Chi Square and K-S
• The metrics used

66
Objective 2 - Summarized
Objective 2

• Understand why and how
• Understand statistical basis for quantifying
  differences
• Procurement cards
• Understand examples done using Excel

Next up p-cards
67
Testing Procurement Card Transactions
Objective 3

• Understand Merchant Charge Codes (MCC)
• Understand common policies
• Test procurement card transactions contained on
  worksheets using VBA
• Ability to test procurement card transactions in
  a file using VBA
• Perform an audit of procurement card transactions
  in a more efficient and effective manner using
  the concepts and techniques presented

68
Audit Benefits(How this test supports the audit)
Objective 3

• Test compliance with policy on an account by
  account basis
• Test compliance with policies on account limits
• Enable 100 testing of transactions
• Audit process which can be tailored for policy
  changes
• Repeatable audit process

69
MCC Structure
Objective 3

• Major Categories
• Airlines 30XX 32XX
• Car Rental 33XX, 34XX
• Hotels 35XX 37XX
• All Other

70
Policy Structure
Objective 3

• Prohibited Codes
• Codes allowed with a monthly limit
• Codes allowed without limit
• Overall card limit

71
Summary and Wrap Up
Objective 3

• Understand Merchant Charge Codes (MCC)
• Understand common policies
• Test procurement card transactions contained on
  worksheets using VBA
• Ability to test procurement card transactions in
  a file using VBA
• Perform an audit of procurement card transactions
  in a more efficient and effective manner using
  the concepts and techniques presented

72
Objective 3 - Summarized

• Understand why and how
• Understand statistical basis for quantifying
  differences
• Procurement cards
• Understand examples done using Excel

Next up Excel
73
Use of Excel
Objective 4

• Built-in functions
• Add-ins
• Macros
• Database access

74
Excel Univariate statistics
Objective 4

• Work with Ranges
• sum, average, stdevp
• largest(Range,1), smallest(Range,1)
• min, max, count
• Tools Data Analysis Descriptive Statistics

75
Excel Histograms
Objective 4

• Tools Data Analysis Histogram
• Bin Range
• Data Range

76
Excel Gaps testing
Objective 4

• Sort by sequential value
• if(thiscell-lastcell ltgt 1,thiscell-lastcell,0)
• Copy/paste special
• Sort

77
Detecting duplicates with Excel
Objective 4

• Sort by sort values
• if testing
• if(and(thiscelllastcell, etc.))

78
Performing audit tests with macros
Objective 4

• Repeatable process
• Audit standardization
• Learning curve
• Streamlining of tests
• Examples - http//tinyurl.com/576tp8

79
Use of Excel
Objective 4

• Built-in functions
• Add-ins
• Macros

80
Objective 4 - Summarized

• Understand why and how
• Understand statistical basis for quantifying
  differences
• Identify ten general tools and techniques
• Understand examples done using Excel

81
Questions?
82
Links for more information

• Kolmogorov-Smirnov
• http//tinyurl.com/y49sec
• Benfords Law http//tinyurl.com/3qapzu
• Chi Square tests http//tinyurl.com/43nkdh
• Continuous monitoring http//tinyurl.com/3pltdl

83
Excel macros used in auditing

• Excel as an audit software http//tinyurl.com/6h3y
  e7
• Selected macros - http//tinyurl.com/576tp8
• Spreadsheets forever - http//tinyurl.com/5ppl7t

84
Contact info

• E-mail Mike.Blakley_at_ezrstats.com
• Web http//ezrstats.com