Chapter 11

Reconciling Accounts

In This Chapter

Calculating your opening bank balance

Getting your first bank reconciliation off the runway

Keeping reports that show you’ve done the deed

Employing tricks of the trade when all seems lost

Lining up for even more fun — reconciling other kinds of accounts

When you reconcile a bank account, you compare the transactions in your set of accounts to those on the corresponding bank statement. You tick these entries off one by one and check that you and your bank agree, down to the very last cent.

Reconciling bank accounts isn’t some arcane activity invented by masochistic bean counters. Rather, reconciling accounts is the only sure-fire way to identify if you have made any mistakes, such as a payment entered as $900 instead of $90, a deposit entered twice or missing bank charges.

The good news is that reconciling bank accounts is surprisingly quick and easy and, although you may find this hard to believe, can even be rather satisfying in a weird, nerdish kind of way. (I’m not really a boring person. Just sometimes, I get excited over boring things. Spot the difference . . .)

Getting Started

Did you ever see that old movie A Christmas Carol, based on the book by Charles Dickens? The beleaguered accounts clerk Bob Cratchit, under pressure from his heartless employer Ebenezer Scrooge, spends Christmas Eve completing the final bank reconciliation for his employer. Only when this reconciliation is complete is he permitted to go home for Christmas dinner with his family.

I hope that you’re never going to spend Christmas Eve getting your bank account to reconcile. Instead, use my easy step-by-step approach to the gentle art of reconciliation, and make sure you spend every festive occasion eating, drinking and being merry.

Deciding what bank accounts to reconcile

Most businesses have a lot of bank accounts, from savings accounts and term deposits, to credit cards and loans. A conscientious bookkeeper — and I hope that’s what you are — knows to reconcile all of these accounts. However, if you’re not conscientious — maybe you’re the owner of the business and you’re happy for your accountant to pick up after you — I suggest you slot bank reconciliations into three categories:

Accounts you have to reconcile: As a bare minimum, always reconcile the main business bank account. Unless you do this, neither you nor anyone else can rely on any financial reports.

Accounts to reconcile if you have time: It’s a good idea to reconcile all credit card, savings and PayPal accounts, as well as the business bank account.

Accounts to reconcile if you’re feeling conscientious: Ideally, reconcile all loan accounts as well. Loan accounts can be tricky, because you have to split up interest and principal on each loan repayment.

Calculating your true bank balance

Unless you’re a new business with a nil opening balance in your bank account, the first time you reconcile a bank account, you need to calculate what the true balance of this account is. By true balance (also sometimes referred to as cashbook balance), I mean the bank statement balance adjusted for any uncleared withdrawals or deposits. For example, maybe my bank statement says I have $1,500 in my account. However, I know that I just wrote a cheque for $200 to puppy pre-school (a tax deduction for sure, this ball of fluff is one fierce guard dog), and that this cheque hasn’t cleared yet. I also accepted a credit card payment from a customer for $1,200 that hasn’t shown up in my account yet. My true bank balance is actually $2,500 (that’s $1,500 less $200 plus $1,200).

Being a nerdy kind of gal, I like to draw up a wee report showing how I calculate this true balance, similar to Figure 11-1. (I typed this report in a word processor, but of course, you can write this report just fine by hand.) The process is a tad technical, but don’t stress out. Here’s what to do:

1. Look up the opening balance on the bank statement.

For example, if you’re starting your books from 1 July 2010, dig out the July bank statement. Write down the statement balance as at this date.

2. List all uncleared withdrawals as at your starting date and write down the total amount.

By uncleared, I mean any cheques written before this date which haven’t been presented yet.

3. List all uncleared deposits as at your starting date and write down the total amount.

Look at the deposit book for this period to see if there are any deposits that didn’t clear immediately. An example may be a deposit dated 30 June that didn’t clear through the bank account until 1 July, or EFTPOS payments received from customers on 30 June that didn’t appear on the bank statement until 1 July.

4. Work out what the true bank account balance was at your starting date.

The true bank account balance is what you would have in your account if all uncleared payments and deposits cleared immediately. In other words, the true bank balance is the amount you wrote down in Step 1 (the balance from your bank statement), less the amount you wrote down in Step 2 (total uncleared payments), plus the amount you wrote down in Step 3 (total uncleared deposits).

5. Write this information neatly as a report.

