New accounting year item 17 18 Method 1 pg 169 170 171

17. Replace Bank amounts from the previous year with one figure

For each bank account, we want to bring forward only the closing balance, not every transaction.

You should already have a row for details of opening balance for a bank account. In that row, replace the amount under column RM-DEBIT or RM-CREDIT (if overdraft) with the new figure. You can get the previous year closing balance figure from the sheet TB or from the previous year accounts.

Next, we need to zerorise all previous year amounts in column RM-DEBIT where its corresponding item in column ACCOUNT DEBIT is the bank account, and also any previous year amount in column RM-CREDIT where its corresponding item in column ACCOUNT-CREDIT is the same bank account. This is discussed in the next paragraph.

18. Zerorise Bank amounts from the previous year

We don't want to carry every row containing bank transactions. We are interested only in the net balance figure of the bank figure to be carried forward to the new accounting year.

There are 3 methods you can use to identify previous year bank amounts to enable us to zerorise them:

Method 1: Use AutoFilter

1. Go to sheet DATA and make sure the data is not in filtering mode.

2. Make sure the active cell is in the data area.

3. Click menu Data/Filter/AutoFilter.

4. A downward-pointing arrowhead will appear on the right side of each data/column titles at row 5.

5. Click on the arrowhead on the right of column title ACCOUNT-DEBIT.

169

6. A list will appear. Select a bank account name.

7. The rows will be filtered accordingly. You will see rows where cells in column ACCOUNT-DEBIT contain the chosen bank account name.

8. Replace each amount in column RM-DEBIT with 0 (zero), one by one - except the opening balance b/f and the current year amounts.

NOTE

A faster way is to copy a zero figure, then highlight the target cells, and then paste into the cells in one fell swoop.

9. Save the file.

10. Next, we want to identify the amounts in column RM-CREDIT.

11. Click on the arrowhead on the right of column title ACCOUNT-CREDIT.

12. A list will appear. Select the same bank account name.

13. The rows will be filtered accordingly. You will see only rows where cells in column ACCOUNT-CREDIT contain the chosen bank account name.

14. Replace each amount in column RM-CREDIT with 0 (zero), one by one, except the opening balance (if any) and the current year amounts.

TIP

A faster way is to copy a zero figure, then highlight all the target cells, and then paste into the target cells at one scoop.

15. Save the file.

16. Repeat the process for other bank account (if any).

170

NOTE

Be careful not to replace the bank account opening balances with 0 (zero). If this happen, just replace the figure.