track 4.5 fraud still happens - iasa fraud happens.pdfcheck tampering - sparklines microsoft...

Post on 21-Sep-2020

1 Views

Category:

Documents

0 Downloads

Preview:

Click to see full reader

TRANSCRIPT

Fraud Still Happens2015 Ohio IASA Conference

Plante & Moran, PLLC Proprietary & Confidential

Presenter

Michelle McHale-Adams, CPA/CFF, CFEPartner - Forensic Investigative Services

Michelle.McHale@plantemoran.com616.643.4059 office616.460.6917 cell

www.forensic-accounting.plantemoran.com

Plante & Moran, PLLC Proprietary & Confidential

Fraud Test

3

Plante & Moran, PLLC Proprietary & Confidential

Fraud Test

Plante & Moran, PLLC Proprietary & Confidential

Agenda

Fraud in today’s world

Proactive prevention methods

Detection methods FormulasSparklinesPowerview Map

Q&A

5

Plante & Moran, PLLC Proprietary & Confidential

Study6

Plante & Moran, PLLC Proprietary & Confidential

ACFE Report to the Nations2010 2012 2014

Annual Revenues lost to Fraud

5% 5% 5%

Median Loss per incident

$160,000 $140,000 $145,000

Primary factor of loss Lack of internal controls

Lack of internalcontrols

Lack of internal controls

Typical scheme time duration

18 months 18 months 18 months

Clean employment histories

85% 87% 87%

Typical occurrences of fraud

Asset misappropriation

Asset misappropriation

Asset misappropriation

Asset Misappropriation 90% 87% 85%

Plante & Moran, PLLC Proprietary & Confidential

Anatomy of a “Fraudster”

Male – 54% of reported occurrences in US

Median loss: Males - $185,000

Females - $83,000

Between 31 and 45 years – 52% of occurrences

Most are “First Time Offenders”

Long-term employees = larger frauds

8

Plante & Moran, PLLC Proprietary & Confidential

Motivating Factors9

Plante & Moran, PLLC Proprietary & Confidential

10

Plante & Moran, PLLC Proprietary & Confidential

Occupational Fraud by Industry11

Plante & Moran, PLLC Proprietary & Confidential

Scheme Types 12

Plante & Moran, PLLC Proprietary & Confidential

Overall Prevention Measures

Effective and enforced internal controls, segregation of duties, oversight #1 Internal control weakness – lack of internal controls

Less than 20% of the frauds involved someone “overriding” the internal controls

13

Plante & Moran, PLLC Proprietary & Confidential

Overall Prevention Measures

Be familiar with the “typical” fraudsterEmployment traitsPersonality traitsLifestyle traits

14

Plante & Moran, PLLC Proprietary & Confidential

Overall Prevention Measures

Periodic background checks Changes in motivating factors

Tone at the Top Rationalization

Employee Support Programs Motive

Mandatory vacations and/or job rotation Who doesn’t like vacation?!

15

Plante & Moran, PLLC Proprietary & Confidential

Overall Prevention Measures

Effective and consistently enforced employee code of conduct No excuses

Signed employee handbook requirement No excuses

Anti-Fraud Training No excuses

16

Plante & Moran, PLLC Proprietary & Confidential

Overall Detection Measures

Provide a mechanism to report tips Should be open for reporting by anyone

17

Plante & Moran, PLLC Proprietary & Confidential

Loss/Length by Detection Method18

Plante & Moran, PLLC Proprietary & Confidential

Overall Detection Methods

Surprise procedures Rationalization

Ask Questions and get answers “Technology problems”

Forensic Accountants

Proactive Data Monitoring/Analysis

19

Plante & Moran, PLLC Proprietary & Confidential

Effectiveness of Overall Measures20

Plante & Moran, PLLC Proprietary & Confidential

#3 Check Tampering

Detection Methods – Data analytics Duplicate Check Numbers (conditional formatting)

Checks cut on non-business days (=weekday)

Check numbers issued out of sequence (=IF(B4-B3>=0,"","OutSeq")

Checks marked as void in the system

Sparklines

21

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering – Dup Check #’s

Duplicate Check Numbers - Conditional Formatting 1) Highlight the column of check numbers

2) On the “HOME” tab, click “Conditional Formatting”.

3) Scroll to “Highlight Cells Rules” and click on “Duplicate Values”. Click OK.

22

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering – Dup Check #’s

Duplicate Check Numbers -Conditional Formatting 4) All duplicates are now

highlighted in your default color (mine is red).

5) Sort the column by color using either data-sort or data-filter.

23

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering – Non-Business Days

Checks Cut on Non-Business Days -=Weekday 1) Insert the formula =Weekday in the

