Dynamics GP – How to Do a Bank Reconciliation

During this recorded webinar, we discuss how to do a Bank Reconciliation in Dynamics GP and share some tips and tricks throughout the process.

Intended audience:

• Dynamics GP users – all product versions

Transcript below:

Melissa: Hello, everyone. Thank you for joining us today for our “Dynamics GP Coffee Break.” This month’s topic is “How to Do a Bank Reconciliation.” So, my name is Melissa. And I’m the facilitator today. If you have any questions during the presentation, please type them into the Questions Area, in the control panel on the right-hand side of your screen, and we’ll answer questions at the end. This session will be recorded and posted on our blog, as well as shared with you later this week. So, I would like to introduce our presenter today, Don McNulty, who is a solutions architect on our Dynamics GP team here at Encore. So, Don, over to you.

Don: Hi, welcome. Thanks for coming out today. So, today we’re going to talk about bank rec. It’s kind of one of those things that accountants do. We do it once a month, so that’s kind of what we’re working on. So, for the agenda here, what we’re going to do is… introductions we’ve done, but, you know, what are the sources of transactions? What goes into a bank rec? What kind of transactions go into bank rec? How do I populate my bank rec transaction? What it does, when to do it, and how to do it. I mean, basically, how do we create a bank rec, so that we can match it up to our bank, and have a third-party authentication on our balance sheet?

Most of this is gonna be done on a show-and-tell basis. And I’m gonna actually show you all the different types of transactions. And we will post them through, and we’ll take a look at the bank rec when it’s finished, so you can see those transactions coming together. And at the very end, we’ll have some questions.

All right. So, the “Source of Transactions.” They come from all over GP, but primarily the number one place that most of the transactions come from is from the accounts payable and accounts receivable. So, accounts payable, all checks that are issued to a checkbook. So, that is off the transaction entry screen, a PO if you’re doing a PO deposit, if you’re doing a check run, a manual check, or a check run, either one, it will automatically push them through to the checkbook, so that you can mark them off.

Each check gets issued, gets entered into the checkbook individually so that you can mark them off one by one as they clear the bank. The voids are removed from the bank rec, but you must watch your dates. If you backdate a transaction on the checkbook register, but not in the GL posting date, you can throw your GL to bank rec, all the balance, if you’re not careful. So, I’ll do a quick void, and I’ll show you how you should be looking at them, that particular screen.

In the account receivable side, there’s a couple of ways of getting them in, cash receipts and sales order processing. So, on the AR side, the cash receipts, when you put the cash receipts in, they all go into the deposits, and then from the deposits, you can create a deposit, and it then shows up on the bank rec. You can also [inaudible 00:03:01] in GP, which allows you to post directly a whole batch, and have it create a summary transaction on the bank rec. So, if you’re doing deposits on a daily basis, and all the checks come into AR, and you process them all in one batch, that batch total equals the amount you’re taking to the bank, and you can use that and we’ll post it directly through without having to do a secondary step, which is to create a bank deposit.

Inside bank rec itself, we have bank transactions, bank transfers, and deposit entries. So, bank transactions that increase and decrease adjustments, checks, withdrawals. This is how you can record a lot of the individual pieces of information, things like lease payments, interest due, service charges, and things like that are done in bank transactions.

Bank transfers is a unique location in that ordinarily a single transaction hits one bank account. The only exception to that is a bank transfer. I mean, a transfer allows you to transfer money from one bank account to another rec bank account. It hits both bank recs at the same time. So, the increase and the decrease are done individually, and then they are posted into both bank recs. It shows as a single transaction in the GL, but it does hit both bank recs.

Deposit entry. Deposit entry is important because what happens is that you get 15 checks in a day, and then you put them on a deposit, [inaudible 00:04:36] you take into the bank. When you get your bank statement back from the bank, it’s got one number for that day. You need to accumulate those pieces into a single number [inaudible 00:04:49] you deposit, and that’s what the deposit entries is about, to create the bank deposit that you actually took to the bank, and have the teller/the drop-off process.

Now, there’s also a miscellaneous payment called a miscellaneous check, and underneath miscellaneous checks, you have the ability to enter and print a check directly from the banker. This does not require a vendor, okay? The limitation on it is that it is functional currency only. So, bank rec itself is multicurrency aware, which we can discuss [inaudible 00:05:28].

