Transcription

Data Analytics:Continuous Controls Monitoring &Predictive AnalyticsMarch 13, 2014Parm Lalli, CISA, ACDAMatt Osbeck, CPA, CIA, ACDA

Sunera SnapshotSAPACLCopyright 2013 Sunera LLC.2

About Our SpeakersParm Lalli, CISA, ACDAParm is an ACL Certified Data Analyst (ACDA) and is the National Director for our Data Analytics Practice.Parm has over 10 years of Data Analytics experience, leading many Continuous Controls Monitoringimplementations for organizations within many different industries. Parm has worked very closely traditionallywith ACL and ACL products.Prior to joining Sunera, Parm was employed with ACL and worked with audit departments, business unitmanagers, and end users to address their Data Analytics needs by using ACL. He worked on developingscripts across many different industries and applications.Matt Osbeck, CPA, CIA, ACDAMatt is a Senior Manager in the Los Angeles office of Sunera LLC and is an ACL Certified Data Analyst(ACDA). Matt has over 5 years experience leading data analytics projects. Matt provides 3-day ACL/Arbutustraining courses and serves as a lead analytic developer. Matt has vast experience developing analytics formultiple ERP systems, including SAP to test a broad range of IT and operational/financial controls. Hisexperience as has included data analysis of Order-to-Cash, Procure-to-Pay, FCPA / ABAC, IT, Manufacturing,Payroll/HR, Forensic/Fraud and other ad-hoc analysis for clients across many industries.Prior to joining Sunera, Matt held Internal Audit roles at Wal-Mart, Inc. and Informa plc., a London-based multinational performing financial, operational and compliance audits.Copyright 2013 Sunera LLC.3

What Is Continuous Controls Monitoring?Continuous Controls Monitoring (CCM) describes the techniques ofcontinuously monitoring and auditing an IT system. Typically, CCM solutionswill be applied to Enterprise Resource Planning systems. A ContinuousControls Monitoring solution can help to reduce compliance costs (throughdecreased manual controls and manual testing of those controls), strengthen acompany's internal control environment, and reduce the risk of unintentional orintentional errors and fraud. The key objective of CCM is:– To enable near real-time and regular monitoring of controlseffectiveness. By monitoring the compliance with key controls, your organization canobtain ongoing assurance on the accuracy and validity of large volumes ofdata flowing through your systems, enabling the isolation and containmentof control failures on a timely basis.Copyright 2013 Sunera LLC.4

Common CCM ToolsA number of tools are available. The right choice will depend on each company’sbusiness requirements and will include how well the proposed tool will integrate withexisting systems and tools. ACL SAP or Oracle GRC Approva Oversight Actuate BIRT Actuate e.Reports Cognos 8 BI Report Studio Crystal Reports InformationBuilders WebFOCUS JasperServer/iReport Microsoft SQL Server Reporting Services MicroStrategy Report Services SAS Web Report StudioGartner February 2009: Critical Capabilities for Business Intelligence ReportingCopyright 2013 Sunera LLC.5

Data Analytic OpportunitiesCopyright 2013 Sunera LLC.6

ISACA Study on CCM 10 Fortune 500 Organizations were involved Identify Challenges faced by Organizations Top Data Analytics tools used––––ACLIdeaArbutusTableau Group of 10 shared knowledge and agreed upon analytics 7 Recommendations madeCopyright 2013 Sunera LLC.7

CCM Documents Required CCM Project Checklist– This document is a complete Project Checklist of typical CCM projecttasks and responsibilities Data Analytic Tests– This spreadsheet has standard analytics for many different businessprocesses. Included are the purposes for each analytic Requirements Document– This is a document that outlines the requirements to carry out a CCMinitiative for a specific business process. This document includesstandard tests, the purpose of each test, frequency, parameters, andsource data mapping.Copyright 2013 Sunera LLC.8

CCM Documents Required Application Guide– This document is a guide on how to use this CCM application. Itincludes information on how to run tests manually and changeparameters. It contains information about source data and how to rerun the process in case of failure. Technical Guide– This document is the technical guide required by IT to rebuild the server.It contains information on how to configure the server and its relatedcomponents.Copyright 2013 Sunera LLC.9

