Procurement fraud
This presentation is the property of its rightful owner.
Sponsored Links
1 / 84

Procurement Fraud PowerPoint PPT Presentation


  • 204 Views
  • Uploaded on
  • Presentation posted in: General

Procurement Fraud. November 11, 2008. Mike Blakley. Detection and Prevention. Session objectives. Current trends, techniques and best practices Understand statistical basis for analysis Procurement cards (p-cards) Understand use of Excel. Top Six Indicators That you might have a fraud.

Download Presentation

Procurement Fraud

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Procurement fraud

ProcurementFraud

November 11, 2008

Mike Blakley

Detection and Prevention


Session objectives

Session objectives

  • Current trends, techniques and best practices

  • Understand statistical basis for analysis

  • Procurement cards (p-cards)

  • Understand use of Excel


Top six indicators that you might have a fraud

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 doesn’t 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. Clerk’s kids drive Porsches between mountain home and beach home


Overview

Overview

  • Fraud patterns detectable with digital analysis

  • Basis for digital analysis approach

  • Usage examples

  • Using Excel


The why and how

The Why and How

Objective 1

  • Two brief examples

  • IIA Guidance Paper

  • Auditors “Top 10”

  • Process Overview

  • Who, What, Why, When & Where


Example 1 school bus transportation fraud

Example 1School Bus Transportation Fraud

Objective 1

  • Supplier Kickback – School Bus parts

  • $5 million

  • Jail sentences

  • Period of years


Regression analysis

Regression Analysis

Objective 1

  • Stepwise to find relationships

    • Forwards

    • Backwards

  • Intervals

    • Confidence

    • Prediction


Data outliers

Data outliers

Objective 1

  • Sometimes an “out and out Liar”

  • But how do you detect it?


Data outliers1

Data Outliers

Objective 1

  • Plot transportation costs vs. number of buses

  • “Drill down” on costs

    • Preventive maintenance

    • Fuel

    • Inspection


Scatter plot with prediction and confidence intervals

Scatter plot with prediction and confidence intervals


Medicare hiv infusion costs

Medicare HIV Infusion Costs

Objective 1

  • CMS Report for 2005

  • South Florida - $2.2 Billion

  • Rest of the country combined - $.1 Billion


Pareto chart

Pareto Chart

Objective 1


Guidance paper

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


Managing the business risk of fraud a practical guide

Objective 1

Managing the Business Risk of Fraud: A Practical Guide

  • IIA, AICPA and ACFE

  • Report issued 5/2008

  • Section 5 – Fraud Detection


Section 5 fraud detection

Section 5 – Fraud Detection

Objective 1

  • Detective Controls

  • Process Controls

  • Anonymous Reporting

  • Internal Auditing

  • Proactive Fraud Detection


Proactive fraud detection

Proactive Fraud Detection

Objective 1

  • Data Analysis to identify:

    • Anomalies

    • Trends

    • Risk indicators


Specific examples cited

Specific Examples Cited

Objective 1

  • Journal entries – suspicious transactions

  • Identification of relationships

  • Benford’s Law

  • Continuous monitoring


Data analysis enhances ability to detect fraud

Objective 1

Data Analysis enhances ability to detect fraud

  • Identify hidden relationships

  • Identify suspicious transactions

  • Assess effectiveness of internal controls

  • Monitor fraud threats

  • Analyze millions of transactions


Peeling the onion

Peeling the Onion

Objective 1c


Fraud pattern detection

Fraud Pattern Detection

Objective 1d


Who uses analytics

Who Uses Analytics

Objective 1e

  • Traditionally, IT specialists

  • With appropriate tools, audit generalists (CAATs)

  • Growing trend of business analytics

  • Essential component of continuous monitoring


Analytics what is it

Objective 1e

Analytics – what is it?

  • Using software to:

    • Classify

    • Quantify

    • Compare

  • Both numeric and non-numeric data


How assessing fraud risk

Objective 1e

How - Assessing fraud risk

  • Basis is quantification

  • Software can do the “leg work”

  • Statistical measures of difference

    • Chi square

    • Kolmogorov-Smirnov

    • D-statistic

  • Specific approaches


Why advantages

Objective 1e

Why - Advantages

  • 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