“What, When, and How?” Okay, these are the big ones. Okay, what? Well, what it does is it allows us to clear transactions off of a list so that we end up with an outstanding list at the end of the month. At the end of the month, we say, “Well, these 35 checks did not clear the bank, these checks did clear the bank.” And check off the ones that cleared the bank. So, we’ve got a list from there. It allows us to create deposits so that we can match them to the statement, match them to the bank statement, and then we can auto-create using the bank documents I mentioned earlier.

Transaction entry, we do the transactions, we do the current entries such as lease payments, and rent payments, and things like that, that may be pre-authorized payments, and you’re not entering them into accounts payable. Service charges, obviously, the banks like to get their pound of flesh from us all. So, we have to pay our service charges. And interest either expense or revenue, depending on whether you carry a positive or negative balance on the bank.

Transfers, so we can move money between them. We hit two checkbooks at the same time. It does automatically look after the multicurrency in most of their transactions. Miscellaneous payments, as I said, allows the printing of a check without a vendor, and it only works in functional currency.

When? Well, most firms, most smaller firms do their bank recs on a monthly basis. At the end of each month, they get their bank statement in the mail/they pick it up at the bank, or they download it, depending on what you’ve arranged with your bank. You then turn around and do your bank rec based on that particular information. Some people will do it more frequently. I have some clients who do this daily, that every day, they clear off all the transactions that have cleared in the bank, and they check it online. And that way they maintain their bank balance throughout the month. You can also do it weekly, or whatever periodic time you wish to do, you can run the same bank rec for the same period over and over again, so that you can always take a look at it, okay?

And then how? Items are checked off using the reconciliation feature. There’s a reconciliation, reconcile bank rec feature. Brings up a list of all the outstanding transactions, and you check them off one by one, or by groups, or whatever it happens to be. Okay.

So, now I’m gonna show you how this actually works. So, let me just switch my screen over to number 3. Here we are. All right, so I have Great Plains. This is a Canadian version of GP. And you can tell that, especially in the bank, because you’ll notice here that it says miscellaneous cheque, and it’s spelled with the C-H-E-Q-U-E. So, on the U.S. side, if you’ve loaded as a U.S. load, you will find that with C-H-E-C-K. So, that’s the difference, really the only difference, and you’ll see that throughout this demo.

So, what I’ve got here is I’ve got a checkbook. So, what I’ve done is when we set up checkbooks, we set them up inside cards, and I’ve set this particular checkbook up for demos, and I called it RBC US Dollar. So, there it is there. We have wired it to a bank account. We’ve [inaudible 00:08:46] with the current balances and we go from there.

Okay. Once we’ve done that, all the transactions that we do go through there. So, as we hit these bank recs, they will automatically post these transactions through. So, inside Purchasing whenever I create a transaction batch, for instance, when I go into my Build a Batch, and I build a payment batch, and I type in Test, and I wanna add… I have to specify a bank account. If I hit RBC US. And so, I can now create a check right now. It’s going to go to this bank account.

So, therefore any checks that I push on this and push through will show up inside my reconcile bank, which I’ve got listed over here. And when I hit the Transactions, I’ll just quickly show you what it looks like underneath. So, that’s what it looks like underneath and we’ve got some deposits. We’ve got some checks. In here, I’ve got a withdrawal, I’ve got an increase adjustment, a decrease adjustment, and I’ve got a couple of bank transfers. And this was so that I had some transactions to check off.

If I was to create a Batch 1 here, and I was to pay somebody, it would then show up on here if I processed it all the way through. Same thing is also true is if I go into here, and I click on a standard transaction, and I’ll just put one in here, I’ll put one for each [inaudible 00:10:08]. Okay. For this demo, I had already used that number [inaudible 00:10:17] put a 9 on it. We’re gonna make it $1,000 like so. We’re gonna take out a deposit. We’re gonna make this just a straight 1,000 bucks. I am going to go on to Check. I’m gonna put in $1,000 here, I can do this, and now I can change my bank account to the checkbook that I wanna use, the RBC, so it shows up, Actions and OK.

And at this point here, my distributions are going to Travel, which is fine. I’ll go OK. And now if I was to post this, this particular transaction with this check number, which is number 2465, will show up on my bank rec. [inaudible 00:11:01]. Now I can close this. Now, in order for it to show up, I have to close this, and I reread it, and when I reread this, there it is right there. That’s the check. Now, when I highlight this, I can always drill down and take a look at it, and this will then show me that this is the check, and there is the check that I just issued. And there’s the distribution, and there is the apply. There is what I applied it to. That was that transaction that I just entered.