See Figure 11-1 for a most glamorous and elegant example.

Recording your opening bank balance using accounting software

If you’re using accounting software, and you’ve already calculated your true bank balance (if not, refer to the section earlier called ‘Calculating your true bank balance’), then you’re ready to set up your first bank reconciliation. Here’s my neat 1-2-3 approach:

1. In your chart of accounts, make sure you not only have an account for your bank account, but also create a new account called Uncleared Transactions.

This account sits immediately below your bank account in your chart of accounts.

2. Enter the opening bank statement balance as the opening balance for your bank account.

The mechanics vary, but any accounting software provides a spot for you to enter opening balances. As the opening balance for your bank account, enter the balance as per the bank statement. (In the example from Figure 11-1, I enter $1,500 as the opening balance for my bank account.)

3. Enter the combined balance of uncleared transactions as the opening balance of your Uncleared Transactions account.

Using the example in Figure 11-1 again, the combined balance of uncleared transactions is $1,000 (a $200 uncleared cheque, which is a minus amount, plus a $1,200 uncleared deposit).

All done? With opening balances for your bank account entered, you’re ready to reconcile your bank account (see ‘Reconciling accounts using software’ later in this chapter for details). When the uncleared cheques or deposits eventually appear on your bank statement, simply record a transaction that transfers the amount out of your uncleared transactions account and into your regular bank account, similar to Figure 11-2.

When all uncleared transactions finally appear on your bank statement and you reconcile these transactions, your Uncleared Transactions account returns to a zero balance. Simple as pie.

Copyright © 2010 Intuit Inc. All rights reserved

Doing Your First Reconciliation

Just like losing your you-know-what (and if you can’t guess what I’m talking about, I won’t spell it out), the first time you reconcile a bank account is certainly the most daunting. But like with most things, practice makes perfect.

Reconciling accounts by hand

The main difference between reconciling a bank account by hand, versus reconciling a bank account using accounting software, is that when you reconcile a bank account by hand, you usually reconcile a month’s worth of transactions at a time. (You also do a whole lot more adding up and ticking off and general fussing around.) So pick up your fountain pen and get ready for action:

1. Write up every single transaction for the month.

Don’t forget irregular transactions such as personal ATM withdrawals or monthly bank fees.

2. Write your opening cashbook balance at the top of your Bank Reconciliation report.

By opening cashbook balance, I mean your true bank balance. (If you’re unsure what your true bank balance is, refer to the section ‘Calculating your true bank balance’ earlier in this chapter.)

Figure 11-3 shows the format of a typical Bank Reconciliation report. Your opening cashbook balance is the first line.

3. Working from the bank statement, tick off every item listed in your receipts journal, checking amounts as you go.

In Chapter 9, I explain how to write up a receipts journal that lists every deposit into your bank account. With this complete, the aim of the game is to compare your receipts journal against the bank statement. Work from the top of the first page of your bank statement, finding the corresponding deposit in your receipts journal and ticking first the transaction in the journal, and then the transaction in your bank statement.

Of course, if you find you’ve made a mistake in your receipts journal, fix it up. Or, if you find you’ve missed any deposits, add them in now.

4. Repeat this process for your payments journal.

Now mark off every withdrawal in your bank statement against your payments journal, again ticking both the bank statement and your journal for every transaction. (Chapter 8 explains more about writing a payments journal.)

5. Add up all receipts in your cash receipts journal.

Write this amount against total monthly receipts in your Bank Reconciliation report, as per Figure 11-3.

6. Add up all payments in your payments journal.

Write this amount against total monthly withdrawals in your Bank Reconciliation report, as per Figure 11-3.

7. Calculate your closing cashbook balance.

Again, look to Figure 11-3 for how this works. Your closing cashbook balance is equal to your opening bank balance plus all receipts for the month less all withdrawals for the month.

8. Make a list of any cheques or deposits that are uncleared.

Make a list of any transactions that you haven’t marked off as appearing on your bank statement, as per Figure 11-3.

9. Calculate your expected bank statement balance.

Your expected bank statement balance is equivalent to your closing cashbook balance plus any uncleared cheques less any uncleared deposits. Yeah, yeah. Bookkeeping is an art form, that’s for sure.

10. Either give yourself a pat on the back, or find out where you went wrong.

