Leveraging Data Analytics to Expand Audit Covera …

1 Leveraging data Analytics to Expand Audit Coverage and Clint McPhersonpgAdd Organizational ValueManaging Director, Dallas, TXCindy HartManager Dallas TXManager, Dallas, TXSetting the Stage - data Analysis DefinedData Analysis isData Analysis is ..the extractionof data from a client s information system in data Analysisyorder to perform data selection, classification, ordering, filtering, translation, CAATSand other functions to provide the client with informationabout their businessDt MiiprocessesData Mining 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third is data ?DiSalesDiv NameSales AmountEmployeeIDTransaction User NameTransactionDateUser NameTransaction TypeCustomer NumberQuantityPriceG/L Account 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Becomes ReportsDiv NameTransaction DateSales AmountUser NameTransaction TypeEmployeeIDData FileInventory ReportCustomer NumberQuantityPriceG/L AccountQuantityWarehousePartDescriptionQ uantityUnit NumberUnit 2010 Protiviti Inc.

2 CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third data Design Reports Div NameTransaction DateSales AmountUser NameTransaction TypeEmployeeIDSummarized by Part NumberCustomer NumberQuantityPriceG/L AccountData FileExtensions & Footings VerifiedQuantityData AnalysisTechniquesPart NumberUnit CostCust NameCust IDExcess InventoryWarehouseNumberUnusual Items 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third from Internal Audit s Use of data AnalyticsBenefits Include:Increased testing coverage (100% of population)Improved timeliness of testingGreater visibilityIndependent testingCreation of Fraud Testing EnvironmentImproved consistencyMore efficient allowing focus on overall process efficiency and effectivenessCost-effective solutionGreater confidence in your SOX initiativesThe Auditing Profession is entering the Age of Continuous AuditingContinuous AuditingThis is the Fourth Age -- (Age of Inspection & Re-performance, Age of Control Focused Auditing, Age of Risk Based Auditing)Annual Audits are being viewed as untimely and obsolete 2010 Protiviti Inc.

3 CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Control issues are expected to be reported almost immediatelyThe 6 Elements of InfrastructurePolicies define processesProcess assigned to key ownersInformed decisions based on reportsInformation facilitates definition of controlsAutomation and data integrity meet needsBusinessPoliciesBusinessProcessesPe ople & OrganizationManagementReportsMethodologi esSystems& DataRisk if element is deficient:People lack the knowledge and experience to perform processReports do not provide information for effective managementMethodologies do not adequately analyze data and informationInformation is not available for analysis and reportingProcess does not carry out established policies or achieve intended result 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Success Factors & Common PitfallsBusinessPoliciesBusinessProcesse sPeople & OrganizationManagementReportsMethodologi es Systems& data Focus on what matters: Fraud, Waste & Abuse Compliance Business Performance Monitoring Risk across the Organization Link the program to business objectives Link the program to business objectives Articulate the specific benefits of investing in a program and the implementation strategy 2010 Protiviti Inc.

4 CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Success Factors & Common PitfallsBusinessPoliciesBusinessProcesse sPeople & OrganizationManagementReportsMethodologi es Systems& data Define a high-level process Inputs Activities Outputs Identify source of inputsHow is information captured? How is information captured? How will inputs be validated? Determine types of activities that will be performed data Analysis & Investigation of Anomaliesyg Manual Audit Procedures Identify expected outputs & audience Define periodic reporting process 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Success Factors & Common PitfallsBusinessPoliciesBusinessProcesse sPeople & OrganizationManagementReportsMethodologi es Systems& data Obtain executive support for the program Identify all key stakeholders Champion Program management and executers data providersRecipients of detailed results and periodic summaries Recipients of detailed results and periodic summaries Understand needs of key stakeholders Obtain buy-in for program from key stakeholders Identify and develop required skills & competenciesypqp Identify and address organizational obstacles 2010 Protiviti Inc.

5 CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Success Factors & Common PitfallsBusinessPoliciesBusinessProcesse sPeople & OrganizationManagementReportsMethodologi es Systems& data Identify data requirements for the program What information is required? Where is that information stored? Who can provide the information? Design a standard data request formatTimeline Timeline Source & Required data Background Information Define data validation process & reportingppg Define reporting requirements by stakeholder What information does the audience want and what questions do they want answered? How will detailed results be summarized? 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Who will make conclusions based on the results? Critical Success Factors & Common PitfallsBusinessPoliciesBusinessProcesse sPeople & OrganizationManagementReportsMethodologi es Systems& data Define test scope & tolerances Develop testing procedures ( rules ) Select or build application, if applicable Understand standard queries Select applicable proceduresEmbed queries into application Embed queries into application Test logic and confirm results Provide adequate training to applicable stakeholders Automate as many rules as practicalyp System-based Audit targets Manually-intensive Audit targets 2010 Protiviti Inc.

