Hello everyone, so, in this session we will be seeing how to prepare a bank reconciliation statement in Excel. So, here we are given bank reconciliation statement as on forced march 2019. So, we have particulars amount amount we have balances for cash flow, then we have some transactions and then we have balance as per bank statement as on first March 2019. Then we have bank statement for the month of March. So, we have the particulars we have transaction number so this has transaction number or your check number, then they have debit credit and balance. Then we have cacheable that does your bank account as per book so, this cash book isn't the word taken format.

So, we have date 40 colors then we have check number or bank transaction number, then we have voucher number. So, this is an internal voucher number then we have debit credit then we have to prepare a bank reconciliation statement as on 31st March 2019. So, before starting with bank reconciliation statement as on 31st March 2019, let us understand what will be the items that will be included in this bank reconciliation statement. So, to begin with, we have to first look at the bank reconciliation statement of the opening period. So, opening period in this cases first March 2019. So, here there are certain transactions which are there in the bank reconciliation statement.

Now we have to check whether this transactions are either in your bank statement or cash book for the month of March. If this same transactions are appearing in your bank statement or cash book for the month of March, then it won't appear in the bank reconciliation statement as on 31st March 2019. But if there are certain pending transactions, which are not appearing in your bank statement or cash book for the month of March, then those pending transaction will also appear in your bank reconciliation statement as on 31st March 2019. Then, there might be certain financial transactions which are recorded in the bank statement but are not there in the cash book. Again, those transactions will be part of our bank reconciliation statement as on 31st March 2019, then cashbook there might be certain entries Which are recorded in our books of accounts but are not appearing in a bank statement.

Again, those transactions will also be recorded in the bank reconciliation statement as on 31st March 2019. Now, let's see the bank reconciliation statement of the opening period that is forced march 2019. So we have balance as per cash book 10 lakhs. Let's verify this. Let's go to the cash book. First March 2019 balance brought forward we have deadlines.

Correct. Then we have some transactions here. We'll look at it later. Then we have balance as per bank statement on first March 2019 12 lakh 50,000. Let's go to the bank statement. First March 2019 balance brought forward credit balance of two lakh 50,000 So, this bank reconciliation statement appears to be correct.

So, this is the way you verify the bank reconciliation statement the balance as for cash book, and balance as per bank statement should match. So this is the first step of verifying if the bank reconciliation statement is correct. Now, before beginning with our working First we'll go to this bank statement. Now in this bank statement, we have to announce we have debit and credit instead of debit or credit we'll be putting this amount is plus or minus. So now credit balance in the bank statement men supposed to balance that has debit balance, mincer negative or overdraft balance. So the formula will give here is credit minus debit that is fo minus t. Now this formula will copy Throughout bank statement Now, let's go to the cash book cash book it's vice versa your debit balances positive and credit balances negativity or drug balance.

So the formula will be debit minus good. So that does this now let's go to the bank reconciliation statement on first March 2019. First, so, we have certain check issued but not present dead for payment. So check issued but not present it means they were appearing in your cash bow before first March 2019. But same are not presented for the payment so they were not appearing in your bank statement. Now let's see if these checks are present.

Dead for the payment in the month of March. Now these are the check numbers. If you check this individually, these are only for transactions but practically there might be lot of transactions were in the checks were issued but not presented for the payment. So instead of looking at manually one by one, what we can do is we can use a B lookup formula. So we look up formula and search this value in the range specified and it will return the value of that particular transaction. Let's see how it works.

As a quiet to be look up, then bracket then now this 345986 I wanted to search in bank statements. So be seven comma i go To bank statement, I view the range I want, we look up to search this in this rage that is in the transaction number. So turn it will search it in that transaction number. Now again I'll put a test karma, I want we look up to return the amount in the fifth column what is the fifth column fifth column is this one. So, karma by then, again karma, I want the V Luca to exactly match my transaction number a check number with the bank statements. So after karma I put it falls, or I can also put to test zero.

