How to reconcile the Checkbook Balance to the GL Cash Account in Microsoft Dynamics GP

How to reconcile the Checkbook Balance to the GL Cash Account in Microsoft Dynamics GP

  • Comments 2

How to reconcile the Checkbook Balance to the GL Cash Account in Microsoft Dynamics GP

Reconciling balances can be fun when it works out, but when it doesn't, users often don't know how to pull the data to compare. So it is a common support request asking how to research the differences between the checkbook balance in Bank Reconciliation and the GL cash account balance to compare.  Here are the steps to help with this issue:

1. CHECKBOOK: Print out a Smartlist of the transactions in Bank Reconciliation that are not reconciled:

A. On the Microsoft Dynamics GP menu, click Smartlist.

B. Expand Financial, and then click Bank Transactions.

C. In the Smartlist window, click the Columns button at the top. 

D. In the Change Column Display Window, click Add.

E. In the Columns window, click Cleared Date. Hold down the CNTL key and also click on Reconciled. Click OK. 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 OK to close the Change Column Display window.

G. Back on the main Smartlist window, click the Search button at the top.

H. In the Search Definition1 section of the Search Bank Transactions window, click Checkbook ID in the Column Name box, click 'is equal to' in the Filter box, and then type your checkbook ID in the Value Box.

I. In the Search Definition 2 section, add another restriction. In the Column Name box, select GL Posting Date, click 'is less than' 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 Search Definition 3 section. Click Reconciled in the Column Name box, select 'is equal to' in the Filter box, and then enter No 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 OK 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 Favorites button at the top. Key in a Name, and select where you want it to be Visible To. Click Add, and click Add Favorite. 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 Print, or click the Excel button 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 as described below:

In GP, go to Inquiry|Financial|Detail and print out a detail report for the cash account for the month you are reconciling. This will be 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 Smartlist, click on Financial|Account Transactions. Click Search and (1) restrict to the Account Number 'is equal to' the GL cash account number. (2) Also restrict to the Transaction Date 'is between' 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 it is missing from the other side and correct as needed.

For a list of reasons on why differences may occur, refer to the Knowledge Base article #864652 below:

864652   The checkbook balance and the general ledger cash account do not balance in Microsoft Dynamics GP

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;864652

In a regular support case, we can instruct how to print the lists above, but digging through the data for you to locate the differences would be considered a consulting expense.  If you are interested in a consulting engagement to help locate the differences, please contact your partner first for assistance.  Otherwise, I hope the steps above will be helpful to users to print out both sides of Bank Rec and GL to get started on finding the differences.

Thank you,

Cheryl W.

Technical Support Engineer | Microsoft Dynamics GP

  • I have seen a lot of people with reconciliation problems. The summary GL entries don't have support information, often creating more questions than answers. It can be very frustrating and extremely time consuming. I did work out a solution that works well. An add-on tool kit that lets me drill down. It took a long time to find.

  • GP2ANTONIO,

    Good to hear that you resolved your situation.  For the sake of our readers, could you share the link to the solution you used?