1 / 43

Continuous Assurance Development

Continuous Assurance Development. With ACL Porter Broyles Continuous Controls Analyst II Harris County Texas. Goals and Objectives. To demonstrate versatile power of ACL. To provide HINTS as to how to tap this power . To provide some best practices.

noe
Download Presentation

Continuous Assurance Development

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. Continuous AssuranceDevelopment With ACL Porter Broyles Continuous Controls Analyst II Harris County Texas

  2. Goals and Objectives • To demonstrate versatile power of ACL. • To provide HINTS as to how to tap this power. • To provide some best practices. • To cover a simple model of project development. • To present some advanced concepts of "next steps."

  3. Harris County Auditor’s Office Projects Auditor’s office projects may have: 90+ individual scripts, 300+ pages of scripting, 100’s of individual tests, Perform millions of comparisons. How do we do it?

  4. Standards and Procedures Want to write projects that • Are easily expandable. • Are easily editable • Others can review and understand • Are modular in design • Do not pose a risk to currently approved projects • That minimize the number of reports generated

  5. With that being said, Let’s have a demonstration Pet Peeve About Presentations

  6. Modular Scripting Modular script writing is the use of standardized scripts to perform repeated processes over and over the exact same way without having to reinvent the wheel every time we write a new script.

  7. Repeated processes Modules can be made out of: • Load Processes • Processes asking for user input • Standardized tests • Repeatable tests • Standardizing reports • Normalization of data • Clean up of the project

  8. Example: The Load Process Key to getting Modules to work • Tables need to be loaded same way every time. • Same field names. • Variables need to be defined consistently. • Same data characteristics. • Field size/type etc • Still an issue even with 9.2+. • Methodology needs to be consistent.

  9. Loading isn’t just loading It’s testing to ensure that the data is as expected: • Did new data exist to be loaded? • Was the data Verified? • Did duplicate data exist in key fields? • How many records were loaded? • If the data was delimited, were there records with too many/few delimiters? • How do these results compare to the last time the data was loaded? In other words: how can we ensure that the data was loaded correctly when we might not be there to view it?

  10. Ok Maybe a little It’s not that scary

  11. How to achieve this • Naming Conventions • Script Names • Variables • Bat Files • Limited Intelligent Scripts • User Inquiry • Dual Purpose Scripting

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

  13. Script Names • AX## = Start up scripts • BX## = repeating scripts within the project • CX## = Data Load Scripts • DX## = ChAD Scripts • EX## -WX## = Processing/Analytic Scripts • Usually 1 or 2 alpha prefixes per project • YX## = Administrative Functions • ZX## = Shut down/Clean Up Scripts

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

  15. Alpha-numerics = outline By using an alpha-numeric combination, we create an outline of the project. AA01 should occur before AA02 AA02 should occur before AA03 AA## should occur before BA## And so forth.

  16. Standardized Variables Variables usually have 3 components. 1) v_ as the prefix to define it as a variable. 2) a description of the variable 3) a qualifier of the description V_TYPE = the alpha-numeric for the project v_Path_{qualifier} = path on hard drive V_date_{qualifier} = a date value of some sort V_COUNT_{qualifier} = a count of something V_flag_{qualifier} = a flag for an error trap/do while sequence

  17. Interesting dilemma You are writing a sub process in a project. There are 5 scripts in the sub process. The process is intended to be a generic process usable with multiple projects. The catch is that the fourth script in the project has to be a little different based upon the criteria of the individual project.

  18. Traditional approach: GL01 ACS01 AP01 XX01 XX01 XX01 copy copy XX02 XX02 XX02 copy copy XX03 XX03 XX03 copy copy XX04 XX04 XX04 XX05 XX05 XX05 copy copy

  19. In other words: What You don’t think it’s cool? This is so Cool XX01 XX01 XX01 XX02 XX02 XX02 XX03 XX03 XX03 XX04 XX04 XX04 XX04_ACS01 XX04_GL01 XX04_AP01 XX04_%v_type% XX05 XX05 XX05

  20. Similar example: Same concept can be used elsewhere: 48 tables use same process, but 2 tables require specialized processing:IF NOT MATCH("%v_table%", "HR_table, AP_table") DO AB01_dataload IF MATCH("%v_table%", "HR_table, AP_table") DO AB01_dataload_%v_table%

  21. Bat File Basics We write all of our scripts and save them as .bat files outside of ACL. The scripts will be saved in specific folders. Some folders are generic for all shared scripts, other folders will be project specific. These paths are then defined in the project start up scripts.

  22. Bat Paths ACS01 Account Code Structure V_type = "ASC01" V_path_gen = "X:\CONAUD\Shared_scripts\" V_path_spec ="X:\CONAUD\ACS01\" GLT01 General Ledger Testing V_type = "GLT01" V_path_gen = "X:\CONAUD\Shared_scripts\" V_path_spec="X:\CONAUD\GLT01\" AP01 Accounts Payable V_type = "AP01" V_path_gen = "X:\CONAUD\Shared_scripts\" V_path_spec="X:\CONAUD\AP01\"

  23. Notice the standardization: ACS01 Account Code Structure V_type = "ASC01" V_path_spec ="X:\CONAUD\ACS01\" GT01 General Ledger Transaction Testing V_type = "GT01" V_path_spec ="X:\CONAUD\GT01\" SA01 Security Analysis V_type = "SA01" V_path_spec ="X:\CONAUD\SA01\" OR V_path_spec ="X:\CONAUD\%v_type%\"

  24. Using a bat file DO "%v_path_spec%XX04_%v_type%.bat" DELETE SCRIPT XX04_%v_type% OK

  25. Calling scripts If calling a generic script:DO SCRIPT "%v_path_gen%XX01_Loadstart.bat" If calling a project specific script: DO SCRIPT "%v_path_spec%XX02_tablelist_%v_type%.bat"

  26. Limited Intelligence (LI) Limited Intelligent Scripts are scripts that use known information to infer what the user would want: • Query the hard drive to determine what the most recent date we have for data. • To identify the most recent two dates we have loaded in ACL to use those dates in an analysis. • To analyze data and rank potential risks (vendors/employees with most transaction, highest dollar amount, most errors, etc.) • To identify tables based upon pre-selected criteria. • To calculate dates/ranges/amounts based upon data.

  27. Limited Intelligence example: Suppose the analysis is looking to start on the previous Sunday and end on the previous Saturday. Rather than hardcoding those dates or asking the user to enter them, we can have ACL calculate the dates in question:v_date_start =CTOD(DATE(),"MM/DD/YYYY")-DOW(CTOD(DATE(),"MM/DD/YYYY")) -6 V_date_end = CTOD(DATE(),"MM/DD/YYYY")-DOW(CTOD(DATE(),"MM/DD/YYYY"))

  28. Interactive – LI Scripting Interactive-Limited Intelligence scripting is the use of asking for user input while providing a default option based upon predetermined assumptions. If a script is coded to always use the variables determined by ACL, then the script is useless under anything but ideal situations.

  29. Interactive – LI Scripting example v_date_start =CTOD(DATE(),"MM/DD/YYYY")-DOW(CTOD(DATE(),"MM/DD/YYYY")) -6 V_date_end = CTOD(DATE(),"MM/DD/YYYY")-DOW(CTOD(DATE(),"MM/DD/YYYY")) V_amt = 5000 DIALOG (DIALOG TITLE "User Dialog" WIDTH 401 HEIGHT 233 ) (BUTTONSET TITLE "&OK;&Cancel" AT 300 24 DEFAULT 1 ) (TEXT TITLE "Enter Start Date" AT 24 64 WIDTH 87 ) (EDIT TO "v_date_start" AT 132 60 DEFAULT "%v_date_start%" ) (TEXT TITLE "Enter End Date" AT 24 100 ) (EDIT TO "v_date_end" AT 132 96 DEFAULT "%v_date_end%" ) (TEXT TITLE "Enter Amount" AT 24 136 ) (EDIT TO "v_amt" AT 132 132 DEFAULT "%v_amt%" )

  30. Variable Substitution/GUI Bug Warning, using percentage signs around a variable is known as Macro Substitution. It tells ACL to read the variable as the value stored in the variable and not the variable itself. If you use a GUI tool to edit a command that includes Macro Substitution when after the variable is defined, then ACL will not save the variable when you leave the GUI, but will hard code the value of the variable into the command! BEST PRACTICE: ALWAYS DELETE VARIABLES BEFORE GUI EDITING!

  31. Dual Purpose Dilemma Write one script that can be used both manually and in an automated mode!

  32. Two start-up scripts Two start off scripts: AA00_Start_manual V_conmon = F V_type = “AP01” DO AA01_start_%v_type% AA00_Start_Auto V_conmon = T V_type = “AP01” DO AA01_start_%v_type%

  33. V_conmon turns on/off dialog boxes v_date_start =CTOD(DATE(),"MM/DD/YYYY")-DOW(CTOD(DATE(),"MM/DD/YYYY")) -6 V_date_end = CTOD(DATE(),"MM/DD/YYYY")-DOW(CTOD(DATE(),"MM/DD/YYYY")) V_amt = {calculated average of the amount field + 1 standard deviation} IF v_CONMON = T DIALOG (DIALOG TITLE "User Dialog" WIDTH 401 HEIGHT 233 ) (BUTTONSET TITLE "&OK;&Cancel" AT 300 24 DEFAULT 1 ) (TEXT TITLE "Enter Start Date" AT 24 64 WIDTH 87 ) (EDIT TO "v_date_start" AT 132 60 DEFAULT "%v_date_start%" ) (TEXT TITLE "Enter End Date" AT 24 100 ) (EDIT TO "v_date_end" AT 132 96 DEFAULT "%v_date_end%" ) (TEXT TITLE "Enter Amount" AT 24 136 ) (EDIT TO "v_amt" AT 132 132 DEFAULT "%v_amt%" ) IF v_CONMON = F DIALOG (DIALOG TITLE "User Dialog" WIDTH 401 HEIGHT 233 ) (BUTTONSET TITLE "&OK;&Cancel" AT 300 24 DEFAULT 1 ) (TEXT TITLE "Enter Start Date" AT 24 64 WIDTH 87 ) (EDIT TO "v_date_start" AT 132 60 DEFAULT "%v_date_start%" ) (TEXT TITLE "Enter End Date" AT 24 100 ) (EDIT TO "v_date_end" AT 132 96 DEFAULT "%v_date_end%" ) (TEXT TITLE "Enter Amount" AT 24 136 ) (EDIT TO "v_amt" AT 132 132 DEFAULT "%v_amt%" )

  34. Shared Script Example:Exception reports Most people when they write a test, they generate a separate report for each test. In other words, if they perform 100 tests, they will have 100 different reports. This becomes cumbersome and impractical for larger Continuous Assurance programs. Who wants to review 100 different reports on a daily basis?

  35. Error Report Process We generate 1 report that captures all of the information from every test performed. Basically, the process is: • Create an exception report as normal • Identify key elements needed to identify record/exception. • ASSIGN those elements to variables. • Execute BE01_ERROR_Report • BE01_Error_report extracts values to standard report

  36. Example: Spending Limit V_test = "’Spending Limit’" V_key_fields = "’PO_Number + Invoice_Number’" V_key_data = ALL(PO_Number) + ALL(Invoice_number) (Assign other values to variables) DO "%V_PATH_GEN%BE01_error_report.BAT"

  37. BE01_error_report The Error Report script is simply a report EXTRACT FIELDS SUB(%v_key_fields%,1,30) as "Key Fields" SUB(%v_key_data%,1,30) as "Key Data" {Other fields as values} TO TEMP101 APPEND BEST PRACTICE: After calling the script redefine or delete the variables so that they are not mistakenly used with another test.

  38. Example: Approver terminated V_test = "’Approver Terminated’" V_key_fields= "’PO_Number+ Invoice_Number’" V_key_data = ALL(PO_Number) + ALL(Invoice_number) (Assign other values to variables) DO "%V_PATH_GEN%BE01_error_report.BAT"

  39. BE01_error_report The Error Report script is simply a report EXTRACT FIELDS SUB(%v_key_fields%,1,30) as "Key Fields" SUB(%v_key_data%,1,30) as "Key Data" {Other fields as values} TO TEMP101 APPEND BEST PRACTICE: After calling the script redefine or delete the variables so that they are not mistakenly used with another test.

  40. Exception Report result

  41. Naming of Reports Guess what… when extracting/exporting the reports, there is a piece of information that should be included in the title… Report_%v_type%_{description of report}_%v_date_run%

  42. Questions? Porter Broyles Porter@SomeClownConsulting.com www.texasacl.com

More Related