column to the right the check date column.

2) The formula should select the check date column as the cell to test, as follows: =Weekday(check-date column)

3) The column must be formatted as accounting or number, without decimal points.

4) Sort the data by this new column

5) Checks with the value “1” were cut on Sunday and checks with the value “7” were cut on Saturday.

24

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering – Out of Sequence

Checks Cut Out of Sequence - (=IF(B4-B3>=0,"","OutSeq") 1) Sort the data by Check Number then Check Date

25

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering – Out of Sequence

Out of Sequence Checks - (=IF(B4-B3>=0,"","OutSeq") 2) Insert a column next to the Check

Date column. The column should be formatted as “General”

3) Start at the second row of data in your set. Insert the following formula: (=IF(B4-B3>=0,"","OutSeq")

The “B” is replaced with whichever column the Check Date is in.

The “4” and “3” is replaced with whichever rows are your second and first rows in your data set.

4) Highlight your new column and calculated formulas. Paste/Special values only so they don’t change when you re-sort for any reason.

26

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering - Sparklines

Microsoft Definition – “a tiny chart in a worksheet cell…to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.”

Trended in relation to itself

You can utilize Sparklines within Excel to quickly test for: Larger than normal checks

Checks on non-payroll days

A basic knowledge of pivot tables is required to format from a list set of data

27

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering - Sparklines

Which one is easier to identify the anomaly?

28

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering - Sparklines

Which one is easier to identify the anomaly?

29

Plante & Moran, PLLC Proprietary & Confidential

Check Tampering - Sparklines

Available in Excel

30

Plante & Moran, PLLC Proprietary & Confidential

Fraud Methods

Billing (#2) Person causes employer, via submission of an invoice, to issue a payment for fictitious

goods or services

Shell companies

Inflated invoices

Personal purchases

31

Plante & Moran, PLLC Proprietary & Confidential

Billing

Prevention Methods Evaluate process to add new vendors—are duties segregated?

Is there a process in place to periodically review approved vendor list?

Detection Methods – Data Analytics Invoice numbers out of sequence (same as check tampering)

Duplicate invoice numbers (same as check tampering)

Subtle changes in invoice numbers (=Len)

Inconsistent invoice numbers (=Len, or advanced software)

Sequential invoices (same as check tampering, but with “=1” instead of “>=0”, or advanced software)

32

Things to Look For – Billing33

Invoice Numbers Subtle changes (A)

Out of sequence

Inconsistent

Duplicate

Things to Look For – Billing34

Things to Look For – Billing35

Things to Look For – Billing36

Plante & Moran, PLLC Proprietary & Confidential

Fraud Methods

Corruption (#1)Frauds involving a 3rd partyKickbacks

Conflicts of Interest

37

Plante & Moran, PLLC Proprietary & Confidential

Corruption

Prevention Methods Difficult to prevent

Ensure segregation of duties are proper

Ethics policy and employee handbook

Detection Methods – Data analytics Sparklines

Monitor vendors

38

Plante & Moran, PLLC Proprietary & Confidential

Corruption Tools are Expensive

Web Analytics

Public Records

Social Media

39

Plante & Moran, PLLC Proprietary & Confidential

Corruption - Sparklines

Don’t add the additional “blank” days

Select “connect data points with line”

40

Plante & Moran, PLLC Proprietary & Confidential

Corruption - Bid Splitting

Bid Splitting Identify the approval thresholds

Pivot total payments by vendor

Remove all vendors from your population with total payments below approval thresholds

Test for checks (stratify) that paid multiple invoices (invoices are often split, but the payments may be combined into one)

Test for different invoices with the same date or amount (duplicates)

41

Plante & Moran, PLLC Proprietary & Confidential

Mapping

Fraud analytics point you in the direction to look; however, you still have to look.

So what should you be looking for?

Next Steps43

Plante & Moran, PLLC Proprietary & Confidential

Manual Review Tips – What to Look For

Missing Information Addresses

Invoice number

Logos

Inconsistent Information Fonts

Formats

Addresses

Business Purpose Re-confirm with approving supervisor

Confirm the legitimacy, not just the existence

44

Plante & Moran, PLLC Proprietary & Confidential

Plante & Moran, PLLC Proprietary & Confidential

Fake Invoice

Actual Order Confirmation

Plante & Moran, PLLC Proprietary & Confidential

Summary

Fraud is rampant and often committed by long term, educated, trusted employees Trust is not an internal control!

Technology has made it much easier to detect fraud Learn Excel skills to enhance your capabilities

Technology has also made it easier to commit fraud You need to confirm the legitimacy of documentation, not just it’s existence

TRUST BUT VERIFY!

47

top related