In this blog we explore the components of doing a bank reconciliation in Dynamics GP. We’ll discuss the different types of transactions, how to post them through, and what the bank reconciliation looks like when completed.

Table of Contents

• Source of Transactions
• Inside the Bank Reconciliation
• The What, When, and How of Bank Reconciliation
• Bank Reconciliation Process
• Creating a Transaction Batch
• Checking off Entries
• Reconciliation Reports
• Adjusting Bank Balance
• Comparing Bank Reconciliation to General Ledger
• Electronic Bank Reconciliation
• Balancing to your General Ledger
• Voiding Transaction
• Multicurrency

Source of Transactions

Sources of Transactions come from all over Dynamics GP, but the place we see most comes from accounts payable and accounts receivable. Accounts payable, for example, would be all cheques that are issued to a chequebook. Accounts payable is off the transaction entry screen, if you’re doing a purchase order deposit, a check run, or a manual check, it will automatically push them through to the chequebook so that you can mark them off.

Each cheque gets issued and then entered into the chequebook individually so that you can mark them off one by one as they clear the bank. The voids are removed from the bank reconciliation, but it’s important for you to watch your dates. If you backdate a transaction on the chequebook register, but not in the general ledger posting date, you can throw your general ledger to bank reconciliation out of balance.

There are a couple of ways of getting them in on the accounts receivable side, for example, cash receipts and sales order processing. When you put the cash receipts in, they all go into the deposits, and then from the deposits you can create a deposit, which then shows up on the bank reconciliation.

There’s a toggle in GP that allows you to directly post a whole batch, and have it create a summary transaction on the bank reconciliation. If you’re doing deposits daily and all of the cheques come into accounts receivable, and you process them all in one batch, that batch total equals the amount you’re taking to the bank. You can use that to post directly through without having to take a secondary step, which would be to create a bank deposit.

Inside the Bank Reconciliation

Inside the bank reconciliation we have bank transactions, bank transfers, and deposit entries.

Bank transactions involve increased and decreased adjustments. Things like lease payments, interest due, and service charges are all done in bank transactions.

Bank transfers are unique in that ordinarily a single transaction hits one bank account. The only exception to that is a bank transfer. The transfer allows you to transfer money from one bank account to another bank account, hitting both bank reconciliations at the same time. The increase and the decrease are done individually, they are then posted into both bank reconciliations. It shows as a single transaction in the general ledger, but it will hit both bank reconciliations.

Deposit entries are important because for example, you may get 15 cheques in a day, and then you put them on a deposit that you take into the bank. When you get your bank statement back from the bank, it shows one number for that day. You need to accumulate those pieces into a single number for your deposit. That’s what the deposit entries are about, to create the bank deposit that you took to the bank and gave the teller or dropped-off.

There’s also a miscellaneous payment called a miscellaneous cheque. Where you can enter and print a cheque directly from the banker. This does not require a vendor. The limitation on miscellaneous cheques is that it is functional currency only. Bank reconciliation itself is multicurrency aware, which will be discussed later.

Inside miscellaneous cheques, you can enter your Chequebook ID and fill out your other information and you can print the cheque in the format that’s listed. This format is not the same as the format for your accounts payable transactions and needs to be modified to match your cheque specs.

The What, When, and How of Bank Reconciliation

What’s Involved in Bank Reconciliation?

Deposit Creation

Deposit creation allows us to clear transactions from a list so that we end up with an outstanding list at the end of the month. For example, at the end of the month, we’ll say, “These 35 cheques did not clear the bank, these cheques did clear the bank.” You’ll check off the ones that cleared the bank and from there you’ll have a list. It allows us to create deposits so we can match them to the bank statement, and then we can auto-create using the bank documents previously mentioned.

Transaction Entry

Transaction entries involve current entries such as lease and rent payments that may be pre-authorized payments that you’re not entering into accounts payable. We must pay our service charges, and interest, either expense or revenue, depending on whether you carry a positive or negative balance on the bank.

Transfers

Transfers allow us to move money between chequebooks, hitting them both at the same time. It will automatically adjust the multicurrency in most transactions.

Miscellaneous Payments

Lastly, miscellaneous payments allows the printing of a cheque without a vendor, and only works in functional currency.

When are Bank Reconciliations Completed?

Most smaller firms do their bank reconciliations monthly. At the end of each month, they get their bank statement and do their bank reconciliation based on that information. Some people may do it more frequently than others. You can do it at whatever periodic time you wish to do, and you can run the same bank reconciliation for the same period repeatedly so that you can always look at it.

How?

Items are checked off using the Bank Reconciliation feature. This feature brings up a list of all the outstanding transactions, and you check them off one by one, or by groups, depending on your preference.

Bank Reconciliation Process

Let’s look at the process of doing a bank reconciliation, creating batch transactions, checking off entries, how to read reconciliation reports, and comparing to the general ledger.

Begin by navigating to a chequebook and enter your Chequebook ID:

Once you’ve selected a Chequebook ID, it will be applied to the bank account:

All the transactions that you do will now go through this chequebook. As we hit these bank reconciliations, these transactions will automatically be posted through.

Creating a Transaction Batch

When creating a transaction batch, you’re going to navigate to ‘purchasing’ > ‘build batch’ > ‘build payment batch’ you can then enter your Batch ID:

You’ll be directed to Payables Batch Entry, and you must specify your bank account in Chequebook ID and press ‘save’:

Now when you create a chequebook, you will see the designated Chequebook ID:

Therefore, any cheques you push through here will show up inside your Reconcile Bank Statement:

Hitting the transactions button in the reconcile bank statement will open all your transactions under your selected chequebook:

Alternatively, you can go into Payables Transaction Entry:

