State of Vermont Office of the State AuditorThomas M. Salmon CPA, State Auditor Using Data Mining Techniques to Identify “Potential Improper Payments”
Panel Participants • Stephen Vantine , CIA – Audit Manager • Jeff Kellar, CPA – Audit Supervisor • Hugh Pritchard, CIA – Senior Auditor
Background of the State Auditors Office Mission • The Vermont State Auditor's Office is a catalyst for assuring accountability in state government through reliable and accurate financial reporting and by promoting economy, efficiency and effectiveness in government. The office does this by conducting audits and reviews, and by providing consultations, technical assistance and training. • Constitutional office, the State Auditor is elected every two years. • Staff size = 13, 9 Audit Staff • Performance audits, statutory audits, counties, municipalities and school districts.
State of Vermont Six Agencies • 75 Departments • ~ 8,000 employees • ~290,000 Vendors • ~623,000 citizens
State of Vermont Financial Management System (VISION) • Oracle/PeopleSoft product (v. 8.8) • Financials and Human Capital Management • Other interfaced IT systems • Decentralized invoice and voucher processing. • Decentralized operational accounting. • Centralized financial reporting
Data Mining Technology IDEA Data Analysis Software (v.7.3) • Key Features: It allows you to import, join, analyze, sample and extract data and manage large volumes of data (our audit was approx. 1,022,000 vouchers). • Maintains the integrity of the original source data. • Creates automatic audit trail/activity log of actions performed on the source data.
“Potential Improper Payments Audit” Objectives • To use data mining techniques to identify potential improper payments made by the State from January 1, 2007 to December 31, 2008. • To review the internal controls related to payments. (The audit was performed in accordance with GAGAS)
Internal Controls Reviewed • IT General Controls • Control Environment • Control Activities • Interfaces
Testing and Algorithms Duplicate Invoices • Full matches: pairs of vouchers where invoice number and date, vendor ID and $ amount all match precisely. • Partial matches: pairs of vouchers where three of the above four fields match, and the fourth differs. • Fuzzy matches: pairs of vouchers where the invoice number and vendor ID match precisely, and the date or $ amount differ slightly. • Vendor ID transposition test.
Testing and Algorithms (continued) Unusual matches with other data sets • Matching Addresses – vendor’s address matches an employee’s address. • Matching Bank Accounts – vendor’s bank account matches an employee’s bank account. • A match of the vendor name, and system users who entered or approved the voucher.
Testing and Algorithms (continued) Unusual Patterns Vendor Vouchers with unusual standard deviations greater than other invoices from that vendor. All of these algorithms identified actual improper payments except for matching bank accounts and matching addresses.
Internal Control Observations • Understanding system user privileges is critical. • In a decentralized environment, departments adopt individual practices which can create lack of uniformity. • Duplicate payments created by not paying from original invoices.
Internal Control Observations - Continued • Intentional/unintentional circumvention of system duplicate payment detection controls. • Master vendor file management process.
Data Mining Tips • The design of your audit objectives is critical. (ex: minimum dollar thresholds). • Ask for the “data dictionary” (if available). • Understand Geek speak. Find a resource that understands the data structures and data elements and the operational business processes.
Data Mining Tips • Use a “programming mindset” during your analysis. • Use precise algorithms to be selective and narrow the data population. • Use desk reviews of the analysis results to increase efficiency of the audit (numerous non-standard accounting entries gave rise to “false positives”).
Data Mining Tips • The audit’s own database file management is essential - file size growth can be problematic for storage and manipulation of data. • Managerial review of work papers requires the reviewer to have a working knowledge of the data mining software.
Contact Information Office of the Vermont State Auditor www.auditor.vermont.gov Tel (802)-828-2281 Thomas M. Salmon Tel. (802) 828-2281 e-mail: firstname.lastname@example.org. Stephen Vantine Tel. (802) 828-2284 e-mail: email@example.com Jeff Kellar Tel. (802) 828-2122 e-mail: firstname.lastname@example.org Hugh Pritchard Tel. (802) 828-4619 e-mail: email@example.com