Transcription
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. 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.
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—and here 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, uh, I mean 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, our bank is like that facility that provides you the banking services. An example of a bank in Kenya is Cooperative Bank or Absa Bank. A bank in other countries, for instance, if—let's say, for instance—to go to a country like Malta, an example of a bank is the Bank of Valletta.
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 in the bank account there, right? So, in bank reconciliation here, remember we are doing the money—I mean, I mean 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—uh, 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—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, a chart of accounts, and I type that, and then I—let's say, for instance—I open all the—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—not necessarily because I just need to show you how to—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. 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—um, nothing. Sorry, not this, but—but instead, I want—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, 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—that amount is going to show here. If there isn't, then it means that it's not the way—the problem is—the problem why you have a balance here is not unclear 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. Let me go back first to this instance here, or to this scenario where you haven't cleared checks, and let's just—uh—generate one invoice and then leave it unclear so that you can see how we can clear that invoice in the—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 Traffic Out 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—I need to enter the—the transaction. So, let me—transaction reference number, let's say 1234, and maybe SD, and then let's see—this—this payment has been received today, and then I save it. And 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 check, 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—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 3,500. 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—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—let me take you back to the accounting module, and now I will show you the functionality behind this Bank clearance—uh—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 payments—interest—and my payment entry must be this one that was done—I did it one 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—will maintain the—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 3,500 goes to 22,000. So, this is now the money that is in our bank account. 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 sales invoice. We can do this as—was payment was done through wire transfer, but then—well, 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 even before we 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—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—this 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—one of the payments that has come from the bank. So, the reconciliation 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 it 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, 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 is—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—this is—this is just an example—a withdrawal, if they 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 that—of the—of the money that was received. So, if, for instance, you have a statement from the bank that does not look like this, you—you have two options. You can decide to map the headers of your—of your—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—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—um—on—on—on—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. 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 per ERP appears 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 these payment entries that are from the bank with some of the payment entries that are in our system that have been—not been mapped properly. So, here—or the 7,000—you want to map—remember we—remember we have this payment entry in the system—let me just show you—just to recap—if I go to payment entry—payment entry here—you can see here 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—um—into accounting module, and we go to—uh—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—this entry or this transaction from the bank here. So, of 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. 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—happy—you have an action. So, in this action, you have Create voucher, you go—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—end of the check number—and then—the—this is the party type, and the party, and then you're going to select the payment—the amount of payment—and then the cost center—not necessarily—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—so today we want to match against our voucher, and the voucher is the payment entry, and this is the payment—23—we want to match against—against—against our—our transaction here, and then we click on Submit. Once we click on Submit, this record is supposed to vanish from their 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—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 monthly constitutional report, the 22,000 is going to be 22,000 plus 7,000, which is now 29,000. Then now we can see that the amount that is not yet reconciled is this 1,000. So, that is how you do bank reconciliation 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 Lupersoft, 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 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.