Why disadvantages

Why - Disadvantages

Objective 1e

  • Costly (time and software costs)

  • Learning curve

  • Requires specialized knowledge


When to use analytics

Objective 1e

When to Use Analytics

  • Traditional – intermittent (one off)

  • Trend is to use it as often as possible

  • Continuous monitoring

  • Scheduled processing


Where is it applicable

Objective 1e

Where Is It Applicable?

  • Any organization with data in digital format, and especially if:

    • Volumes are large

    • Data structures are complex

    • Potential for fraud exists


Objective 1 summarized

Objective 1 Summarized

Objective 1

  • Two brief examples

  • IIA Guidance Paper

  • “Top 10” Metrics

  • Process Overview


Objective 1 summarized1

Objective 1 - Summarized

  • Understand why and how

  • Understand statistical basis for quantifying differences

  • Identify ten general tools and techniques

Next is the basis …


Basis for pattern detection

Objective 2

Basis for Pattern Detection

  • Analytical review

  • Isolate the “significant few”

  • Detection of errors

  • Quantified approach


Trapping anomalies

Objective 3

Objective 2

Trapping anomalies


Understanding the basis

Objective 2

Understanding the Basis

  • Quantified Approach

  • Population vs. Groups

  • Measuring the Difference

  • Stat 101 – Counts, Totals, Chi Square and K-S

  • The metrics used


Quantified approach

Objective 2a

Quantified Approach

  • Based on measureable differences

  • Population vs. Group

  • “Shotgun” technique


Detection of fraud characteristics

Objective 2a

Detection of Fraud Characteristics

  • Something is different than expected


Fraud patterns

Objective 2b

Fraud patterns

  • Common theme – “something is different”

  • Groups

  • Group pattern is different than overall population


Measurement basis

Objective 2c

Measurement Basis

  • Transaction counts

  • Transaction amounts


How is digital analysis done

Objective 2d

How is digital analysis done?

  • 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


Histograms

Objective 2d

Histograms

  • Attributes tallied and categorized into “bins”

  • Counts or sums of amounts


Two histograms obtained

Objective 2d

Two histograms obtained

  • Population and group


Compute cumulative amount for each

Objective 2d

Compute Cumulative Amount for each


Are the histograms different

Objective 2d

Are the histograms different?

  • Two statistical measures of difference

  • Chi Squared (counts)

  • K-S (distribution)

  • Both yield a difference metric


Chi squared

Objective 2d

Chi Squared

  • Classic test on data in a table

  • Answers the question – are the rows/columns different

  • Some limitations on when it can be applied


Chi squared1

Objective 2d

Chi Squared

  • Table of Counts

  • Degrees of Freedom

  • Chi Squared Value

  • P-statistic

  • Computationally intensive


Kolmogorov smirnov

Objective 2d

Kolmogorov-Smirnov

  • Two Russian mathematicians

  • Comparison of distributions

  • Metric is the “d-statistic”


How is k s test done

Objective 2d

How is K-S test done?

  • Four step process

    • For each cluster element determine percentage

    • Then calculate cumulative percentage

    • Compare the differences in cumulative percentages

    • Identify the largest difference


Kolmogorov smirnov1

Objective 2d - KS

Kolmogorov-Smirnov


Classification by metrics

Objective 2e

Classification by metrics

  • Stratification

  • Day of week

  • Happens on holiday

  • Round numbers

  • Variability

  • Benford’s Law

  • Trend lines

  • Relationships (market basket)

  • Gaps

  • Duplicates


Fraud pattern detection1

Objective 3

Fraud Pattern Detection


What can be detected

What can be detected

Objective 2

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


Benford s law using excel

Benford’s 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)


Made up numbers

Made up numbers

  • Benford’s Law

  • Check Chi Square and d-statistic

  • First 1,2,3 digits

  • Last 1,2 digits

  • Second digit

  • Sources for more info


How is it done

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)


Invoice amounts tested with benford s law example results

Invoice Amounts tested with Benford’s law - Example Results

Objective 2

During tests of invoices by store, two stores, 324 and 563 have significantly more differences than any other store as measured by Benford’s Law.


Next metric

Next Metric

