1 / 19

ACL Scripts

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

sarah-mcknight
Download Presentation

ACL Scripts

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. ACL Scripts Using ACL Scripts Georgia Department of Audits Education Audit Division Kristina A. Turner, CPA, CISA Deputy Director

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

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

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

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

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

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

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

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

  10. 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)

  11. EAD - Audit Toolkit

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

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

  14. Functions • ALLTRIM(string) • BETWEEN(value, min, max) • CLEAN (string, extra invalid characters) • SUBSTR(string, start, length) • VALUE(string, decimals) • ROUND(number)

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

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

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

  18. Fraud Toolkit • We use the Fraud Toolkit issued by Ekaros Analytics. • We developed a P-Card Fraud Toolkit.

  19. EAD – ACL In Action • Other Uses • Testing Allocation Process • Tuition & Fees in Banner • Grant/Expense Allocations in PeopleSoft • Grant Confirmations from PeopleSoft Data

More Related