Types of Data Analytics Ad Hoc Analysis–––––Time consumingData typically supplied by ITUp to 50% more budgeted time requiredDifficult to repeat tests if not documentedExploratory type analysis Repeatable Analysis–––––More skills required than Ad Hoc testingPre-defined scripts created to perform same tests over and over againMore consistent and can be run more frequentlyData may be supplied, but imports are automatedGood documentation for the scripts/analyticsCopyright 2013 Sunera LLC.10

Types of Data Analytics Centralized Analysis–––––––Development, storing, and running of repeatable analytics is centralizedA single, powerful server is set up for the repeatable analyticsData imports are all automatedStandards in place for developing tests and scriptingSource data and results are stored on serverBetter security for data files and result filesGreat deal of documentation on tests, scripts, data, and sample logic Continuous Auditing––––Process of performing audit related tasks in a continuous mannerContinuous risk and control assessments types of testingCompliance (SOX) control testingSecurity even monitoringCopyright 2013 Sunera LLC.11

Types of Data Analytics Continuous Controls Monitoring (CCM)– Very skilled and experienced individuals are able to script andimplement– All analytics and data imports are fully automated– No interaction from end users required– Allows for notifications to be sent to Business Unit Manager aboutidentified exceptions– May involve a web dashboard interface, workflow, remediation tracking,and heat maps– Better role based security for reviewing results– May provide management with areas for improvement with internalcontrols– A better likelihood of identifying fraudulent activity– Acts as a very good deterrent systemCopyright 2013 Sunera LLC.12

Benefits of Data Analytics Access data from many disparatesourcesIndependent of the systems andpeople being audited100% transaction coverage withunlimited file sizesRead-only data access to ensurethe integrity of the dataAudit trails are available to identifysteps takenScripting/batching capabilities tocapture test logic (like macros)Very fast to run and produce resultsEasier to comply with the provisionsof Section 404 of the SarbanesOxley ActCopyright 2013 Sunera LLC. 13Close control loopholes before fraudescalatesQuantifies the impact of fraudCost-effectiveActs as a deterrentCan be automated for continuousmonitoringProvides focus based on risk andprobability of fraudDirect pointers to critical evidenceSupport for regulatory complianceLogs for review and evidenceScalability – Build on what you needExternal Audit reliance

Data Analytics Automation BenefitsValidates effectiveness of internal controlsIdentifies occurrences of potential fraudIdentifies transactional errorsIdentifies segregation of duties violationsIdentifies process deficienciesUtilizes a technology driven processTests 100% of transactions as opposed to samplingAccesses data across disparate systems and geographiesProvides prompt notification of control breakdownsQuantifies exposure of business riskProvides an auditable history of compliance tests and follow-upactivities Enables better allocation of skilled audit/technical resources within theorganization Copyright 2013 Sunera LLC.14

What Are the Challenges Implementing changeChanging culture for the organizationDefining what CCM can accomplishGathering large volumes of data in multiple applicationsUnderstanding data and processesMonitoring of manual controlsRelying on reportingImplementing costsIntegrating with multiple compliance frameworks and into theexisting IT environmentsHOW DO YOU MAXIMIZE YOUR INVESTMENT IN CCM?Copyright 2013 Sunera LLC.15

Where to Apply Data AnalyticsWhat controls are eligible for automated testing? Electronic data is available and accessible.Access to data through an automated process is possible.Rules can be documented or captured within test logic.Internal controls and Compliance controls are eligible.What are the ideal conditions for automatedtesting? Large number of controls are in place.Large volumes of data are available.Multiple systems and data sources are available.Data is at multiple locationsFraudulent activities are caught prior to a transaction reaching the endof a process.Copyright 2013 Sunera LLC.16

Data Analytics Steps for Mature Rollout Steps for Implementing Continuous Controls Monitoring/Auditing1.2.3.4.5.6.7.8.9.Vendor selection and product evaluationAssess controlsScope and design system requirementsData warehouse implementationData access requirements definitionAnalytics script developmentResults verification and reviewAdjusting logic, parameters, and thresholdsRolloutCopyright 2013 Sunera LLC.17

