190 likes | 282 Views
ACL Scripts. Using ACL Scripts Georgia Department of Audits Education Audit Division Kristina A. Turner, CPA, CISA Deputy Director. Overview. Georgia Department of Audits Education Audit Division (EAD) Our Responsibility Our Organization ACL In Action Planning Analytical Procedures
E N D
ACL Scripts Using ACL Scripts Georgia Department of Audits Education Audit Division Kristina A. Turner, CPA, CISA Deputy Director
Overview • Georgia Department of AuditsEducation Audit Division (EAD) • Our Responsibility • Our Organization • ACL In Action • Planning Analytical Procedures • Review of Financial Statement Mappings • Fraud Toolkit • Other Uses • Common Scripting Tools & Commands • Functions
EAD – Our Responsibility • Performance of Financial Engagements for: • State CAFR • State Colleges (35 and Cognizant Agency) • State Technical Colleges (25 and Cognizant Agency) • Includes Audits & Other Types of Engagements • Local Boards of Education (155) – Financial Audits • Charter Schools (2) – Financial Audits
EAD – Our Organization Headquarters – Atlanta 8 Regional Offices 106 Allotted Positions; 8 frozen positions 89 Auditors in the Field 9 Responsible for Planning, Training, Report Processing
EAD – ACL in Action • 2 licenses in each regional office • Each office has an “IT Liaison” responsible for day to day usage questions related to ACL and other applications used by EAD.(1 license assigned to the liaison) • Other license is used by other auditors • Network version is available
EAD – ACL in Action • Licenses for Planning Team members • Scripts are created and maintained at this level based on needs identified by Deputy Directors and Audit Supervisors • Currently knowledge of scripting resides with two individuals
EAD – ACL in Action • Became a significant tool to the audit process in 2001 • Initial use: • Converting .txt and print files to Excel • Retrieving Statistical Information for a dataset • Stratifying datasets for target testing • Pulling Samples
EAD – ACL in Action • Evolution • EAD collected Financial data from local Board of Education • Software System at the Board determine how files were received • PC Genesis – GA Dept. of Education developed application for districts – 8 files • Other Vendors – Using a standard format developed by EAD – 2 files
EAD – ACL In Action • Goals for Scripting • Creation of Planning Information for audit • Trial Balances • Materiality • Individually Significant Items • Preliminary Analytical Review • Prior Year to Current Year Comparison By Account • Exceptions Identified based on Pre-Defined Thresholds • Comparison of Summary Data to Detail Data • Detail Transaction History • Identification of Anomalies/Target Testing Transactions
EAD – ACL In Action • Auditors linked in the files received from the Local Boards of Education • After prompting ACL to process the scripts, the auditors had 20+ output files in less than 5 minutes. • Method to Scripting • Used arithmetic, functions, joins, summarization, etc. to create a standard ACL table for analysis • With standard tables, scripts were developed to produce output files • Separate Scripts based on Function (21)
Common Scripting Tools & Commands • Assign Variables • ASSIGN variable_name = value <IF test> • Define New Fields • DEFINE FIELD field_name COMPUTED expression < IF test > • Sort • SORT ON {key_field <D>} <...n> TO tablename <IF test> • Summarize • SUMMARIZE <ON> key_field<...n> <SUBTOTAL field <...n>> <OTHER field <...n>><TO SCREEN|table_name|PRINT> <IF test>
Common Scripting Tools & Commands • Extract information into Another Table for Analysis • EXTRACT {RECORD | FIELDS field_name <...n> | ALL} TO filename <IF test> • Export to External Software like Excel • EXPORT <FIELDS> field_name <AS export_name> <...n> export_type TO filename <IF test> • Join Tables based on Like Key to use records from both tables • JOIN <PRIMARY|BOTH|SECONDARY|PRIMARY SECONDARY|UNMATCHED|MANY>PKEY primary_key_fields FIELDS primary_fields SKEY secondary_key_field<WITH secondary_fields> <IF test> <WHILE test> <{FIRST|NEXT} range>TO table_name • Save Log to File • SAVE LOG <SESSION> AS file_name ASCII|HTML <OK>
Functions • ALLTRIM(string) • BETWEEN(value, min, max) • CLEAN (string, extra invalid characters) • SUBSTR(string, start, length) • VALUE(string, decimals) • ROUND(number)
EAD – ACL In Action • Dynamic Scripting • Files from Ga. Dept. of Education for all districts • Needed method for identifying Individual Entity information within the Script • Two methods: • If data is included in the file, in the script, set a variable equal to the entity • If the data is not included, prompt the user for the information.
EAD - ACL In Action • User Entry • DIALOG (DIALOG TITLE "User Dialog" WIDTH 335 HEIGHT 172 ) (BUTTONSET TITLE "&OK;&Cancel" AT 240 24 DEFAULT 1 ) (RADIOBUTTON TITL • Do Command • IF CHECKBOX1 = T DO A_Create_Trial_Balance • IF CHECKBOX2 = T DO B_Create_SNA • IF CHECKBOX3 = T DO C_Create_SRECNA • IF CHECKBOX4 = T DO D_Create_Budget • IF CHECKBOX4 = T DO E_College_Budget_to_Actuals
Financial Statement Mapping Test • Technical Colleges – Excel Macro Mapping Process • College – PeopleSoft Nvision • School Districts – GDOE developed Web application • Using Document Crosswalks from the Books to the Financial Statements, ACL creates a set of preliminary Financial Statements • This is compared to Entity’s Financial Statements
Fraud Toolkit • We use the Fraud Toolkit issued by Ekaros Analytics. • We developed a P-Card Fraud Toolkit.
EAD – ACL In Action • Other Uses • Testing Allocation Process • Tuition & Fees in Banner • Grant/Expense Allocations in PeopleSoft • Grant Confirmations from PeopleSoft Data