Detecting Occupational Fraud: Billing Schemes

By Mark W. Lehman and Marcia L. Weidenmier

E-mail Story
Print Story
APRIL 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.

The 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.”

Microsoft 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.

Employee 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 vendor.

When 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.

Common red flags include the following:

  • An employee’s home address matches a vendor’s address.
  • 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.

The sample dataset used to demonstrate the analysis below is available for download from A basic knowledge of Access (version 2003) is assumed.

Accessing Data

The 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 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.

Open 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.

Red Flag 1: Matching Employee and Vendor Addresses

To 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.

Red Flag 2: Matching Employee Initials to Vendor Names

Fraudsters 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.

Open 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 expressions:

  • First: Left([First Name],1)
  • Last: Left([Last Name],1)
  • Initials: [First]+[Middle Initial]+[Last]

The 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.

A second 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, PDC Consulting.

Red Flags 3 and 4: Identifying Cash Vendors and P.O. Addresses

Fraudsters 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.

To 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 addresses.

Red Flag 5: Missing Data

Fraudsters 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.

To 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 5).

After 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.

Red Flag 6: Illogically Formatted Vendor Data

Some 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 a fraud.

Just a Beginning

Auditors 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.

Mark 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.




















The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.

©2009 The New York State Society of CPAs. Legal Notices


Visit the new