To err is human, and it happens to even the most diligent of us. When entering transactions into your accounting system, it is almost guaranteed that you will make an error at some point. Sometimes this is due to an errant keystroke, or getting distracted and losing your train of thought, or maybe even a lack of specific knowledge about how to properly enter a transaction.
Whatever the reason, it is important to find them quickly, as they can add up to large swings in your financial statements. In an earlier post, we covered the classes of clerical errors: error of omission, error of commission, compensating error, error of duplication, and error of principle. We are going to cover some of the most common clerical errors I have found over the years, and how to manually find them in your system.
This error occurs when you have debt that is accruing interest, but don’t properly break out the payments you are making between interest (expense) and principal (the amount you still owe the lender). This is one of the most common mistakes I see in my consulting business, and can be a real nightmare to clean up depending on how long the mistake has been ongoing. Usually the issue is that people apply the full amount to principal, or use the ratio from an earlier payment to calculate the breakout. Errors of this type can lead to material misstatements of liabilities.
That said, the answer is simple! Regular monthly reconciliations of these liability accounts will flag any errors quickly. To find errors like this manually, run a balance sheet for the day your statement ends and make sure the ending balance from the account statement matches the number you see in your books. Better yet, use the reconciliation tool in your accounting system to reconcile the statement balance to the books on that day. If the amounts don’t balance, you are either missing a transaction or the payment to that account is broken out wrong.
When a refund comes into the system, it can look like a deposit (increase) and get mistakenly coded to an income account. Instead, the offset should be a reduction in the expense account associated with that transaction. This can happen for a number of reasons, but is usually due to someone seeing an increase in cash and just assuming that is income. The tell-tale sign that this has happened is that you have a transaction with a vendor name associated with it charged to an income account.
The process for finding this type of error is a little more involved, but worth doing. Start by running a detailed general ledger for the month and exporting it to Excel. That should give you a transaction by transaction view of everything that happened in your books. Find the income account transactions and copy them to a separate tab in the spreadsheet. Then export a vendor list and paste that into your working spreadsheet. Next to your transaction columns, create another column called IsVendor. Use the COUNTIF function to lookup that transaction’s entity name in the list of vendors. Copy this formula for every transaction row, and then sort by descending to find all the values equal to one. Those will be transactions that need closer scrutiny, as they are flagging as being associated with vendor names.
This is the inverse problem of the one above. When you refund a customer, it can look like an expense, but should actually be a reduction of income. Ideally, you are using the refund functionality in your accounting system, but sometimes (as with Stripe) the transaction reversals just show as decreases in the bank account, and can be easily confused with other software expenses or other expenses.
Use the same process for finding these transactions as is laid above, but instead of using the income accounts, use the expense accounts. Also, run a customer list instead of a vendor list.
This one happens a lot, unfortunately, and the reasons aren’t always clear. Missing checks can be due to theft, accidental deletion or not recording them in the system when they are written. The most frequent reasons are the last two - someone deletes a check in the system instead of voiding it or the checks are being cut outside of the accounting system and not recorded until they clear the bank.
Both of those are easily avoidable errors. Firstly, checks that have already been written or printed should never be deleted, only voided. The reason is that the physical checks increment their check numbers, and you need that to stay in sync with the incrementing in the accounting system. If a physical check has already been created, and you need to shred it or stop payment, by all means do so. When you go to the system to record that, write what you did with the physical check in the memo or description and then void it. That way there's still a record of what happened to the physical check. Secondly, always try to create the check in the system and print it, or record it immediately if your company still writes physical checks. It is impossible to manage cash properly if you don’t know how much of it has already been effectively spent.
I’ll get off my soapbox and tell you how to find the gaps now. First run a general ledger for the period you want to examine and export it to Excel. Ideally you would do this from the beginning of time, then regularly check shorter periods on an ongoing basis. In the general ledger sort transactions by their type, and copy the transaction numbers for check transactions to a separate tab. Now you should have a list of all numbers associated with checks each on a separate row. Select all the rows with numbers and then click Data >> Remove Duplicates. Now sort the remaining list in ascending order. In a column next to the check numbers, enter a formula that subtracts the preceding check number from the check number in that row, and copy it down to all rows. No number in this second column should be higher than “1”. If it is, then you have at least one missing check in between those two check numbers.
As your business grows, you will likely want to increase the granularity of certain accounts in your accounting system. You can do that by adding children accounts with more detailed names to a more general parent account. For instance, you might have a parent account called “Consulting” with “Accounting”, “Legal”, and “Marketing” as separate children accounts used to track consulting spend for each of those categories. Sometimes, people will code transactions to those parent accounts instead of pushing it to the lower accounts. This can be done mistakenly or because they don’t feel there is an appropriate child account for the transaction. Either way, if children accounts exist, they must be used exclusively. Using the parent account for some transactions and the children account for others creates muddy statements and should be avoided.
To find this type of error, regularly run a balance sheet and income statement and look for balances being carried in parent accounts with children. Then double click the account and find the transactions that were coded to the top level and change them. The fix is really easy, but you’d be surprised how many people don’t do this or don’t think it is an issue.
These are some of the most common issues I find, but there are others. To make sure your department is issuing error-free financial statements, it is a good idea to look for all of these errors and others regularly. You might be wondering where you’re going to find all the time to do this manually. Don’t worry about it. That’s why we created Scrutinize. Instead of spending hours every month searching for issues in your accounting system, we can generate an anomaly report for you in less time than you spent reading this article.
For answers to questions you have about this or related topics, schedule your free consultation today.
.png)