New accounting year item 19 20 21 pg 174 175 176

19. Replace Fixed Assets category b/f amounts with the new figure

This book expects that you maintain a separate (detailed) system for fixed assets. The system in this book only covers the broad category of assets. The usual broad categories of assets are Land, Buildings, Plants & Machinery, Office Equipment, Furniture & Fittings, Motor Vehicles, etc.

You may have more than one account to cater for a few categories of fixed assets. For each assets account, we want to bring forward only the closing balance amount. (The provisions for fixed assets depreciation is handled separately).

You should already have a row for details of opening balance for every assets category account. In that row, replace the amount under column RM-DEBIT with the new figure. You can get the previous year closing balance figure by applying a formula similar to this:
=SUMPRODUCT((G4:G30000="PLANT")*(B4:B30000≤#12/31/2003#)*(H4:H30000)*SUMPRODUCT((14:130000="PLANT")" (B4:830000≤ #12/31/2003#)*(J4:J30000))

Insert this formula in any cell in column AB.

Next, we want to zerorise all previous year amounts affecting that assets account in both column RM-DEBIT and RM-CREDIT. This is discussed in the next paragraph.

174

20. Zerorise Fixed Assets category accounts amounts

Since we expect the number of transactions related to assets will not be many, we can use an AutoFilter operation to identify the amounts.

Here are the steps:

1. Make sure the data in sheet DATA is in AutoFilter mode.

2. Click the arrow-head on the right of the column title ACCOUNT-DEBIT.

3. A list will appear. Select the assets category account name that you want.

4. Now, you should see only transactions rows where the item in column ACCOUNT DEBIT is the account name you selected.

5. Replace each previous year figure under the column RM-DEBIT with 0 (zero), one by one. You probably will not have many to replace. If otherwise, to zerorise faster, copy a zero figure, then select the target range and paste into the selected range.

6. Save the file.

7. Remove the filtering mode.

8. Next we want to see only transactions rows where the item in column ACCOUNT CREDIT is the same assets account name. So, switch to the AutoFilter mode again.

9. Click the arrow-head on the right of the column title ACCOUNT CREDIT.

10. A list will appear. Select the same assets category account name.

11. Now you should see only transactions rows where the item in column ACCOUNT CREDIT is the assets account name you selected.

12. Replace each previous year figure under column RM-CREDIT with 0 (zero). You probably will not have many to replace. However, if otherwise, to zerorise faster, copy a zero figure, then select the target range and paste into the selected range.

13. Save the file.

14. Remove the filtering mode.

175

15. Check that the absolute total of RM-DEBIT (in cell H1) and RM-CREDIT (in cell J1) is still the same. Otherwise, you may not have identified the amounts correctly. A review of the assets accounts may help you identify the reason.

14. Repeat the operation for each assets category account.

21. Replace Investments b/f accounts figure with the new one

This book assumes that you maintain a separate (detailed) system for investments. So, this book shows only how to handle broad categories of investments. Usually the broad categories of investments are Quoted Investment and Unquoted Investments.

For each category of investment, you should already have rows for details of opening balance for it. Replace the figure with the new one. You can get the new figure by using a formula similar to this:
=SUMPRODUCT((G4:G30000="QUOTED")*(B4:B30000≤#12/31/2003#)*(H4:H30000)+ SUMPRODUCT((14:130000="QUOTED")* (B4:B30000≤ #12/31/2003#)*(J4:J30000))

Next, we want to zerorise all amounts relating to the investment account in both column RM-DEBIT and RM-CREDIT. This is discussed in the next paragraph.