Data Analytics Project Team Project Team Skills– Project Manager – Managers who organize and manage all resourcesto complete the implementation project within the defined scope, time,and cost– Business – Key owners of each business process to be monitored– Audit – Process and control experts to identify areas of risk and testdesign– IT – Key owners of the data and primary systems related to each of theprocesses– Technical – Specialized experts to build, configure, and implement themonitoring toolsCopyright 2013 Sunera LLC.18

Data Analytics — Sunera Approach Step 1– Review existing business process risk documentation– Review existing analytics for efficiency and effectiveness– Update existing analytics for full automation Step 2– Conduct additional reviews of business processes and identify riskareas– Identify opportunities for improving process through Data Analytics– Identify analytics opportunities within specific business processes– Identify and verify all compensating controlsCopyright 2013 Sunera LLC.19

Data Analytics — Sunera Approach Step 3– Add risk rating for identified analytics across business units High, Medium, Low– Quantify risk areas based on business units ( )– Obtain management agreement on ratings and quantitative measures Step 4– Create requirements documentation Data requirements from all available sourcesConfirm test logicConfirm required parametersConfirm reporting fields– Obtain agreement and sign off on requirements documentCopyright 2013 Sunera LLC.20

Data Analytics — Sunera Approach Step 5– Obtain sample data from all required sources Directly or data dump– Verify data based on requirements document All fields present No corruption of data– Perform data preparation Step 6––––Create scripts for testsCreate Excel result setsHave end user and/or business unit manager verify resultsTweak any tests to remove false positivesCopyright 2013 Sunera LLC.21

Data Analytics—Sunera Approach Step 7– Create scheduling for all tests Daily, weekly, monthly, quarterly– Move all pieces into production environment– Verify data connections/feeds Step 8– Create documentation for handoff– Provide training to CCM stakeholdersCopyright 2013 Sunera LLC.22

A Mature Data Analytics Overview Self-contained on dedicated serverFully automated and scheduledAlerts to business unit managers or stakeholders of resultsClear and concise documentation– For all scripted analytics– For setup and configuration Training provided to any and all individuals involved, including newhires Ongoing review of existing analytics and possible new analyticsbased on new business processes Maintain a change log for any addition or removal of scripts orchanges to configurationCopyright 2013 Sunera LLC.23

Data Analytics ExamplesCopyright 2013 Sunera LLC.24

Data Analytics Examples Purchasing Purchase splitting Purchase cards Inappropriate, unauthorized purchases Travel & Entertainment Expenses Duplicate claims, inappropriate activity Adult bars using MCC and description Payroll Phantom employees SSN TestCopyright 2013 Sunera LLC.25

Data Analytics ExamplesAccounts Payable Questionable invoices Invoices without a valid P.O. Sequential invoices Vendor Invoice Formats Duplicate invoices Multiple invoices for same item description Invoices for same amount on the same date Multiple invoices for same P.O. and date Vendors Phantom vendors PO BOX Test Vendor/Employee collusion One time Vendors or Vendors not used in over a yearCopyright 2013 Sunera LLC.26

Data Analytics — Non-Industry SpecificPurchasing Questionable purchases P.O./invoices with amount paid amount received Purchases of consumer items Split purchases Similar transactions for same vendor within specific timeframe Inflated prices Compare prices to standard price lists or to historical pricesCopyright 2013 Sunera LLC.27

Data Analytics — Non-Industry SpecificPurchase Cards Split purchases to avoid purchasing card limits Purchases processed as two or more separate transactions Identified by isolating purchases from specific vendors within shortperiods of time Favored vendors for kickbacks Trend analysis to compare current transaction volumes to previous timeperiod Suspicious purchases Transactions that occur on weekends, holidays, or vacations Travel related charges not on travel expenditure reportsCopyright 2013 Sunera LLC.28

Data Analytics—Non-Industry SpecificTime and Expense Duplicate claims Submitting claims twice Tracking “no receipt” claims Isolate expenses without receipts and identify underlying trends throughprofiling techniques Threshold reviews Track personnel exceeding thresholds Inappropriate activity Compare expenses to travel records to ensure expenses claimed forvalid trips Trends by employee compared to peers Fuel vs Mileage claims Fuel purchase location vs Branch locationCopyright 2013 Sunera LLC.29