Then I'll close the bracket and complete the formula and see I got 60,003 45986 so let's verify this 345986 Let's verify this manually. So, in bank statement, we have 345986 and the amount of 60,000 on the debit side that is a payment. So, here it is. So it showing 60,000 negative. So, this value is returned in the bank reconciliation statement. So, this 60,000 minus 60,000 corresponding to this 345986 that is fifth column first, second, third, fourth fifth is returned in the bank reconciliation statement here.

Now, we will be copying this formula throughout to see if all checks issued are presented. Now, before copying this formula, this D four two X 23 range in the bank statement I wanted to be constant So, I'll be the my cursor here near D for then and depressing effort. So I got this dollar sign that means this is constant, this range will remain constant, even if I copy the formula to all the cells. So let me copy this. See even if I copy the formula, the Steve four two X 23 and remain constant. Now what has done we look up, it has looked for this value 345987 insell be eight in this range that is bank statement, the four h 23.

And it has returned the value in column number five bed corresponding to this check number in the bank statement. So, now, once I do this, not these amounts, whatever I'm getting it here. All this amount Our check issued and presidented are appearing in the bank statement in the month of March. Again, this check deposited six by 8975 is cleared in the month of March we can verify this manually six by 8975 let's do this six by 8975 cb herot here 658975 25,000 so it has returned this Friday 25,000 here. Now, these are cleared, so they won't appear in their bank reconciliation statement as on 31st March 2019. But there are certain transactions which are not cleared So, for example, this this is not clear so it will appear in their bank reconciliation statement as on 31st March.

2019 Let's mark this. And this. This we can ignore, there are no amounts here, I'll just delete the formula. So now, the 50,000 and the 16,000 will appear in the bank reconciliation statement as on 31st, March 2019. Now, let's go to the bank statement. Now there might be certain transactions in the bank statement, which are there in the bank statement, but I'm not there in that cash book.

How do we find out we use we look up again. So we look up this transaction number in that cash book. So cash book range and return the value in the fifth column. Elements this one and the value has to be exactly match. So zero but again also put falls. And now, before copying this formula to all the rows, I'll just put the effort here to make the range constant.

Now I'll copy this formula. If you see this amounts appearing they are in your bank statement as well as in your cash book. So these would be appearing in the bank reconciliation statement as on 31st March 2019. Now about this any now this is opening balance so anyway, this can be ignored. I'll delete this Now, there might be certain transactions from here, which were there in the bank reconciliation statement as on first March 2019. Now, so we'll do a V look up here, the fi in this and Dawn, the value in the second column siedel.

So before copying this formula, I'll make this constant. I'll copy this formula to all other rows. So this is the R s first March 2019. And this is from cashbook De Luca from cash. Now, if you see here, now, these are already cleared, so no problem now. These enemies so now these even though they are not appearing in the cash book they're appearing in the bank reconciliation statement as on first March 2019.

So, this na may not be recorded in the bank reconciliation statement as on 31st March 2019 then, so, either there has to be value in this or this column. So, we can ignore till here now, after this this transaction one by 454545 for this of 10,000 does neither appearing in the cash boo not it is appearing in your bank reconciliation statement as on forced march 2019. So, all these transactions will be appearing in a bank reconciliation statement as on 31st March 2019. Let's move this Similarly, now let's go to cash flow. Now, there might be certain transactions in the cash book, which are appearing in the bank statement or which are appearing in the bank reconciliation statement as on first March 2019. Let's use a V Luca formula to find out this.

So here this by the men and the artist xon last March 2019. Let's do a real lookup for a bank statement. We look up this value in bank statement and return the value of fifth column Now, I've just made this range constant by pressing f4 f4 doesn't work you can press function key and effort together. Now let me copy this formula. Now these transactions are appearing in your bank statement, so they won't come in the bank reconciliation statement. For this, this is the opening balance so it won't appear in the bank statement also.

