Data Analytics:Applying Data Analytics to a Continuous ControlsAuditing / Monitoring SolutionDecember 10, 2014Parm Lalli, CISA, ACDA

Sunera SnapshotProfessional consultancy with core competency in:Internal Audit IT Audit Regulatory Compliance PCIInformation Security Data Privacy IT Strategy & RiskFinance & Accounting Interim CFO/Controller TrainingOffices across the United States and CanadaDelivered thousands of projects for hundreds oforganizations across all major industries andsectors. Adept in servicing the Fortune 1000 butvery adaptable to smaller organizations andgovernment entitiesTrained and certified professionals withappropriate oversight utilizing proven, pragmaticmethodologies to ensure quality resultsThe nation’s largest independent providerof technology risk consultingSolution-oriented teams that tailor projects to clientneeds, complementing clients’ internal capabilitiesA PCI Qualified Security Assessor and ApprovedScanning Vendor (QSA & ASV)SAPCertified SAP integration partner with specificexpertise in SAP security, GRC, and controlsACLThe only authorized reseller of ACL products inNorth America, solidifying our reputation as amarket leader in Continuous Controls MonitoringTrack record of projects achieving anticipatedbenefits, on-time, and within budget. Results drivenby rigorous project management discipline andfinance and IT capabilitiesRegistered with NASBA to offer CPEs for ourexternal Internal Audit and ACL training courses.

About Your SpeakersParm Lalli, CISA, ACDAParm is a Director with Sunera and leads the national data analytics practice. Parm has over 13 years of dataanalytics, audit, and controls experience with Sunera and other IT consulting firms. This experience includesleading multiple data analytics and CCM initiatives; installing, implementing, and configuring ACL AuditExchange; and being involved in work on IT general controls, application controls, internal audit, IT riskassessment, process improvement advisory, operational audit, Sarbanes-Oxley Act (SOX), and NationalInstrument 52-109. Parm has also been involved in conducting vulnerability assessments and penetrationtesting for clients. Parm has a great deal of experience with CAAT’s tools, performing data analytics, anddeveloping Continuous Controls Monitoring applications for many different business processes. He has over 13years experience with ACL Software. Parm is a Certified Information Systems Auditor (CISA) and ACL CertifiedData Analyst (ACDA).Parm also has over 10 years experience with Arbutus Software and implementing and configuring Arbutus forclients CCM needs. Parm was involved in a major feasibility study by ISACA on the concepts of ContinuousControls Monitoring (CCM) and what organizations need to do and/or have in place to kick of such an initiative.Prior to joining Sunera, Parm worked in Compliance Audits, IT Risk Assessments, Vulnerability and Penetrationtesting, Data Analytics, and IT Audits with similar firms. Parm also worked at PwC in the Advisory groupperforming Revenue Assurance consultancy with the Telco Industry. Prior to that, Parm worked with ACL forover 5 years where he led Data Analytics and Continuous Controls Monitoring projects. Parm is a CertifiedInformation Systems Auditor (CISA) and ACL Certified Data Analyst (ACDA).Copyright 2013 Sunera LLC.3

Analytic DefinitionsData Analytics(DA)Continuous Auditing(CA)Continuous ControlMonitoring(CCM) Management or Internal Audit derive insight from operational, financial, andother forms of electronic data internal or external to the organization. Insights can be historical, real-time, or predictive and can also be riskfocused (e.g., controls effectiveness, fraud, waste, abuse, policy/regulatorynoncompliance) Collection of audit evidence and indicators by an Internal Auditoron information technology (IT) systems, processes, transactions, andcontrols on a frequent repeatable, and sustainable basis. Feedback mechanism used by Management to ensure that controlsoperate as designed and transactions are processed as prescribed. Thismonitoring method is the responsibility of management and can forman important element of the internal control environment.

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.ACLSAP or Oracle GRCApprovaOversightActuate BIRTActuate e.ReportsCognos 8 BI Report StudioCrystal ReportsInformationBuilders WebFOCUSJasperServer/iReportMicrosoft SQL Server Reporting ServicesMicroStrategy Report ServicesSAS 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 CCM10 Fortune 500 Organizations were involvedIdentify Challenges faced by OrganizationsTop Data Analytics tools used––––ACLIdeaArbutusTableauGroup of 10 shared knowledge and agreed upon analytics7 Recommendations madeCopyright 2013 Sunera LLC.7

CCM Documents RequiredCCM Project Checklist– This document is a complete Project Checklist of typical CCM projecttasks and responsibilitiesData Analytic Tests– This spreadsheet has standard analytics for many different businessprocesses. Included are the purposes for each analyticRequirements 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 RequiredApplication 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 AnalyticsAd Hoc Analysis–––––Time consumingData typically supplied by ITUp to 50% more budgeted time requiredDifficult to repeat tests if not documentedExploratory type analysisRepeatable 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 AnalyticsCentralized 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 logicContinuous 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 AnalyticsContinuous 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 AnalyticsAccess 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.Close 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 reliance13

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-upactivitiesEnables better allocation of skilled audit/technical resources within theorganizationCopyright 2013 Sunera LLC.14

What Are the ChallengesImplementing 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

