Using and managing the current year file

Using and managing the current year file

After you create the file and set the structure in sheets DATA and TB, you can start using the file.

Here are some guidelines on how to use and manage the file.

1. Keep a sample copy

Keep a sample (blank copy) of the completed file as a template. Give it an appropriate name. This file should contain only the structure. No data inside. Do remember the folder where you keep the file. Make sure you also have a back-up copy of this file, safely kept in a diskette, in case your computer becomes unusable.

2. Make a working copy

Make another copy. This is the copy in which you will enter data. Give it an appropriate name. We will refer to this copy as the 'working copy'.

Using the working copy file and capturing monthly transactions up to a certain date.

As you enter data from the first day and then from day to day, the Balance Sheet in sheet BS and the Profit and Loss statement (in sheet PL) and the Trial Balance (in sheet TB) will be updated automatically (unless you set Excel to manual calculation mode).

NOTE

Remember that when we make a copy of a file using File/Save As.., the active file that is being copied will be closed automatically while the copied (new) file becomes active instead. That is why we want to close the active file after we make the copy operation - the active file is actually the copy (new) file, and we don't want to add new data to the file anymore. We refer to this file as a historical copy.

You enter data into the working file from day to day. So, the working file always contains the most up-to-date data.

146

Normally, you would close the accounts up to a particular date, at each end-of-month, such as end of January, end of February, end of March, etc. However, you would not close the accounts as soon as you reach the end of the month. You would allow a few more days before closing. Some of you may allow a few weeks or even months before closing. This may be because you want to make sure that all relevant documents for the current period (e.g. supplier invoices) have been recorded. Other reasons for the delay could be the delay in receiving statements from banks.

As soon as you know that the transactions up to a certain date have been completely captured, you would close the accounts.

As an example, let's say the current date is 18 March 2003. Of course, data for March is not complete. Data for February may also not be complete because of the delays mentioned above. However, data for January may already be complete, so you will want to close the accounts up to the end of January.

Closing the January accounts is done by first making a copy of the working file. Use File/Save As. Give it an appropriate and easy-to-remember name such as Acc. Jan-03.

Since this is a copy of the working file, it contains data beyond the intended closing date, i.e. it contains data for February and March while we want only the data up to January. So, we will need to remove data related to irrelevant months (February and March, in this case). Here are the steps:

1. Use the menu Data/Filter/AutoFilter to set the data into Filtering mode.

2. Click the small downward arrow in cell C4 (containing MTH). Select Custom.

3. Set the Custom AutoFilter to read that you want to see only data of months 2 and 3 (because later, you will delete the rows containing months 2 and 3). Click OK.

4. Highlight all the rows starting from row 4 to the last row of data below.

5. To actually select only the visible cells, use the menu Edit/Go To/Specials/Visible Cells Only. Click OK.

147

This way, Excel will not select the hidden cells/rows, but will select only the visible cells/rows.

6. To delete all visible rows set above, use the menu Edit/Delete/Entire Row. Now, rows containing months 2 and 3 will be deleted, leaving only rows containing data of month 1 (January).

7. Now, remember to remove the filtering (use the menu Data/Filter/Show All) so that you can see the hidden rows which effectively contain data of month 1 only.

8. Save the file.

9. Your Balance Sheet, Profit & Loss statement, and the Trial Balance should be updated automatically to relect the data up to month 1.

10. Open the working file to continue entering data from day to day.

New accounting year

This matter is addressed in the next chapter.

148