So I'll delete this. This is a closing balance again, this one be there in the bank statement. Delete the source. Now for this transaction, let's see if they're in the bank reconciliation statement as on forced march 2019. So don't be look up. So, now I will be making this constant before copying it and man Copy this formula.

Now if you see all this transactions are not appearing in a bank reconciliation statement of first March 2019 also. So now these transactions will be part of your bank reconciliation statement as on 31st March 2019. Now, we'll start preparing a bank reconciliation statement as on 31st March 2019. bank reconciliation statement as on 31st month. Let's begin with balance as cashbook. So what is the balance as to cashbook z equal to? Let's go to the cash book, what does the balances on 31st March 2019 10 lakh 30,000.

So we will and now let's look at all the transactions first. Now in the bank reconciliation statement as on first March 2019, this was check issued but not present ID for the payment. So now these are the checks which are issued, but they were not presented for the payment. Now here we are moving from cash to bank statement. So these checks were issued. So your balance in the cash book has reduced to that extent but since they're not presented for the payment, they are not appearing in the bank statement.

And this continues for the month of March also. So this will be added to your bank reconciliation statement as on 31st March 2019. Ad checks issued but not dead for beaming. Now let's copy this bank reconciliation statement just to control I'll just change the color of this transaction since it is included in the bank reconciliation statement. Now, this is check deposited but not cleared to 58741 15,000. So this was jack which was deposited before first March 2019.

At this not cleared in the month of March also. So now this check deposited so it has increased our battery In the cash book, but the same is not recorded in the bank statement. So the to the extent of 15,000 our bank balance as per the bank statement will be less as compared to cash book. Now we are moving from cash book to bank statement. So we will reduce this 15,000 in the bank reconciliation statement as on 31st March 2019. Let's go to the bank reconciliation statement.

Now, I'll be leaving from three four lines as there might be certain additional transactions of Jackie should but not presented less checks deposited but not cleared. Now we'll copy this. And just to keep a check or control, just change the color of the skin green. Now let's move to the bank statement. bank statement, these are the transactions which are required to be included in your bank Rico. Now, if you see this transaction transport transaction number and the credit, now these three transactions are similar.

So, let's analyze what is the nature of this transactions there is a credit. So, there is some amount which is been transferred and credited to your bank statement, but the same amount is not recorded in the cash book because of this credit entries. Your balance as per bank statement has increased to the extent of 30,000 total 10,000 10,000 10,000 but since the same are not recorded in the cash flow, the balance to that extent is less as compared to the bank statement. Now we are moving from cash book to bank statements. So we have to ask This in the bank reconciliation statement as on 31st, March 2019. Let's do this.

So in bank reconciliation statement, and direct bank transfers credited in the bank statement but not recorded in the books, so let's copy this, this transaction number and the amount let's copy this transaction number first and the amount so, we just highlight this the different color, sticky but check next dp D rent to make now, DPT lectricity now these are the direct payments which are done by the bank but the same are not ready cord in the cash book. Now, what has happened because of this is because of this direct payment, the balance in your bank statement has reduced to this extent as compared to cash flow. Now in bank reconciliation statement we are moving from cash to bank statement. So, we have to reduce this amount in the bank reconciliation statement. Let's do the less direct payments by bank not recorded in the books of accounts.

Let's copy this transaction number and amount. let's mark this different color. Next, that it was March 2019 interest credited by bank by thousand and it is not recorded in the cash book. So, since the interest is credited by bank, it has increased the balance as per bank statement, but same is not recorded in the cash book. So, to the extent of 5000 the balance in the cash book is less now in bank reconciliation statement we are moving from cash book to bank statement. So, we have to add this in bank reconciliation statement.

So, let's go to the bank reconciliation statement add interest credited by a bank not recorded in the books amongst us by thousand now, this is only one transaction So, I will write this 5000 and the outer column. Now, let's highlight this very good to the cashbook. So, these are some fees which are received and these are the checks and they are debited in the cash flow. So, these are the checks which are deposited but not yet cleared so they are appearing in the cash book but they're not appearing in the bank statement. So, now what has happened because of this is our balance in the cash book has increased by the same are not recorded in a bank statement. So, balance in the bank statement is less as compared to cash to the extent of 30,000.

