1 / 19

ACL

ACL. AUDIT COMMAND LANGUAGE What Is it? Why Do We use it? Tips on Using it. Ellen Fleming, Sr. Examiner, MS Accounting Accounting Educators Conference. Outline Of Presentation. Brief overview of my experience with ACL Overview of ACL (What ACL Can Do for You) Some Tests Explained

cicely
Download Presentation

ACL

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 AUDIT COMMAND LANGUAGE What Is it? Why Do We use it? Tips on Using it. Ellen Fleming, Sr. Examiner, MS Accounting Accounting Educators Conference

  2. Outline Of Presentation • Brief overview of my experience with ACL • Overview of ACL (What ACL Can Do for You) • Some Tests Explained • Using Basic Filters/Defining Data • Importing Data/Tips • Joining/Relating Tables • Questions/Comments

  3. Overview- ACL • Software (Current Version 9) • Helps Improve efficiency (much faster with Larger records that other Software) • Detects areas of “interest” for further review (does not replace hard evidence) • Aids to focus the Audit • Brings Data in as Read only (can not change original Data)

  4. Overview- ACL • Keeps a log of everything done in ACL • Capable of Analyzing Whole population (not just a sample) • Imports Unlimited # of Records (~65K limit in Excel) • Exports easily to other software (Excel) • Easily Filters Data on User specified criteria (which can be then extracted to smaller more manageable files for others to use)

  5. What can ACL Do? • Easily Summarize/Classify information (Identifying inefficiencies-Numerous pmts to Post Master @ $5.00) • Search for specific relevant data (BOE and Emp as Vend) • Matching on different files for address, Name, Phone numbers, etc. • Searching within one file for “ ” • Find and Quantify Payments on DOW or dates • Weekend Payments • When were warrants issued (1st and 15th) • Find duplicate transactions (Inv #s, Names, Vend IDs, Checks, etc)

  6. What can ACL Do? • Gaps in sequence (Test of completeness on Character, Numeric, or Date) • Find sequences (Vendor received payments for 10 invoices all in Numeric Order) • Quickly find missing data (Blanks) • Predictable Patterns (Benford’s Law) • Aging (default 30,60,90,120 or define your own) • Statistics and Sampling • Graphs (Histogram) • Sorts by unlimited fields • Finds one word throughout file whether in Inv #, Name, Description, etc. (Ex: Hotel, Catering, Lunch, etc)

  7. What can ACL Do? • Compare/Combine numerous data elements among different Software Extracts • Combining Payroll File with Employee Master files for Addresses, Hire Dates, etc. • You can create a common view of data from diff files and analyze it as if it existed in one file (two years with diff software)

  8. Can ACL Be Limited?! • Limited by User’s Imagination!!! • Not knowing your Output • Not knowing the Capabilities • Limited to Data received

  9. Benford’s Law Explained Data analysis method that identifies possible errors, potential fraud or other irregularities. Benford’s Law states that numbers and number sequences in a dataset (defined in our case as the total # of records in CD file 2006/2007) follow a predictable pattern). “Actual Count” is the number of checks beginning with the first 2 digits (for example, there were 50 invoices starting with “50” whether they are $50.00, $500.23 or $50,456.43). Expected count is the number of checks that Benford expects to appear (there should be 20 checks beginning with “50”). Some Tests Explained

  10. Benford’s Law Graph

  11. Some Tests Explained BOE and Emp Name Matches • ACL Looks for First and Last Name (find “JOE” and “SMITH” in within Vendor Name) Employee “Joe Smith” could return in the following Vendor Names Matches against the CD File: JoE Smith; JOESmITH; Joe Smith Construction; Smith/Joe; smith/ joe-Petty Cash; Joe Michael Smith LLP; Joe W. Smith Attorneys at Law; Joe.Smith-Conference; Joe-Smith;

  12. Some Tests Explained Possible Duplicate Invoices ? • Test 1: ACL Looks for exact duplicates on Vend ID, Invoice # and Amount • Blank Invoices • Generic Descriptions (Dates, “Reim”, Medicare”, “clothing allowances”) • Test 2: ACL Looks for exact duplicates on CK Date, Invoice # and Amount • Cases where Invoice was paid twice on same day to possibly 2 different Vendors. • Or where Invoice was paid twice on same day to same Vendor Name different ID Analyze-Look for Duplicates- ON: Vend ID, Amt, INV#

  13. Some Test Explained Employee Vendor Address Matches • If one file is consistent with “ROAD” and one is consistent with “RD.” we will replace that word in one file so that we get better matches. • “110 State Road, Albany, NY 12236” would match to “110 STATE RD ALBANY NY, 12236-0001” if we did something similar to a “find replace” in Excel within our ACL file. Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Address, "ROAD", "RD") "AVENUE", "AVE") "DRIVE", "DR") "STREET","ST"), "LANE","LN") , "CIRCLE", "CR"),"BOULEVARD", "BLVD")"COURT", "CT")

  14. Some Test Explained Employee Vendor Address Matches • We remove (Exclude) all spacing, lower case letters, commas, periods, and Zip Codes (as some come in as 5 and some are 9) • “110 State Street, Albany, NY 12236” would match to “110STATE STREET ALBANY NY 12236-0001” SUBSTR(ALLTRIM(UPPER( EXCLUDE( Address+City+State, ",./!@#$%^&*() <>?:;'[]\{}|`~-_=+"))),1,100)

  15. Some Tests Explained Same Vendor ID different Name or Address • If a Vendor Name was swapped in the system or Address was swapped. (testing controls or finding possible fraudulent transactions) Examples: VIDVend NameVIDVend NamVend Addr 101 Smith, LLP. 456 ABC Inc. PO Box 100 101 Apple, Inc. 456 ABC Inc. 100 Maiden Ln.

  16. Using Basic Filters/Defining Data • 1st you need to know what Field type to use for each function. • Make sure your Fields are assigned appropriately at the beginning. (Edit-Table Layout) • You can always convert one to another (Ex: Making a Number a Character) • Do you want part of an already existing field in a new field? (adding a new data field to the table layout) Ex: Date and Time are in one field and you want 2 separate fields. • You need to know what kind of field do you want to “compute” • A permanent field (expression) • A “temporary” view field (quick filter)

  17. Importing Data/ Tips If Importing more than 1 data set with same format to be combined in ACL (2 diff yrs) • If Excel Files: • Import using ODBC (make sure Numbers are formatted as numbers and Characters are formatted as characters (INV#) –works 99.9% of the time • If Text Files: • Type the Headers using the same format that’s in the Text file (If comma delimited: “Vend ID”, “Ck Num”, ) • If Text Flat File and is the same for both years- Make a copy and “link to a new source”

  18. Joining\Relating Tables • BOTH need a common field (Key field) • Relating • Virtual- adding only to view • Can Extract fields at any time • Joining • You will create a 3rd Table after joining. • Need to decided if you want those that “match” or “don’t Match” on the Key field.

  19. Questions? Do you Love ACL ?

More Related