Technology has made fraud easier to spot. Our financial statement have hundreds of metrics; labor gross profit percent, expenses as a percent of gross, and gross profit per unit. Creating metrics of these items is the best way to tell if there are mistakes on your financial statement. Probably one of the most creative cases of theft that I have seen involved pizza and the dealership’s payroll clerk who felt she deserved a raise. Her husband owned a small pizza restaurant that had fallen into some tough times and couldn’t afford to make their payroll tax deposits. The clerk merely made the deposits for him out of the dealership’s bank account and debited payroll tax expense. The controller had even questioned her about the high payroll taxes and the clerk answered that it was because they had an unusual amount of spiffs, bonuses and work in process variances.
Payroll tax is a pretty simple metric early in the first half of the year. Most employees have not met the FICA or Medicare maximum so the tax should be the total of these two percentages. FUTA and SUTA – are both unemployment Insurance so are not a tax and should be in an insurance expense account. That means 7.65% of your wages should equal your payroll tax expense for the month. How do you determine your total wages? If your financial statement doesn’t have this as a memo item, either get it from the payroll register or add up these accounts; salaries, bonuses, commissions, vacation, and the cost of sales for labor for technicians. Yes, there could be some timing issues; the commission expense account might have the expense matching the deals for this month, but you didn’t pay them until next the next month – but that means that you paid this month the commissions accrued from last month. This same problem happens with Work in Process and Vacation accrual – so I like to get total payroll from the payroll register. The two should be close – or you can do a timing reconciliation to make sure they are the same. If your metric of 7.65% of the total payroll matches the total of your payroll expense accounts, then you’ve plug ½ of the hole. But what if the thief didn’t debit payroll expense and covered her tracks using another account? How can we use technology to find that?
Part 2 of this exercise is to see if your EFT deposits “Match” your payroll register. Since she could have used cost of sales, other deductions, or even an asset account like parts inventory or vehicles to cover her tracks, we need to make sure every outgoing EFT is valid. During most of Excel lessons I mention the wonderful VLookup and how valuable that can be for our industry. Excel’s VLookup function replaces the old yellow highlighters and lets you find data from two sources. For example you might have a schedule of floorplan balances and a statement of floorplan due from your lender. Using VLookup on the VIN# you can quickly find out if your lender is charging you for vehicles that you don’t own or on the flip side – if you have vehicles that you own that are not floored. This is especially helpful if you’re looking for cash today! But with the EFT deposits – you don’t have a “VIN” to lookup. Instead you have a huge list of payroll deposits due from your payroll system and an even bigger list of charges to your bank account. How do you “Match” without a common reference? The answer is to match the amounts and use Excel’s Match function to find where the amount from one list is located on the 2nd list. If you combine this with the Index function, you can even do more pinpointing.
The end result for this case story was that the metric showed that payroll expense was about $9,000 too high. In addition, using the Match function, we found 3 EFT payments that didn’t match the required payroll deposits and they totaled about $9,000. The clerk was fired and took out a loan to repay the amount but since she had been there for many years, we’ll always wonder how much did she really get away with?
