6 Steps to Make BRS in Excel

Today, you’ll learn how to make BRS in Excel.

Here are the steps to make BRS in Excel:

1. Gather all the accounting records and the bank statement for the reconciliation period

First, you should get the subsidiary ledger of the bank account for reconciliation and the bank statement of your bank.

The subsidiary ledger, which contains the debits and credits, should include all the transactions of the current month.

Also, the bank statement’s period must coincide with the period of the subsidiary ledger.

Now, create four Excel sheets, which include book debits, bank credits, book credits, and bank debits.

Book debits for bank Reconciliation
Book Debits
Bank statement credits for bank reconciliation
Bank Statement Credits
Book credits for bank reconciliation
Book Credits
Bank statement debits for bank reconciliation
Bank Statement Debits

2. Forward all unreconciled items from the previous bank reconciliation

For example, previous months’ deposits in transit are usually included in the book debits and previous months’ outstanding checks.

Total book debits = Previous months' deposits in transit +current month's book debits
Total book credits= Previous months' outstanding checks + current months' book credits

3. Start from the beginning balance of the accounting records and bank statement

Bank reconciliation should start with the beginning balance of the period reconciled.

4. Reconcile deposits

Next, add deposits on the book’s beginning balance and on the bank statement’s beginning balance.

Reconcile total book debits with the bank statement’s credits.

You can use the SUMIF Function, which is available in Excel for reconciling deposits.

If you did it correctly, your bank reconciliation statement should look like this.

Bank reconciliation statement after reconciling deposits

As you can see, we have a bank reconciliation statement before reconciling disbursements.

5. Reconcile disbursements

Now, deduct book credits from the book’s unadjusted balance and book debit from the unadjusted bank balance.

Deduct book credits and bank credits to the unadjusted balance

Next, reconcile disbursements, which include checks, bank charges, and fund transfers.

You can use the formulas, which can be the VLOOKUP function of Excel, here for more information (Automated Bank Reconciliation in Excel (Free Excel Download).

If did a good job, your bank reconciliation statement should look like this.

Final bank reconciliation but it still needs to be formated

6. Prepare the final bank reconciliation statement

The final step is a process, in which you need to format the bank reconciliation to be more understandable.

Here is an example of a good format.

Example of a bank reconciliation template for download

You can improve the format, whichever you like.

Jason John Wethe
Follow Me
Latest posts by Jason John Wethe (see all)
Scroll to Top