This is Part (II) of two parts, because the original post seemed to be getting long. In Part (I), I had tried to identify some of the common difficulties that can arise and problems that can stir up during the bank reconciliation process. In this part, I will discuss ways in which I overcome each of those situations and provide you with some tips to help make reconciling your banks and cash accounts easier.
Reconciling – Computer Cheques
Firstly, I ask them to sort the cheques in numerical order when they get the cancelled cheques with their bank statement. Lately, some banks and credit unions do not return the cheques, and instead just include microfiche copies of cancelled cheques that cleared. I find that these are displayed in numerical order already. This is one of the ways the client can reduce their monthly accounting bill – by saving me time by not sorting out these cheques.
Currently, I do not print any computer payable cheques for my clients. I used to, but not anymore. If my client has an Accounts Payable module and is setting up payables and printing computerized cheques, I like to obtain a copy of of their computer data and retrieve the information myself and post it directly into my accounting system. If they are posting it into their own system, I instruct them to not consolidate the transactions. I would rather see each check issued in the General Ledger accounts than a summary. There is a cost savings at time of Year End seeing all of the details .. because it’s faster to browse the G/L than go through tons of drawers of posting journals and transactions. I know I might be printing extra sheets of paper.
For computer cheques .. I request that an additional copy of the printout. I then make a 3rd photocopy for my monthly or bank reconciliation file. When I am comparing cheques that cleared the bank to the list, I take a black pen and carefully stroke out that cheque on the photocopy and keep it forward with all the future bank recs until all cheques have cleared the bank.
Reconciling – Manual Cheques
When I enter manual cheques into the computer, I use the “Source Code” (2 characters) to designate the type of bank it is. For instance .. “ck” for their main current account, “cq” for their second chequing account, “sv” for savings account, “ch” for third chequing account, “us” for all USD account transactions, etc etc.
For outstanding cheques .. I use the source code “os” instead of “ck” .. (or “oc”, “oq”, “ou”, etc depending on which bank account it relates to). This way – I can generate a report and that lists all transactions for a specific source code .. and can get a list of outstanding cheques at month-end. I use this report for the bank reconciliation. It sure saves me time.
Reconciling – Blank Cheque Stubs
I want to be sure that I input all of the cheques. Generally, if you are entering data directly from the cheque stubs, it is easy. But, if you are entering cheques that cleared the bank .. you need some control over missing cheques. Especially at the year end, as there may be cheques issued but outstanding. When I’m entering cheques from the cheque stubs and see a blank cheque or voided cheque – I still enter those cheques with the cheque number into my suspense account.
I am able to generate a report by source code and by reference order. I’m pretty consistent with numbering. For instance, if the cheque number are 4 digits, I won’t forget to input 0998, 0999, 1000, etc. Because as you know – when numbers are sorted … 0999 comes before 1000 … but, 1000 comes before 999 …(sorted alphanumerically from left to right).
Reconciling – Computerized Payroll
You don’t want to play around with payroll records. You want to be sure that the T4’s are prepared correctly and reconciled on a calendar basis to the G/L figures. With all services like Ceridian Payroll Service (or any other payroll service or even your in-house computerized payroll) .. there is a summary journal entry created and I recap it in my monthly Excel spreadsheet. In my chart of accounts – I keep separate accounts for the gross wages, company CPP and company EI expense. For cheques for sub-contact work or casual work with no deduction, I keep separate G/L accounts for them as well.
My journal entry ends up posting the net payroll cheque total to the current liability section, in an account called “Accrued Wages”. When the cheque clears the bank, I credit the bank and debit the accrued wages account. Alternatively, I may set up an account in the wages expense section called “Payroll Cheques Clearing” and run them through there instead.
Reconciling – Pre-Authorized Payments
There are three types of pre-authorized payments that usually go through the bank accounts .. (1) fixed and repetitive payments that never change month in and month out (2) automatic payments that are withdrawn out of the bank that change every month and (3) fixed and repetitive amounts, like loan payments that are the same each momth.. but the interest and principal amounts change each month.
For the fixed payments, I just recap it on a sheet kept “Top of File” in my monthly folder, for the G/L codes and break down of expense and GST ITC. For loan payments that are not broken down by interest and principle or with credit unions included on the main members’ credit union account .. I create a monthly estimate using my amortization program to separate the interest breakdown. At the year end, I will always confirm these balances anyway – so, if I’m out – I will adjust it later.
Reconciling – Transfers Between Accounts
When my clients have multiple bank accounts, I try to create G/L account numbers that are not consecutive and maybe separated by two digits. For instance, the CDN chequing account may be 1010 and the savings account may be 1012 and the USD savings account maybe account 1014. If these were all TD Canada Trust accounts .. I would also create a new account 1011 and call it “TDCT Transfers”. When there is a transfer from chequing to savings – my journal entry would be:..debit 1011 and credit 1010 . When I am recording transactions in the savings account and see the transfer .. my journal entry would be debit 1012 and credit 1011. At the end of the month or year the net balance of 1011 should be zero.
For transfers between CDN and USD accounts .. i do the same thing, but run it through the income statement. I might have an account 4011 called “Foreign Exchange Gain (Loss)” and an account 1015 USD savings account – “Exchange”. All transfers between CDN and USD run through this 4011 account, and at the end of the month I physically calculate what the exchange should be to adjust the bank based on the month-end exchange rate. I just use the Bank of Canada’s Foreign Exchange to calculate what 1015 should be. The difference is adjusted to 4011.
Reconciling – Debit Card Purchases
Debit card purchases are great for individuals, but sometimes a nightmare for us bookkeepers when businesses use them all the time. To save ME time and THEM money .. this is how I try to handle it (scare tactics but it works).
I charge everything to the shareholder as a drawing. When I get the receipts, I will credit them. If they don’t provide me with receipts – I’ll write it off – as a wage to the owner. To avoid being T4’ed for shortages of legitimate company expenses .. I just suggest that they keep a pen in their pocket or car and just write what the expense is all about (if it’s not obvious) and then put the receipt in an envelope. At the end of the month – send me the envelope of receipts. It’s much easier to do a quick tape job of all meals, office supply purchases and other debit card purchases. The client can either just pay with the debit card or take a larger cash withdrawal (still charged to the shareholder as a drawing) and pay the bills by cash.
Reconciling – Daily Cash Deposits
For some clients .. creating daily deposit recaps works best. For other clients, I might just use a “cash on hand” (or clearing) account. As long as all the incoming cash is accountanted for, to record receipts on accounts receivable, cash sales, and other income type of deposits (commissions received) or other type of non-income refunds (GST refund perhaps). In the latter case, for bank reconciliations, there would be no outstanding deposits as it would be reflected in the Cash On Hand account. My journal entries would be something like debit cash credit accounts receivable (for monies received) and then offset it from the bank statement debit bank and credit cash (for monies deposited). Usually, I would also run the cash expenses through this “cash” account, depending on what I would be doing with the previous topic – debit card purchases.
Reconciling – Credit Card Deposits + Third Party Deposits and Withdrawals
Businesses that accept credit card payments and debit card payments are confusing, when it comes time to reconcile the bank. The best way is to not reconcile your bank – reconcile each of the individual credit card deposit accounts. For instance, AMEX gives their own monthly statement of transactions. MONERIS gives their own monthly statement of transactions. There maybe accounts like DIRECT CASH for ATM transactions. Sometimes, there really are no bank statements for these credit card or cash management companies, but you should be able to access the information online and print out a summary of the transactions for the month. That is how I do this.
Reconciling – Utility Bills
For the utilities, I actually request that my clients just include all the bills that go through the bank when they give me their monthly stuff. It’s just easier that way and then I can correctly pick up the GST. For those clients that do not want to do this, I do sometimes estimate the GST. I have a dummy Excel spreadsheet open and create a little calculation to divide a number by 1.14 (now 1.13) then multiply by 1.07 (now 1.06) to get the expense. Of course, the difference will be the GST.
Reconciling – PTB and Cash Withdrawals
I touched on this earlier in conjunction with the debit card purchases. I just charge (debit) these withdrawals to the shareholder.
Reconciling – Weird Cut-off
Remember that when you are doing monthly accounting, you are supposed to record all the transactions. It should not make any difference reconciling the bank G/L balance to a balance on the bank statement at any point in time. If you need a refresher course, check out my Concept of Reconciliation post.
Stuff For The Taking
I have modified a basic template that I use for my more ~complicated bank reconciliations .. that I also use to assist me in the preparation of monthly accounting and process.
You are welcome to take this template and use it for your own purposes. You are also free to pass it along (because itâ€™s not that big of a deal), although I hope that you remember where you got the idea and give me credit. Maybe stroke my EGO here, and give me a blog comment to let me know that you have taken this template of mention it somewhere on your blog and link back to this URL. If this really helps you a lot .. maybe you might consider joining HART’s Coffee Club? (See sidebar).
I am currently using Microsoft Excel for Windows 95, version 7.0 as my preferred spreadsheet program and version. You can change the text of anything I use to suit your own needs .. it is just a guideline.
HARTâ€™s 2006-PAYROLL RECAP ON EXCELâ€”-> OPEN to view, SAVE to Disk ..
Stuff for the taking: http://1800HART.com/stuff/1800HART.com_CLIENT_Monthly_Recap_2006-08.xls