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.
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.
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.
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.
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.
You can improve the format, whichever you like.