Now, if I go into a different check in here, and I drill down, I can always take a look and see what this one is. This is for Alton Manufacturing. They are checks, and it tells you what these [inaudible 00:11:40]. So, these are all the checks. And right now I’ve got the adjusted book balance, 100,000, whatever it happens to be in there, I can just start to check off the pieces.

So, this will be where I take my bank rec, and I will start to check off, okay, these loans, deposits have cleared, and these checks have cleared. I’m gonna leave this check, the 82,000 outstanding, and I’m gonna leave that one, and then these are direct… We’re gonna check those off, and the bank transfers off, took some money out, put some money in, so about $26,000. So, that means that my $100,000 over here is not right, I did not calculate, or have a bank rec in front of me to be able to do this, but this bank statement and the balance comes from your bank.

Once you’ve checked everything off, it should balance to zero. When this goes to zero you can reconcile. And when you reconcile, what it does is it allows you to print a number of different things. And I’m gonna hit the Print button here, and I’m just gonna print all four, [inaudible 00:12:47] OK. Screen, Screen, Screen, Screen, there we are. So, the first report shows me my bank statement and the balance, the outstanding checks, and my adjusted balance. This balance here equals the cut-off, 17,707 is the cut-off there, and then these should all be balanced all the way through. That would then allow me to say that this is a balanced bank rec.

So, when I go to the second one, there is no adjustments because I didn’t put any in. But the third is most important, this is the list of all the… these are the marked transactions. These are what I’ve marked off. So, these ones have all been marked off. And yes, these have occurred on my bank statement and in my system. So, I’m good.

Then the last one lists off the outstanding checks. These are the two checks that were issued but have not yet cleared the bank. So, you may have a lot of them, you may have a few of them. You could have deposits on here that crossed the month, and I put the deposit in on April 30th. But in actual fact, the actual deposit didn’t hit the bank till May 1st because I went after 3:00 on the 30th of April.

So, once I’ve done that, I can then close this. I can do an adjustment here, so if I’ve got things that I know, the interest income for instance, and I go… and there should be some interest in here somewhere. It’s interest expense. So, there we are there. So, writing this difference off, so there we are, we go, OK. So, now at this point, I got a 17,707.57 should be what I have in my general ledger. And when I post everything through, I will get that number in my GL. So, this adjustment now I can reconcile. I can’t hit that reconcile until this goes to zero. This here then shows you that same rec [inaudible 00:15:01] the adjustments. You see them here. Now I’m down to 17,707. There’s my bank plus my outstanding checks this is $17,707 as well.

So, these two now balance. And then the third balance that you need to do is this, you have to take this balance and compare it to your general ledger. If you don’t do it to your general ledger, your general ledger is what shows up on your financial statements. Your bank rec does not necessarily do that [inaudible 00:15:27]. So, I could post this through, and all of these transactions would then close. Okay. And that would be where the $100,050 comes from.

But, you know, well, I don’t have enough deposits. I’m missing some deposits, and I know I deposited more than this. So, we have to say where did the deposits go? So, when we go into Transactions, again, back into Financial, when we go into Deposits. Yeah, I’m gonna go 04/20/27, and we’re working in 2027 checkbook Id is RBC USD.

Now, inside here, I can see these are unprocessed deposits. So, let me change this. I made a mistake there. Let’s say this is the 10th. So, on the 10th, I deposited this check, 258. And I also deposited this check on the 10th, and I deposited this check on the 10th. And I deposited this check on the 10th. But this one didn’t go in until the 13th. So, this is the $14,000 that I deposited on the 10th of April. So, I can now process this and go, okay, I’m gonna post that, and when I post it. And now when I come back in here, and I go 04/13/27, I now only have the one left. Oh, well, that’s my one from there. So, I can then clear that one off as well. Now, these will add [inaudible 00:17:01] here. And we go Post.

No transaction occurs when we do that unless you’re using multicurrency. And I’ll talk about that. I can talk about that [inaudible 00:17:13]. And we close this. So, now when I go back to here, and I go, OK, and I go to Transactions. Right now have two uncleared deposits. They have now shown up. This will equal what’s on my bank statement. So, I can now select this one and select this one, and I’ll go and make my bank balance again, [inaudible 00:17:37]. So, now I got a 41,000 that I’m owed because I put these two numbers together. So, as I go through this, I would then make sure that all my transactions [inaudible 00:17:56].

