Continuous Assurance Best Practices: Laying the Roadmap to Success

1. Continuous Assurance Best Practices:  Laying the Roadmap to Success

2. My Background • BA’s in History and Religion • Studied for an Master’s Degree in Church History • Got a job twisting balloons while in grad school. • Worked for Babies R Us as an Assistant Manager. • Worked Customer Service while working on an MBA. • Took Intro to Pascal in college to fulfill a distribution requirement!

3. Why is that relevant? Because I am not a techie.

4. How I ended up here? Prior to learning ACL: • I was answering phones • And twisting balloons If you can take some clown off the street and teach them ACL, then you can teach anybody ACL.

5. Recognized ACL Expert • 2007 – ACL’s Seven Habits of Successful Auditor’s Campaign • Founder and first president of TexasACL User Group • Second person ever recognized as “ACL Super User” • 2008 – Spoke at ACL’s Annual Convention • 2008 – Participated on ACL Round Table • 2009 – Keynote speaker at ACL Annual Conference • 2009 – Asked by David Coderre to endorse book • 2011 – Spoke at ACL Business Assurance Seminar • 2012 – Presented Webinar for ACL

6. Continuous Assurance Program • 6 Major Projects • Projects range from 40-100+ scripts • New projects are built upon a common “skeleton”. • New projects re-use same scripts! • Projects may perform over 100 different tests • Continuous Assurance results are sent to one of several standardized report formats • Some projects produce over 10K worth of ACL Logging per day!

7. Outline • Commands • Comments • End of Scripts • Field Names • In Scripts • Naming Conventions • Opening Sections • Project Names • Reports • Script Library • Script Names • Start of Scripts • Table Names • Variable names • Naming Conventions • Project Names • Script Names • Table Names • Variable Names • Field Names • Comments • Start of Script • In Scripts • End of Scripts • Opening Sections • Comments • Commands • Reports

8. Project Names

9. Alpha Numeric Concept All projects begin with two or three letters followed by two numbers. The letters organize the scripts by type/purpose/objective/process. The numbers organize the script within the process.

10. Project Names We use alpha-numeric combinations: 2-3 letters to describe the nature of the project AP --- Accounts Payable AR --- Account Receivable GL --- General Ledger GLT --- General Ledger Transactions ACS --- Account Code Structure 2 numbers o1 -99

11. V_type The alpha-numeric construct used to identify projects can be used throughout the project to perform various tasks.

12. Script Names

13. Script Naming Conventions Again a four digit alpha-numeric construct can be used to define and organize script names.

14. Advance naming concept Use the Alpha characters to represent the same processes in every project. For example: AX## = Start up scripts BX## = repeating scripts within the project CX## = Communal scripts DX## = Data Load Scripts EX## -WX## = Processing/Analytic Scripts YX## = Administrative Functions ZX## = Shut down/Clean Up Scripts

15. 2nd and 3rd Parts With both scripts and projects, I will have a second part and possibly 3rd part that describes what the script does. The second part is usually what the script/project does and the third part may describe or qualify the second. But when referring to the project or script, I always do so via the alpha-numeric constructs.

16. Example: AA01Naming Conventions AA02 Script Names AA03 Table Names AA04 Data Tables AA05 Results Tables AA06 Working Tables AA07 Temp Tables AA08 Variable Names AA09 Field Names AA10Project Names BA01Comments BA02 Start of Script BA03 In Scripts BA04 End of Scripts CA01 Opening Sections CA02 Comments CA03 Commands DA01 Other Issues to Consider • Naming Conventions • Script Names • Table Names • Data Tables • Results Tables • Working Tables • Temp Tables • Variable Names • Field Names • Project Names • Comments • Start of Script • In Scripts • End of Scripts • Opening Sections • Comments • Commands • Other Issues to Consider

17. Tables

18. Table Naming Conventions There are 4 types of tables: 1) Original Data 2) Computed data/working tables 3) Temporary files 4) Final Product(s)/Reports‏

19. Table Naming Conventions Original data files---Naming should include 3 parts: • A prefix to identify the file as original data. • The original table name without any non-alpha-numeric characters. • The date of the data---consider YYMMDD v MMDDYY AAA_Employeefile_111013 AP_VendorFile_111013 STD_memberfile_111013 MSTR_Hrfile_121212 {corporate initials}_Employeefile_111013

20. Table name – temp files Temporary files are labeled TEMP001-TEMPXXX. This allows for • Easy trouble shooting • Helps to identify where issues exist • Helps to determine if expected results flowed through temp files • Clean up of project via a standardized clean up script