Data Analytics Sample Logic Duplicates– Exact Duplicate – All fields identical within investigation period– Almost Duplicate Variance, Same-Different Duplicates Purchase Order: Same Vendor and Similar AmountPayments: Different Vendor Same Bank AccountPayments: Same Vendor Different Invoice Number Similar AmountPayments: Same Vendor Same Invoice, Same Amount, Different DatePayments: Same Vendor Name, Same Amount, Same Date, DifferentVendor IDCopyright 2013 Sunera LLC.30

Data Analytics Sample Logic Authorization Limits Single and multiple accumulated values exceeding limits Transaction amounts that exceed or are just below the authorizationlimit Requisitions, Purchase Orders, Invoices, Payments Accumulated transaction amounts that exceed the authorization limit Split Requisitions, Split Purchase Orders, Split Invoices, Split Payments Aging Single Record Age Days difference between Create Date and Approval Date Stale Requisitions, Stale Purchase Orders, Stale Invoices Multiple Files Aging Retroactive PO vs. Invoice (Invoice Create Date prior to PO Create Date)Copyright 2013 Sunera LLC.31

Data Analytics Sample Logic Data Quality– Identifying fields where critical data elements deviate from expected valuesand formats Invalid ID formats, missing key values, invalid characters, invalid values Requisitions, Purchase Orders, Invoices, Received Goods, Payments Segregation of Duties– SOD Security Table Level Comparing roles within ERP security tables to a conflict matrix– SOD at Transaction Level Single Record Create/Modify vs. Approve» Requisitions, Purchase Orders, Invoices, Payments Multiple files» Create/Modify PO vs. Create/Modify/Approve Vendor Master Update» Create/Modify PO vs. Receiver ID for Goods Received» Create/Modify PO vs. Create/Modify InvoiceCopyright 2013 Sunera LLC.32

Data Analytics Sample Logic Numeric Pattern Matching Benford digital analysis: exceptions which reveal themselves as digitalanomalies. Higher than expected PO amount of 49,000, bypassing controls on amountsover 50,000. Numeric Sequence or Gaps: exceptions which reveal themselves in anumeric sequence or gap. Invoice Number Sequences (suspect invoices) Transactions with even dollar amounts based on a divisor number,minimum transaction count, and threshold value. Expense Report Amounts with even dollar valuesCopyright 2013 Sunera LLC.33

Data Analytics Sample Logic String Pattern Matching– Name Match (% word match) Word exclusion lists to remove common words like: The, company, and, etc. Invoice: Employee Vendor Name Match – (Phantom Vendor) Invoice: Prohibited Vendors– Address Match (Numeric or Alpha Numeric match) Match on zip/postal code plus numeric digits from address field. Match on alpha-numeric values from the address field (no spaces or specialcharacters) Invoice: Employee Vendor Address Match – (Phantom Vendor)– Soundslike Match (phonetic match) SOUNDEX algorithmSOUNDSLIKE algorithmPayroll: similar employee namesT&E: Different expense cards assigned to employees with similar namesCopyright 2013 Sunera LLC.34

Transition CCM into Predictive AnalyticsTransition CCM into Predictive AnalyticsCopyright 2013 Sunera LLC.35

Data Analytic ClassificationCopyright 2013 Sunera LLC.36

Predictive Analytics Defined Predictive Analytics “ is business intelligence technology that produces a predictive score for each customeror other organizational element. Assigning these predictive scores is the job of a predictivemodel which has, in turn, been trained over your data, learning from the experience of yourorganization.”Source: Predictive Analytics World“.encompasses a variety of techniques from statistics, modeling, machine learning, anddata mining that analyze current and historical facts to make predictions about future orotherwise unknown events.In business, predictive models exploit patterns found in historical and transactional data toidentify risks and opportunities. Models capture relationships among many factors to allowassessment of risk or potential associated with a particular set of conditions guidingdecision making for candidate transactions.Predictive analytics is used in actuarial science, marketing, financial services, insurance,telecommunications, retail, travel, healthcare, pharmaceuticals and other fields.”Source: WikipediaCopyright 2013 Sunera LLC.37

Copyright 2013 Sunera LLC.38

Why is Predictive Analytics Important? Enable forecasting by allowing organizations to learn from databased on historical trends Provide insight of what’s expected to happen based on datapopulations Allow management determine growth potential Gain a competitive advantageCopyright 2013 Sunera LLC.39