So, you can do whatever you need to inside here in order to get the right additional list. What you’re most concerned about with this are these two reports, the reconciliation report that tells you whether you reconciled, and the outstanding transaction report. So, this is a list of your outstanding transactions. As you can see, I got three checks now and a withdrawal. I don’t have a deposit, so my deposit is clear.

So, that is how you do a bank rec. Now, there is an electronic version of this, where you can, if you have large numbers of checks that get processed through on a monthly basis, ticking and checking them off over, you know, through to a 1,000 checks is a lot of work. Electronic bank rec is included in GP, and certain banks and the bank files can be then used and imported directly from a BAI2 [SP] file, directly into the bank rec, and it will check off all your checks and deposits that match… It checks the control number for the check, as well as the amount. On the deposit, it looks at the amount, and the date must be within five days of the actual date because it knows that it’s possible that you deposited on the 28th, but the bank didn’t process it till the 29th. So, it does look at that. I did not set that up into these. But that is something that we have.

Other things that you need to know about is that deposits that come through from sales order processing will automatically flow through to your bank rec, as will your deposit slip as you create them for the prepayment. Okay. Now, the last thing that happens is of course that when I look at this, and I say, Okay, well these are all my transactions, is this correct, and things like that. Does it balance to my general ledger?”

So, there is a reconcile-to-GL function inside GP for routines. And inside here we have a choice of payables, receivables, inventory, and bank rec. So, if I grab my bank rec, and I bring up my checkbook like so. And I type in 04/30/27. So, then I pick up all my [inaudible 00:20:39] transactions, and when I go Process, just put this on the desktop. [inaudible 00:20:49] Process. When this processes, this is going to generate an Excel Spreadsheet. As you can see, there it is, right there. We’ll also open Excel. And it will tell me potentially matched transactions.

Now, the thing that will throw your bank rec out, if you do a journal entry to the bank rec, your bank will not balance to your GL. So, inside here, we’ve got matched and unmatched transactions. You’ll notice inside here, I’ve got nothing listed on the left-hand side. Okay. Let me just do this, like so. I have no transactions on the left-hand side. [inaudible 00:21:44], it’s kind of strange. Why do I have that? I’m gonna go, Don’t Save, and then go back to here and go, “Well, why did that not work properly, I expected to see some transactions.” And really go to Series Post here, is because all of these CMTRX’s [inaudible 00:22:02], and my payables transactions, we go to Mark All, and go Post. I have not been posting through my general ledger, so nothing got posted to the general ledger. It only got posted directly to it. As it happened, I put these transactions in this morning.

Here we are, they’re all posted. So, now when I come back to here, and I go to this bank rec, pick up my RBC, 04/30/27, and I hit Process. Now I got matching transactions. I got unmatched transactions, I got potentially matched transactions, and I’ve got matched transactions. So, inside here, and this is a list of all the transactions that have gone through. Why they don’t match, maybe they’re not posted yet. What’s going on? I’m not sure. I have to see. Like, these ones here, it looks like there’s a deposit that’s missing. Because there is some information in my general ledger, right, that is not in my bank rec here. So, that means, I mean, the cash receipts.

Again, I don’t know why this is making it so small, so it’s hard to see. But let me just make this a little bigger. So, you can see it. See these are cash receipts. So, these payments 319 and 495 in $900 and $956 is sitting in my general ledger, but it is not in my RBC transaction. So, there’s no deposit. Now, there’s the deposit here of 958. This matches this because I created it in [inaudible 00:23:57] transaction.

Potentially matched, these ones match 1,094, this is only 156, is right there, as well as 1,094 for the total deposited receipts. And there’s the total there. As we move through. So, it does show everything that we need to do. The difference is nothing, my bank rec beginning and my ending is exactly right, which is 59,663. So, they are actually matched, but these appear to be unmatched simply because of the way they’ve been posted. But it does show you if you do a journal entry, it’ll show up on this side here, and not on this side at all.

Okay. So, that’s something to be aware of. You need to make sure that it balances correctly to your general ledger because that’s what’s gonna show up on the financial statement. All right. Now, we’re going to… Let’s see here, [inaudible 00:24:54]. Oh questions. Melissa, did we get any questions?

