Open Forum

Expand all | Collapse all

Cash Account Reconciliation

  • 1.  Cash Account Reconciliation

    Posted Feb 01, 2019 01:01 PM
    ​​Hi, does anyone have any good techniques, tips, tricks to look for differences between the gl cash account and the bank statement?  The bank statement reconciles but when comparing to the GL there is a difference.  There are no non-bank transactions in the GL (not allowed).

    Any good smartlist you are using?

    Any thoughts would be appreciated.


    Dale McCormick

  • 2.  RE: Cash Account Reconciliation

    Posted Feb 01, 2019 04:38 PM
      |   view attached
    Generally we don't have this issue but on occasion I've seen a check clear for a different dollar amount that what it was issued for.  What I do is download the list of cleared checks from the bank and dump a smartlist of all the uncleared checks into Excel.  I create a file with the information of the cleared checks on the left side of the worksheet and the uncleared checks on the right (see screenshot example attached).  In column T I have a formula [ =IFERROR(VLOOKUP(S37,$F$4:$H$42,$H$2,FALSE),"") ] to look for the check # that is in column S (book data) and return the amount for that check that posted to the bank (column H).  Then in column U I have a simple formula to return the variance between the amount we issued that check for (column N) vs. the amount that cleared (column T).  Any variances would indicate the check cleared for an amount other than what it was issued for.  I use this report then to clear my checks in bank reconciliation.  Not sure if you'll find this helpful.


    Kris Elliott
    Ovation Fertility
    Encino CA

  • 3.  RE: Cash Account Reconciliation

    Posted Feb 04, 2019 08:25 AM
    @Dale McCormick,

    Your situation is exactly what the Reconcile to GL tool was made for! Starting in GP2013 you are able to use this Financial routine for Bank Reconciliation. It performs a match for you between the Bank transactions in the subsidiary ledger to the GL transactions in the account assigned to the Checkbook. It will show you Unmatched Transactions, Potentially Matched Transactions, and, of course, Matched Transactions (hopefully the largest part of the ​report). Your results will get pushed to an excel spreadsheet.

    I would start with the first month after the last time you can ascertain that the Checkbook and GL match. Unless you have a very small amount of transactions, I do not recommend running this report for more than one month at a time. If you find items in the Unmatched section, it may be obvious to you what the issue is (a lot of times it is a timing issue - check was voided on one date and the GL posting in a different month). If you are unable to discern what the issue it, there are hyperlinks on each item that appear in the report that will take you back to GP so you can investigate the items. You may get a popup warning that the link is unsafe. You can ignore that.

    This should give you a good start to finding your difference. Please let us know if you have additional questions.

    Barbara Gavron
    Atlantic Beach / Jacksonville FL

  • 4.  RE: Cash Account Reconciliation

    Posted Feb 04, 2019 09:27 AM
    1. ​If you have "GJ" trx in the GL account, review those.  To allow only checkbook trx, set up the account to not allow account entry (uncheck the Allow Account Entry box on the account maintenance window. 
    2. Other issues could be void timing.  We use a smartlist that will list GL entries during the recon month where the Void GL date <> Void date.  Void differences

    Selena Breedlove
    SVP of Finance/Controller
    US Assure Inc
    Jacksonville FL

  • 5.  RE: Cash Account Reconciliation

    Posted Feb 04, 2019 05:51 PM
    @Dale McCormick there are a few possibilities. Have you firs done the Reconcile to GL routine? This will compare your Bank reconciliation transactions to your Cash GL account(s) and help to find differences. ​Find the utility under Financial/Routines/Reconcile to GL. Be sure to choose the Bank Reconciliation in the Module drop down and in the list of accounts, include ALL G/L accounts that that particular bank account posts to. Click Process and an Excel file will be populated that will show you transactions that match between the two, those that for sure don't match and those that may match. It's a good starting point.

    As for what may have happened, I usually see this when someone in my AP department, messes up the distributions on an invoice entry (specifically if we are paying something to Cash right from the Transaction Entry window). If they try to post something to the Cash G/L account that does not have a 'type' of cash, this will result in differences between my bank rec and my g/l account. In that case, I simply void the transaction and re-enter correctly.

    You could also do a general smartlist search on the Account Transaction section and search either the debit or credit amount for the amount of your difference and hope you find it (this obviously only works if the issue is related to one error).

    Hope some of this helps!

    Tiffany Ziegler
    Vice President & Controller
    Velcor Leasing Corporation
    Madison WI

If you've found this thread useful, dive deeper into User Group community content by role