21. REPORT tables Option 1 Reports begin with the word “REPORT” followed by the alpha numeric script identifier that generated the report and a short description. So one might have a REPORT_DA01_DuplicateAddress.

22. REPORT tables Option 2 Reports begin with the word “REPORT” followed by the alpha numeric project identifier that generated the report and a short description with dates. So one might have a REPORT_ACS01_091812_092012.

23. Working Tables Working tables are generally one of two types of tables: • Data derived from basic data tables. • Data contained within the project, but not derived from current data tables. These tables should be descriptive and contained in a special folder.

24. Variables

25. Standardized Variable Names Two or three part name in creating variables. Part 1 – the v_ that identifies the value as a variable. Part 2 – the type of variable being defined Part 3 – a short description/qualifier for the variable (optional)

26. Variable Naming Conventions User defined values should: • Not be unique and not potentially used by the client. Poor values would include “Amount”, “Company”, “Name”, etc. • They should be descriptive enough to remember and to understand. Variable “A_1” would not be a good option. • Ideally they should mirror common or anticipated names.

27. Sample Variables • V_Path • V_path_bat • V_path_data • V_path_results • V_date • V_date_run • V_date_DOS • V_date1 • V_table • V_table_AP • V_com • V_com_test1 • V_cnt/v_cntr/V_count • V_cnt_test/v_cntr_test • V_table • v_table_list • V_define_tablename • V_Drive • V_type

28. Field Names

29. Fields Names Field names generated by ACL should always be readily identified as such. • Amount vsamt? • Dept vs Department? Use a prefix “F_”ield or something comparable to identify a field name.

30. COMMENTS

31. COMMENT Every Line Best practice is to put the COM at the start of every record which should be commented out.

32. Start of Script Comment Comments that go at the start of a script should be highlighted with two rows of COM followed by asterisks. E.g.: COM****************** COM****************** COM Written by Porter Broyles Date: 10/13/11 COM Description: This makes it easier to find the start of COM subscripts in the log. COM****************** COM******************

33. Opening Comments Comments should be used at the start of every script to: • Identify who wrote the script • When the script was written • When the script was last modified (by whom) • What the intent of the script is • Any special considerations

34. Middle of Script Comment When writing comments they should stand out. To help them stand out add a line before and after the comment itself. This should include the word COM with a series of asterisks after it: COM********************* COM details of the comment COM*********************

35. End of Script Comments When using scripts and subscripts, it can often be a challenge to determine where one is when reviewing the project log. Whenever a subscript is called, the last command in the subscript should be a note indicating that the subscript is completed: DO AA01_subscript_A ~~~~ COM******************* COM Returning from AA01_Subscript_A COM*******************

36. SET LOG Comments Before changing logs, always indicate what the next log is going to be. After changing logs, always indicate what the previous log was. COM************* COM Next Log [new logname] COM************* SET LOG [new logname] COM************* COM Old Log [old logname] COM*************

37. Syntax: Should be familiar • CAPITALIZE ACL commands and functions() • Lowercase for field names • DELETE FIELDS before DEFINing them • DELETE tables both .fil and format before creating • Don’t abbreviate functions or commands.

38. International Operations Functions use the thousandths separator to separate parts. In the US this is the COMMA: SUBSTRING(fieldname,start,length) In most countries, this the is period: SUBSTRING(fieldname.start.length) Generic option, use a blank space: SUBSTRING(fieldname start length

39. Other things to consider • Development v Production • Change Control • Version Archiving • Crash Log

40. Flow Charting Diagramming a CAMS via flow charting is an excellent way to understand how a script operates and which scripts/routines are called from different locations. You can indicate what scripts are shared scripts, scripts called via a DO-WHILE sequence, scripts called via a DO IF sequence, etc.

42. Development vs Production All changes to scripts in production should be first implemented in a separate development area. The scripts in production should not be changed until and unless vetted in development first.

43. Change Control and Version Archiving All script changes should be approved by the manager/director over the project area. When changes are made, do not simply overwrite the old scripts, but copy them to an archive. That way if questions arise as to logic at a previous date, the exact scripts can be revisited.

44. Crash Log The crash log is one of my favorite best practices. It does several things: • Serves as documentation should a external reviewer ask about a process on a date where the script crashed or had to be reperformed. • Serves as a source to identify issues with the system/IT. • Serve as a means to evaluate one’s own script. • Serve as a means to identify bugs (both with the software and with your scripts.)

45. Crash Log

46. Questions Porter Broyles porter@someclownconsulting.com www.someclownconsulting.com www.texasacl.com