Compare your expected bank statement balance with your actual bank balance. If the two don’t match, start at Step 1 and figure out where you went wrong. (Don’t be sad. Without some challenge, you don’t get that sweet rush of victory when things finally balance.)

Reconciling accounts with spreadsheets

You can either use a spreadsheet to mirror a set of handwritten books, with one worksheet for payments and another for receipts, or you can create a single worksheet for each bank account, listing payments and receipts in date order, with a running bank account balance down the right-hand side. (I talk more about both of these methods in Chapters 8 and 9.)

If you use one worksheet for payments and another for receipts, you do a bank reconciliation using exactly the same method as if you reconcile by hand (see ‘Reconciling accounts by hand’ earlier in this chapter for details). Of course, you can create a bank reconciliation as part of this spreadsheet, and use formulas to do all the adding and subtracting.

If you list both receipts and payments in a single worksheet, with a running bank balance, then use this simple approach instead:

1. Compare your bank statement against the running bank balance in the spreadsheet, line by line.

If the running balance differs at any point, find where you’ve gone wrong and fix up the mistake.

2. Go through your chequebook — if you still have one! — and identify any uncleared cheques (cheques that have been written but that haven’t been cashed yet).

3. Look at your merchant receipts or deposit book and identify any uncleared deposits (deposits that you’ve received or banked but haven’t shown upon the bank statement yet).

4. List all uncleared cheques and deposits at the bottom of the worksheet, in the same way as you list any other withdrawals or deposits.

The balance of your bank account, after allowing for these uncleared transactions, is your true bank balance. (If you’re unsure what your true bank balance is, refer to the section ‘Calculating your true bank balance’ earlier in this chapter.)

Reconciling accounts using software

Reconciling your bank account using accounting software is a pretty straightforward process. Take my (rather hot and sweaty) hand in yours, and let me show you the way:

1. Enter all transactions right up to date.

Don’t forget miscellaneous transactions such as ATM withdrawals or bank fees. Also, some software lets you import transactions straight from your bank account, rather than keying transactions one by one. If this is your bag, that’s fine by me.

2. Compare the opening bank balance of your bank statement against the opening bank balance in the reconciliation window.

With QuickBooks, you compare the Beginning Balance in the Begin Reconciliation window against the running balance on your bank statement. When you find the spot on your bank statement that matches your Beginning Balance, you’re in luck. Start reconciling from this point onwards.

MYOB software is a similar deal. Before you mark off any transactions, compare the Calculated Statement Balance in the Reconcile Accounts window against the running balance on your bank statement.

3. Enter the date you’re going to reconcile up to.

When you do your first bank reconciliation, don’t try to reconcile a whole page at a time. Instead, work in small, bite-sized chunks of a third or half of a page.

4. Enter the closing balance from your bank statement into the appropriate spot.

In Figure 11-4, you can see how QuickBooks prompts me to enter an Ending Balance for the day I’m reconciling up to. I get this balance straight off my bank statement. With MYOB, I enter this balance into the New Statement Balance field at the top of the Reconcile Accounts window.

Copyright © 2010 Intuit Inc. All rights reserved

5. Working from the bank statement, tick off every transaction.

For every withdrawal or deposit into your bank account, click against the corresponding transaction in your accounting software.

6. Look to see if your bank account balances.

All accounting software has some way or other of letting you know whether you’ve got the goods, or not. QuickBooks shows a running ‘Difference’ amount (which in Figure 11-5, you can see is 0.00); MYOB shows a running ‘Out of Balance’ amount. The aim of your game, of course, is to get this figure down to zero.

7. If you’re in luck, follow the prompts to complete your reconciliation.

When your bank account balances, you can holler with glee. You’ve just completed your first bank reconciliation. If your bank account doesn’t balance, don’t despair. I devote whole pages of the rest of this chapter to troubleshooting tricks.

Copyright © 2010 Intuit Inc. All rights reserved

Keeping Proof that You’ve Done the Deed

Every time you complete a bank reconciliation by hand, you create a report, similar to the one I show in Figure 11-3. Usually, bookkeepers write up this report in the cashbook itself, meaning that for every month of books, there’s always a finished reconciliation. The same principle applies if you use a spreadsheet — the very act of doing the bank reconciliation creates a report.