6 CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Success Factors & Common PitfallsBusinessPoliciesBusinessProcesse sPeople & OrganizationManagementReportsMethodologi es Systems& data Understand how applicable data is captured & reported in operational and financial systems Procurement through Payment Sale through Cash Application Payroll & Expense Reimbursement General & Sub-LedgersBank information Bank information External Databases Understand system interfaces (automated & manual) Advocate automating data capture where practicalgpp Know the Audit tools available and their capabilities Select the right tools for program/procedures Focus on driving efficiency over time vs. initial investment 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third , this is all great stuff Clint but how do we use this and what are some good examples?

7 Examples? data Analysis - Suggested Approach 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Examples for ConsiderationTravel and Entertainment ExamplesSdbElLDllEBfd LJlEtiG/L and Journal Entry Examples Spend by Employee Analysis of Expenses by Employee (just below threshold, comparison of employees expensing duplicates, Large Dollar Expenses Identification Non-Timely Expense SubmissionExpense Analysis by Category Benford sLaw on Journal Entries by User Journal Entries identifying outliers (Uncommon Accounts, Profit Centers, Cost Centers)expensing airfare but no hotel (vice versa), expensing car but no airfare, etc) Analysis of MCCG and MCC of T&E or P-Card Transactions Expense Analysis by Category ( , Airfare, Office Supply, Cell Phone, Professional Dues) Per Diem Expense Identification and comparison to trips, policy Manual Round Dollar Entries Unusual Posting Dates/Times Analysis of Split Entries (Entries just below Approval Threshold)T&E or PCard Transactions Benford s Law Analysis on Employee Expenses Expense Dollar and Volume Stratificationthreshold, and potential duplicates in meal reimbursement and per diem Duplicate Expense SubmissionAli fWkd Analysis of Suspense, Clearing, and Intercompany Accounts Credits vs.

8 Aged Invoices Reversed Month End Journal Inactive Employee Spend Analysis Spend by Expense Type Analysis of Weekend Transaction Dates Analysis of No Activity from Personnel in Expense-Centric DepartmentsEntries Entries within Accounts Inactive Accounts Entries Calculate and sort percentage 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third and sort percentage variances in GL accounts between periodsCase Study 1 Background Organization: Global management consulting, technology services and outsourcing company with offices and operations in more than 50 countries and annual revenues in excess of $21 billion. Internal Audit (IA) personnel were using ACL to perform limited analyses as part of tlid Jl E t(JE)ifth13 illijltgquarterly company-wide Journal Entry (JE) reviews of more than 13 million journal entry lines. All analyses were performed manually through the ACL graphical user interface.

9 IA personnel were using Excel to perform limited analyses for employee Time & Expense (T&E) Objectives Implement routines ( , scripts) in ACL to automate the existing limited JE and T&E additional a tomated testing ro tines to be e ec ted as q arterl Contin o sObjectives Create additional automated testing routines to be executed as quarterly Continuous Controls Monitoring (CCM) procedures. 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third Testing Overview Quarterly Company-Wide JE Review data integrity testing such as reconciliation to control totals, analysis of reporting period, search for blanks in key fields, period, search for blanks in key fields, etc. Analysis of JE approvers to those on authorized list, analysis of manual and automated entries by document type, identify entries where document header or line item text is blank, etc. CCM: data ExplorationClifilfkd t fi ld i l did Classify unique values for key data fields including: company code, transaction code, manual vs.

10 Automated flag, year/period, and currency Statistics on amountand posting date fields CCM: Duplicates Testing Identify duplicates (same accountand amount) for manual JE s CCM: Fraud Analytics Keyword search for items such as plug , miscellaneous , temporary , adjust , t it d i t10t ii thtitii dli ti Entries posted in top 10 countries in the corruption perceptions index listing All manual JE s with same person to enter and post CCM: High Risk Account Entries Identify all entries posted to high risk accounts 2010 Protiviti Inc. CONFIDENTIAL: This document is for your company's internal use only and may not be copied nor distributed to another third CCM: Timeliness of Postings Analysis Calculate number of days between JE entry and posting datesT&E Testing Overview General data Overview Create record count and dollar amount totals by Year and Month ( , to reconcile to control totals)reconcile to control totals) Classify unique values for key data fields including: expense type and entry date Identify the top 100 highest and lowest transaction amounts Statistics on amount field T&E Population Analyses Identify transactions just under $25 threshold for receipts per US Policy Extract all transactions that are round multiples of $100 Approval threshold analyses for certain expense types per policy, including : Training / Publication > $500 Business Meals > $1 000 and Travel /.