Approach – IA Analytics1. Internal Discussion to Identify Data Analytic Integration PointsoooReview Annual Audit PlanReview Individual Audit ProgramsReview Sunera “Test Bank” for Standard Analytics Step 2-4 Determined after Step 1 2. Identify & Obtain Data SetsooUnderstand Data SourcesValidate / Reconcile Obtained Data3. Perform Exploratory Analytics (Pre-Audit)oBasic analytic steps to determine feasibility & benefit4. Analytic DevelopmentoPrepare value-add analytics for live audit

Data Analytics Steps for Mature RolloutSteps for Implementing Continuous Controls Monitoring/Auditing1. 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.18

Data Analytics Implementation ApproachPhase 4:Phase 3:DataPhase lyticsPhase 6:Phase 2:ImplementIdentifyCustomOpportunitiesPhase 1:Data Analytic ToolSelectionAnalyticsDataAnalyticsProgramPhase 7:Training &KnowledgeTransfer

Data Analytics Project TeamProject 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.20

Data Analytics — Sunera ApproachStep 1– Review existing business process risk documentation– Review existing analytics for efficiency and effectiveness– Update existing analytics for full automationStep 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.21

Data Analytics — Sunera ApproachStep 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 measuresStep 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.22

Data Analytics — Sunera ApproachStep 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 preparationStep 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.23

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

A Mature Data Analytics OverviewSelf-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 configurationTraining provided to any and all individuals involved, including newhiresOngoing review of existing analytics and possible new analyticsbased on new business processesMaintain a change log for any addition or removal of scripts orchanges to configurationCopyright 2013 Sunera LLC.25

Data Analytics nalyticsManualTestingOwnershipShift to BusinessContinuousMonitoring

Critical Success FactorsBusiness Buy-inIA Executive Management SupportAnalytic Optimization– Minimize False Positives for quicker buy-in– Quick wins – Low hanging FruitData Identification & AccessCoordination with existing analytic efforts going on in the business– Work together and eliminate redundancy.

Data Analytics ExamplesCopyright 2013 Sunera LLC.28

Data Analytics ExamplesPurchasingPurchase splittingPurchase cardsInappropriate, unauthorized purchasesTravel & Entertainment ExpensesDuplicate claims, inappropriate activityAdult bars using MCC and descriptionPayrollPhantom employeesSSN TestCopyright 2013 Sunera LLC.29

Data Analytics ExamplesAccounts PayableQuestionable invoicesInvoices without a valid P.O.Sequential invoicesVendor Invoice FormatsDuplicate invoicesMultiple invoices for same item descriptionInvoices for same amount on the same dateMultiple invoices for same P.O. and dateVendorsPhantom vendorsPO BOX TestVendor/Employee collusionOne time Vendors or Vendors not used in over a yearCopyright 2013 Sunera LLC.30

Data Analytics — Non-Industry SpecificPurchasingQuestionable purchasesP.O./invoices with amount paid amount receivedPurchases of consumer itemsSplit purchasesSimilar transactions for same vendor within specific timeframeInflated pricesCompare prices to standard price lists or to historical pricesCopyright 2013 Sunera LLC.31

Data Analytics — Non-Industry SpecificPurchase CardsSplit purchases to avoid purchasing card limitsPurchases processed as two or more separate transactionsIdentified by isolating purchases from specific vendors within shortperiods of timeFavored vendors for kickbacksTrend analysis to compare current transaction volumes to previous timeperiodSuspicious purchasesTransactions that occur on weekends, holidays, or vacationsTravel related charges not on travel expenditure reportsCopyright 2013 Sunera LLC.32

Data Analytics—Non-Industry SpecificTime and ExpenseDuplicate claimsSubmitting claims twiceTracking “no receipt” claimsIsolate expenses without receipts and identify underlying trends throughprofiling techniquesThreshold reviewsTrack personnel exceeding thresholdsInappropriate activityCompare expenses to travel records to ensure expenses claimed forvalid tripsTrends by employee compared to peersFuel vs Mileage claimsFuel purchase location vs Branch locationCopyright 2013 Sunera LLC.33

Data Analytics Sample LogicDuplicates– 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.34

Data Analytics Sample LogicAuthorization LimitsSingle and multiple accumulated values exceeding limitsTransaction amounts that exceed or are just below the authorizationlimitRequisitions, Purchase Orders, Invoices, PaymentsAccumulated transaction amounts that exceed the authorization limitSplit Requisitions, Split Purchase Orders, Split Invoices, Split PaymentsAgingSingle Record AgeDays difference between Create Date and Approval DateStale Requisitions, Stale Purchase Orders, Stale InvoicesMultiple Files AgingRetroactive PO vs. Invoice (Invoice Create Date prior to PO Create Date)Copyright 2013 Sunera LLC.35

Data Analytics Sample LogicData 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, PaymentsSegregation 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.36

Data Analytics Sample LogicNumeric Pattern MatchingBenford 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.37

Data Analytics Sample LogicString 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.38

Data Analytics Rollout OptionsInsource Internal resources plan and deploy all CCM initiativesOutsource Sunera resources (or other provider) perform all the activities required forCCM rollout Provide documentation and training to client staff for maintenance of theprogramCo-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.39

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

Contact InformationFor additional information on Sunera’s services, visit our website or contact:Parm LalliDirector(949) [email protected] 2013 Sunera LLC.41

business requirements and will include how well the proposed tool will integrate with existing 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