Data Analytics & Internal Audits

1. Data Analytics & Internal Audits IIA, Boise Chapter March 2014

2. How Did I Get Here? • Student Auditor at University Internal Audit Department • Accounting Degree • Protiviti Internal Controls Testing • Protiviti Data Analytics Team

3. Objectives • Encourage the use of data analytics in existing internal audit reviews • Share fundamental knowledge to successfully incorporate data analytics into audit work programs • Introduce Continuous Monitoring concepts

4. Establishing a Common Language Data Analytics Techniques used by auditors or management to manipulate large volumes of data to provide meaningful insight into activities occurring throughout the business. Continuous Auditing Methodused by auditors to perform audit-related activities on a continuous basis. Activities range from continuous control assessment to continuous risk assessment. Continuous Monitoring Process that management puts in place to ensure that its policies and procedures are adhered to, and that business processes are operating effectively. Continuous monitoring typically involves automated continuous testing of ALL transactions within a given business process area against a suite of controls.

5. Why Data Analytics?

6. Optimize the return on your existing data investments Provide insights to help pinpoint new opportunities and improve operational efficiencies and visibility across the organization Enable faster problem-solving and decision-making at the strategic, operational and tactical levels Find hidden meaning – patterns, trends, relationships – in your data Deliver intelligence to the field in real-time Mitigate the risk of fraud Improve your company's competitive advantage Achieve or validate compliance with government and regulatory guidelines Confirm existing controls are working properly Reconcile data across disparate systems Data Analytics Opportunities and Value Proposition

7. Example Internal Audit Case Studies Case Study #1 Organization Need Our client, a grocery chain, systematically transmitted multiple price change files per day originating from three different corporate systems to each of their 90 store locations. Store systems relied on manual processes for price files to be imported and applied at the register. Compliance with corporate supplied prices had never been tested. Solution To test compliance with corporate supplied pricing, we sought to analyze 100% of the transactions in each store during the audit period to determine if the corporate supplied price had been appropriately applied. This POS data extract totaled 300 million records for the one year audit period. After interviewing corporate and store-level stakeholders, Protiviti identified the relevant price system data sources (Ad, DSD, Pricing Dept.) and the rules governing price hierarchy. Prices were systematically applied based on the key characteristics of SKU, store, date, and price type. Result: Underpricing on sales of $14M. Results of this analysis were validated and reviewed with management. Root cause was identified as store department managers manually overriding corporate suggested prices to sell excess inventory purchases.

8. Testing of full populations No need to extrapolate sample results Drill down to individual transactions Efficient & Repeatable New understanding can be incorporated and the calculations rerun Undisputed results With agreement on inputs and model Enhanced risk assessment for audit area selection Targeted samples for testing Advantages of Data Analytics

9. Tools for Internal Audit Data Analytics Comparative Tool Features and Efficiencies

10. Scoping • Data Request • Data Integration • Data Analysis • Findings Validation & Reporting Phases of a Successful Internal AuditUsing Data Analytics These phases are not unique to data analytics focused internal audits. They can easily be integrated into the existing framework of internal audit work programs.

11. Scoping • Data Request • Data Integration • Data Analysis • Findings Validation & Reporting Methodology Phases • Objectives • Perform existing scoping activities to prepare for the review • Understand areas where Data Analytics would be most useful • Identify relevant systems and data sets • Key Questions • What data exists? • Can the subject of our audit be readily observed in existing data sets? • Does the auditee agree that Data Analytics can lead to an accurate answer? • Are multiple data sets required? Can these be tied together? • What thresholds, characteristics, etc. constitute an exception? • What business processes support the generation of the data?

12. Example Internal Audit Case Studies Case Study #2 – Importance of Scoping Organization Need A large telecom client maintains a large contingent workforce supplied by a third party who themselves subcontract to 135 staffing firms. Our client desired to audit contingent workforce invoice details to determine if contractual terms including appropriate hourly rates were being observed. Result Client insisted that the contract party not be engaged during the scoping phase. Relevant data sets existed in a Vendor Management System to which our client had access. After over 100 hours of analysis, data analysis results showed millions of dollars of overbilling due to excessive hourly rates being applied. Upon review with the staffing firm, it was learned that data extracts used did not reflect approved waivers to hourly rates and that these waivers, in fact, did not exist in a system of record. All findings were cleared.

13. Methodology Phases • Scoping • Data Request • Data Integration • Data Analysis • Findings Validation & Reporting • Objectives • Obtain data necessary to complete the audit • Minimize need to re-request data • Key Questions • Who can provide the data? • Business Unit: May not be able to change filters, output fields, or file format • IT: May take longer, may not understand “business” meaning of data fields, will give you exactly what you ask for • Do we understand what data is available? • More vs. Less – Fields, filters, etc. • How do we want the data? • How can we receive the data? • Size & Security?

14. Methodology Phases • Scoping • Data Request • Data Integration • Data Analysis • Findings Validation & Reporting • Objectives • Prepare data received for analysis • Validate completeness of data received and imported • Key Tasks • Select the technology best suited for the analytics selected. Things to consider: • Volume and type of data • Complexity of the modeling • Accessibility of certain technologies • In-house expertise and skills • Importing data into analysis tool – data types are important! • Validating completeness using record counts, system report, GL, etc. • Determine how disparate data will be integrated (“joined”) • Prepare any transformations or mappings

