Chapter 10

Reconciling Accounts

In This Chapter

Calculating your opening bank balance

Getting your first bank reconciliation off the runway

Maintaining 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

In this chapter, I talk about the process of comparing the closing balance in your set of accounts against the closing balance for the same date on the statement from your bank. Accountants and bookkeepers call this process reconciling your bank account.

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

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 …)

Note: Xero — rather strangely to my mind — uses the term reconciling to refer to the approval, matching and entry of transactions. In this chapter, I explain how to take this process a step further using Xero, and independently verify account balances.

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 few bank accounts, including savings accounts, PayPal accounts, 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 may 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.

• If you never write cheques, and you don’t receive payments from customers via EFTPOS, the true balance of your bank account is simply the balance as per your bank statement. Easy.
• If you still occasionally write cheques for suppliers, the true balance of your bank account is equal to the balance as per your bank statement, less the value of any cheques that you’ve posted that haven’t been banked by the supplier yet.
• If you receive payments from customers by EFTPOS, the true balance of your bank account is equal to the balance as per your bank statement, plus the value of any merchant deposits that haven’t cleared into your bank account yet.

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 a supplier, 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 10-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, dig out June’s bank statement. Write down the closing statement balance as at 30 June.

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 any deposits 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 or later.

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 10-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 preceding section), 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 Opening Balance Clearing.

   If you have no uncleared payments or deposits, you don’t need to do this step, because you have no uncleared transactions.

2. As the opening balance for your bank account, enter the balance as per the bank statement

   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 10-1, I enter $1,500 as the opening balance for my bank account.)

3. Enter a separate transaction for each uncleared withdrawal, allocating to your Opening Balance Clearing account.

   Using the example in Figure 10-1 again, I record a transaction for $200 (the amount of the uncleared cheque) and I allocate this withdrawal to the Opening Balance Clearing account.

4. Enter a separate transaction for each uncleared deposit, again allocating to your Opening Balance Clearing account.

   Still referring to Figure 10-1, I record a transaction for $1,200 (the amount of the uncleared deposit) and allocate this deposit to the Opening Balance Clearing account.

