Acl 5538l

Use of ACL in Audits & Investigations Lon S. Heuer, A, CIA Associate Vice President for Institutional Compliance and Director, Office of Internal Audits

Dyan G. Hudson, CISA Associate Director, Office of Internal Audits

Agenda Overview of Generalized Audit Software  How to Get Started  Getting Basic File Information  Looking for Anomalies  Detailed Transaction Analysis  Example – Procurement Card Case Study 

Overview What does it do? Allows auditors to extract and analyze data independent of programmers and auditees  Summarizes large amounts of data  100% testing of large populations 

– Increases probability of detecting errors and omissions – Increases probability of detecting fraud

Getting Started Know Your Data! 

Know where to get it – Platform (PC/mainframe/other) and format – Quantity and extract options – Knowledgeable staff



Know what it should look like – Important fields – Statistical expectations



Check key fields for validity – Numeric / alphanumeric – Blank / non-blank – Valid codes

Getting Started

Validity Checks Demo

Get Basic Information 

Generate summaries and statistics on key fields – Record count – Totals and key subtotals – Average, maximum, minimum values



Run “overview” reports (Classify, Summarize)

Get Basic Information

Summary statistics demo

Overview reports demo

Look for Anomalies  

Exception reports Statistical deviance and digital analysis – Benford’s Law – Rounding of amounts – Even dollar amounts Stuff to Read: www.utexas.edu//audit/files/ Using Audit Tools Case Studies Digital Analysis

Look for Anomalies

Look for Anomalies

Look for Anomalies Exception report demo

Benford’s analysis demo

Detailed Transaction Analysis Extract “suspect” records for review  Select statistically valid sample for review and extract 

Detailed Transaction Analysis Extract Demo

ACL Exercises Go to

www.utexas.edu//audit/files/ all files. Open CARDUSE.ACL using ACL Workbook and follow instructions in EXERCISES.DOC.

Example Procurement Card Fraud Background  Fiscal Year 2000 Audit Plan – Spot Check  Procurement Card Program Fiscal Year 99  Follow up to 1997 audit  Statistics – Over 300 departments and 1,680 cards – 281,000 transactions (through 5/31/2002) (78,463 in FY01, 63,559 in FY02 through 5/31)

– $41.3M ($12.0M in FY01, $9.3M in FY02 through 5/31)

Obtaining Data for Analysis Data from Bank of America Card Data Transaction Data

Merged Transaction Data File in ACL

Merchant Classes

Data from UT ing System ing/Payment Data

Data Analysis Using ACL Summaries & Statistics    

High volume cardholders High dollar cardholders High volume merchants High dollar merchants

Exceptions & Anomalies  Policy violations  Other unusual transactions

Policy Violations Type of Purchase Created reports based on merchant class code to identify unusual types of purchases  International items Gas  Gifts Equipment Rental  Postal Service  Internet Transportation

 Clothing stores  Flowers

 Pets  Bicycle shop

Charities Schools Colleges Travel-related  Utilities (including telephone services)  Grocery stores  Antiques

Other Policy Violations and Unusual Transactions Transactions over $999.00 limit  Split Orders – multiple transactions to single 

vendor on single day with total amount over $999.00

Even dollar amounts  Sales tax paid to Texas merchants 

Match Suspect Transactions to ing Records Complicated postings and transfers between s  Matched dollar amounts and dates using ACL’s Duplicate function to identify movement of funds between s  Scrutinized electronic routing and approval of electronic payment documents to identify weaknesses in segregation of duties and/or insufficient /transaction reviews 

Initial Investigation 

Reviewed Existing Reports



Ran Additional Reports - Single Card Use



Collection of Receipts



Meeting with Principal Investigators



Personnel Actions Secure Electronic Hardware/ Files/ etc. Coordination with Police and District Attorney’s Office Arrest of subject

 



Compilation of Evidence 

Problems Card Use (10/97 - 4/00) • Transactions: 1,840 • Volume: $209,403

Post 4/99 Receipts Destroyed (65%) Complicated Postings Number/Type of Vendors

Compilation of Evidence 

Approach  Document Each Transaction  Evaluate Source Documents: • Original receipt

• Receipt copy - On request or by subpoena • Vendor web sites - order history & info • E-mail purchase & shipping confirmations

Compilation of Evidence 

Case I



Case II

 Receipts

 Receipt with forgery

 Online order history

 Online order history

 Email confirmations

 Email confirmations

 Returns for credit on personal cards



Case I - Receipts



Case I - Receipts



Case I – Online Order History



Case I – Email Confirmation



Case I – Returns Return

Order

Credit



Case II – Receipt With Forgery

Forged Receipt



Case II – Receipt With Forgery

Actual Receipt

Forged Receipt

Control Issues 

Separation of Duties



Sharing of s



Reconciliations



Minimal Reviews

Corrective Steps 



 

Letters to Deans, Directors, Principal Investigators, etc. Follow-up Confirmations to Hierarchical Groups Improvement of Control Structure On-going Part of Compliance Program

Improvement of Control Structure 

Website Information http://www.utexas.edu//purchasing/procard/pcardwelcome.html



Testing of new cardholders http://www.utexas.edu//purchasing/procard/pcardmodule1.html

Tightened card use limits  Review/acknowledge voucher approval 

On-going Compliance Activities Inclusion in Compliance Verification System  Approvals of monthly vouchers  Quarterly reviews using ACL 

Quarterly ACL Reviews 

Card Use Reports – Ranks by $ amount – Ranks by # transactions

Card Use Summary

Quarterly ACL Reviews 

Card Use Reports – Ranks by $ amount – Ranks by # transactions



Compliance/Miscellaneous Reports – Transactions > $999.00 – Potential Split Orders – Posting delays > 30 days

Compliance/Misc. Summary

Compliance/Misc. Summary

Quarterly ACL Reviews 

Card Use Reports – Ranks by $ amount – Ranks by # transactions



Compliance/Miscellaneous Reports – Transactions > $999.00 – Potential Split Orders – Posting delays > 30 days



College/Department Level Reports

Quarterly ACL Reviews 

Merchant Summary Reports – High $ Merchants – “Suspect” Merchants – Merchant Types



Specific Card Investigations/Watches

Procard Review Procedures Reports  Coordination with Other Departments 

– Internal Audits – Purchasing – s Payable 

Follow-up

Lon Heuer [email protected]

Questions?

Dyan Hudson [email protected]