Introduction to Bank Reconciliation in ERPNext
Hello Community. Today I want us to look at bank reconciliation and how it is done in ERPNext.
Now first, we need to understand what bank reconciliation is. The bank reconciliation simply means the process by which you compare the statement that has come from the bank with the statement that is showing in your accounting software. That is a definition by a software engineer, not by an accountant.
Now in ERPNext, we have two types of bank reconciliation. We have the manual bank reconciliation and we have the semi-automated bank reconciliation. Now today I would like us to look at the semi-automated bank reconciliation and in between, we may look at one or two things about the manual bank reconciliation process. Let's get started.
Overview of the Banking & Payments Module
So in ERPNext, semi-automated bank reconciliation happens here. Let me show you. Here we have this banking... First of all, in the accounting module as you can see here on the left, you can see we have Banking and payments here. Now here we have a number of things. This is where we even enter the bank accounts, we enter the banks, we enter the bank account, we enter Bank clearance. We enter a bank reconciliation—this is the tool itself. And then here we have bank reconciliation statements, and we have other records down there.
Now there, I have received questions from a number of people about the bank and bank account. Now a bank is like that facility that provides you the banking services. An example of a bank in Kenya is Cooperative Bank or Absa Bank. And a bank in other countries, for instance if let's say for instance you go to a country like Malta, an example of a banker is the Bank of Valeta.
Now you may have a number of bank accounts in one bank. For instance, you may have two bank accounts in Cooperative Bank, three bank accounts in Absa and all that. And that is why you need to have a bank and the bank account there. Right.
So in bank reconciliation here, remember we are doing the semi-automated one, not the manual. This is where this happens. So if for instance, I click on bank reconciliation here, I can see that I have one that... this is an instance that I had created. If you don't see this, just go ahead and fill the form that you see there. With the company, it will come pre-populated. If you have a number of companies you can select your company there. This is just my test company called Opensoft. Then you're going to need to select the bank account here. So this is the bank account where you want to do a reconciliation on. And then you need to select the period here, for which period do you want to get this statement or this reconciliation done. I have done January 1st to today's, to tomorrow's date, whatever you want to select here, that's just a date range.
And then here, you have something very important here. This is the closing balance for the bank account. So you will have the closing balance that you have from the bank. All right? The closing balance as per ERP will be fetched automatically by the system from the chart of accounts. Let me show you. If I go here to the chart of accounts, and I type that and then let's say for instance I open all of this, I have my Banks here. You can see here I have this Standard Chartered bank account and my balance is showing as 18,500. All right? If I go back to here, you can see that is the balance that has been fetched for that bank account, since it is what I have selected up here.
So closing balance as per ERP will be fetched automatically by the system. But then the closing balance as per bank statement, you need to enter it here manually. So if you change this for instance to something like 30,000 and then I save it, this is going to change to thirty thousand. Whatever is here, you can see that. So now here we can see, I'm not going to change that necessarily, because I just need to show you how to balance this amount so that here you have a difference of zero. Mine doesn't have to balance because this is just a test environment. All right?
Diagnosing Balance Differences with Reports
So if you see a balance here, for instance here we can see we have a positive balance. So we have more money in the bank apparently than we have in the ERP. So this could mean a number of things. It could mean that there are checks that have been cleared by the bank that you have not marked as cleared in the system. Let me show you that.
If we go back to another tool that I want to show you, let me go back to the accounting module. You see here in the banking and payment section that I showed you, we have this Bank clearance section here. So if I click on this, this is going to show me nothing. Sorry, not this, but instead I want the bank reconciliation statement. The bank reconciliation statement report is what is going to show me whether I have any checks that have not been cleared yet. So let me reload this to be just to be sure. And you can see here I have a number of things here. I have outstanding checks and deposits to clear. And if you have checks that have not been cleared in the system, that amount is going to show here. If there isn't, then it means that's not where the problem is. The problem could be, because the problem why you have a balance here is not uncleared checks. And that will mean that there are statements in the bank that you don't have in the ERP. And that is why now you need to upload a bank statement. We will come there.
Creating an Uncleared Payment Entry
Let me go back first to this instance here, or to this scenario where you haven't cleared checks. And let's just generate one invoice and then leave it unclear so that you can see how we can clear that invoice.
So we go here, allow me to open another tab, and here I am going to go into the sales invoices section. And then from here, I will just duplicate one of these invoices. For instance, let's say I'm paying Travix or something. So I'll duplicate this invoice. I don't need to change anything. Maybe I can just change the, maybe the due date will be maybe today. And then that is 3,000, or we can change this to something like 3,500, doesn't matter. We save it and then we go ahead and submit this invoice. So this invoice is expecting payment. So let's say we received this payment via check and we have not cleared that check. So we go here, receive a payment.
And then from here we need to select the mode of payment as check. And remember this mode of payment, if we open that mode of payment, you will realize that... let me save it first. I need to enter the transaction, so let me reference number, let's say 1234 and maybe SD. And then let's see, this payment has been received today, and then I save it.
What I wanted to show you is that the check that I have selected here, I want to show you that it has been mapped to receive the monies in the Standard Chartered account. So this is the mode of payment. The company of course is Opensoft. The mode of payment is checked and it has been mapped to Standard Chartered. All right. So that is the mode I have selected here. If I push that money ahead, so that payment has been received now. And I go back to this report, remember the bank Reconciliation Report, then I reload this, there is now going to be an outstanding amount. An outstanding amount here. Let me get that back and I open this a little bit wider. All right. Here we can see that we have outstanding checks and deposits to clear of 3500. So this is the check that we just entered.
And if we now go back to our Reconciliation Report, you don't expect to see any balance in the amount that you have in your ERP here. So if you come here the amount still remains as 18,500, although we have received a payment in the system. That is because that payment has not been cleared yet. So how do we clear it?
Let me take you back to the accounting module and now I will show you the functionality behind this Bank clearance link that we have here. So if I open this Bank clearance now, I can go to a new record here and then I can edit it. I will see that I want to get this from a payment entry, right? Payment entry. And I will take that specific payment entry. So let me just get a specific payment entry so that I can be able to receive that payment there. So I go to payment entries and my payment entry must be this one that was done a minute ago. And then the payment entry is 009. So I can even copy this number and then I can bring it here and paste it here. So this is my payment entry. And I will tell you when this payment was cleared. So I'm just informing the system that by the way, this payment entry that was received by a check, this check cleared today and the money is now in the bank. So when I do that, I just need to click on clear that and that tells me that clearance data has been updated.
And now when I go to this, first of all I look at the bank reconciliation statement, I refresh this, the amount that was here, outstanding checks and deposits to clear, now goes to zero. And when I go to this, I'm expecting that 3,500 is going to be updated here once I reload this report. So when I do that, you can see this one now has been updated. So 18,500 plus 3500 goes to 22,000. So this is now the money that is in our bank account.
Preparing for Statement-Based Reconciliation
So that is in the event that you have not cleared checks. Now what about when you need to update or upload a statement? Let's say for instance we have another invoice. Let's go and generate another invoice here. And we are going to generate the invoice for the remaining amount. So this is the 8,000 we have here. So I mean invoice, so this is going to be a sales invoice and then we are going to duplicate one of these invoices just to save our time. Right? So we duplicate this invoice and then the payment date, let's just keep it as today. And then we're going to change this amount from 3,500 to 8,000. Or we can maybe change this to 7,000. So the 7,000 is so that we can still have a balance of 1,000 here after we have reconciled this upload. All right? So we go ahead and save this and then we submit this record. Right?
So here we have a sales invoice which has not been paid yet. So we go ahead and receive a payment against this invoice. We can do this as payment was done through wire transfer. But then I need to also provide the reference for that. So maybe 1234567 and this is today, the transfer was done today. But then there is something I want to show you also here before we even go. The wire transfer payment mode has also been linked to the bank account where we are receiving the money. You can see that, the Standard Chartered. So you need to make sure that it's linked properly. And then you go ahead and click on submit. So the payment has been received.
Go back to your Reconciliation Report. That money will not be here yet. So you can see this still remains as 22,000 and that we still have a balance of 8,000. So let's say for instance we have received a statement from the bank and this payment entry is one of the payments that has come from the bank. So the consideration on this one has not been done right? So let me assume, or let's assume that this is the statement that has come from the bank. This is how the statement looks. Let me just enlarge this for the sake of your eyes. And then let's change this last transaction here. These are dummy transactions remember. So we can just go ahead and modify it to receive 7,000. So we have this 7,000 as the payment we want to reconcile there. So I have added a few more records here so that you can see how that looks in case you have a number of records coming from your bank. So I'll go ahead and save that.
How to Upload a Bank Statement
And then I'll minimize it and I'll go back to my bank reconciliation tool. So in the bank reconciliation tool here, I need to do an upload. So let me go here and then this wants me to create a new record, that is fine. I can go ahead and save it. And then this allows you to do a number of things. You can attach a file or you can download the template. I would prefer that you go ahead and first download the template. If you have the template, so here is our template. I mean, if you want the template then you can check it. This is how the template looks.
So the template expects you to provide a date of the transaction, a deposit. So this is just an example with a withdrawal if there are withdrawals, a description of whatever happened there, a reference number of course every transaction will come with a reference number, and then the bank account. This is very, very important because of course that is where the transactions will go. That's the bank account name and then the currency of the money that was received.
So if for instance you have a statement from the back that does not look like this, you have two options. You can decide to map the headers of your statement to look like this or you can just go ahead and upload that statement as is here. So go ahead and attach it and select your statement. My statement is this one for instance, and then upload.
Now in my case, the statement will just pick and show me the transactions here as they look on the Excel sheet or on the CSV that we are uploading, which is this one here in my case. But in your case, if the columns or the headers are not the same, by that I mean if the headers are not looking like this, the system will complain to you that it's not able to match the records and then there is this map columns button that shows up here. So if you click on that, you can go ahead and map these columns. You can see mine has been mapped automatically because they are matching. And then you can go ahead and click on submit there. So that's going to do that and then you can go ahead and click on start import.
So when you click on start import, mine shows a success here. So you can see all the transactions have been mapped successfully and imported into the system. Now these transactions are normally imported on the bank transaction record. So if we open a new tab here just to show you, and then we look at bank transaction list, you can see here we have these transactions showing up here. They have been uploaded now. That is where those transactions go.
Matching Imported Transactions with Vouchers
All right. So now if we go back to our import and we go back to the bank reconciliation statement, this is how it looks. We still have our balance here. So the closing bank balance as per ERP is 22,000 and then the difference is 8k. Remember we have just uploaded this 7,000. We have approved this bank statement and we want to map this payment entries that are from the bank with some of the payment entries that are in our system that have not been mapped properly.
So here, for the 7,000 you want to map, remember we have this payment entry in the system. Let me just show you just to recap. If I go to payment entry here, you can see we have this payment entry that we did for 7,000. It has not been mapped. So if we go back to accounting software, and we go to the accounting module and we go to Bank Reconstruction Statement, we can see that we have this 7,000 that has not been cleared yet. So how do we clear this 7,000? We don't want to clear it through the bank clearance, we want to clear it through a bank reconciliation.
So we go here and then we click on actions. When we click on actions with the payment entry selected here, we can see the 7,000 has already been mapped down here. So we can go ahead and map directly the 7,000... this 7,000... let me open up this. We can map this 7,000 with this payment down here. All right? This payment entry can be mapped with this entry or this transaction from the bank here. So off the actions, you can map that.
Now this provides you a number of things also. You can filter by sales invoice, you can filter by expense. So every time you click, it filters this, it adds a filter to this record and then it depicts the records that have not been mapped. Now notice up here you have an action. In this action you have 'create voucher'. So you can go ahead and create a voucher that you can go ahead and map against this 7,000. That is you are going to select the party, you are going to select the check number and then the party type and the party and then you're going to select the amount of payment and then the cost center. You must not enter the cost center. And then you can go ahead and submit this and it's going to clear this payment.
The other option here is to update bank's transaction, so that this Bank transaction can also be updated with some records like the party type and then you select the party and then you can go ahead and... So today we want to match against our voucher and the voucher is the payment entry. And this is the payment entry we want to match against our transaction here and then we click on submit. Once we click on submit, this record is supposed to vanish from the system, this number five here, the 7,000 is supposed to vanish from these transactions that are showing here that have not been reconciled. So let's go ahead and click that.
And now you notice 7,000 is no longer in that list, number one. And then when we go to this payment, this report here and we click on this action button, you can see now the 7,000 has also vanished from this. So it has been reconciled properly.
And now when we go to our bank reconciliation report and we reload this, the 22,000 is going to be updated with 7,000, which is now 29,000. Then now we can see that the amount that is not reconciled yet is this 1,000.
So that is how you do Bank requisition in ERPNext. If there are any questions, please feel free to drop them on the comment section or you can reach me through my blog at codewithkarani.com and I will answer those questions.
Remember we are also an ERP consulting company called Operasoft, and we provide... this is our company, operasoft.com, like that. We provide ERPNext consultancy services. We are also software engineers and we are also Frappe Partners, certified Frappe Partners. So for any queries about your business, about Frappe development or the business side, feel free to contact us. We are willing to help you in your journey. Thank you so much and I hope to see you in my next video.