Objective 2

  • Outliers

  • Stratification

  • Day of Week

  • Round Numbers

  • Made Up Numbers

  • Market basket

  • Trends

  • Gaps

  • Duplicates

  • Dates


Duplicates

Duplicates

Objective 2

Why is there more than one?

Same, Same, Same, and

Same, Same,Different


Two types of related tests

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


Duplicate payments

Duplicate Payments

Objective 2

  • High payback area

  • “Fuzzy” logic

  • Overriding software controls


Fuzzy matching with software

Fuzzy matching with software

Objective 2

  • Levenshtein distance

  • Soundex

  • “Like” clause in SQL

  • Regular expression testing in SQL

  • Vendor/employee situations

Russian physicist


How is it done1

How is it done?

Objective 2

  • First, sort file in sequence for testing

  • Compare items in consecutive rows

  • Extract exceptions for follow-up


Possible duplicates example results

Possible Duplicates - Example Results

Objective 2

Five invoices may be duplicates.


Next metric1

Next Metric

Objective 2

  • Outliers

  • Stratification

  • Day of Week

  • Round Numbers

  • Made Up Numbers

  • Market basket

  • Trends

  • Gaps

  • Duplicates

  • Dates


Holiday date testing

Holiday Date Testing

Objective 2

  • Red Flag indicator


Typical audit areas

Typical audit areas

Objective 2

  • Invoices

  • Receiving reports

  • Purchase orders


Federal holidays

Federal Holidays

Objective 2

  • Established by Law

  • Ten dates

  • Specific date (unless weekend), OR

  • Floating holiday


Understanding the basis1

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


Objective 2 summarized

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 …


Testing procurement card transactions

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


Audit benefits how this test supports the audit

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


Mcc structure

MCC Structure

Objective 3

  • Major Categories

  • Airlines 30XX – 32XX

  • Car Rental 33XX, 34XX

  • Hotels 35XX – 37XX

  • All Other


Policy structure

Policy Structure

Objective 3

  • Prohibited Codes

  • Codes allowed with a monthly limit

  • Codes allowed without limit

  • Overall card limit


Summary and wrap up

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 conceptsand techniques presented


Objective 3 summarized

Objective 3 - Summarized

  • Understand why and how

  • Understand statistical basis for quantifying differences

  • Procurement cards

  • Understand examples done using Excel

Next up: Excel …


Use of excel

Use of Excel

Objective 4

  • Built-in functions

  • Add-ins

  • Macros

  • Database access


Excel univariate statistics

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


Excel histograms

Excel Histograms

Objective 4

  • Tools | Data Analysis | Histogram

  • Bin Range

  • Data Range


Excel gaps testing

Excel Gaps testing

Objective 4

  • Sort by sequential value

  • =if(thiscell-lastcell <> 1,thiscell-lastcell,0)

  • Copy/paste special

  • Sort


Detecting duplicates with excel

Objective 4

Detecting duplicates with Excel

  • Sort by sort values

  • =if testing

  • =if(=and(thiscell=lastcell, etc.))


Performing audit tests with macros

Performing audit tests with macros

Objective 4

  • Repeatable process

  • Audit standardization

  • Learning curve

  • Streamlining of tests

  • Examples - http://tinyurl.com/576tp8


Use of excel1

Use of Excel

Objective 4

  • Built-in functions

  • Add-ins

  • Macros


Objective 4 summarized

Objective 4 - Summarized

  • Understand why and how

  • Understand statistical basis for quantifying differences

  • Identify ten general tools and techniques

  • Understand examples done using Excel


Questions

Questions?


Links for more information

Links for more information

  • Kolmogorov-Smirnov

  • http://tinyurl.com/y49sec

  • Benford’s Law http://tinyurl.com/3qapzu

  • Chi Square tests http://tinyurl.com/43nkdh

  • Continuous monitoring http://tinyurl.com/3pltdl


Excel macros used in auditing

Excel macros used in auditing

  • Excel as an audit software http://tinyurl.com/6h3ye7

  • Selected macros - http://tinyurl.com/576tp8

  • Spreadsheets forever - http://tinyurl.com/5ppl7t


Contact info

Contact info

  • E-mail: [email protected]

  • Web: http://ezrstats.com


  • Login