Analytic ToolsThere are a wide variety of tools that specialize in or offer the abilityto develop predictive analytics, including but not limited to:Copyright 2013 Sunera LLC.40

Copyright 2013 Sunera LLC.41

Predictive Analytic UsesAreaPredictionCredit ScoresBased on many criteria, several agencies have an entire business based on predictinghow much risk you present to others in defaulting on your debt.FraudIdentify control weaknesses that may lead to fraud or estimate the amount of theft(shrink) in retails stores around given demographics.Marketing & SalesPredicting sales based on varied pricing and product models.Security Risk Indicators(KRIs)Predicting vulnerability to external threats that may impact the organizationCustomer Retention &Turnover RatesUse demographic / socio-economic information about customers to determine theirlikelihood to remain a customer and how much they will spend over their lifetime (LargeRetailer).Inventory / ProductionForecast the timing of orders and production at the customer level and seasonality level(not just sales)Copyright 2013 Sunera LLC.42

Real world uses of Predictive AnalyticsSource: Harvard Business Review – January 2006Copyright 2013 Sunera LLC.43

Amazon Example of Predictive AnalyticsPredictions based on OthersFact based on your searchCopyright 2013 Sunera LLC.44

Kayak Example of Predictive Analytics Recommendations on historic trendsCopyright 2013 Sunera LLC.45

Target Example of Predictive Analytics Target knows when you are pregnant!– Target assigns each customer an ID based on credit card number andother details.– Then compares purchases made by that person and with a purchasingpattern of pregnant women.– Example: One high school girl was purchasing products that indicatedshe was pregnant (high pregnancy score based on the Target factpattern). Target sent coupons to her house (which happened to be herparents) for diapers, etc. The dad was appalled that someone wouldsend diaper coupons to a high school girl. Dad then drove to target anddemanded to speak with a manager. Later he apologized to themanager after speaking with his daughter, and found out there aresome things going on in his house that Target knew but he did not.Copyright 2013 Sunera LLC.46

Google Search Example – 4 Step Methodology1. Identify the Objective Web search example would be to find the actual page you wantto read based on the query submitted2. Figure out what Levers you have to pull Levers would be things that you can change yourself. LeverGoogle pulled is the ordering of sites3. What Data we have and can connect that hooks up those leverswith the outcome we want Data on website tells us people that like website X will also likeY4. Hook together the Levers to the objectives using the data. This is how Google was builtCopyright 2013 Sunera LLC.47

Other Examples Pandora– Automatically plays music based on music you like / select Netflix– Selects movies/TV Shows you might like based on what you recentlywatched. Internet Ad Placements– These are now highly targeted based on things you have searched for. Apple– Calculate production numbers of their products based on past salesCopyright 2013 Sunera LLC.48

Predictive Analytics Models Predictive Models models of the relation between the specific performance of a unit in asample and one or more known attributes or features of the unit. objective of the model is to assess the likelihood that a similar unit in adifferent sample will exhibit the specific performanceDescriptive Models models quantify relationships in data in a way that is often used to classifycustomers or prospects into groups models identify many different relationships between customers orproductsDecision Models Models describe the relationship between all the elements of adecisiongenerally used to develop decision logic or a set of business rulesthat will produce the desired action for every customer orcircumstance.Copyright 2013 Sunera LLC.49

Regression Analysis Regression The difference between regression and descriptive is that regressiondeals with numerical/continuous target attributes, whereasdescriptive deals with discrete/categorical target attributes. Ie. if the target attribute contains continuous (floating-point) values, aregression technique is required. If the target attribute containscategorical (string or discrete integer) values, a classificationtechnique is called for.Copyright 2013 Sunera LLC.50

Predictive Analytic Techniques Regression Most commonly used for Predictive Analytics Uses mathematical equation as a model to represent theinteractions between the different variables in consideration Machine Learning originally employed to develop techniques to enable computersto learn (Artificial Intelligence) techniques emulate human cognition and learn from trainingexamples to predict future eventsCopyright 2013 Sunera LLC.51

