Transcript Use of ACL in Audits & Investigations

Use of ACL
in Audits & Investigations
Lon S. Heuer, CPA, 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/admin/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/admin/audit/files/
Download 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 Accounting System
Accounting/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 Accounting Records
Complicated account postings and transfers
between accounts
 Matched dollar amounts and dates using
ACL’s Duplicate function to identify
movement of funds between accounts
 Scrutinized electronic routing and approval
of electronic payment documents to identify
weaknesses in segregation of duties and/or
insufficient account/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 Account 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 & account 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 Passwords

Account Reconciliations

Minimal Account 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/admin/purchasing/procard/pcardwelcome.html

Testing of new cardholders
http://www.utexas.edu/admin/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
– Accounts Payable

Follow-up
Lon Heuer
[email protected]
Questions?
Dyan Hudson
[email protected]