New accounting year item 1 2 3 pg 149 150 151

New Accounting Year

If the current working file has encroached into the next (new) accounting year (i.e. it contains records of the new year), you will want to carry only the balance sheet items into the next accounting year (and at the same time effectively removing current Profit & Loss items).

Before you do anything at all, make a copy of the current working file (which contains the data for the current accounting year and the new year). Give it an appropriate name such as Acc-Jan-03.x1s. This will become the file for the first month of the next (new) accounting year. In this new accounting year file we will remove previous Profit & Loss items and leave only the Balance Sheet items of the previous accounting year).

Below are the steps we take to carry only the balance sheet items into the next accounting year (and at the same time effectively removing Profit & Loss items). We will also want to retain certain information.

Allocate 2 hours to complete this task which must be done before you enter any data for the next year.

1. Change all items relating to previous year in the column MTH to 0 (zero).

0 (zero) in column MTH indicate opening balance month.

In cell AB5, enter this formula:
=IF(B5 (#12/31/2003#, "NR", C5)

Copy this formula down until the last row of data. This formula will generate Os (zeros) if the previous year accounting date is #12/31/2003#. (Note: #12/31/2003# refers to 31 December 2003. This is the manner in which you state a date in a formula. You can replace #12/31/2003# with the relevant accounting year end.) Select all the cells in column AB starting from row 5 to the last row, copy them (using Edit/Copy) and paste their values, not the formulas (using Edit/Paste Special/Values), to column C starting from cell C5.

149

2. Let the items in certain columns remain as they are.

The items in the following columns can be retained:

SRC, NOTE, DEBTOR STTMNT, CREDIT AGE, DATE, CHECK ACCOUNT-DEBIT, DEBT AGE, CREDIT AGE GROUP, SRC NO, CHECK ACCOUNT-CREDIT, DEBT AGE GROUP, HOLD, TERM, DUE DATE, OTHER REF/NO, BANK RECON., CREDITOR STTMNT, TEMP, ORIGINAL ORDER.

3. Change items under certain columns into NR.

Change items that relate to the previous year in the following columns into NR: ITEM PURCHASED, ITEM SOLD, QTY, UNIT and PRICE.
This is to avoid confusion on the statistics for the new year.

We can use a formula to quickly enter NR in the respective columns. For example, in cell AB5, enter this formula:
=IF(B5 (#12/31/2003#, "NR", W5)

This formula assumes that 31 December 2003 is the end of the previous accounting year. (Change this date to suit your own requirements). This formula will generate "NR" in cell AB5 if the date in cell B5 is earlier than or equal to 31 December 2003, otherwise it will return whatever is stated in cell W5. Copy this formula down until the last row (the same as the last row in column A). Then copy the value (not the formula) in every cell in column AB to the corresponding cell in column W.

Similarly, to generate "NR" in the column ITEM SOLD, used a similar formula. Insert this formula in cell AB5:
=IF(B5 (#12/31/2003#, "NR", X5)

This formula will generate "NR" in cell AB5 if the date in cell B5 is earlier than or equal to 31 December 2003. Copy this formula down until the last row (the same as the last row in column A). Then copy the value (not the formula) in every cell in column AB to the corresponding cell in column X.

NOTE

To copy the value of a formula instead of the formula itself, copy the cell contents as usual (using Edit/Copy), then use the menu Edit/Paste Special/Value Only to paste.

Use similar technique to insert "NR" into columns QTY, UNIT and PRICE. Instead of generating "NR", you can generate a blank.