Practical Implementation of Data Analysis IIA OC Chapter Meeting - - PowerPoint PPT Presentation

Practical Implementation of Data Analysis

IIA OC Chapter Meeting February 18, 2014 Edwards Lifesciences Global Internal Audit

Agenda

• Introduction
• Data Mining

– Travel and Expense Audit

• Special Words
• Stratification

– Cash vs. Credit Card

• Excel Risk and Validation

– Best practices – Spreadsheet Validation Demonstrations

• Abel Casanova, Sr. Manager
• Orlando Lopez, Sr. Manager
• Daphne Chi, Sr. Auditor
• Julie Ann Fan, Auditor

Global Internal Audit Team

Data Mining

– What is Data Mining?

• Automated or semi-automated analysis which

transfers large-scale data into understandable information

• Using computer tools to examine all (or nearly all)

the population in an audit test

– Why Data Mine?

• Data mining is a fast and efficient way to reveal

hidden exceptions, patterns, and trends

Data Mining Example: Expense Report Analysis

Allan Capone Jr.

– Sales Representative based in Las Vegas, Nevada – Expense reports from 2011-2013

• Obtained extract of data

from the T&E System

• 2,125 transactions over 36

months

Data Mining Audit Test – Special Words

– What are “Special Words”?

• “Special Words” is a type of audit test used to

identify transactions with suspicious words

• “Special Words” is an audit test used to

analyze the “grey” areas and is a type of fuzzy logic

– Why use Fuzzy Logic?

• Incorporates reasoning in the audit test to

identify fraud or exceptions

• Can find really neat items

• Golf, mini, bar, cash, advance, gift, bribe, adjustment,

allocation, government, party, cell phone, event, service

– Additional fun words: Jack Daniels

• Using Caseware IDEA, the @Isini function searches for

the occurrence of a specified string or piece of text in a character field, date field, or string and if found it returns the starting character position of the specified string. If the string is not found, a value of 0 is returned.

@Isini ( "Golf" ,COMMENT) .OR. @Isini( "mini" , COMMENT) .OR. @Isini( "bar" , COMMENT) .OR. @Isini( "jack" , COMMENT) .OR. @Isini( "cash" ,COMMENT) .OR. @Isini( "advance" , COMMENT) .OR. @Isini( "gift" , COMMENT) .OR. @Isini( "bribe" , COMMENT) .OR. @Isini( "allocation" ,COMMENT) .OR. @Isini( "adjustment" , COMMENT) .OR. @Isini( "event" , COMMENT) .OR. @Isini( "adjust" ,COMMENT) .OR. @Isini( "party" , COMMENT) .OR. @Isini( "service" , COMMENT) .OR. @Isini( "government" , COMMENT) .OR. @isini("cellphone", COMMENT)

Data Mining Audit Test – Special Words

Data Mining Audit Test – Special Words

Data Mining Audit Test – Special Words

Golf, mini, bar, cash, advance, gift, bribe, adjustment, allocation, government, party, cell phone, event, service, Jack Daniels

Results

– What did we do?

• Used logical reasoning and applied the special words test

– What did we find?

• Allan Capone Jr. has charged non-compliant transactions to

the company card

– What can Special Words be used for?

• Travel & Expense Reports
• Journal Entries
• Accounts Payable

– Tip

• Consider acronyms, foreign languages or ask local speakers

for slang

Data Mining Audit Test – Special Words

Data Mining Audit Test – Stratification

– What is Stratification?

• Stratification is the process of dividing the

population into subgroups before sampling

– Why use Stratification?

• Narrows down large data into subgroups and can

provide information by dollar amount, type of transaction or transaction frequency

• Conclusions or analysis may be easier to apply to

population subgroups

• Good way to begin sampling

• Stratify by Amounts

– $0 - $20 – $20 - $45 – $45 - $100 – $100 - $1,000 – $1,000 - $2,000 – $2,000 - $5,000

• Stratify by transaction line amounts

Data Mining Audit Test – Stratification

Data Mining Audit Test – Stratification

Data Mining Audit Test – Stratification

• Why would a sales representative need to charge amounts over

$1,000?

• Investigate!

• Why do these flights cost so much?

– First Class/ Business Class flights are not allowed under Corporate Policy – Possible multiple changes to flights?/ Last minute flights? – Paying for miles?

Data Mining Audit Test – Stratification

• Now we look at smaller stratus because the last stratus only

indicated possible exceptions over $1,000 but we should try and investigate exceptions under $1,000.

• Stratification - Cash vs. Card

– $0 - $10 – $10 - $20 – $20 - $25 – $25 - $50 – $50 - $100 – $100 - $1,000 – $1,000 - $5,000

Data Mining Audit Test – Stratification

Data Mining Audit Test – Stratification Results

• Analyze the Results
• Look for patterns
• Do the results seem

reasonable?

Data Mining Audit Test – Stratification Comparison

18 2/18/2014

– What did we do?

• We used the stratification to narrow down the transactions

into smaller samples

– What did we find?

• Exceptions to T&E policy (i.e., Business class seats and

suspicious cash transaction)

– What can stratifications be used for?

