Occupational Fraud: Billing Schemes
Mark W. Lehman and Marcia L. Weidenmier
2005 - According to a 2004 report by the Association of Certified
Fraud Examiners, organizations are estimated to lose 6% of
their annual revenues to occupational fraud. Moreover, almost
a quarter of all frauds are detected by accident (21.3%),
as opposed to internal auditors (23.8%) or external auditors
(10.9%). It is imperative that auditors, both internal and
external, improve the effectiveness of their audits and detect
a higher proportion of frauds.
2004 ACFE report classifies occupational fraud into three
categories: corruption, where the fraudster improperly uses
influence to gain some benefit (e.g., bribes, kickbacks);
fraudulent statements, where the fraudster knowingly misstates
financial statements; and asset misappropriations, where
the fraudster steals or misuses organizational assets. Asset
misappropriation is involved in over 90% of reported fraud
cases. The most common type of asset misappropriations,
billing schemes, makes up 33.5% of all fraud cases, according
to the ACFE. These are described as schemes in which the
“fraudster causes the victim organization to issue
a payment by submitting invoices for fictitious goods or
services, inflated invoices, or invoice for personal purchases.”
Access can be a useful tool in detecting six common red
flags of billing schemes. Auditors seeking to improve fraud
detection are advised to take advantage of its capabilities.
fraudsters often set up fictitious vendors to commit a billing-scheme
fraud. The fictitious vendor might be a shell company that
provides no products or services. Or it might be a pass-through
company, where the fraudster becomes an unnecessary intermediary
between the legitimate company and the victim company to
earn an unauthorized profit on payments to the legitimate
setting up fictitious vendors in accounting information
systems, fraudsters often leave behind clues, or red flags,
that enable auditors to detect their crimes. The tools and
techniques outlined below demonstrate some of the ways Access
can be used to identify these red flags and help stop fraudsters.
red flags include the following:
An employee’s home address matches a vendor’s
An employee’s initials match a vendor’s name.
Checks are written to “cash.”
A vendor’s address contains a P.O. box.
Vendor data are missing
Vendor data are formatted illogically.
sample dataset used to demonstrate the analysis below is
available for download from www.cpajournal.com/down.htm.
A basic knowledge of Access (version 2003) is assumed.
first step is loading the data into Access. Any accounting
information system should be able to provide data in text-delimited
files. As an example of how to import this data, the downloadable
package (from www.cpaj.com) includes the employee master
file as a text-delimited file, Employee.txt, to be imported
into the database file, fraud_data.mdb. The Import Data
Wizard provides assistance in identifying the data type
(such as number, text, and date) of each data field.
the Access file fraud_data.mdb. The database already includes
five tables previously imported. To import the sixth table,
containing employee data, select file, get external data,
import. In the dialog box, select the filetype “text
files,” and select Employees,txt. The Import Text
Wizard will be activated. Click “next” on the
first step. Then click the “first row contains field
names” checkbox, and then click “next”
twice. In the next step, change the “data type”
menu to “text” (Exhibit
1) and then click “next.” Select the “choose
my own primary key” button, and select the “Employee
Number” field from the menu. Then click “next,”
followed by “finish” and “OK” to
complete the import process.
Flag 1: Matching Employee and Vendor Addresses
obtain a check written to a fictitious company, a fraudster
might enter a personal address for the fictitious company.
These common addresses can be detected by comparing the
address fields of the Employees and Vendors tables. Open
a new query in “design view.” Select the Employees
table and click “add,” and then add the Vendors
table as well. Click and drag the Address field in the Employees
table and drop it on the Vendor Address field in the Vendors
table. A line now indicates the relationship between the
fields. The bottom portion of the query-by-example (QBE)
window identifies the fields to be displayed. Double-clicking
on a field name selects the field. From the Employees table,
select the First Name, Last Name, and Address fields. From
the Vendors table, select the Vendor Name and Vendor Address
fields. The result should now resemble Exhibit
2. The query can be saved as Common Addresses. Opening
the query in Datasheet view will show that one employee,
James Skinner, has the same address as a vendor, CCS Consulting.
Flag 2: Matching Employee Initials to Vendor Names
may also establish a fictitious company using their initials
as the beginning of the business name, such as John P. Stevens
using the name JPS Consulting. This practice makes it easier
for the fraudster to cash checks and conduct the “business”
of the fictitious company. Thus, a comparison of employee
initials to vendor names may identify fictitious companies.
a new query in the Design View and select the Employees
table. Select the First Name, Middle Initial, and Last Name
fields. In the next three QBE columns, enter the following
First: Left([First Name],1)
Last: Left([Last Name],1)
Initials: [First]+[Middle Initial]+[Last]
Left function extracts a number of characters beginning
at the left of a text field. Here, the function was used
to extract the first letter and create a three-letter abbreviation.
Save the query as Employee Initials.
query will compare the initials to vendor names. Open a
new query in the Design View and select the Vendors table
and the Employee Initials query, then select the Vendor
field and all of the employee name fields, including the
Initials field. To select employees whose initials match
the first three letters of a vendor’s name, enter
=Left([Vendor Name],3) in the criteria row of the Initials
field column (Exhibit
3). This will compare employee’s initials to the
first three characters of a vendor’s name. After saving
the query and viewing the results, it is apparent that one
employee, Paul D. Clemons, has the same initials as a vendor,
Flags 3 and 4: Identifying Cash Vendors and P.O. Addresses
could establish a fictitious vendor using “cash”
in the vendor’s name, to make it easier to cash checks.
Similarly, they could use a P.O. box as the vendor’s
address, to make it easier to collect checks. A simple text
search of the Vendors table should identify “cash”
in the name or “P. O.” in the address. Using
a wildcard search like “P*O*” will detect any
of the following variations: PO Box, P O Box, P.O.Box, and
P. O. Box.
perform such a search, open a new query in the Design View
and select the Vendors table. Select the Vendor Number,
Vendor Name, and Vendor Address fields. To select vendors
with “cash” in the name, enter Like “*cash*”
in the criteria row for Vendor Name (Exhibit
4). To select vendors with a P.O. address, enter Like
“P*O*” in the “or:” row for Vendor
Address (criteria entered in the same row would indicate
the “and” logical operator). After saving the
query and viewing the results, it is apparent that two vendors
have cash in their name, and four vendors have P.O. box
Flag 5: Missing Data
creating fictitious companies often enter the minimum amount
of data necessary to receive a fraudulent check. They may
avoid entering phone numbers or taxpayer identification
numbers if the accounting system does not require this information
in order to issue a check.
search for such missing data, open a new query in the Design
View and select the Vendors table. Select the Vendor Number,
Vendor Name, and Vendor Phone fields. To select vendors
with no phone number, enter Is Null in the criteria row
for Vendor Phone (Exhibit
saving the query and viewing the results, it is apparent
that LEP Marketing does not have a phone number. This may
or may not indicate that it is a fictitious vendor. Similar
searches for data such as a taxpayer identification number
might provide further reasons to investigate.
Flag 6: Illogically Formatted Vendor Data
data items should adhere to a required format. For example,
a vendor’s taxpayer identification number (TIN) can
have one of two possible formats, depending upon whether
the vendor is a business or an individual. Businesses have
TINs with two digits, a hyphen, then seven more digits.
Individual taxpayers use their Social Security numbers as
their TIN. When entering data for a shell company, a fraudster
may simply fill the field with random characters. Using
tools above, a query can search for vendor identification
numbers. A numerical wildcard character, the pound sign
(#), is used to represent any digit. To perform a search,
open a new query in the Design View and select the Vendors
table. Select the Vendor Number, Vendor Name, and Vendor
TIN fields. To select vendors lacking the acceptable format
for a business TIN, enter Not Like“##-#######”
in the criteria row for Vendor TIN (Exhibit
6). To simultaneously search for unacceptable Social
Security numbers formats, add And Not Like “###-##-####.”
After saving the query and viewing the results, it is apparent
that CCS Consulting has only five digits in its TIN. Polk
Publications has the correct number of digits, but contains
two letters. As with all other audit tests, further investigation
is required to determine if these red flags have revealed
must actively search for fraud. Microsoft Access can be
a useful tool in finding evidence of common billing schemes.
Finding one or more of the red flags would call for the
application of more traditional audit procedures, such as
inquiry of employees and inspection of source documents,
to assess the nature of the transactions. Auditors that
can use software imaginatively can detect fictitious companies
that traditional audit procedures might overlook.
W. Lehman, PhD, CPA, is an associate professor of
accountancy and Marcia L. Weidenmier, PhD, CPA,
is an assistant professor of accountancy, both at Mississippi
State University, Starkville, Miss.