Types of Regression AnalysisRegression Types and Their UsesLinearCan be adapted to a wide variety of data types, includingTime-series, cross-sectional, pooled or panel dataPartial and StepwiseMeasures how one independent variable and thedependent variable are related after determining theeffect of all the other independent variables in the modelLogit or ProbitAllow one to predict a discrete outcome (yes or no) from aset of variables that may be continuous, discrete, and/ordichotomousRegression SplinesAllow different regression models to model data overdifferent regions of the dependent variableCopyright 2013 Sunera LLC.52

Regression Basics Explain relationships between variables by examining the entirepopulations. However, you are only drawing data from yoursegment of the population (i.e. your population of customers is notthe entire population across the industry/product segment). Toensure the best results: Build the best model of the “TRUE” relatioinships between variables. Analyze confidence in the model (mathematical description of itsaccuracy)Copyright 2013 Sunera LLC.53

Regression Analysis Regression (con’t) The most common form of regression is linear regression, in which aline that best fits the data is calculated, that is, the line that minimizesthe average distance of all the points from the line. This line becomes a predictive model when the value of thedependent variable is not known; its value is predicted by the pointon the line that corresponds to the values of the independentvariables for that record.Copyright 2013 Sunera LLC.54

Regression Analysis Regression (con’t) Time Series - models are used for predicting or forecasting the futurebehavior of variables. Account for the fact that data points taken over time may have aninternal structure that should be accounted for ie. TrendsCopyright 2013 Sunera LLC.55

Copyright 2013 Sunera LLC.56

Sunera Predictive ApproachPhase 3:Phase 4:Develop & RunRemove FalsePositivesAnalyticsPhase 2:Phase 5:Determine DataSourcesImplementAnalyticsPhase 1:Identify Internal& ExternalVariablesCopyright 2013 Sunera LLC.PredictiveAnalyticsSolution57Phase 6:MonitorProgress

Gift Card Case Study Developed at global multi-billion dollar restaurant company with manydifferent brands Goal: Calculate Breakage (Unredeemed Gift Cards) Revenue &Revenue flows to the organization. Factors Impacting Gift Card Sales /Redemptions:– External Variable(s): Economy– Internal Variable(s): Seasonal Sales and Promotions Brand Selling the Card Sales Channel vs In-Store Results:– Level of cross-redemption of branded cards– GAAP based rationale for Breakage Revenue– FP&A Forecast of Gift Card Sales, Redemptions, Breakage under multiplescenarios (Automated)Copyright 2013 Sunera LLC.58

Copyright 2013 Sunera LLC.59

Data Analytics Rollout Options Insource Internal resources plan and deploy all CCM initiatives Outsource Sunera resources (or other provider) perform all the activities required forCCM rollout Provide documentation and training to client staff for maintenance of theprogram Co-source Sunera (or other provider) provides the knowledge and expertise and workswith client staff Shares the work of developing and creating tests Provides guidance Performs reviews of client work conducted and provide feedback/insight Conducts coaching sessions Provides ongoing support and adviceCopyright 2013 Sunera LLC.60

Data Analytics Training OptionSunera Training Offers: 2 or 3 day training classes Training is hands-on and classes are tailored to meets your participants’ skilllevels (beginner, intermediate, advanced). We customize the training by integrating your company’s data into the seminar,so your employees get to work with realistic company scenarios. Sunera is registered with the National Association of State Boards ofAccountancy (NASB) as a sponsor of continuing professional education on theNational Registry of CPE sponsors and offers CPE credits to those who attendand completed the training. Training is provided by a Sunera Director or Principal Level Associate withmultiple years and project experience using ACL. We are planning on offering a training course in Calgary in October pendingsufficient interest. Training (classroom instruction) can be provided to organizations directly.Copyright 2013 Sunera LLC.61

Contact InformationFor additional information on Sunera’s services, visit our website atwww.sunera.com or contact:Parm LalliDirector(949) [email protected] 2013 Sunera LLC.Matt OsbeckSenior Manager(714)[email protected]

Actuate BIRT Actuate e.Reports Cognos 8 BI Report Studio Crystal Reports InformationBuilders WebFOCUS JasperServer/iReport Microsoft SQL Server Reporting Services MicroStrategy Report Services SAS Web Report Studio Gartner February 2009: Critical Capabili