You can enter all of your information here, post, and this particular transaction will show up on your bank reconciliation.

For it to show on the bank reconciliation you want to enter your Reconcile Bank Statement transactions, you can drill into the cheque number and you can see the cheque you’ve just issued:

Inside this cheque, you can look at the distribution and what you’ve applied it to.

Checking Off Entries

In your selected chequebook, you can start to check off the loans, deposits, and cheques that have been cleared and leave balances that are outstanding:

Your difference will be displayed at the bottom of this screen. Compare this to your ending balance in your bank reconciliation to verify it has balanced to zero. When this balance goes to zero, you can reconcile.

Reconciliation Reports

When you reconcile, you can print a few different things. Hit the print button at the top of your chequebook and you can select to print reports and lists:

The Reconcile Edit List displays your bank statement and the balance, outstanding cheques, and your adjusted balance. If the balance is equal to the cut-off, they should all be balanced all the way through. That would then allow you to say that it’s a balanced bank reconciliation.

There will only be adjustments in the Bank Adjustments Edit List if you were to input adjustments.

The Marked Transaction Report is the most important of the four. This is where you’ll see what you’ve marked off, they should have occurred on your bank statement and in your system.

The Outstanding Transactions Report lists the outstanding cheques. Cheques that have been issued but have not yet cleared the bank will appear here. It’s possible to have deposits there that crossed the month, for example, say I put the deposit in on April 30^th, but in fact the deposit didn’t hit the bank until May 1^st because it was deposited after 3:00pm on the 30^th of April.

Adjusting Bank Balance

If you have things that you know belong somewhere, you can do an adjustment. Your adjusted bank balance should then equal your adjusted book balance. This should be what you have in your general ledger once everything is posted through. This adjustment is how you can reconcile, you will not be able to hit reconcile until it is balanced.

It’s important to note that you cannot change the default transaction from interest income to other income, you will have to change the transaction each time you make an adjustment.

Comparing Bank Reconciliation to General Ledger

The third balance you need to do is compare it to your general ledger. Your general ledger is what will show up on your financial statements, your bank reconciliation does not necessarily do that.

If you are missing some deposits and need to find out where they went, go into your financial module, and navigate to bank deposits. You will need to enter your deposit date and Chequebook ID to populate a list of the open deposits:

From here, you can check off the cheques that have been deposited. You can then process this and post the entries. No transaction will occur when this is done unless you’re using multicurrency.

Back in your bank transactions, you can view your uncleared deposits. These should equal what’s on your bank statement. You can check off those deposits and click ‘adjustments’:

You can then enter your bank balance again in Reconcile Bank Adjustments:

You can do whatever you need to inside your chequebook to get the right additional lists. What you’ll be most concerned about is the Reconciliation Edit List and the Outstanding Transactions Report.

Electronic Bank Reconciliation

There is an electronic version of doing bank reconciliations. If you have large numbers of cheques that get processed monthly, ticking and checking them off repeatedly is a lot of work.

Electronic bank reconciliation is included in Dynamics GP, and the bank files can be used and imported directly from a BAI2 file, directly into the bank reconciliation. It will check off your cheques and deposits that match. It will check the control number for the cheque as well as the amount. It will look at the amount of deposits but note that the date must be within five days of the actual date because it knows that it’s possible that you deposited on the 28^th, but the bank didn’t process it until the 29^th.

It’s important to know that deposits that come through from sales order processing will automatically flow through to your bank reconciliation, as will your deposit slips as you create them for the prepayment.

Balancing to your General Ledger

There is a Reconcile to GL function inside Dynamics GP for routines. Within this function you have a choice of payables, receivables, inventory, and bank reconciliation:

Selecting the Bank Reconciliation module will open your chequebook and selected date range, it will bring up all your equal transactions. Clicking ‘process’ in the top right corner will generate an Excel spreadsheet that indicates potentially matched and unmatched transactions:

You may notice that there are no transactions on the right-hand side. In this case you can go into the Series Posting function and you may see that payables transactions and bank transactions have not been posted through your general ledger, it has only been posted directly to it. You’ll want to select those transactions and select ‘mark all’ and then post your desired transactions.

It’s important to note that if you do a journal entry to the bank reconciliation, your bank will not balance to your general ledger.

Remember to save your changes in the Reconcile to GL function and you can go back into your bank reconciliation module to view an updated Excel spreadsheet with a list of all the transactions that have gone through:

Some may be unmatched because they were just posted, because there’s a deposit that’s missing, or because they are cash receipts. They appear to be unmatched simply because of the way they’ve been posted, but your general ledger and bank reconciliation should still be balanced.

Voiding Transactions

When you void a transaction and go into the void historical transactions function, your posting date will affect your general ledger and the void date is when it affects your bank reconciliation. If the two dates are not in the same period, your bank will not balance to the GL:

Your bank reconciliation will not balance because the two dates are not in sync, and it will tell you whether they’ve been reconciled or not. So, when you do a void, make sure you take note of the void and posting dates, because if you don’t, they will cause you lots of grief on your bank reconciliation.

The same is true for the deposits on your dates. When you go into your financial module and navigate to bank deposits and select your deposit date and chequebook ID, you can mark all the available cheques if necessary.

If an accounts payable cheque has been voided after the bank reconciliation was done, the cheque will be marked and moved to clearance, it will put in a negative amount against it in the new bank reconciliation. The cheque should then show as a negative balance against the bank reconciliation.

Multicurrency

The currency that you see posted in your transactions is the currency of the bank, each bank chequebook can only have a single currency. But you can change this through bank transfer, and all your adjustments will be done in the currency of the bank reconciliation.

For a full walkthrough of bank reconciliation, please watch the video below:

If you have any questions about doing bank reconciliations in Dynamics GP, please contact us.