In the bank reconciliation statement we are moving from cashbook to bank statement, so we have to reduce this check deposit dead but not cleared. Let's go to the bank reconciliation statement. Now check deposited but not clear. We already recorded one check from our previous bank reconciliation statement. Let's add this text to This all so cash, these are the check numbers and this is the amount let's highlight the next 30th March 31 March. So, we have some payments, we are paid salary and payment to a we had checks year which are credited so they are payments these are check issued, but they are not yet presented for the payment.

So, now what has happened because of this your balance as for cash because read us, but since they are not presented for the payment, the balance as per bank statement to that this extent is Great as compared to cash book, so, we will be adding this to our bank reconciliation statement since we are moving from cash book to bank statement. Let's add this. Let's go to the bank reconciliation statement check issued but not presented. We already have one check which was issued in the previous month, but it has not cleared in the month of March. We have taken this from the our opening bank reconciliation statement as on first March 2019. So let's include all the districts here.

So this is check number. Let's copy this. Then. This is the let's go. Let's do the color just to keep it checked. Now, all fields are marked green the gas So, all entries from the cash book are included in the bank reconciliation statement.

All the entries of bank statement are also included, then all the entries of bank reconciliation, which were pending are also included in the bank reconciliation statement. Now let's take the total of bank reconciliation statement. So some of this then yeah. Some of this. Yeah. Again, we have some, this some, this three.

Now, it started with the balance aspect cash flow. Now we'll be getting here by islands US bank statement as on 31st March 2019 year sequel to 10 lakh 30,000 then we have to add this plus one lakh 30,000 then we have to reduce 45,000 So, less 45,000 this 30,000 we have to add add 30,000 then less this 95,000 then interest we have to act equal 10 lakhs to 5000 So, let's see the bank statement now balances on 31st March 2019 is 10 lakh 55,000. So, this bank reconciliation statement is correct. Now what we can do is, we can just take this d 35 minus your balance as per bank statement has to be zero. So, this shows that all the reconciliation items are considered and your bank reconciliation statement is correct. Now, instead of starting with balance as the cash book, we can also start us balance as per bank statement and then we can move from bank statement to cash flow.

So, so, this is a bank reconciliation statement, wherein we all started with the balance as per bank statement, and we have found out the balance of store cash book. Now, practically once you prepare the bank reconciliation statement, after this bank reconciliation statement, whatever are the pending entries or rectification entries in the cash book, we pass those pending or rectification entries in the cash book and then we remove those items. From bank reconciliation statement, for example, this first check issued but not presented for the payment. So, these are already in the cash book but not recorded in the bank statement. So, nothing can be done about this. So, this will remain in the bank reconciliation statement, then this check deposited but not cleared again these are there in your cash book but not in the bank statement.

So, again nothing can be done about this and this will appear in your bank reconciliation statement unless they're cleared. Then third, now, this bank transfers credited in the bank statement, but not recorded in the books not this transactions will be recorded in the books of accounts. This transactions are pending transactions and they will be recorded in the books of accounts and they'll be removed from your bank reconciliation statement. Then, direct payment by the man not recorded in the books of accounts again, this will be recorded The books of accounts and then they will be removed in your bank reconciliation statement, third interest credited by bank but not recorded in the book. So, this is also pending to be recorded in their cash book. So, it will be recorded and removed from your bank reconciliation statement.

So, at the end of a particular period, your bank reconciliation statement when you do it for the first time all those transactions will appear, then all those entries are passed in the cash book and subsequently they are removed from the bank reconciliation statement. So, your bank reconciliation statement will have only those transactions, which will be like check issued but not presenter for the payment or check deposited but not cleared. So this was about how to prepare a bank reconciliation statement in Excel.