Making Bank Reconciliations a Snap with Online Banking

QuickBooks Online Banking feature not only allows you to easily track bank and credit card balances, but also saves a lot of time doing bank and credit card reconciliations. This feature allows users to update transactions in their account register for items that have cleared the bank.  If used properly, it can be a great timesaver and cash management tool.  If not used properly, it can be a source of frustration so let's talk about how to make it work for you.

First, the “gotchas”

I do NOT recommend downloading transactions from your bank be used in lieu of entering transactions as they occur. This practice can easily result in bounced checks and makes managing cash flow impossible.  QuickBooks will attempt to match transactions entered into QuickBooks by payee and amount. When an exact match is found the transaction is flagged as having cleared the bank automatically and that can make the process almost instantaneous. I enter credit card transactions from my receipts on a daily basis.  I recently automatically matched thirty credit card transactions in the blink of an eye.  I had neglected to record three transaction and these showed up to me manually entered and recorded as a matched.  The whole process took me about one minute.

Be careful about the date range for transactions you select to download

Be careful when selecting the date range for cleared transactions to download.  QuickBooks does not match against transactions that have been cleared in bank reconciliations. As a result, these will be included in a list of unmatched items, requiring that you manually identify them as previously cleared transactions and delete them from list of items to match.

1. Always select a date range the day after the end of the last bank statement you have reconciled in QuickBooks.
2. Avoid selecting an ending date range that is later than the ending date of your latest statement.

Watch out for erroneous booking of transactions matched by vendor naming rules

QuickBooks helps you by automatically match transactions in your register with downloaded cleared transactions, based on the amount, vendor name, and date. As you manually match remaining downloaded transactions, QuickBooks will apply new matching rules to improve matching on future transactions. For example, if you match a downloaded transaction with “Exxon Littlefield, TX” to the name “Exxon” in the vendor file, QuickBooks will automatically match on future downloaded transactions with the same amount and “Exxon” anywhere in the description. This works great, most of the time.

A problem occurs, however, when you use the same vendor for multiple type of purchases because QuickBooks assumes you will always be purchasing the same thing and will post a charge to the same accounts it matched on previously.

For example, I use Costco to purchase both gas and office supplies. QuickBooks assumes that, if it finds a match on vendor name on a transaction I neglected to enter in my books that the charge will always be for fuel.

1. Keep track of vendors from who you purchase multiple products and services
2. Run detail transaction reports on theses vendors from to assure that the correct expense or asset categories are posted for each transaction after downloading and matching transactions that have cleared your bank.

Now the tips to save time on bank reconciliations

1. Once you have the ending date of your bank statement, download any transactions that have cleared the bank between the day after the closing date of your last statement and the closing date of your current bank statement
2. Once you have completed the process of matching transactions cleared by your bank, you will probably have a small number of remaining uncleared transactions.
3. Quickly identify these items by opening the account register and change the sort order to “cleared status".
   
4. Only transactions that do not have a check mark or lightning bolt in the “CLR” column should be uncleared on your bank statement.. This is usually a small fraction of the transactions that have cleared. Make note of these transactions manually or run a transaction detail report and export it to EXCEL to you can refer back to it during the reconciliation process.
5. When you begin the reconciliation, select “Mark All” and then de-select the items that you have noted as not cleared. This can save you having to individually match and check a large number of transactions as opposed to checking all and un-checking a small number of transactions

Robert Guild is certified QuickBooks ProAdvisor in Austin, TX who conducts CPE courses for CPAs and individual training and group classes to QuickBooks users.  His company Accounting Insight maintains a sixteen station QuickBooks lab, providing hands on training.  You can contact him directly at rguild@accountinginsight.biz or follow him on twitter at QBPro