Tech Tips
Balancing Bank Reconciliation and General Ledger
Where to Compare Balances (between Bank Reconciliation and General Ledger)
There are two windows in Microsoft Dynamics GP that display both the balance of your cash account(s) in both the Bank Reconciliation module and the General Ledger module.
1. Checkbook Maintenance window
Cards ~ Financial ~ Checkbook
The two balances in the top right corner of this window represent the total ending balance in both modules. The Current Checkbook Balance is the balance that the Bank Reconciliation module has for this checkbook. The Cash Account Balance is the net balance that General Ledger has for this checkbook. Please note that both balances give no regard to periods or if a year has been closed. This means that any items dated in the future will also be included in this balance.
2. Checkbook Balance Inquiry window
The two balances in the top right corner of this window represent the total ending balance in both modules. The Current Checkbook Balance is the balance that the Bank Reconciliation module has for this checkbook. The Cash Account Balance is the net balance that General Ledger has for this checkbook. Please note that both balances give no regard to periods or if a year has been closed OR the date range entered on this window. This means that any items dated in the future will also be included in this balance.
TIP FOR THIS WINDOW: Enter the date range BEFORE you enter the Checkbook ID. If you enter the Checkbook ID first, you’ll have to wait for every transaction ever entered for this Checkbook to load before you can enter the date range.
You may also want to verify the month end balance to the GL Summary Inquiry balance to ensure they are the same. It is possible that the ending balances match, but the month end balance is off due to date errors.
Most Common Reasons for Variances in Balances
There are two common reasons why the Bank Reconciliation Checkbook Balance and the General Ledger Checkbook Balance do not match.
1. There are unposted batches in the General Ledger.
There are some transactions (e.g. Payables Management Computer Checks) that post to both the Bank Reconciliation AND the General Ledger. When these items are posted in their originating module, they automatically post in Bank Reconciliation affecting the balance; HOWEVER, depending on how you have your posting options setup, they may sit in an unposted batch in the General Ledger. This is easy to solve: Simply post your batches in the General Ledger.
2. Deposits haven’t been created in the General Ledger.
This is a similar scenario to the previous reason, but somewhat in reverse. When payments received are entered in Receivables Management, Sales Order Processing and as receipts in Bank Reconciliation, they are sent directly to the General Ledger but placed in a holding spot to be entered on a deposit. Why? For easier bank balancing (and easier bank transactions analysis). Deposits in the Bank Reconciliation module
should match the actual deposits with the bank. However, the General Ledger module may need to be setup to show the detail of receipts or set to “summary” depending upon your needs or desires. The solution: Check your Bank Deposit Entry window for any receipts not deposited.
How to Find Other Variances
What are some of the other reasons that the two would be out of balance?
Coding Error: You should never manually enter the Bank Account GL account. It will always default automatically when the Checkbook ID is referenced.
1. A General Ledger was coded to the GL account that represents the Checkbook. If you are using Bank Reconciliation, this is a BIG NO NO!
2. A Distribution Entry for a non-GL module was coded to the GL account that represents the Checkbook for a line other than CASH for the Distribution Type. Also a BIG NO NO!
Date Error: Document dates and the GL posting date span different fiscal periods.
To find errors, pull up the Checkbook Balance Inquiry window referenced above starting with the first day of the month or perhaps the first 10 days of the month and compare to the GP Report, the General Ledger Trial Balance for the same date range. Keep adding a day(s) until you find the entry that is causing the problem.
Good Documentation to Include with Financial Statements
1. Once you have confirmed that your balance in Bank Reconciliation is correct, print your Checkbook Balance Inquiry Report. Just open the window with the date range reflecting the period desired and click the printer icon in the top right corner.
2. Provide copies of all your bank reconciliation reports (including the unmarked report) for all bank reconciliations performed during the month.
I am an advocate of performing Bank Reconciliations on a daily basis. As such, the Bank Reconciliation reports will not always match up to the last day of the month. Providing your Accountants/Auditors with all reconciliation reports AND the detail of what was in in Bank Reconciliation for the month (with the month ending balance that matches the Balance Sheet) will provide excellent information and satisfy your Accountants/Auditors.
Advice from Belinda
1. COMPARE YOUR BALANCES AND BALANCE EVERY DAY!
2. Reconcile your checkbook more frequently than once a month. Do it daily for accounts with a lot of activity or weekly for less frequently used accounts. It will not take any longer than doing it once at the end of the month, and it’ll help you keep an eye on your cash balance.
3. Reconcile in GP – NOT in Excel for all accounts. I know some accounts have so little activity that some people use Excel, BUT the consistency of having all accounts in the same system is valuable. Besides, if you use GP for all accounts, you’ll be backing up your bank activity.
About the Author: Belinda Allen (Microsoft MVP) is both a Microsoft Certified Professional for Dynamics GP (MCP) and a
Microsoft Certified Trainer (MCT). She is also the co-founder of Smith & Allen Consulting, Inc. (www.saci.com), a business management software consulting firm for over 21 years. Belinda is also well known for her Blog
www.BelindaTheGPcsi.com. Her blog is about sharing really useful information about the product quickly and succinctly. She has earned the nickname GP CSI because she excels at reviewing GP problems and figuring what went wrong and why. With followers all over the Globe she is able to share knowledge and achieve her mission: “To Improve the Lives and Business Success of my Followers.”