1 / 44

Presented to: & October 4, 2013

A Case Study in Continuous Controls Monitoring. Presented to: & October 4, 2013. Course goal s & objectives. To guide participants through the terminology, concepts and value proposition for deploying Continuous Controls Monitoring (CCM). Review of what CCM is Benefits of CCM

Download Presentation

Presented to: & October 4, 2013

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. A Case Study in Continuous Controls Monitoring Presented to: & October 4, 2013

  2. Course goals & objectives To guide participants through the terminology, concepts and value proposition for deploying Continuous Controls Monitoring (CCM). Review of what CCM is Benefits of CCM Required Components Discuss the Emory Case Study Tools to leverage The Approach and Best Practices Discuss Emory’s ROI

  3. About the Presenters • Mark Hafitz, Director of Information Technology Special Projects, Emory University • Mark has worked for Emory University for over 20 years. His career at Emory started in the Information Technology Division working as a Programmer/Analyst supporting financial applications.  Several years later he began working in the Human Resources Division as the Assistant Director, Information Systems. He managed the daily operations of the Human Resources Information Systems area and oversaw the development and maintenance of all Human Resources systems.  For the last 15 years he has worked in the Finance Division managing and overseeing the completion of the division wide financial systems projects as the Director of Financial Projects. Prior to Emory, Mark spent several years as a Programmer/Analyst working with the Information Systems group at Kimberly-Clark Corporation. Mark received his Master of Business Information Systems degree from Georgia State University, and a Bachelor of Arts in English Literature from Emory University. • Scott Stevenson, CIA CPA, Interim Chief Audit Officer, Emory University • Scott is responsible for overseeing the University administrative compliance and continuous auditing programs and special projects, which include conducting investigations and leading the anti-fraud and financial attestation programs.  Before joining Emory in 2005, Scott served as a Director at Bon Secours Health System and a consultant with Ernst & Young and PricewaterhouseCoopers.  He spent a year in Savannah as the Chief Audit Officer for the Memorial Health System.  A member of the Institute of Internal Auditors, and the HealthCare Financial Management Association, Scott has over 25 years of accounting and audit experience.  He is a Certified Public Accountant (CPA), a Certified Internal Auditor (CIA), and holds a BS from Wake Forest University and an MBA from Averett University. • Wylie Roberts, CPA, Senior Manager, Business Advisory Services - Solomon Edwards • Wylie Roberts has extensive finance, accounting, and technology experience, including project management, financial reporting, system implementations, internal controls, business process improvement, and hands-on development of CPM applications. Wylie’s recent work for clients has included building interactive web-based Business Intelligence Reporting platforms, technical lead consultant developing Continuous Controls Monitoring software, acting as an Interim CFO, helping a company convert legacy data to a new payroll systems, and helping a company centralize and improve its AP function. Wylie began his career and earned his CPA license with KPMG. He is a recognized authority on GRC and recently authored an article on IT related fraud risk that was published in Accounting Today.

  4. What is CCM • Continuous Controls Monitoring (CCM) is an ongoing systematic practice of observing and checking, for reasonable assurance, that Information Technology Systems (hardware and/or software) operate as designed. These supervisory practices, against IT systems, have a basis for maintaining data validity, reliability, and integrity. Several areas where modern organizations depend on IT systems to operate continuously, accurately and effectively: • The reporting of organization finances. • E-Commerce and Electronic Funds Transfer. • Network and Computing Platform Security. • Medical, Criminal, or Federal Data Records Management and Retention. • Public Telecommunications Voice and Data Networks • National Energy Grids and Utilities. -Wikipedia

  5. Benefits of CCM • Proactive Mitigation -- Results can be acted on rapidly, sometimes before transaction cycle completes • Goes beyond just “reporting” to provide an actionable control framework • Cutting costs, prevents fraud, stops broken processes– Ex: T&E expense submissions • Automate manual tasks – Ex: Reconciliations, IA testing procedures, Reviews • Enhance Revenue – Ex: sales or source documents that trigger revenue recognition missing • Creates a “virtuous Cycle” – Audit area’s remain under “testing” from that point forward, removing the need to repeat, freeing up time for testing in novel areas – Ex: Periodic testing of Access Controls: never need to assign resources to do this again once you set this up. • Creates an environment that deters attempts at defalcations

  6. Examples T&E: • Submissions related to terminated or non-existent employees • Duplicate Submissions (of same expense item) on different dates • Same Employee on multiple meal submissions for same date (including on perdiem as well as named as attendee on itemized meal expense) • Trend analysis that indicates abuse of receipt requirement thresholds or other unusual activity (Recurring unusual or just below limit submissions) • Itemized gas expense on or about same period that mileage expense is claimed • Exceeding reasonable limit (examples: over $100 per person meal expense, or more than one standard deviation above the average for a given city for hotel expense) • Timing suspicious (example: occurs on date that employee reported vacation or sick time on timesheet; requires access to payroll data) • Unapproved merchant or type of expense (retail stores, liquor stores, strip clubs, etc.)

  7. Examples (Cont.) Reduce GRC Costs/Audit Fees • Catch systems user permissions that violate segregation of duties principles (Automated testing of SOD/Access Controls) • Catch Journal Entries with improper or missing approval (based on amount, account, center, or user) • Catch Journal Entries made to unusual accounts for a given center/user (statistical anomalies) Catch Fraud • Notification of Vendor File tampering (Ex: change and change-back fraud attempt) • Vendors who’s address is similar to an employee’s • Notify of entries with unusual amounts or time of day

  8. Examples (Cont.) Control and Reconciliation Automation: • Find duplicate or missing checks by bank account • Produce automated notification of events requiring review or authorization (changes to salary rates, payments over certain limits, etc.) • Catch terminated or other non-employees with systems access or on payroll Stop Revenue Leakage: • Notify of missing Proof of Delivery

  9. Examples of What CCM Does (Cont.) Reduce Operational Costs • Catch PO’s where cost exceeds retail price (or where margin would be below a set threshold) • Identify products with Zero quantities or prices • Notify of duplicate items added to inventory master or purchasing master Transactions out of the norm (statistical techniques – commissions, rebate programs, etc.) Improve Working Capital: • Duplicate Invoices (caught before check-run)

  10. Exceptions Highlight Broken Processes Your time is freed up to partner with operations to add strategic value by helping to fix the processes that allowed the exceptions: • Operational Audits • Process Improvement • Training • Policy Development & Documentation • System Enhancements • Efficiency initiatives • Quality Initiatives

  11. The Business Case CCM can be a key control for Sarbanes-Oxley purposes but…. Most companies rely on CCM for operational improvement and cost reductions with true ROI: • Reduce operational & GRC costs • Reduce external audit fees • Stop Revenue “Leakage” • Control and reconciliation automation • Improve working capital • Improve Quality (and therefore future sales) • Catch fraud attempts • Improve Compliance (preventing fines) • Support Management information needs • Policy Enforcement

  12. Creating Value

  13. Components of CCM Reports & Dashboards Source Systems GL Reporting Tool AP Statistical and Analytical Routines Continuously Performed on Data Exceptions Identified AR Exception Management Interface Email Alerts Replication Or ETL HR Replicated Data For Analysis Other Invoice A123 from “Acme Solutions” in the amount of $543.21 may be a duplicate of invoice 123-1 in the amount of $543.21Dated 4-14-2010 from “Acme Inc.”

  14. Step 4: User Components Step 1: Design Controls Step 2: Implement ETL Step 3: Develop Analytics • Workshop(s): • Determine Risks and Control Use Cases needed • Understand the systems involved • Understand the Data • Define Analytical Logic needed, by Use Case • Define Exception Resolution workflow & Status Codes • Develop High Level Architecture • Security Requirements • Create Design Document • Collaborate w/ IT: • Set up hardware & Connectivity • Select ETL Approach • Develop ETL process • Develop Transformations, if required • Test Integrity and Impact on Production Environment • Start scheduler/Cron job • Analytics DB In Place • Create Exceptions DB • Iteratively for Each Use Case: • Develop Algorithm(s) • Unit Testing: Review and refine to reduce false positives • Set up Reports & Dashboards Tool (IIS or SharePoint) • Set up Exceptions Management Interface • System Documentation • Develop Reports & Dashboards • User Training • User Acceptance Testing • Tuning & Optimization • Develop Email Alerts (Optional) • Roll Out Approach

  15. Requirements Gathering Step 1: Design Controls • Control Objective • Functional Description of the control objective (Integrity Check) • Functional Description of the Data Involved • Triggering Event • Financial Impact • Data Specifics • Data to Exclude from Analyzing • Insufficiency Criteria • Delay (if applicable) • Data to Display • Indicator(s) • Exception Resolution Process

  16. Export Transform & Load Step 2: Implement ETL Our Analytics DB Source Transformations (if needed) Extract Load • Multiple ways to approach: • Linked Server Object (typical for an Oracle DB Source) • ETL Tools (such as SSIS; open source tools are available too) • Replication (Publication/Subscription model) • If Mirror or Replicated instance of production data is already available that is the preferred method. If not, indexed short running queries with “Read Only” accounts to pull in daily incremental activity and only from needed tables/columns, based on scheduled job (during non-peak hours) is recommended. • Key objective is automated availability of near real-time data needed to support continuous analytics • Example of possible “Transformation”: Mapping might be needed to translate to parent’s Chart of Accounts or other common model from various autonomous systems to allow cross-comparisons • Some analytics need to track “changes” to Master Data (Example: Vendor Master File Tampering Testing); in this case, we created logic to create versioned snapshots of records in the ETL logic • Because we want the Exception to be able to be resolved by the Owner without them having to log into the source application, we pull all data needed to understand and resolve the Exceptions for presentation in the Exception Report

  17. Specific Indicators Step 3: Develop Analytics Each Indicator becomes a part of the Where Clause and possibly helps drive joins

  18. Script Development Step 3: Develop Analytics • Multiple Indicators in SQL Select Exception ID (key), ‘Duplicate Invoice’ as ‘Exception_Type’, XXX as Probability, Etc…(All data needed) From Vouchers as V Join Vouchers as V_Same_PO on V.PO = V_Same_PO.PO Join Vouchers as V_Same_Amt on V.Amt = V_Same_Amt.Amt Where V.Vendor + V.InvNo <> V_Same_PO.Vendor + V_Same_PO.InvNo And (V_Same_PO.VoucherNum not Null OR V_Same_Amt.VoucherNum not Null)

  19. Script Development (continued) Step 3: Develop Analytics • The Use of Probabilities The “Probability” field in your select clause: Select 'Probability' = (case WHEN V.PO = V_Same_PO.PO THEN 80% end) + (case when V.Amt = V_Same_Amt.Amt then 20% end) From Table_X

  20. Script Development (continued) Step 3: Develop Analytics • Duplicate Invoice Actual Script PROBABILITY = (case when CCM.dbo.fn_StrClean(ISNULL(V_Prior_Addr.ADDRESS1, ' ') + ISNULL(V_Prior_Addr.ADDRESS2, ' ') + ISNULL(V_Prior_Addr.City, ' ')) = CCM.dbo.fn_StrClean(ISNULL(V_Dup_Addr.ADDRESS1, ' ') + ISNULL(V_Dup_Addr.ADDRESS2, ' ') + ISNULL(V_Dup_Addr.City, ' ')) then .6 End) + (Case when CCM.dbo.fn_calculateJaroWinkler(CCM.dbo.fn_StrClean(ISNULL(V_Name_Dup.NAME1, ' ')) , CCM.dbo.fn_StrClean(ISNULL(V_Name_Prior.NAME1, ' ') )) > .97 then .6 else (.5-.5) End), V_Dup.GROSS_AMTAS FINANCIAL_IMPACT, VAL_1 = V_Dup.INVOICE_ID, DEF_1 = 'Duplicate Invoice No.', VAL_2 = V_Prior.INVOICE_ID , DEF_2 = 'Prior Invoice No', VAL_3 = V_Dup.GROSS_AMT, DEF_3 = 'Invoice Amt', VAL_4 = V_Dup.Voucher_ID, DEF_4 = 'Duplicate VoucherID', VAL_5 = V_Prior.Voucher_ID, DEF_5 = 'Prior_Voucher_ID', VAL_6 = V_Dup.VENDOR_ID, DEF_6 = 'Duplicate Vendor ID', VAL_7 = V_Prior.Vendor_ID, DEF_7 = 'Prior Vendor ID', --5.7B Possible Duplicate Invoice: Same Invoice No and Amount for Similar Vendors, within 60 days. Insert into CCM.dbo.EXCEPTIONS_Stage SELECT Case when V_Dup.BUSINESS_UNIT in ('EMUNV') then 'EUV' else 'EHC' end AS COMPANY, '' AS DEPT_NUM, '' AS DEPT_NAME, 'EXCEPTION' AS TYPE, 'Procure to Pay' AS CATEGORY, '5.7B Possible Duplicate Invoice - Same Invoice No, Different But Similar Vendors' as Exception_Name, '5.7B' + V_Dup.BUSINESS_UNIT + V_Dup.Voucher_ID as EXCEPTIONID, GETDATE() AS EXCEPTION_DATE, V_Dup.OPRID_LAST_UPDT AS ASSOCIATED_USER, 'NEW' AS STATUS, '' AS EXCEPTION_OWNER, '' AS NOTES, INDICATORS = (case when CCM.dbo.fn_StrClean(ISNULL(V_Prior_Addr.ADDRESS1, ' ') + ISNULL(V_Prior_Addr.ADDRESS2, ' ') + ISNULL(V_Prior_Addr.City, ' ')) = CCM.dbo.fn_StrClean(ISNULL(V_Dup_Addr.ADDRESS1, ' ') + ISNULL(V_Dup_Addr.ADDRESS2, ' ') + ISNULL(V_Dup_Addr.City, ' ')) then 'Same Invoice Number for Vendors with Similar Address. ' else ' ' end) + (Case when CCM.dbo.fn_calculateJaroWinkler(CCM.dbo.fn_StrClean(ISNULL(V_Name_Dup.NAME1, ' ')) , CCM.dbo.fn_StrClean(ISNULL(V_Name_Prior.NAME1, ' ') )) > .97 then 'Same Invoice Number for Vendors with Similar Name. ' else ' ' End),

  21. Script Development (continued) Step 3: Develop Analytics • Duplicate Invoice Actual Script (Continued) VAL_8 = V_Name_Dup.NAME1, DEF_8 = 'Duplicate Vendor Name', VAL_9 = V_Name_Prior.NAME1, DEF_9 = 'Prior Vendor Name', VAL_10 = V_Dup.Invoice_DT, DEF_10 = 'Duplicate Invoice Date', VAL_11 = V_Prior.INVOICE_DT, DEF_11 = 'Prior Invoice Date', VAL_12 = CCM.dbo.fn_StrClean(ISNULL(V_Dup_Addr.ADDRESS1, ' ') + ISNULL(V_Dup_Addr.ADDRESS2, ' ') + ISNULL(V_Dup_Addr.City, ' ')), DEF_12 = 'Scrubbed Duplicate Vendor Address', --VAL_13 = CCM.dbo.fn_StrClean(ISNULL(V_Prior_Addr.ADDRESS1, ' ') + ISNULL(V_Prior_Addr.ADDRESS2, ' ') + ISNULL(V_Prior_Addr.City, ' ')), DEF_13 = 'Scrubbed Prior Vendor Address', VAL_14 = NULL, DEF_14 = NULL, VAL_15 = NULL, DEF_15 = NULL, VAL_16 = NULL, DEF_16 = NULL, VAL_17 = NULL, DEF_17 = NULL, VAL_18 = NULL, DEF_18 = NULL, VAL_19 = NULL, DEF_19 = NULL, VAL_20 = NULL, DEF_20 = NULL, VAL_21 = NULL, DEF_21 = NULL, VAL_22 = NULL, DEF_22 = NULL, VAL_23 = NULL, DEF_23 = NULL, VAL_24 = NULL, DEF_24 = NULL, VAL_25 = NULL, DEF_25 = NULL, VAL_26 = NULL, DEF_26 = NULL, VAL_27 = NULL, DEF_27 = NULL, VAL_28 = NULL, DEF_28 = NULL, VAL_29 = NULL, DEF_29 = NULL, VAL_30 = NULL, DEF_30 = NULL from CCM.dbo.PS_VOUCHER as V_Dup left outer join CCM.dbo.PS_VOUCHER as V_Prior on V_Prior.INVOICE_ID = V_Dup.INVOICE_ID and V_Prior.VENDOR_ID != V_Dup.VENDOR_ID and V_Prior.INVOICE_DT >= DATEADD(dd, -60, V_Dup.INVOICE_DT) and V_Dup.ENTERED_DT >= V_Prior.ENTERED_DT left outer join CCM.dbo.PS_VENDOR as V_Name_Dup on V_Name_Dup.VENDOR_ID = V_Dup.VENDOR_ID left outer join CCM.dbo.PS_VENDOR as V_Name_Prior on V_Name_Prior.VENDOR_ID = V_Prior.VENDOR_ID left outer Join CCM.dbo.PS_VENDOR_ADDR as V_Dup_Addr on V_Dup_Addr.VENDOR_ID = V_Dup.Vendor_ID and V_Dup_Addr.ADDRESS_SEQ_NUM = V_Dup.ADDRESS_SEQ_NUM left outer Join CCM.dbo.PS_VENDOR_ADDR as V_Prior_Addr on V_Prior_Addr.VENDOR_ID = V_Prior.Vendor_ID and V_Prior_Addr.ADDRESS_SEQ_NUM = V_Prior.ADDRESS_SEQ_NUM where V_Dup.ENTERED_DT >= DATEADD(dd, -5, V_Dup.INVOICE_DT) and V_Prior.ENTRY_STATUS NOT IN ('X', 'R') and V_Prior.Vendor_ID is not null and V_Dup.GROSS_AMT = V_Prior.GROSS_AMT and ( CCM.dbo.fn_StrClean(ISNULL(V_Prior_Addr.ADDRESS1, ' ') + ISNULL(V_Prior_Addr.ADDRESS2, ' ') + ISNULL(V_Prior_Addr.City, ' ')) = CCM.dbo.fn_StrClean(ISNULL(V_Dup_Addr.ADDRESS1, ' ') + ISNULL(V_Dup_Addr.ADDRESS2, ' ') + ISNULL(V_Dup_Addr.City, ' ')) OR CCM.dbo.fn_calculateJaroWinkler(CCM.dbo.fn_StrClean(ISNULL(V_Name_Dup.NAME1, ' ')) , CCM.dbo.fn_StrClean(ISNULL(V_Name_Prior.NAME1, ' ') )) > .97 ); GO

  22. Reporting Step 4: User Components • Direct Reports against Transactional Data (Open PO’s > 90 days, Top 5 customers, Edit Checks, etc.) • Exceptions Based/Meta Reporting (Top 5 types of exceptions, top 5 people/centers violating policy, etc.) • Can be tabular or graphical • Can we “webified” and made available to a broad audience

  23. Use an Iterative Approach

  24. Prioritizing Easy Complex High Benefit Low Benefit

  25. Case Study – Issues • Significant Control Weaknesses • Decentralized structure • Multiple disbursement processes • Broadly distributed access • Duties not segregated • Few restrictions/discretionary accounts • Poor monitoring controls • Bleeding From a Thousand Cuts • 1 million annually in inaccurate payments • Multiple frauds • Resource Limitations

  26. Case Study – Considerations • Desire to be “Proactive vs. Reactive” • Catch errors before paid • Budget • No Licensing fees/annual commitments • Flexibility/Ease of Use • Audit department to maintain/minimal IT support • Growth potential: handle unlimited data sources and continually add new logic tests • Communications (notice of exceptions; resolution) • Transferability to management • Reporting capabilities • Security & Compliance • The environment needed to be secure (SSL) to safeguard confidential information

  27. Case Study – Why SEG • Why Emory decided to Partner with SEG and use an open-source solution: • Subject Matter Expertise • Cost effective/No additional licensing fees • Co-Sourced Approach • Leveraged use of existing technology/Not tied to vendor • Knowledge transfer/ability to support in house • Ability to deploy in a phased approach

  28. Algorithms Created – Phase I • Vendor Master Integrity Checks • Conflicts of interest • Duplicate vendors • Vendor Master Tampering • Payment Integrity Checks • Duplicate Payments • Potential personal purchases on corporate card • Expenses/ Per Diems • Travel agent/employee ID not validated • HR Checks • Rehire of terminated employees • New hire background checks • FMLA Status Consistency • FLSA Error Checking

  29. Components of CCM At Emory Email Alerts generated embedded link to a report Statistical and Analytical Routines Continuously Performed on Data • Source Systems • PeopleSoft • HR, • Payroll, • Payables, • Procurement • Kronos • LDAP • VB Script Email Alerts Exceptions Identified Exception Management Interface ASP.net Web form SQL Replicated Data For Analysis ETL Reporting Tool IIS Web Based Reports & Dashboards

  30. Automated Processing • Automated notification of daily ETL feed and Analytics Success for Failure is sent to management, giving positive assurance that the application is continuously testing transactional data. Most days, no exception occurs, and there is nothing to report, so this allows confidence that the application is actually turned on and working.

  31. Email Alerts • When exceptions do occur, user specific Email Alerts are generated (when exceptions relevant to only specified users occur) with an embedded link to a report that only allows them to see authorized data unique to that user.

  32. Security Considerations The Reports and Exception Management interface needed to be secure. We utilized the innate SSL capabilities that come with SQL Server and IIS. • The high level steps to enable SSL Encryption are: • Requesting a server certificate for the computer that is running IIS. If the IIS server already has a server certificate, you can go to step 4. • Obtaining a server certificate from a certification authority. • Installing the newly issued server certificate into IIS, Binding it. • Enabling SSL encryption.

  33. IIS Reporting

  34. IIS Reporting

  35. Alternate View • Clicking the above link presents an Alternate view of all data related to that specific exception.

  36. Alternate View (continued) • Scrolling down to bottom, the user can click the link to enter “Edit Mode” (shown on next screen shot).

  37. Edit Mode

  38. Edit Mode • After updating the information, user clicks the “Update” link

  39. Update Success

  40. Management Reporting

  41. Case Study - Results

  42. Case Study - ROI • Implementation costs recovered in 6 weeks • Duplicate payments (invoices and supplemental pay) • Caught prior to disbursement (reduced costs to correct) • Control Effectiveness Monitoring Results • Conflicts of interest • Expenses/ Per Diems • Rehire of terminated employees • Inconsistent FMLA status • Travel agent/employee ID not validated • Future Opportunities/Next Steps • Revenue • RAC Audits • Compliance: grants and contracts • Removal of network access for terminated employees • Statistical analysis

  43. Does CCM Make Sense For Your Company? • Any manual processes subject to human error? • Any Manual Audits or Reconciliations? • Any recurring Analytical procedures that consume a lot of time or are pain-points? • Concerns about policy compliance? • Concerns about employee theft? If the answer is “yes” to any of these, it is likely that CCM can bring solid value to your company enabling you to increase its Audit Capability Maturity Level while allowing the finance and audit teams to show their strategic value to the rest of the company.

  44. For More Information Wylie Roberts, CPA SolomonEdwards Sr. Manager, Business Advisory Services wroberts@solomonedwards.com Cell: 404-218-6892 Scott Stevenson, CIA, CPA Emory University Associate Chief Audit Officer Office: 404-686-2916 sjsteve@emory.edu SolomonEdwardsGroup, LLC Atlanta Office Five Concourse Parkway, Suite 1450 Atlanta, Georgia 30328

More Related