If you use accounting software, you can choose to either print a report, or not. (Have a look at Figure 11-6 for a typical reconciliation report from QuickBooks.) If you’re new to reconciling, I recommend you print a Bank Reconciliation report at the end of every statement. When you’re accustomed to the whole deal, you can get away with printing a Bank Reconciliation report at the end of each month. I like to staple these reports to the front of the relevant bank statement.

Regardless of what method you use to reconcile your bank account, to guarantee the affections of your company accountant (is this necessarily wise, you may ask?) always produce a Bank Reconciliation report as at the last day of your financial year (31 March in New Zealand, 30 June in Australia).

Copyright © 2010 Intuit Inc. All rights reserved

Troubleshooting Tricks

If your bank account doesn’t balance, don’t sweat. You wouldn’t be human if everything went right first time around. Instead, try the following:

Call on your hidden powers: How much are you out by? Does this amount ring a bell?

Check that your opening balance is correct: If reconciling by hand, check that your opening balance matches the closing balance for the previous month. If reconciling using accounting software, check that the opening balance matches against the starting point on your bank statement. (With accounting software, one of the most likely reasons for a bank account not reconciling is if someone deletes or changes a transaction belonging to a previous period which has already been reconciled.)

If your opening balance isn’t correct, the solution can be quite complicated. See the sidebar ‘Miss Marple does it again’ for some possible tactics.

Compare the magic totals: Somewhere on your bank statement you can usually find a summary of total debits and total credits. If you do a bank reconciliation for the same date range as this statement, you can match these totals against the totals in your reconciliation.

Divide by two: Try dividing your out-of-balance amount by two, and look for a transaction for this amount. In other words, if you’re out by $90, look for a transaction equalling $45. This trick helps locate transactions that have been entered the wrong way round (a debit instead of a credit, a payment instead of a deposit and so on).

Do the number nine trick: If you’re out by a multiple of nine, look to see if you put in two numbers back to front — for example, you entered 43 instead of 34, or 685 instead of 658. (It’s a curious thing, but if you turn a number back to front and subtract your result from the original number, the difference is always exactly divisible by nine.)

Look for missing amounts: For every line on your bank statement, make sure you tick off the corresponding transaction in your payments or receipts journal (if reconciling by hand or by a spreadsheet) or in your reconciliation window (if reconciling using software).

Make sure you’re in the right spot: Did you start off from the right spot in your bank statement? It’s easy to accidentally skip a page or part of a page.

Match every amount: Do all the amounts match? For example, don’t simply compare cheque numbers on the statement against cheque numbers in your books. Make sure that the amount of every cheque is the same in both places.

Start again: Untick everything then start again but this time reconcile just half a page of the bank statement.

Watch out when you’re in the red: If your bank account is normally in credit, but you’re overdrawn, be sure to show your new balance as a minus amount.

Balancing Other Kinds of Accounts

Inexperienced bookkeepers often think that reconciling the business cheque account is a noble deed (true, true) and is as far as any reasonable person should go with this whole reconciliation caper (not so true). In fact, as a guerrilla bookkeeper committed to fight for the cause, your duty extends much further. You need to reconcile every single bank account in a business: Not just cheque accounts, but savings accounts, credit card accounts, loan accounts, PayPal accounts and clearing accounts too.

Clearing accounts: A clearing account is typically a holding account where transactions come in and then go out, such as Customer Deposits Held, Undeposited Funds, Electronic Clearing Account, Employee Advances and Suspense Accounts. When you reconcile a clearing account using accounting software, you match up debits against credits, ticking off transactions when they match against one another. The statement balance of a clearing account is always $0.00. This process of elimination means that once you’re done, only transactions that don’t have a partner yet show up as unreconciled.

Credit card accounts: Reconciling a credit card account is the same as reconciling any other bank account. The main difference between reconciling a credit card account and reconciling a regular bank account is that you don’t have to account for uncleared transactions on a credit card (you simply record credit card withdrawals as they appear on the credit card statements).

Loan accounts: Get hold of the loan statement and record the amount of interest charged by the bank every month. When you reconcile this loan, every interest charge shows as a withdrawal on the loan account, and every loan repayment shows as a deposit. You can then reconcile this account in the same way as you reconcile any other bank account.

Savings accounts: Savings accounts are easy to reconcile. Simply make sure you record all deposits and withdrawals from the account, add the interest income transactions, and reconcile as normal.