A Case Study in Continuous Controls Monitoring Presented
A Case Study in Continuous Controls Monitoring Presented to: & October 4, 2013 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 Emorys ROI 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. Wylies 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. 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 4 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 areas 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 5 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.) 6 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 whos address is similar to an employees Notify of entries with unusual amounts or time of day 7 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 8 Examples of What CCM Does (Cont.) Reduce Operational Costs Catch POs 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) 9 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 10 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 11 Creating Value 12 Components of CCM Source Systems Reports & Dashboards GL AP Statistical and Analytical Routines Continuously Performed on Data
AR Exceptions Identified Exception Management Interface Email Alerts HR Other Reporting Tool Replication Or ETL Replicated Data For Analysis
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. 13 Approach Step 1: Step 2: Step 3: Step 4: Design Controls Implement ETL Develop Analytics User Components
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 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 Create Design Document 14 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 15 Step 2: Implement ETL Export Transform & Load Source Extract
Transformations Load (if needed) Our Analytics DB 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 nonpeak 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 parents Chart of Accounts or other common model from various autonomous systems to allow crosscomparisons 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 16
Step 3: Develop Analytics Specific Indicators Name Duplicate PO Description Test for multiple occurrences of the same PO number being referenced by multiple invoices. Functional Logic / Algorithm For each given voucher, test all vouchers within the previous 60 days for the same PO number being referenced. Probability 80% Name
Duplicate Amount Description Test for multiple occurrences of the same Amount being referenced by multiple invoices from the Same Vendor. Functional Logic / Algorithm For each given voucher, test all vouchers within the previous 60 days from the same vendor that are for the same amount. Probability 20% Each Indicator becomes a part of the Where Clause and possibly helps drive joins 17 Step 3: Develop Analytics Script Development
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) 18 Step 3: Develop Analytics Script Development (continued) 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 19 Step 3: Develop Analytics Script Development (continued) Duplicate Invoice Actual Script --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), 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.NAM E1, ' ')) , CCM.dbo.fn_StrClean(ISNULL(V_Name_Prior.NAME1, ' ') )) > .97 then .6 else (.5-.5) End),
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 21 Step 4: User Components Reporting Direct Reports against Transactional Data (Open POs > 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 22 Use an Iterative Approach Area Integrity Check
Value Complexity (1 Low to 5 Hi) (-1 Low to -5 Hi) Total Score Development Order PTP Duplicate Vouchers 5 -5 0 1
OTC Employee Sales of Damaged Goods 4 -3 1 2 OTC Unapplied Credit Memos > 90 days 5 -3 2 3 PTP Payables Discounts Not Taken
4 -1 3 4 OTC Unearned Discounts Taken 4 -2 2 5 PTP Open POs Over 90 Days 3
-1 2 6 OTC Picks exceed Order QTY 2 -1 1 7 OTC Largest Unpaid Balances 1 -1 0
8 PTP Received, not vouchered 2 -2 0 9 PTP Invoice w/out PO 3 -3 0 10
OTC Shipped, Not Invoiced 3 -3 0 11 OTC Shipped, No Invoice or $0 Invoice 3 -4 -1 12 OTC Unusual Quantity or Value of
Inventory Adjustments 3 -5 -2 13 PTP Vendor is an Employee 2 -4 -2 14 23 Prioritizing Easy
Complex Low Benefit High Benefit 24 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 25
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 26 Case Study Why SEG Why Emory decided to Partner with SEG and use an opensource 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 27 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 28
Components of CCM At Emory Statistical and Analytical Routines Continuously Performed on Data Source Systems PeopleSoft HR, Payroll, Payables, Procurement Kronos LDAP VB Script ETL SQL Replicated Data For Analysis Email Alerts generated embedded link to a report
Email Alerts Exceptions Identified Exception Management Interface ASP.net Web form Reporting Tool IIS Web Based Reports & Dashboards 29 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. 30
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. 31 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: 1. 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. 2. Obtaining a server certificate from a certification authority. 3. Installing the newly issued server certificate into IIS, Binding it. 4. Enabling SSL encryption. 32 IIS Reporting 33 IIS Reporting 34
Alternate View Clicking the above link presents an Alternate view of all data related to that specific exception. 35 Alternate View (continued) Scrolling down to bottom, the user can click the link to enter Edit Mode (shown on next screen shot). 36 Edit Mode 37 Edit Mode After updating the information, user clicks the Update link 38 Update Success 39 Management Reporting
40 Case Study - Results Duplicate Payments: April - August 140,000 120,000 100,000 80,000 60,000 40,000 20,000 Number of Exceptions - All Tests April May June July August 25 20 15
10 5 0 April May June July August 41 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 42 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. 43 For More Information Wylie Roberts, CPA SolomonEdwards
Sr. Manager, Business Advisory Services [email protected] Cell: 404-218-6892 Scott Stevenson, CIA, CPA Emory University Associate Chief Audit Officer Office: 404-686-2916 [email protected] SolomonEdwardsGroup, LLC Atlanta Office Five Concourse Parkway, Suite 1450 Atlanta, Georgia 30328
A harder problem: plotting the solution. This is a plot of how the weight measured by a scale would change with respect to . mg. Notice that the maximum difference is at the equator, as expected, and it is a...
Enthalpy Change of formation is the enthalpy change when one mole of a compound is formed from its constituent elements under standard conditions.. Enthalpy Change of reaction . is the enthalpy change that accompanies a chemical reaction in molar quantities...
Certain MCC codes are excluded and will need HQMC approval - you will not know your card is declined until charge is ran thru the vendor. You will call Citibank and obtain the MCC code and then contact your AAPC....
Document presentation format: On-screen Show Company: Happen' Group Other titles: Arial MS Pゴシック Wingdings Blends Argument in the AP English Language Classroom Argument vs. Persuasion Types of Argument Toulmin Model - terminology The Toulmin Sentence Writing the Toulmin Sentence Toulmin...
Technology in the Church. We used technology to save money in the following areas: ... Rhapsody, or Pandora. Multimedia. Video. Use camcorder instead of expensive camera. Use DVR to archive services instead of DVDs. Live Video streaming with Ustream.tv (free)...
Health & Safety in the Food Technology Room This PowerPoint is a theory resource, it will help you identify the answers for all your health & safety evidence in your project. Lesson objective - to collate information that informs you...
Middle Kingdom Middle Kingdom F.I. P. F.I.P. Old Kingdom Old Kingdom or? ... Times Arial Impact Times New Roman Blank Presentation Microsoft Equation The Age of Things: Sticks Stones and the Universe PowerPoint Presentation PowerPoint Presentation PowerPoint Presentation PowerPoint Presentation...
The decision to purchase a package from a vendor would come after the requirements have been completed. Therefore, choices B and C are incorrect. Choice A is incorrect because a project timetable normally would not be found in a requirements...
Ready to download the document? Go ahead and hit continue!