• AR Aging
• AP Aging

– Tip

• Stratifications are the beginning of audit analysis not the end

Data Mining Audit Test – Stratification

Data Mining Audit Test – Policy Compliance

– What is stratification sub-group analysis?

• Taking the next step data stratification
• One can build audit rules surrounding

company policy

– Why analyze stratification sub-groups?

• Detects non-compliance transactions to

corporate policy

• GREAT practical application of data analysis

• Company T&E Policy - Cash

– Cash transactions under $25 do not require receipt – Cash transactions over $20 but less than $25 – 246 cash transactions were between $20 & $25

AMOUNT > 20 .AND. AMOUNT < 25

Data Mining Audit Test – Policy Compliance

Data Mining Audit Test – Policy Compliance

• taxi and valet parking in the same report is suspicious
• $21.38 at heartbeat café is curious activity.
• American Airlines baggage fee is $25 and requires a

receipt under company policy

• What did we do?

– Reviewed transactions between $20-$25

• What did we find?

– Fake/non-compliant charges to T&E

• What can policy compliance test be used for

– Approval Thresholds – Willful bypass

• Tip

– This can be used for many company policies. Be creative!

Data Mining Audit Test – Policy Compliance

Allan Capone Jr.

• Our Excel Journey

– Only doing the minimum on spreadsheet controls – More COSO/SOX demands were on the way – Wanted to bring more audit and control value

• Spreadsheet risks
• Best Practices
• Spreadsheet Validation Demonstration

Spreadsheet Risks and Validation

Our Excel Journey

• We still use many spreadsheets
• Those spreadsheets have risks
• Select best practices were implemented
• Needed easy-to-use validation tool
• Selected the following Excel add-on tools

by Incisive:

– Xcellerator – Diff Interactive

Common Use of Spreadsheets

• Income Statement Fluctuation Analysis
• Accounts Receivable Reserve Analysis
• Excess & Obsolete Inventory Reserve
• Vacation Accrual Update
• Rebates Calculation
• Outstanding Shares Calculation
• Stock Repurchase Daily Summary
• Statement of Cash Flow Calculation

Spreadsheet Risks

• Unauthorized changes to the data by users
• Hidden worksheets or cells
• Formula overwritten with text or numbers
• Formula fails to cover full area
• Incorrect referencing
• Calculations are not refreshed

Best Practices

• Inventory and risk rank your spreadsheets

based on complexity and potential impact

• Set security access levels for authorized

users

• Establish version control and restrict

changes to formulas

• Secure key spreadsheets on servers for

backup purposes and security

• Routinely review spreadsheets for key

changes

Spreadsheet Validation Tools

• Validation tools by Incisive:

– Xcellerator: add-in application created for use with Microsoft Excel. This application scans spreadsheets for likely errors and inconsistencies, allowing users to find and fix errors before they become problems. – Diff Interactive: spreadsheet comparison software to quickly determine if and/or what spreadsheet changes were made by users.

Assumptions:

• O&D Corporation is a medical device

manufacturing company

• Business model: Direct Customers or Distributors
• 2Q2013 vs. 1Q2013 AR Reserve Analysis

Demonstration Background

Demonstration of Xcellerator

• What will we do?

– Analyze an AR aging report and calculation of the allowance for doubtful accounts

• Examples of kinds of tests will we run:

– “#” Errors and source – Data in formula range – Constant in formula (e.g., hardcoded numbers inside a formula) – Hidden Worksheets/Cells/References – External workbook references

Demonstration Part I - Xcellerator

Data in Formula Range Test Results

Red Flag: Hardcoded numbers instead of formula

Constant in Formula Test Results

Red Flag: Hardcoded numbers within formula

Hidden Cell Test Results

Red Flag: Why are these cells hidden?

Hidden Worksheet Test Results

Red Flag: Why is this worksheet hidden?

Demonstration of Diff Interactive

• What will we do?

– Compare spreadsheets between 1Q and 2Q AR aging reports and allowance for doubtful accounts

• What tests and report will we run?

– Spreadsheet Comparison

• Cell values
• Calculated values
• Formula/formula references

– Reporting of Testing Results

Demonstration Part II – Diff Interactive

Red Flag: Reserve percentages changed from prior quarter

Spreadsheets Comparison: Cell Values

Red Flag: What’s the nature of these changes?

Spreadsheets Comparison Cont.

Red Flag: Is it reasonable to change formula/reference?

Spreadsheets Comparison: Formula

Spreadsheets Comparison Cont.

Another example of formula/reference changed

• Summarize results by test types
• Share test results with others

Reporting of Results

• What other spreadsheet tests can we run?

– Xcellerator:

• Formula fails to cover area
• Broken formula region
• Inconsistent formula or complex formula
• Currency errors
• Few formula occurrences
• Number formatted as text
• Referencing blank cells or white space

– Diff Interactive:

• Cell differences (e.g., cell values and formulas)
• Sheet differences
• Inserts/deletes
• Tip:

– Be skeptical, spreadsheet errors are very common

Other Spreadsheet Validation Tests

Helping Patients is Our Life’s Work, and