Overview/Narrative

Between July 1, 2002 and December 31, 2005, Wake County School employees in the school transportation department and Barnes Motor & Parts Co., based in Wilson, NC, submitted at least $3.8 million in fraudulent invoices for school bus and automotive parts. Much of the money was used by the employees to buy personal items such as automobiles, campers, golf carts and plasma-screen televisions. This despite a bid limit of just $2,500. Wake County schools had one internal auditor. The story received wide press. An audit report explaining many of the details is available on the School Board's web page.

Once the The School district fired the employees and an investigation was performed, $4.8 million was recovered from Barnes and the former employees. There were numerous red flags that were not noticed:

• payments to Barnes Motor & Parts increased 342% from 2002 to 2003 (an increase in excess of
  $3,700,000).
• 2/3 of the invoices from Barnes Motor & Parts were under the $2,500 bid limit, and did not have a corresponding purchase order
  
• for two years, over 99.95% of invoices were under $2,500
  
• on 24 occasions, there were 50 or more invoices from Barnes Motor & Parts with the same invoice date, and a majority of these invoices had consecutive invoice numbers
  
• On June 10, 2003, as the fiscal year end approached, Barnes Motor & Parts submitted 466 invoices totaling $909,266.

It is essential to detect when instances of circumvention of procurement controls may be happening. Basic histogram charts and data analysis can readily disclose many anomalies.

Audit Objective

The objective is to determine compliance with controls over purchases.

Audit Procedures / Audit Program

1. Obtain invoice data in electronic format, note data elements.
   
2. Sort the data by supplier.
   
3. For each supplier and each data element expected to conform with Benford's law, prepare a population distribution and a chart, using the first digit.
   
4. For each surveyor and each applicable survey element, compute the D-Statistic using the Kolmogorov-Smirnov metric.
   
5. Review each chart and data table prepared to determine if the data appears to conform with Benford's Law.
   
6. For data which does not appear to conform, how does the D-Statistic compare with the others?
   
7. Write a report explaining your observations, and provide conclusions and recommendations.

Example Data (Excel and Flat File)

Excel WorkBook Sheet Wake County
Tab separated file wake.tab

Description of Analytical Procedures Performed

First sort the file provided in Vendor Sequence
This can be done by opening the file in Excel, sorting and then saving as a tab separated file, or by using EZ-R Stats for Windows Tools | Sort and specify the input file as "wake.tab", the output file as "wakesort.tab" and the sort key as vendor.

Using EZ-R Stats for Excel, choose the menu option Tools | Pareto.
Specify input file "wakesort.tab", output file "WakePareto.tab"

Variable to be tested will be VendorNo

Screen Shots of Analysis

Screen Shots of Output

From a Pareto distribution for the vendor, we see that Vendor A11 accounts for almost 50% of the transactions.

We then produce a histogram of the invoice dates and see that they are clustered at the end of the County's fiscal year:

Discussion of Results

Alternative Approach - Batch Commands / Scripts

The analysis can also be done using a command line:

sort /ezs/temp/sort.ezs wake.tab wakesort.tab Surveyor

then run the pareto analysis

CtrlBrk.exe -cpareto -e/ezs/temp/pareto.ezs -iwakesort.tab -o/ezs/temp/wakeresults.tab -vVendorNo -mMAX title=WakeCo

The analysis can also be performed using scripts:

options obs=max
libname wakeco 'c:/temp/wake';
proc sort data=wake;
by VendorNo;
out = wakeco.sortwake;

proc pareto data=sortwake;
var VendorNo;
out=wakeco.par;