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.

2 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. 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.

3 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.

4 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.

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 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. 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?

6 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.

7 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?

8 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. 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)

9 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?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)

10 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. 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.