One of the most frequent issue we come across is the mismatch between GL and checkbook balance.I stumbled upon a KB article worth sharing ..
Check it out.
Any of the following circumstances may cause a difference between the checkbook balance in Bank Reconciliation and the general ledger cash account.
For more information about how to correct these situations and to tie the Bank Reconciliation to the general ledger cash account, contact technical support for Microsoft Dynamics GP.
RESOLUTION/STEPS TO RECONCILE:Use one of the two methods listed below to reconcile the GL cash account balance to the checkbook balance in Bank Reconciliation:
In Microsoft Dynamics GP 2013, has been added to the routine to help automate the matching process between the GL cash account detail and the Bank Reconciliation detail. This routine will produce an Excel spreadsheet that will determine the matched, potentially matched, and unmatched transactions between Bank Reconciliation and the General Ledger for the date range and GL accounts entered. This Reconcile to GL tool can be found at:
1. Click on under , point to , point to and click .
2. Accept the default number.
3. Change the if desired. This field is informational.
4. Enter the to be reconciled. Note: It works best to reconcile a smaller date range such as a monthly basis.
5. Select the of .
6. Select the to be reconciled.
7. In the , browse out to a location to save the Excel Spreadsheet* to and a default file name will be generated (consisting of the checkbook ID, sequence number and beginning date of the date ranged entered). (The system will store this location and default it in for the next reconciliation for Bank Reconciliation, but you can override it at any time.)
*It is recommended to make a folder for your reconciliation spreadsheets, as you are allowed to save your reconciliation history.
8. Under , the default cash account defined on the Checkbook ID will default in. You can add more GL accounts if needed or override it.
9. Click .
10. An Excel spreadsheet will open displaying the items from the Bank Reconciliation table on the left side, and the GL entries on the right side. The items are listed according to sections for Unmatched Transactions, Potentially Matched Transactions and Matched Transactions. You will need to research the Unmatched Transactions to investigate why there is not a match. Reasons for unmatched items are listed at the top of this KB article.
Note: It is suggested to use this spreadsheet as an 'aid' for your regular reconciliation. Focus mainly on researching the items in the Unmatched Transactions section to help you reconcile. The balances should be taken from the Checkbook and the GL Trial balance, and not relied on in this spreadsheet.
11. Back on the window in GP, clickto save this reconciliation, if you wish to go back and view your reconciliation history it any time. You can click the button to reopen the Excel spreadsheet from any saved reconciliation.
This method has you print out a list of unreconciled transactions from Bank Rec and a list of what hit the GL cash account to compare to each other, to see what one side has that the other side does not. (This assumes you have balanced in the past and have not marked any new items as reconciled in Bank Rec since.) Here are the steps to print the lists of each side:
1. CHECKBOOK: Print out a Smartlist of the unreconciled transactions in Bank Reconciliation using the steps below:
A. On the menu, click .
B. Expand , and then click .
C. In the Smartlist window, click the button at the top.
D. In the Change Column Display Window, click .
E. In the Columns window, click . Hold down the key and also click on . Click. Both fields should now be added to the Change Column Display window. You can use the buttons in the right margin to move the order of the columns around. Use the Add button to add more columns at any time. Use the Remove button to remove any column names. Or click Default to set the columns displayed back how it originally was.
F. Click to close the Change Column Display window.
G. Back on the main Smartlist window, click the button at the top.
H. In the section of the Search Bank Transactions window, click in the Column Name box, click in the Filter box, and then type your checkbook ID in the Value Box.
I. In the section, add another restriction. In the Column Name box, select , click in the Filter box, and then select the date. Either key in the date, or use the calendar icon to select it. Note: The filter 'is less than' does not include that date. So for example, if you wanted transactions less than July 31st, you would have to enter is less than Aug 1 in your restriction in order for July 31st transactions to be included.
J. Add another restriction in the section. Click in the Column Name box, select in the Filter box, and then enter in the Value Box. (This will give you a list of all unreconciled transactions to date in Bank Rec. This should be the same list as you see in the Bank Rec window.) This assumes you haven't marked off any items for reconciling yet for the current month, or since you last reconciled.
K. Click to close the Search Bank Transactions window.
L. The Smartlist window should refresh and data should populate in the columns.
-- You can click on the title of any column to have it re-sort by that column. (Click once for ascending order, or click on it again for descending order.)
-- Click the Columns button to add or remove any columns from the display, or change the order of the columns.
M. To save this modified Smartlist as a Favorite, click the button at the top. Key in a Name, and select where you want it to be Visible To. Click , and click . The Smartlist should refresh and you should see the name of this smartlist on the left under Bank Transactions, to be used again for the future.
N. With results in the window, you can also click on , or click thebutton at the top if you would like to view the results in an Excel spreadsheet instead. (It works well to put this in Excel, so you can mark off reconciled items, or color code items as you do your matching.)
2. CASH ACCOUNT: Print out a detail report for the cash account. In GP, click on , point to and click on. Print out a detail report for the cash account for the month you are reconciling. Enter the GL cash account number, and the date range from when you last reconciled. This report will list all the transactions that affected your cash account balance since the last time you reconciled.
OR, you could print this list from Smartlist if preferred. In click on and. Click and (1) restrict to the 'is equal to' the GL cash account number. (2) Also restrict to the the first and last day of the month you are reconciling. This should produce the same list as the Detail Report in GP.
3. COMPARE: You will need to compare the two lists printed above, and cross off items that match, to determine what items are on one side that are missing from the other. This will help you to locate all the differences. Research any outstanding items to see why an entry is missing from the other side and correct as needed.
In a regular support case, we can tell you how to print the lists above, but digging through the data for you to locate the differences would be considered a consulting expense and not something we will do in a regular support case.