Skip to main content

Notifications

Announcements

No record found.

Business Central forum
Suggested answer

Bank Reconciliation is not balancing

Posted on by 905

 

 

Hi

 

I’m trying to figure out how to use the bank reconciliation if the option in GL setup “Bank Reconciliation auto match is turned off”

Mainly I think I m having an issue with getting the first reconciliation to balance because I m not sure how to reflect last reconciled balance

For example, I have a bank account 11603 that is linked to GL account 11603

In the opening balance transaction, I entered the balance of the GL account as of the TB to be $200

So now the GL balance of 11603 is $ 200

I Entered 2 cash receipts of $80

According to the last reconciliation that was done outside of D365 the reconciled balance was $100

The new reconciliation which will be the first Reconciliation in D365 is 180 assuming

But after I mark both deposits, I still have a difference of 100 $

Not sure why the reconciled balance has to match the GL balance , they are 2 different things

Also, where do I put the last reconciled balance that I did before doing the bank Reconciliation in D365 for things to balance

Thanks

Ola

 

 

Recon1.png

Recon2.png

  • Suggested answer
    RE: Bank Reconciliation is not balancing

    Hi Ola,

    Thank you for getting back to me with this detailed explanation.

    Here are the steps that I would suggest for getting started with this Reconciliation. You stated that at the time of your cutover as of 1 January 2017, you had a G/L Account balance of 1000 CAD. Additionally, you have five outstanding cheques which have not yet cleared the bank.

    Because we need to allow for these cheques to clear in the future, we do need to record these cheques within D365Fin. In the process of recording these cheques, the Bank Account and G/L Account balances will be reduced by 500 CAD. So I would advocate that the beginning balance posted to the G/L Account and Bank Account should be 1500 CAD--this also corresponds with the Last Reconciled Balance on 31 December 2016.

    1. The first step is that I believe that we have a discrepancy in the balances which should have been posted to the G/L Cash and A/P Accounts. These account balances are presently understated by 500 CAD because we still need to enter and post the outstanding transactions. The entries taken from the original trial balance should have been adjusted for the outstanding transactions. A General Journal should be posted which will be:

    Cash G/L Account     500         (bal) A/P G/L Account

    The resulting entry will:

    debit     Cash G/L Account    500

    credit    A/P G/L Account                500

    Now the balance in the Cash Account in the G/L is 1500 CAD.

    2. The next step is to get the Last Reconciled Balance into the Bank Account. We will accomplish this via a General Journal. The entry will look like this:

    Bank Account          1500        (bal) Cash G/L Account

    The resulting entry will:

    debit   Bank Account       1500

    credit  Cash G/L Account               1500

    The effect of the above posting on the G/L will be a wash as the same Cash G/L Account will be both debited and credited.

    3. We need to enter and post the outstanding cheques (that will be step 4 below), but we need something to apply these cheques to. This means that we need to post some invoices for the vendors. But we don't want to affect the G/L Accounts for Purchases or A/P. So we will ensure that the effect on G/L is a wash. To do this enter General Journals like these:

    Vendor 1                 -100       (bal) A/P G/L Account

    Vendor 2                 -100       (bal) A/P G/L Account      

    Vendor 3                 -100       (bal) A/P G/L Account

    Vendor 4                 -100       (bal) A/P G/L Account

    Vendor 5                 -100       (bal) A/P G/L Account

    The effect of the above will be a wash as the same A/P G/L Account will be both debited and credited.

    4. Enter and post the outstanding cheques using the Payment Journal. There should be a line for each of the five payments. You should ensure that each of the payments is applied to one of the above invoices. Here is what I entered on my payment journal:

    Vendor 1                  100       (bal) Bank Account

    Vendor 2                  100       (bal) Bank Account

    Vendor 3                  100       (bal) Bank Account

    Vendor 4                  100       (bal) Bank Account

    Vendor 5                  100       (bal) Bank Account

    The resulting effect on the G/L will be:

    debit       A/P G/L Account (via vendor accounts)          a total of 500

    credit      Cash G/L Account (via Bank account)                           a total of 500

    5. Finally, we are ready to do the Bank Account Reconciliation. We should have everything in place to do the reconciliation as of 31 January.

     a. Enter the Balance on Statement as 1200 as from the bank.

     b. Suggest Lines to populate the lower sections.

     c. Mark the three cheques which have cleared.

     d. Print the Bank Rec. Test Report. The balances should match and you will be able to post this reconciliation.

    I know that this is involved. Please let me know what further questions you may have.

  • Ola Darwish Profile Picture
    Ola Darwish 905 on at
    RE: Bank Reconciliation is not balancing

    Hi Tammy

     

    Thank you for your response

    I meant that this will be the ending balance on the bank statement.

    I meant that the GL account balance is not related to the reconciled balance because you can issue 5 cheques from your chequebook which makes your balance on your GL 500CAD (assuming the GL balance was 1000 CAD and each cheque is 100 CAD

    But none of the people you issue the cheques for goes to the bank and cash out those cheques

    That will make your GL balance 500 CAD, whereas your reconciled balanced still 1000 cause the bank have no idea that you issued those cheques yet.

    I think I didn’t do a good job in explaining my problem so let me explain in more details

    If my client is moving from system XYZ to D365

    Assumptions :

    • They have one chequebook that is linked to one GL account

    • Their Cut over date from Xyz to D365 is 1st of January 2017

    • Their Bank GL account balance is 1000 CAD

    • The 1000 CAD is composed of the following transactions

      • 2000 CAD Cash Receipt

      • 10 Cheques issued to Vendors, each one is 100 dollars

      • 5 of those cheques are cash out and they showed up in the last bank Reconciliation that they did in system XYZ , so their last Reconciled bank balance as of 31 December 2016 was 1500 CAD, so they still have 5 Unreconciled cheques that might come in the next bank statement that they will reconcile in D365

      • The GL balance of the account that is linked to the bank in D365 will be entered to D365 as part of the Trial balance as a Journal Enter and it will be 1000 CAD

      • So I have a few questions

          • How can I enter the 5 unreconciled cheques that might come in the bank reconciliation of 31st of Jan 2017 without affecting again the GL balance to avoid Duplication?

          • Where can I say that my last Reconciled balance with the bank as of 31st of Dec 2016 was 1500 CAD . or I don’t need this piece of information to be entered at all ?

          • How will it balance if the statement of 31st January is 1200 CAD assuming only 3 cashed out their cheques and no other bank transactions happened in January for simplicity

          • So if I try to Reconcile Jan 31st in D365, I think I will see the following ; GL balance 1000, the bank statement ending balance is 1200 CAD which is the balance that I will enter in the statement balance at the top right side.

     I think my problem is only getting the first reconciliation to balance , because from that point, I think it will flow smoothly

    Thanks

    Ola

  • RE: Bank Reconciliation is not balancing

    Hi Ola,

    Thank you for the explanation of what you are seeing and the supporting screen shots. I do have one question. It seems like you didn't quite finish your thought on this sentence: "The new reconciliation which will be the first Reconciliation in D365 is 180 assuming". Is there more to that statement? Is the ending balance on the statement 180, or is the net change of the statement 180?

    Also, I wanted to respond to your statement, "Not sure why the reconciled balance has to match the GL balance, they are 2 different things". I do understand where you are coming from, however, the Bank Rec. Worksheet in Financials makes the assumption that you have a single G/L Account associated with this single Bank Account. That appears to be the case for your situation as well as you have taken care to even number them identically. When entering and processing transactions in Financials, the G/L Account should always be affected (indirectly) through the Bank Account. When that occurs, the changes in the two entities can be expected to match--and in fact the balances should match as long as we can get them to match initially.

  • ChrisDesRo Profile Picture
    ChrisDesRo on at
    RE: Bank Reconciliation is not balancing

    Hi !

    I have the same problem here; I have to finish my bank reconciliation for June 2017 and I cannot finish because I have difference and don't know how it is possible !

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,420 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,672 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans