Bank reconciliation pg 202 203 204

1. Design the reconciliation statement as you like.
2. Use a column in the data entry sheet to mark bank entry that has it corresponding entry in the statement provided by the bank.
3. Ummarked cell means unreconciled items.
4. Bring the unmarked item details into the reconciliation statement. (formula)

	K	L	M	N	O	P
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts	Bank Recon.	Sales Inv. Status
2				(formula)
3
4
5
6
7
8
9				(formula)
10
11
12

---

25. Bank Reconciliation

Introduction
In the structure of our file i.e. in sheet DATA, we provide a column (with title BANK RECON) for the purpose of helping us in our preparation of Bank Reconciliation. Under that column title, we will identify whether a transaction amount is subject to reconciliation with the statement received from a bank. Clearly, only transactions affecting a bank account (whether in debit or credit side) will give effect.

If a transaction (e.g. Credit sales) is irrelevant to bank reconciliation, we write NR in this column (NR is short for Not relevant). If a transaction is a bank receipt/payment and the receipt/payment does not appear ear in bank statement, we write open. Initially all bank receipt/payment will be marked open. When we receive a bank statement and the receipt/payment appear on the bank statement, we write the month of the bank statement (e.g. "jan", "feb", etc). When we receive a bank statement and we notice that an item in the statement does not appear in our bank transactions, update our transaction and write the month of the bank transaction in the BANK RECON column.

Example

To do bank reconciliation for a particular bank, we need to select all rows in which the cell in column BANK RECON is marked with open and cells in column ACCOUNT DEBIT or ACCOUNT CREDIT contain the name of that particular bank account.

This row selection process require us to use AdvanceFiltering because it uses more than one column. Here are the steps:

1. Set a criteria block similar to the following:

	A	B				C				D
28
29		ACCOUNT-DEBIT	ACCOUNT-CREDIT	BANK RECON
30		BANK A							open
31						BANK A			open
32

202

The above criteria reads: Select rows where column ACCOUNT-DEBIT contain item 'BANK A' and column BANK RECON contain item 'open' (both items in the same row), and select also rows where column ACCOUNT-CREDIT contain item 'BANK A' and column BANK RECON contain item 'open' (both items in the same row)

2. Then, activate the sheet that contain our data (sheet DATA).

3. Click menu Data/Filter/AdvancedFilter to open the Advance Filter dialog box.

4. The List range box is automatically filled in with the address of the data area.

5. Fill the Criteria range box with address of the area of your criteria block.

6. Click OK.

7. You should see the data filtered according to the criteria block. You can copy relevant data to your bank reconciliation working sheet.

8. Repeat the process for each of the other bank accounts.

NOTE

You can add another sheet to the file for the purpose of bank reconciliation or you can use a separate file.

Below is a simplified sample of a bank reconciliation format:

	A		B			C								D
1 	BANK RECONCILIATION
2 	BANK A
3
4 	Balance as per Bank A book as at 31-05-02			300,000.00
5 	Add/Less: Unmatched items
6 	PV No.	Cheque No	Payee/Payer						RM
7 	PV1		00023456	Sandal Co						200,00
8 	PV 7	00023477	Shoes Co						100.00
9	
10 	Total												300.00
11
12 	Balance as per statement from bank A for 31.05.02	300,300.00
13

203

As you can see, we only copy 4 columns from the filtered data ie. PV No, Cheque No, Payee and Amount (RM).

If your bank balance does not reconcile with the statement from the bank, check the following:

1. Items marked open.
2. Items appearing in the statement but not in the bank account transaction.
3. Items appearing in the bank account transaction but not in the bank statement.

In the subsequent month, some of the unmatched items may match and new unmatched items are available in the sheet DATA. In the reconciliation sheet, delete the matched item and add the new items and adjust the new balances for the bank book and bank statement. Before you do that, it is good to have a back-up copy of the previous month's reconciliation.

204