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
Global Internal Audit Team • Abel Casanova, Sr. Manager • Orlando Lopez, Sr. Manager • Daphne Chi, Sr. Auditor • Julie Ann Fan, Auditor
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
Data Mining Audit Test – Special Words • 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 Results Golf, mini, bar, cash, advance, gift, bribe, adjustment, allocation, government, party, cell phone, event, service, Jack Daniels
Data Mining Audit Test – Special Words – 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 – 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
Data Mining Audit Test – Stratification • 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 • Why would a sales representative need to charge amounts over $1,000? • Investigate!
Data Mining Audit Test – Stratification • 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 • Stratification - Cash vs. Card – $0 - $10 – $10 - $20 – $20 - $25 – $25 - $50 – $50 - $100 – $100 - $1,000 – $1,000 - $5,000 • 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.
Data Mining Audit Test – Stratification Results • Analyze the Results • Look for patterns • Do the results seem reasonable?
Data Mining Audit Test – Stratification Comparison 2/18/2014 18
Data Mining Audit Test – Stratification – 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 – 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
Data Mining Audit Test – Policy Compliance • Company T&E Policy - Cash – Cash transactions under $25 do not require receipt – Cash transactions over $20 but less than $25 AMOUNT > 20 .AND. AMOUNT < 25 – 246 cash transactions were between $20 & $25
Data Mining Audit Test – Policy Compliance - $21.38 at heartbeat café is curious activity. -taxi and valet parking in the same report is suspicious - American Airlines baggage fee is $25 and requires a receipt under company policy
Data Mining Audit Test – Policy Compliance • 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!
Allan Capone Jr.
Spreadsheet Risks and Validation • 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
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.
Demonstration Background Assumptions: • O&D Corporation is a medical device manufacturing company • Business model: Direct Customers or Distributors • 2Q2013 vs. 1Q2013 AR Reserve Analysis
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
Recommend
More recommend