All done? With opening balances for your bank account entered, you’re ready to reconcile your bank account (see the next section 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 Opening Balance Clearing account and into your regular bank account.

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

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.

Keep the following in mind when performing your first reconciliation:

• Most desktop accounting software (such as MYOB AccountRight and AccountEdge, and Reckon Accounts) requires you to mark off each transaction individually as part of the reconciliation process. See ‘Reconciling accounts when you’re not using bank feeds’ for details.
• For accounting software that has bank feeds enabled, the process of approving, matching or adding a transaction for each line on the bank feed automatically ‘reconciles’ the statement by marking the transaction as cleared. However, you still need to double-check this process by making a manual comparison between the balance as per your accounting system, and the balance as per your bank statement. See ‘Reconciling accounts when you have bank feeds’ for more details.
• If you use a spreadsheet, reconciling simply involves matching the running balance of the spreadsheet against your bank statement. See ‘Reconciling accounts with spreadsheets’ for details.

Reconciling accounts when you’re not using bank feeds

As I explain at the beginning of this chapter, you only mark off transactions and reconcile accounts after you’ve recorded all banking transactions such as employee pays, supplier payments or customer receipts.

Reconciling your bank account using accounting software is a pretty straightforward process. Grab your most recent bank statement, or log onto internet banking and print a statement. (Note: Take care to print a statement, rather than a transaction listing, because you want to be able to view the opening and closing balance, as well as a running balance.)

Then take my (rather hot and sweaty) hand in yours, and I’ll show you the way:

1. Enter all transactions right up to date.

   Don’t forget miscellaneous transactions such as ATM withdrawals or bank fees.

2. Go to the reconcile function in your software.

   In the desktop versions of MYOB, you go to the Banking command centre and click Reconcile Accounts. In Reckon Accounts, you go to Banking and then click Reconcile.

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

   The desktop versions of MYOB all work in a similar way. Before you mark off any transactions, compare the Calculated Statement Balance in the Reconcile Accounts window against the opening balance on your bank statement.

   With Reckon Accounts, 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.

4. 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 at a time.

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

   So, if you decided to reconcile your bank account up to 15 August, you enter the closing balance as at 15 August. With MYOB, I enter this balance into the New Statement Balance field at the top of the Reconcile Accounts window. In Figure 10-2, you can see how Reckon Accounts prompts me to enter an Ending Balance for the day I’m reconciling up to. I get this balance straight off my bank statement.

6. 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.

7. 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. Reckon Accounts shows a running ‘Difference’ amount; MYOB shows a running ‘Out of Balance’ amount. The aim of your game, of course, is to get this figure down to zero.

8. 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 at the end of this chapter to troubleshooting tricks.

Reconciling accounts when you have bank feeds

When you have bank feeds enabled in your software, the very process of reviewing transactions (and approving, matching or adding them) is the equivalent of marking the transaction as cleared or reconciled. (Indeed, Xero even refers to the process of reviewing and allocating transactions as reconciling your bank account.)

Don’t let this terminology hoodwink you into thinking that you really have reconciled your bank account. Just because you click off every transaction from the bank feed menu doesn’t mean that your bank account is reconciled, even if you’re working with Xero and the dashboard actually says your bank account is reconciled. Bank feeds are not 100 per cent accurate — in fact, at the time of writing, the Yodlee service provider (which provides bank feeds in some instances for Xero) says it has a 99.8 per cent accuracy. Sounds okay, but think deeper — 99.8 per cent accuracy translates to one error for every 500 transactions.

Don’t panic — all you have to do is double-check that the closing balance at the end of each week or the end of each month reconciles. In MYOB AccountRight, for example, go to the Reconcile Accounts window. If you’ve processed all transactions from your bank feed, these transactions should all be marked as cleared. Simply enter the closing balance from your bank statement for the end of the month in the top-right corner, check that the Out of Balance amount is $0.00, and click Reconcile. In Xero, you take a different approach, comparing the bank balance as per your Bank Reconciliation Summary report against your bank statement. Investigate any uncleared transactions and ensure the balance on this report matches to the cent against your bank statement.

Reconciling accounts with spreadsheets

In Chapter 8, I explain how 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 side. If you list both receipts and payments in a single worksheet, with a running bank balance, then use this simple approach to reconcile your accounts:

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 up on 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.)

Keeping Proof that You’ve Done the Deed

Every time you reconcile a bank account, I suggest you keep a record of your work. For example, Figure 10-3 shows a Bank Reconciliation Summary from Xero, listing the beginning balance of the bank account, any uncleared transactions, and the ending balance.

I recommend you generate a bank reconciliation report at the end of each month. I like to either print and attach these reports to the front of the relevant bank statement, or save an electronic version in a folder called Bank Recs.

Here are some things to remember when generating bank reconciliation reports:

• When working with bank feeds software, you need to verify the closing balance on the reconciliation report against the closing balance that your bank shows for the same date. This is the only way you can guard against possible inaccuracies in bank feeds.
• If your bank reconciliation report lists any uncleared transactions that are more than seven days old, chances are these transactions are errors or problems. You need to investigate each and every one of these troublemakers.
• Guarantee the affections of your company accountant (is this necessarily wise, you may ask?) and always produce a Bank Reconciliation report as at the last day of your financial year (31 March in New Zealand, 30 June in Australia).

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: 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 that 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 duplicate transactions: If you’re using bank feeds, it’s possible that an error in the bank feed process can cause a transaction to be imported twice.
• 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.
• Start again: If possible, unclick everything and then start again, but this time reconcile just half a page of the bank statement. Similarly, some software allows you to undo previous reconciliations so you can go back in time to when the bank last balanced. (However, be very careful before starting to undo bank reconciliations. If you’re not, you can make your problem worse, not better. Always do a backup first and follow the recommended procedures.)
• 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 business bank accounts, but savings accounts, credit card accounts, loan accounts, PayPal accounts and clearing accounts too, as follows:

• 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. Unless you’re using browser-based software (which generally doesn’t have a ‘ticking-off feature’) the best way to reconcile a clearing account is to use the reconcile accounts feature to mark off debits against credits, ticking off transactions when they match against one another. The statement balance of a clearing account should always be $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.