We live in a world where time and resources are at a premium.  As a result, some responsibilities get pushed to the “back burner.”  However, we also live in a world where the possibility for employee fraud is real,  but the process to uncover such fraud could be difficult and time consuming. One way to get some high quality return on time investment is by using simple Excel functions.

As an auditor, I’ve had the privilege of being involved in thorough data mining projects designed to detect fraudulent activity.  During these projects, data mining software is used to perform a suite of complex tests that can comb through significant amounts of data and summarize it to obtain leads related to suspicious activity. This software can produce amazing results; however, owning this type of software can be expensive and it takes time to learn the more difficult tests.

Luckily, as long as you have the ability to export certain data to Excel, then you will be able to perform the following Excel functions in seconds and the results only require minor follow-up.   These tests will never replace data mining or other internal audit procedures that your organization is already doing, but if you don’t have any internal audit procedures in place, the following steps would be a good place to start:

Non-Payroll Check Register

  • Subtotal vendor checks by count and dollar amounts
    • You might find that a vendor you should be paying monthly is getting paid more often.
    • Look for vendors that you are unfamiliar with but are getting paid substantial amounts of money.
  • Checks on weekend
    • The formula is very simple for this test “=weekday([cell with the date])”, but it could highlight a listing of checks that someone was cutting while no one else was in the office (for the results of the formula, 1=Sunday and 7=Saturday).
  • Checks written to “cash”
    • By simply using the find feature (Ctrl+F) you can locate all instances of checks that were cut to payees with “cash” in the name. Does the dollar amount of the checks cut to petty cash seem reasonable?  Are there more checks to cash than you expected?

Payroll Check Register/ACH listing

  • Subtotal by count and amount
    • Looking for employees that have been paid more than the number of pay periods your organization has during a year could be a fraud indicator that is easy to spot.
    • Comparing amounts paid for a year to salary information in the employee file on a test basis, could show wage rate abuse. Because the follow-up on this one is a little more intensive (comparing data to employee files), you could limit your follow-up selections to higher risk employees such as those individuals with access to entering or editing payroll.
    • Even a simple review of the names of the employees on the listing could help you identify if any ghost employees have been set up.
  • A subtotal counting bank accounts (ACH payments) or addresses (mailed checks)
    • Use the subtotal count function and look for bank accounts or addresses that show up more than one time. If you see something listed more than once, this could be a big red flag (or perhaps merely a situation that occurred because you employ a married couple).

All of these tests provide information on where to dig deeper to identify fraudulent activity.  Even if these tests don’t specifically find a fraud, they could help to prevent one.

There are several elements that are consistent among people who commit fraud.  These elements are often referred to as the fraud triangle.  One part of the fraud triangle is that the individual committing the fraud needs to have “an opportunity” for the fraud to take place.  By simply asking questions of staff members related the results of the Excel tests listed above, you are letting your employees know that some degree of internal audit function is being performed.  This could cause employees to perceive less of “an opportunity” to commit fraud.

Of course, the purpose of these tests is not to accuse your employees of committing fraud.  The messaging related to the follow-up on the Excel results is important.  Part of that messaging could be that procedures such as these can help find mistakes or inefficiencies that should be corrected moving forward.  This would make the jobs of the people working in the department better on an ongoing basis.

Just think: by devoting as much time as you devoted to reading this article, you could have exported a check register to Excel and subtotaled it.  It makes you wonder what clues you could have uncovered related to errors, inefficiencies or potentially even fraudulent activity.

© Clark Nuber PS, 2015.  All Rights Reserved

This article contains general information only and should not be construed as accounting, business, financial, investment, legal, tax, or other professional advice or services. Before making any decision or taking any action, you should engage a qualified professional advisor.