15. Example Internal Audit Case Studies Case Study #3 – Validating Completeness Organization Need Many of our clients have licensing agreements requiring the contract party to make periodic royalty payments based on sales activity. Our clients exercise the audit clause of these agreements to validate the completeness and accuracy of these royalty payments. Solution Protiviti employs a top-down approach to these audits where a complete population of the licensee’s sales detail is requested. Completeness of the sales data provided is validated by agreeing to audited financial statements. Once completeness has been established, we can isolate those sales which are subject to the agreement and perform a full recreation of payment obligations.

16. Transformations and Mappings Some “housekeeping” may be required to make data usable for analysis. Transformations: Mappings

17. Methodology Phases • Scoping • Data Request • Data Integration • Data Analysis • Findings Validation & Reporting • Objectives • Perform desired analysis • Key Tasks • Be Logical • Be Creative • Be Evolving • Build test scripts • Validate the accuracy of the scripts and other applicable KPIs or metrics • Confirm test scripts are identifying the intended results

18. Be Logical Build analysis step-by-step. Royalty example:

19. Example Internal Audit Case Studies Case Study #4 – Time & Expense Analysis Organization Need A technology client terminated a field rep for submitting fraudulent and abusive expense reports. Our client desired to identify if any other field reps within his department were utilizing similar schemes in travel expense reimbursement. Solution • Utilized the expense categories of submitted expense reports in performing analysis to identify fraud or abuse: • Incompatible expense reimbursements (fuel without a rental car, etc.) • Excessive reimbursements (More than 3 meals a day) • Ratio analysis (Lodging cost per travel night) • Simple descriptive spend summaries (Total yearly reimbursements for hotel, car, meal, etc.)

20. Example Internal Audit Case Studies Case Study #5 – Pattern Analysis Organization Need A hospital system terminated two employees who had gotten access to patient records with the intention of possibly using the information to file false tax returns. Our client wanted to identify if any other employees were using the same scheme to steal client information. Solution The hospital system identified that the terminated employees had been using their appropriate access to admittance systems to steal patient information. Our client identified the objects (“screens”) within that system which contained sensitive client information. Using the log records from that system, we identified a pattern of use consistent with a user stealing PII from the admittance system.

21. Business Performance identifies key ratios and metrics that track how business operations and processes are functioning: Financial, operational metrics Scorecards and KPIs Clustering data classifies data variables into similar data types for easy visualization and identification of problem areas: Grouping Deciles, quartiles, percentiles or other rank order measurements Stratifications Geographical, product, business unit or other segmentation criteria Trendingcreates visual displays of the data over time showing information such as: Cyclicality (e.g., time series analysis, by day of the week, month, season, etc.) Event driven results Abnormalities Descriptive statistical analysis brings the science of statistics intodata analysis: Distributions Outliers and standard deviation measurements (z-scores, etc.) Correlations and regression Volatility Historical and Descriptive Analysis • Although organizations will generally solve customized problems, recognized data and statistical analysis techniques are the basis for solving those problems.

22. Methodology Phases • Scoping • Data Request • Data Integration • Data Analysis • Findings Validation & Reporting • Objectives • Deliver quality and actionable audit results • Key Tasks • Validate approach with auditee • Validate any exceptions with auditee • Update data analysis and incorporate lessons learned as appropriate • Investigate variances, offending transactions, etc. • Categorize variances if possible • Finalize audit report • 100% of the population tested • Undisputed results • Targeted follow-up

23. Scoping • Data Request • Data Integration • Data Analysis • Findings Validation & Reporting Transitioning Data Analytics to Continuous Monitoring Continuous Monitoring is an outgrowth of the Data Analytics phases discussed. Data Analytics are formalized, productionalized, and scheduled to allow for repeatable auditing and monitoring. Key characteristics of these phases as they apply to Continuous Monitoring are summarized below. • Inventory potential analytics areas at a macro level • Select analytics area and identify detailed tests/metrics • Define Requirements for the individual tests or metrics • Formalize data request • Schedule periodic supply of data • Design transfer protocols for data extracts • Automate periodic refresh or load of data request • Automate data validation procedures • Design analysis to be performed • Make analysis repeatable • Make results of analysis available to end users in near real time

24. Example Internal Audit Case Studies Case Study #6 – Continuous Monitoring Organization Need A large retailer wanted to monitor its point-of-sale (POS) transactions trying to identify fraud and abuse by associates at the register. Solution The following was performed as the solution to the need: Established daily ETL procedures to obtain data from the POS systems for approximately 600 stores. Created a data warehouse and supporting data models to maintain and store data into perpetuity and drive dashboard performance. Created approximately 20 red-flag algorithms that monitored transaction activity on a daily basis. Transactions that are flagged by the algorithms are systematically placed in an Excel file and emailed directly to the divisional personnel responsible for loss prevention. Created a web-based dashboard and score-card solution that identifies outliers and gives end-users the ability to perform research (see trends, investigate transactions, and extract data to Excel for ease-of-use). Below are sample screenshots of the email alerts and scorecard/dashboard.

25. Questions?

26. Contact Information Reed Belliston Manager – Internal Audit and Financial Advisory reed.belliston@protiviti.com P: 801.401.8166