San Diego Chapter of ACFE

1. San Diego Chapter of ACFE Who left the CAATs out – Alternative Uses of Data Analytics Tools Tim Smith, CPA CISA, CISSP March 28, 2013

2. The Corporate Caveats The concepts presented are my own and do not represent LPL Financial or LPL Financial Internal Audit.

3. What we are going to cover • CAATs revisited • How can they be used in new ways • Why auditors need to learn to use them • What tools exist • CAATs Close-up • Looking at security with CAATs • Some IDEA functions for new tricks • Some IDEA / CAATs success stories

4. A few things to use CAATs for Validating data entry dates / times / users to identify postings or data entry times that are inappropriate or suspicious. Classification to find patterns and associations among groups of data elements. Gap testing to identify missing numbers in sequential data. Joining different data sources to identify inappropriately matching values such as names, addresses, and account numbers in disparate systems.

5. Before you ask IT – ask yourself • What are you trying to test? • Controls • Metadata • Reports • Data • Transaction Details • Reports • Data • Reports • Off-the-shelf • Custom / ad-hoc

6. Metadata in an accounting system • Non-financial fields discussing the • Who • What • When • How About the fields in the records comprising financial information Together, these data can provide a diagnostic view of the accounting system

7. What might we need to look at • Retroactively • Transaction data – especially between systems • Transaction metadata • Module or journal entries • Logs • Prospectively • System access • Program change management

8. Working with system access information • Larger software vendors are targeting the small to medium enterprise space – SAP, Oracle, Microsoft. As a result, many businesses have access listings containing thousands of lines • System access information can be complex – very granular, with difficult formats • Data may cover multiple menu layers and multiple modules within an application • Therefore, it is vital to gain a understanding of basic access information structure and what you want to test before starting

9. A few systems with complex security reports Oracle Financials SAP (SmartExporter) Microsoft Dynamics – Great Plains Sage MAS 500 ADP Enterprise HR (EV5)-- Formerly PeopleSoft HRMS

10. MS Great Plains v10 security model – four levels Security Operations refers to access to all windows, tables, reports and miscellaneous permissions A Security Task is a set of Security Operations required to perform a specific task A Security Role combines multiple Security Tasks required to perform a specific role Each User and Company combination can have multiple Security Roles assigned to it

11. Complex Access From a higher level – viewed From the role

12. Unexpected functions within the roles

13. What are the tools? • Excel – row limitation (was 65K lines, now 1m or so); data easily changeable • Access – data also easily changed; might also hit a size limitation (1GB for pre 2003; 2-3 GB now) • SQL Server – again, data changeability; probable need for programming knowledge (SQL) • Specific CAATs software packages • ACL – Audit Command Language • IDEA – Interactive Data Extraction and Analysis

14. Key functionalities of IDEA Profiling the data Extractions Gaps and Duplicates Adding a new field Smart Analyzer (an Add-on module) Joining Databases

15. CAATs success stories 1 GAO report 02-406 Significant internal control weaknesses in Education’s payment processes and poor physical control over its computer assets made the department vulnerable to and in some cases resulted in fraud, improper payments, and lost assets.

16. CAATs success stories 2 Assisted a Federal agency evaluate problems with its accounting system, taking it from a disclaimer in year 1 to a qualified balance sheet in year 2 to a clean opinion in year 3.

17. MS GreatPlains

18. IIA 10/10/2012

19. RACF security – User Attributes

20. iSeries – Display Object

21. Report Reader Can be used with formatted text files Can be used with non-picture PDF files Create a template that can be used for future files of similar construction Crucial for work with non-columnar reports or reports with header / trailer information to be ignored

22. Smart Analyzer – built in tests • Tests Looking at the Metadata • Journal Entries Posted on Weekends • Journal Entries Posted on Specific Dates and Times • Journal Entries by User • Journal Entries with Specific Comments

23. Joining databases - concepts Primary Secondary No matches in Secondary Note that ‘1005 Berlin’ also will be included and no empty columns from secondary database will be included • Lagos • Cairo • New York • Paris • Berlin • Sydney • Toronto • Durban • London 1004 France 1004 China 1006 Australia 1007 Canada 1008 South Africa 1009 UK 1010 Brazil 1011 Austria 1012 Peru All records from Primarynote that ‘1004 China’ will not be included Matches Only note that ‘1005 Berlin’ and ‘1004 China’ will be excluded No matches in Primary Note that ‘1004 China’ will NOT be included andempty record from primary will be add to these 2 columns All records from secondary is not included -> select secondary file as primary file All records in both files

24. Joining databases - results

25. CAATs Success Stories 3 Determined the extent of data changed by an A/R manager modified data to awards for efficient A/R management Discovered numerous instances of cash awards where the same person proposed, approved, and received.

26. MAYHEM…..and CAATs The authors describe manipulating a major financial accounting systems used by corporations large and small (Great Plains) to show the importance of good information security and accounting controls. They identify information security and accounting controls needed to detect these types of attacks. http://www.securestate.com/Research%20and%20Innovation/Pages/Tools.aspx In this time of reduced resources….don’t leave the CAATs out.

27. Questions or Comments? 27

28. Contact Information Tim Smith Tim.cpa4IT.@yahoo.com 619-929-1221