Melissa: Yes, actually, we have a couple of questions if you’re ready for those now.

Don: Sure.

Melissa: Okay. So, first one, if an AP check was voided after the bank rec was done, how can we reprint the reports, would the process be the same if next month bank rec is already started?

Don: Once you’ve cleared the bank, and it’s marked it and moved it to clearance, it’ll put in a negative amount against that in the new bank rec. Because it should show a negative against the bank rec. So, the check will then have a negative number.

Melissa: Great. So, next one. On the adjustment screen, can you change the default on the transactions from interest income to other income?

Don: Yes. So, when you hit the Adjustments inside here, you can change this to whatever you wish. Oh, you mean the defaults [inaudible 00:25:56], no, it automatically defaults to the first one. There is no way of changing that default that I’m aware of. So, to make it so it always comes up looking like this, no, I’m afraid not. You have to change that each time.

Melissa: Okay, great. That’s what we had for questions. And that’s where we…

Don: Okay. I did mention earlier is that I did say I would talk a bit about voiding transactions. When you void a transaction, what’s important to do on the void, from using a void open, void the historical transactions. And I’ll use Ace Travel because I know I’ve got a bunch of checks in there open. There it is there. When you void this transaction, this void date that you see here, and this posted date, this is what affects your general ledger. This is when it’s gonna affect your bank rec. If these two dates are not in the same period, your banks will not balance. Okay? They will not balance to the GL if I were to put this back to March.

So, if I take this one, and I voided it back in 2023, and I void this in 2024, right, I’m gonna have a problem, my bank rec will not balance because the two dates are not in sync. And it will tell you here whether or not they’ve been reconciled or not, these particular items have not. These have been reconciled. Okay? So, if I click on this one here, I can change this to 04/01/27. So, if I do that, and then I void this, this will then void it, and it will make my April bank rec correct. The older bank recs are already done and marked and gone through the history. When I hit void that will void those transactions, and put them now that particular ones in a different a different bank account. Okay?

But that is when you do the void, make sure you watch these two dates because if you don’t, they will cause you lots of grief on your bank rec. Okay. And the timing on the bank rec is all based upon these dates here, the statement cut off these here. The same is true on the deposits for your dates as well. So, when you go into your Financial, when you go into Deposits, remember I was missing a bank deposit. If I go like this, 04/30/27 checkbook RBC USD. These inside here, you gotta watch the dates inside here as well. I have, you know, marked them all off at this point. But if I go [inaudible 00:28:45] trust me, I [inaudible 00:28:47] there’s some checks from long ago, and I can mark all kinds of different checks if I need to, like so. Okay? [inaudible 00:29:03] changes the bank [inaudible 00:29:04]?

All right. All right. I think that was it. I’m right on time this morning. So, multicurrency is the other one that I should mention. It does do multicurrency. The currency that you see here is the currency of all these transactions are in the currency of the bank, each bank checkbook can only have a single currency. So, in this particular case, these are all U.S. dollars.

But if I was to change this here, and I was to close this, and bring up my Canadian one and go say, 04/30/2027, 04/30/2027, I’m not sure I’ve done these transactions in here [inaudible 00:29:50]. This particular one is the transfer that I did earlier today. This transfer was that $1,000 U.S. that you saw. So, when I go like this, and I look at transfer, I transferred $1,000 out. You remember I checked it off on the bank rec for the RBC US? Here it is on the RBC Canadian. And it’s running at 1,333. Because I have an exchange rate attached of 0.75, 75 cents to the dollar. So, for every U.S. dollar, it’s a $1.3333. Okay? So, it does look after that. So, all of your transactions that you see on this list here are in Canadian dollars, and all of your adjustments and stuff like that are done in the currency of the bank rec.

And the last piece, which I did mention earlier, this is what the miscellaneous check looks like. You pick the Check tab, you put your information [inaudible 00:30:47] Print. You can actually print the check in the format that’s listed. This format is not the same as the format for your AP transactions and needs to be modified to match your check specs. Okay? Just so that you know. All right. All right. And I think that was it. So, thank you all very much. Let me just…

Melissa: Yep, that’s great. Thank you, Don, for the information today. And thanks, everyone, for joining us today. And have a great rest of your day. Bye, everyone.