Research

Make Reconciliation Easier with Free Accounting Reconciliation Templates

Oct 23, 2020 | By Michael Whitmire

Reconciliation is one of the most important processes in accounting. It’s how we determine that the numbers are right. Standardizing reconciliation processes is an easy way to get a quick win in streamlining the month-end close, and it’s a great start in creating a strong foundation for your organization’s financial management. 

But even with that importance, reconciliation is also one of the most poorly taught skills. Most people learn it on the job, in the crunch to get the books closed. If you’re lucky, someone gave you an accounting reconciliation template they created. If you’re not so lucky, hopefully, you created a usable template. But in case you don’t have one or inherited one that looks like it was created by Dr. Frankenstein, we can lend you a hand.

What Is Accounting Reconciliation and How to Do It?

Accounting reconciliation is how accountants verify that account balances are correct and complete. It involves comparing the general ledger account balance to a credible and independent source of information. Account reconciliation is NOT simply listing the transactions from the GL account and verifying that the total is correct. 

Because of the way that accounting works, once you have the balance sheet accounts nailed down, then what squeezes out at the end is net income. This means that reconciling balance sheet accounts plays a key role in ensuring that financial reports and general accounting records are accurate. Getting the balance sheet right is what determines the bottom line on the income statement.

How often should you perform account reconciliation? That depends on the volume of transactions that pass through a given balance sheet account. Large organizations may need to reconcile their operating cash account daily. Other organizations may do just fine with a monthly bank reconciliation statement. Small businesses that don’t do a hard monthly close can often get away with reconciling the rest of their balance sheet accounts at the end of their fiscal year, when it’s time to do the tax return.

While the details of how you reconcile a particular account depend on the kind of account and the nature of the transactions that pass through it, the first step is almost always to export the transactions since the last reconciliation to a spreadsheet. Depending on your organization’s preferences, this might be Microsoft Excel or a Google Sheet. 

The next step is crucial, but is easy to overlook: verify that the beginning balance in your accounting software matches the ending balance of your last reconciliation. If these don’t agree, there is no way you’ll get your reconciliation for the current period to tie out. 

If they don’t match, you’ll have to backtrack through your previous reconciliation and the account transactions to find what changed. 

Once you have a solid starting point, the reconciliation procedures you’ll use will depend on the type of account you’re working with. We’ll go through the four different methods in turn. 

Reconcile to an External Statement

This is the process used for bank accounts, credit cards, loans, and any other account where there’s a third party statement that shows transactions. Bank statement reconciliation is probably the most common kind of reconciliation performed. Your accounting system may have a built-in reconciliation module, but if yours lacks that functionality, using a reconciliation template will make it easier. 

First, look at the reconciling items from last month or last period to see if any of those cleared this month. Check them off on both sides. 

With the “easy” part out of the way, it’s time to start matching the transactions on the external statement to the transactions in the accounting system. Amounts, dates, and descriptions can guide you, but sometimes one transaction in the accounting system will appear as multiple lines on the statement, or vice versa. These may not be readily apparent until the very end. 

Finally, look at transactions that are on the statement but not in the accounting system, and vice versa. Are the amounts correct? Is it a timing difference? Are there fees or bank interest that need to be recorded in the general ledger? Did the bank make a mistake? Was the transaction recorded correctly in the accounting system? Are there duplicate or missing entries? Add and subtract these reconciling items as appropriate until you arrive at reconciled balances that match. 

Reconcile to Account Activity

Some accounts, like prepaid expenses, accrued revenues, accrued liabilities, and certain receivables, have to be reconciled based on the transactions that were posted (or should have been posted) to that account. This may require looking at amortization schedules or other documentation. 

For example, if the schedules for deferred revenue show that the balance should have dropped by a net of $45,000 to $790,000, but the balance in the GL is nowhere near that, you’ll have to investigate the difference. Maybe a journal entry wasn’t recorded correctly, or maybe the schedule wasn’t updated correctly. 

Reconcile to Subsidiary Ledger Activity

Some accounts are tracked in separate subsidiary ledgers or schedules. These include accounts receivable, fixed assets, and accounts payable. Reconciling these accounts is a simple matter of verifying that those subsidiary ledgers or schedules are complete and that the balance matches the general ledger balance. 

Reconcile With Rollforward

Equity accounts are generally reconciled by performing an account rollforward. Start with the ending balance from the last period, add and subtract the increases and decreases and verify that the ending balances in the reconciliation and the general ledger are the same. 

Whichever method you use, your tied-out reconciliation is part of the documentation for that period’s financial statements.

Why Is Reconciliation Important?

Balance sheet account reconciliations are a crucial part of bookkeeping. It’s how we ensure that the debits and credits of journal entries are correct. As I said above, verifying that the balances in the balance sheet accounts are correct is how you ensure that net income on the income statement is correct. But reconciliation also plays an important role in many other areas of financial management. 

Internal controls The objective of internal controls is to prevent or detect errors or fraud. Reconciliation does that by identifying duplicate or missing transactions or transactions that are in the wrong period. It also alerts business owners and executives to sudden and unexplained changes in inventory or other business assets. Verifying that reconciliations are performed correctly and timely is one of the chief tools that the internal audit team will use to make sure controls are working. 

Fraud and theft detection This is really part of internal controls, but it’s so important that I want to highlight it. Cash, and especially petty cash, are easy targets for fraudsters in any organization. Keeping a close eye on cash account reconciliations is the easiest way to catch theft early, before it gets out of hand (and it always does). 

Performance monitoring In any competitive sport, the vital number is the score. For businesses, that score is the bottom line, or net income. Reconciling balance sheet accounts is how you verify that bottom line. Without that knowledge, decision makers in that business are flying blind and don’t have any way to verify that their strategy is working. 

Cash flow To project your cash flow accurately, you need a firm starting point. Without regular reconciliations, you don’t know about outstanding checks and how much of the cash in your bank account is already spoken for. 

How Do I Use Account Reconciliation Templates?

Now that you know how important it is to perform balance sheet reconciliations regularly, using templates for your reconciliations ensures that you’re following best practices. You can be sure you’re not missing something, or doing something wrong. Using accounting templates throughout your work saves you the time and energy of reinventing the wheel every time you do the same procedure. They also help to streamline your processes to make the month-end close a bit less painful. Customizing them as you see fit makes them your own. 

Download Accounting Reconciliation Templates for Free

These balance sheet reconciliation templates were created using best practices developed by top-line accounting teams from across the country. Go ahead, give these a try, and leave your Frankenstein template behind. 

Learn what industry experts from Ventana Research have to say about the month-end close moving forward

Michael Whitmire
Michael Whitmire
As CEO and Co-Founder, Mike leads FloQast’s corporate vision, strategy and execution. Prior to founding FloQast, he managed the accounting team at Cornerstone OnDemand, a SaaS company in Los Angeles. He began his career at Ernst & Young in Los Angeles where he performed public company audits, opening balance sheet audits, cash to GAAP restatements, compilation reviews, international reporting, merger and acquisition audits and SOX compliance testing. He holds a Bachelor’s degree in Accounting from Syracuse University.

Check out research, videos, case studies, and more!

Learn more about working at FloQast!