New accounting year item 8 9 pg 156 157 158 159

8 Zerorise all credit amounts of suppliers invoices from the previous year which we have paid.

In the new accounting year, we do not want to bring forward the amount in the column RM-CREDIT which is an entry due to a previous year sales invoice to a customer but the invoice has been paid. We want to zerorise such amounts. We also want to zerorise the matching amounts in the column RM-DEBIT (payments by suppliers), so that the total RM-DEBIT still equals the total RM-CREDIT.

We need a way to identify those matching amounts, supplier by supplier. In other words, we want to identify rows where the name of a supplier appear in column ACCOUNT-CREDIT and also rows where the name of the same supplier appears in column ACCOUNT-DEBIT (not necessarily in the same row).

Since this involves 2 column titles i.e. ACCOUNT-DEBIT and ACCOUNT-CREDIT, we will use an AdvanceFilter operation to extract the relevant rows.

Just follow these steps:

1. Set a criteria (in the sheet Criteria) as shown here:

	A					B
1	ACCOUNT-DEBIT		ACCOUNT-CREDIT
2	SUPPLIER A
3				SUPPLIER A

The above criteria setting means that we want to select rows which contain the item 'SUPPLIER A' under the column ACCOUNT-CREDIT and also rows which contain the same supplier name under column ACCOUNT-DEBIT.

156

2. After we have set the above criteria, go to sheet DATA.

3. Make sure the active cell is not a blank cell and is inside the data area.

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

5. The List range box is automatically filled with the address of the data area. Check that it is correct, otherwise you have to correct your data area.

6. Fill the Criteria range box with the address of the criteria you set above. (To easily fill the box with the address, follow these steps: Make sure the insertion bar is already in the box. Click on the tab of the sheet where the criteria reside. Highlight the criteria block. You will notice that as you highlight the criteria block, the Criteria Range box is automatically filled with the address of the criteria block).

7. Click OK.

8. You should see the data is filtered according to the criteria you set. Look for matching amounts and zerorise them (replace with 0). You can check with items 'pd' in column CREDITOR STTMNT to confirm paid invoices. (Make sure you do not zerorise the current year invoices.)

9. Save the file.

10. Remove the filtering mode.

11. Repeat the process for each (other) supplier. This may take some time if you have many suppliers. However, there is no need to rush to finish all of them at one go. You can do so selectively.

9. Zerorise accruals amounts from the previous year which were paid

You usually have accrual accounts, such as Salary Payable, EPF Payable, Tax Payable, Electricity Payable etc. Usually the number of accrual accounts is not too many (around 8). The transactions for each account usually come up only once in a month. The payment is also usually made once in a month.

157

Now, we want to identify rows of transactions relating to accrual accounts from the previous year, account by account. We want to identify rows where column ACCOUNT-DEBIT and column ACCOUNT-CREDIT contain an accrual account name we are interested in. Since we will look for the items in 2 different columns, we will use AdvanceFilter operation. Here are the steps:

1. If you do not have a sheet to write criteria block, create one now.

2. Set the criteria block in the criteria sheet similar to that shown here:

	A			B
1	ACCOUNT-DEBIT		ACCOUNT-CREDIT
2	SALARY PAYABLE		
3				SALARY PAYABLE

This criteria block means that we want to select rows where there is item 'SALARY PAYABLE' in the column ACCOUNT-DEBIT as well as rows where there is item "SALARY PAYABLE' in column ACCOUNT CREDIT (the 'SALARY PAYABLE' item does not necessarily appear in the same row).

3. Activate the sheet where our data reside (sheet DATA).

4. Make sure the active cell is inside the data area.

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

6. The List range box is automatically filled with the address of the data area. Check that it is correct, otherwise you have to correct your data area.

7. Fill the Criteria range box with the address of the criteria you set above. (To easily fill the box with the address, follow these steps: Make sure the insertion bar is already in the box. Click on the tab of the sheet where the criteria reside. Highlight the criteria block. You will notice that as you highlight the criteria block, the Criteria Range box is automatically filled with the address of the criteria block).

8. Click OK.

158

9. Now you should see only rows containing transactions involving the account you set in the criteria block.

10.

10. Zerorise amounts in column RM-DEBIT and RM-CREDIT where the amounts matched. (If a particular credit entry is paid, replace both amounts in the RM-CREDIT and RM-DEBIT with zero). Make sure you do not zerorise the current year accruals.

11. Save the file.

12. Remove the filtering mode (menu Data/Filter/Show All).

13. Repeat the steps for other accrual accounts.

(This process will leave unpaid accrual amounts intact).

New accounting year item 6 7 pg 153 154 155 156

6. Replace the accumulated profit & loss b/f figure with the new one.

The suggested account name for accumulated profit/loss brought forward from the previous year is UNAPPROPRIATED PROFIT B/F. The unappropriated profit b/f figure you have in the file currently is a figure up to the end of previous 2 years (beginning of the last year). You can get the new unappropriated profit for up to the end of previous year (beginning of the current year) from the previous year's Balance Sheet.

The net profit & loss amounts you zerorise in step 4 and 5 above will tally with the profit figure for the previous year alone.

Actually, before you perform steps 4 and 5 above, you should find the total debit and credit amounts of profit and loss items that would be affected. The UNAPPROPRIATED PROFIT C/F (old) + Total debit amounts zerorised + Total Credit amounts zerorised should equal the current (new) UNAPPROPRIATED PROFIT B/F in the current working file.

153

To get the total debit amounts that would get zerorised, write this formula in cell AB5:
=IF(VLOOKUP(G5, INDIRECT("TB!A4:B2000"),2,FALSE)="UNAPPROPRIATED PROFIT", H5,0)

Use the SUM function in cell AB1 to get the total. Jot down this total somewhere.

To get the total credit amounts that would get zerorised, write this formula in cell AB5 (overwrite existing content):
=IF(VLOOKUP(I5, INDIRECT("TB!A4:B2000"), 2, FALSE)="UNAPPROPRIATED PROFIT", H5,0)

Use the SUM function in cell AB1 to get the total. Jot down this total somewhere.

Now try to get the sum of both figures, and compare this with the UNAPPROPRIATED PROFIT B/F as available in the previous year. The amounts should tally, and the accounts still balance.

This process should take less than 5 minutes.

7. Zerorise all amounts related to invoices from the previous year to debtors which have been paid (Optional).

In the new accounting year, we may not want to bring forward the amount in the column RM-DEBIT which is an entry due to a sales invoice to a previous year customer but the invoice has been paid. We want to zerorise such amounts. We also want to zerorise the matching amounts in the column RM-CREDIT (payments by customers), so that the total RM. DEBIT still equals the total RM-CREDIT.

We need to identify those matching amounts, customer by customer. In other words, we want to identify rows where the name of a customer appear in column ACCOUNT-DEBIT and also rows where the name of the same customer appears in column ACCOUNT-CREDIT (not necessarily in the same row), but in both cases, the date is before the new year.

Since this involves 2 column titles i.e. ACCOUNT DEBIT and ACCOUNT CREDIT, we will use an AdvanceFilter operation to extract the relevant rows.

154

Just follow these steps:

1. Set a criteria (in the sheet Criteria) as shown here:

	A					B
1	ACCOUNT-DEBIT		ACCOUNT-CREDIT
2	CUSTOMER A
3						CUSTOMER A

The above criteria setting means that we want to select rows which contain the item 'CUSTOMER A' under the column ACCOUNT DEBIT and also rows which contain the same customer name under column ACCOUNT CREDIT. (The items may not appear in the same row.)

2. After we have set the above criteria, go to sheet DATA.

3. Make sure the active cell is not a blank cell and is inside the data area.

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

5. The List range box is automatically filled with the address of the data area. Check that it is correct, otherwise you have to correct your data area.

6. Fill the Criteria range box with the address of the criteria you set above. (To easily fill the box with the address, follow these steps: Make sure the insertion bar is already in the box. Click on the tab of the sheet where the criteria reside. Highlight the criteria block. You will notice that as you highlight the criteria block, the Criteria Range box is automatically filled with the address of the criteria block).

7. Click OK.

8. You should see the data is filtered according to the criteria you set. Look for matching amounts and zerorise them (replace with 0). You can check with items 'pd' in column DEBTOR STTMNT to confirm paid invoices. (Make sure you do not zerorise current year invoices.)

155

9. Save the file.

10. Remove the filtering mode (menu Data/Filter/Show All).

11. Repeat the process for each (other) customer.

If you have many customers, you will take some time to finish on all of them. However, there is no urgency to finish all of them at one go. You can do so selectively.

New accounting year item 4 5 pg 151 152 153

5. Zerorise all Profit & Loss amounts in cells under column title RM-DEBIT relating to the previous year's transactions

We will replace all profit & loss amounts for the transactions for the previous year with 0 (zero) and update the amount for the UNAPPROPRIATED PROFIT B/F account.

Here is how you do it quickly:

In cell AB5 (column TEMP.), write this formula (overwrite existing content, if any):
=IF(AND(VLOOKUP(G5, INDIRECT("TB!A4:B2000"),2,FALSE)="UNAPPROPRIATED PROFIT", B5≤#12/31/2002#),0,H5)

This formula will write 0 (zero) to the cell AB5 (a temporary column) if cell G5 (in sheet DATA) contains a Profit & Loss item (by relying on the chart of accounts in sheet TB) if the date in cell B5 is earlier than or equal to the previous year and (#12/31/2002# in this case). This formula will write #NA if it finds that the account is not in the list in the chart of accounts.

Next, copy this formula down to the last row in the same column (TEMP) in the data area (To copy down fast: Select the cell AB5. Double-click on its bottom-right handle. This will copy its contents down to the last cell). Review for #NA items and make corrections as necessary.

151

Then, highlight all cells in this same column (from AB5 down to the last cell). To highlight all the cells quickly: Select cell AB5. Hold down the Ctrl key and press the Shift key. While still holding down Ctrl and Shift, press the downward arrow key. All relevant cells will be highlighted.

Now, we will copy the value (not the formula) of each highlighted cell into a corresponding area in column ACCOUNT-DEBIT. To do this, click menu Edit/Copy (make sure the cells are still higlighted). Go to cell H5 (under column title RM-DEBIT). Click menu Edit/Paste Special. A dialog titled Paste Special will appear. Under the Paste label, select Values (This means we want to copy the value, not the formula). Click OK. You should find that the Profit & Loss amounts under column RM-DEBIT now have been replaced with 0 values (without the formula).

6. Zerorise all Profit & Loss amounts in cells under column title RM-CREDIT relating to the previous year's transactions

Replace all amounts in column RM-CREDIT with 0 (zero) if its corresponding item in column ACCOUNT-CREDIT is a profit & loss item from the previous year.

Here is how you do it quickly:

In cell AB5 (column TEMP.), write this formula (overwrite existing content, if any):
=IF(AND(VLOOKUP (15, INDIRECT("TB!A4:B2000"), 2, FALSE)="UNAPPROPRIATED PROFIT", B5≤#12/31/2002#,0,H5)

This formula will write 0 (zero) into cell AB5 (a temporary column) if cell 15 contain a profit & loss account, and if cell B5 contains a date earlier than or equal to the prerious accounting year end (#12/31/2003# in this care). Otherwise it will write whatever amount is in cell J5 (under column RM-CREDIT).

Next, copy this formula down to the last row in the same column (TEMP) in the data area (To copy down fast: Select the cell AB5. Double-click on its bottom-right handle. This will copy its contents down to the last cell).

152

Then, highlight all cells in this same column starting from cell AB5. To highlight all the cells quickly: Select the topmost cell containing the formula (AB5). Hold down the Ctrl key and press the Shift key. While still holding down Ctrl and Shift, press the downward arrow key. All relevant cells will be highlighted.

Now, we will copy the amount (not the formula) of each highlighted cell into a corresponding area in column RM-CREDIT. To do this, click menu Edit/Copy. Go to cell J5. Click menu Edit/Paste Special. A dialog titled Paste Special will appear. Under the Paste label, select Values (This means we want to copy the value, not the formula). Click OK.

You should find that each amount under column RM-CREDIT where its corresponding item in column ACCOUNT-CREDIT is a Profit & Loss item has now been replaced with the figure 0 (without the underlying formula).

Save the file.

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.

Using and managing the current year file pg 146 147 148

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

Sorting data pg 144 145

Sorting Data

We can sort the data area based on any column. When we sort data based on a particular column, all the data in the other columns will be rearranged accordingly. Sorting data will change the original sequence of rows.

Do not sort only part of the data area because while the selected data will be sorted (rearranged), the other data will remain in their original rows (unless that is what you want). Sorting part of your data is dangerous and may render your data meaningless.

How To Sort Data

1. Make sure a cell in the data area is active.

2. Click menu Data/Sort.

3. A dialog titled Sort will appear:

Sort							?X

Sort by
 SRC					    Ascending
					    Descending
Then by
					    Ascending
					    Descending
Then by
					    Ascending
					    Descending
My list has
	Header row	No header row

Options...	OK	Cancel

144

4. Under the Sort by label, select the column title for the sort to be based on. You will notice that Excel automatically recognises the data titles if you had set the data area correctly. You can further sort the (sorted) data by selecting another column title under the Then by label.

The Ascending sort order is automatically selected for you. So too is the My list has Header row option. Here, Header row refers to the column titles. (Excel expects that your data has appropriate titles. If not, select No Header row.) This ensures that the 'header row' (data titles) will not be sorted. Only data will be sorted.

5. Click OK. Your data will be sorted according to the column (or columns) you selected.

Sorting Data In The Sheet DATA

If possible, do not sort the data in the sheet DATA. Sorting data in this sheet will alter the original sequence of rows you maintain for the data. For example, say you originally place all records for invoices in rows above the payments for the invoices. If you then sort the data, the rows for data payments may appear above the invoices. Proceed with sorting only if you do not need to maintain the sequence.

An alternative way then is to make a copy of the sheet and do the sorting in the copy sheet. Once finished with the work on the copy, you can delete the copy. This way, your original data is not affected at all.

If you, for some reason, still want to sort the data in the sheet DATA, use the column ORIGINAL ORDER to record the original sequence of rows of data. After you sort the data, you can always re-sort the data based on this column to revert to the original sequence of rows.

Sorting Data In The Sheet TB

You can sort data in the sheet TB based on any column. You can sort in ascending or descending order. Sorting of data on this sheet will not have any adverse effect.

145

Replacing data pg 142 143

Replacing Data

We can use searching ability of Excel to search for a particular, number, word, part of word, phrases, etc and replace it with another.

1. Use menu Edit/Replace. The following dialog box will appear:

Replace						?X

Find what:
som							Find Next
							Close
Replace with:
sam

Search:	By Rows
							Match case
							Replace

Find entire cells only
Replace All

2. In a box under the Find What label, enter the word or number that you want to find.

3. In a box under the Replace with label, enter the word or number that you want as replacement.

Remember, in the Replace dialog box, you can also ask Excel to search By Rows (row to row) or By Columns (column to column). Use the combo box on the right of label Search.

You can set the search to look for matching case by clicking in the box on the left of the Match case label. You can also set the search to look for cells containing only the search string you indicated (not other item) by clicking on a box on the left of Find entire cell only.

4. Click Find Next to start the search. If Excel does not find the item that you asked for, it will display a message to that effect. If Excel does find the item that you asked for, it will stop and highlight the cell which contains the item. Click Replace to make the replacement. Automatically, Excel will continue looking for the next occurrence of the word.

142

TIP

Always check that Excel correctly highlights the word that you intended to replace before you click the Replace button. For example, you may want to replace 'time' with 'tame', but Excel will also find the word 'sometime' (unless you mark Find entire cells only). In such a case, if you click to replace 'time' with 'tame', 'sometime' will become 'sometame',

If you select Replace all, Excel will search and replace at one go all occurrences of the word, without highlighting the item one by one. However, if you are not careful, you may replace 'sometime' with 'sometame' when you really want to replace 'time' with 'tame'.

5. Click Close or press the Esc key to close the dialog box.

If you think that you want to reverse the action, use menu Edit/Undo before saving the file. After you save the file, you will not be able to use the Undo.

The Replace function can also be accessed from menu Edit/Find. After you click this menu, the Find dialog box will appear. In the dialog, you can click the Replace button to open the same Replace dialog box shown above.

Searching for data pg 140 141

Searching For Data

Introduction

We can use the searching ability of Excel to search for a particular number, word, part of word, phrases, etc.

If your data has grown big, you may not remember easily the location of a particular word, number, or date. Excel's search facility will help you in this area.

1. Use the menu Edit --> Find. The following dialog box will appear.

Find						?X

Find what:					Find Next
					        Close
						Replace...

Search:	By Rows			Match case
				Find entire cells only

Look in:	Formulas

2. In a box under the Find What label, enter the word or number you want to find, then click Find Next button. Excel will highlight the first cell it finds which contain the word or number.

NOTE

A message will pop up if Excel does not find the item you asked for.

3. Click Find Next button again for Excel to find and activate the next cell which contain the word or number.

4. Click Close button or press Esc key to close the dialog box.

140 TIP

You can use wildcards (special characters) to find more specific data:

If we use the character ? (question mark) such as in sm?th, Excel will finds words containing any single character between sm and th, such as smith or smyth or smoth etc.

If we use the character * (asterisk) such as in *s, Excel will find all words ending with letter s.

In the Find dialog box, you can also ask Excel to search by rows (row to row) or by columns (column to column). You can set the search to look for matching case by clicking the box on the left of Match case. You can set the search to look for cells containing only the search string you indicated (not other items) by clicking on a box on the left of Find entire cells only.

The Edit --> Find function will not search all sheets, but only the current sheet.

Searching For Dates

Remember that internally, Excel stores dates in this format: m/d/yy or as per the setting in the Control Panel. You may see '2-Mar-1998' but internally it is 3/2/98. So, when you search for a date, use the format that Excel uses to store dates internally, not what you see on the screen. For example, use 3/2/98 to search for 2nd March 1998.

Searching For Numbers

Remember that internally, Excel stores numbers without any formatting (no comma or currency sign). You may see RM3,000.67 but internally it is 3000.67. So, when you search for a number, use the format that Excel uses to store numbers internally, not what you see on the screen. For example, enter 3000.67 in the Find what box, not RM3,000.67.

You can search for part of a number. For instance, if you search for "123", Excel will find "12345", "65123", "A123G" etc. To search for whole numbers only, put a check mark in the Find entire cells only box.

The Replace button will have the same effect as using the Edit/Replace menu.

141

Not completed yet

This blog is not completed yet

Front cover

BOOK-KEEPING WITH MICROSOFT EXCEL

You can use Microsoft Excel for all your book-keeping tasks, from recording raw data to preparation of Trial Balance, Balance Sheet, Profit & Loss statement, and other reports. This book shows you how.

ISMAIL HASHIM

Cover page

Excel 95, 97, 2000, XP

Microsoft Excel has long been used to help in book-keeping tasks. Many businesses rely on Excel to manage their book-keeping chores.

There are many different ways to implement it. This book shows one way, may be a better way.

In this book, formulas are used to automate updating of Trial Balance, Balance Sheet, Profit & Loss and other reports. Only a few formulas are used. This books shows what are the formulas. No programming at all. With AutoFilter, CustomFilter, AdvanceFilter and PivotTable operations, we can get the desired listings, summaries, sub-totals and totals immediately.

Just follow the guide in the book to build the data structure in an Excel file, set formulas and functions and you are ready to enter data. If you don't want to build from scratch, use a sample file provided with this book. This book is also serve as an extensive manual as your immediate reference while doing your book-keeping work.

Book-keepers, Accounts Assistants, Accounts Executives, Accountants, Accountig students would appreciate this book. Others with basic knowledge in the concept of debit and credit should be able to understand and use this material.

Introduction

Introduction

Here I show how to use Microsoft Excel as a complete book-keeping accounting tool.

Useful for freelancers, book-keeping service provider, small businesses, students and for those who want to work from home.

VBA is NOT used at all. 

Developing A Book-Keeping System With Microsoft Excel

Microsoft Excel has long been used to help in book-keeping and accounting tasks. Many businesses rely on Excel to manage their book-keeping chores.

There are many different ways to implement it. This book shows one way, perhaps better way It shows how you can use Microsoft Excel to do complete book-keeping tasks. Book-keeping tasks here means recording of data from its sources, preparation of Trial balance, Balance Sheet, Profit & Loss statement, and other reports.

You will see how formulas can be used to automate updating of your Trial Balance, Balance Sheet, Profit & Loss, and other reports. Only a few formulas are used. This book shows you all the formulas. No programming at all is needed. With Excel's AutoFilter, CustomFilter AdvanceFilter and PivotTable operations, we can get the desired listings, summaries, and totals immediately.

Just follow the steps outlined in this book to build the data structure in an Excel file, set the formulas and functions, and you will be ready to enter data. If you do not want to build the file from scratch, you can use the sample file provided with this book. This book also serves as an extensive user's manual which you can use as your immediate reference as you perform your book-keeping tasks.

Book-keepers, Accounts Assistants, Accounts Executives, Accountants, and Accounting students will find this book of interest. Other users, with a basic knowledge of the concept of Debit and Credit should be able to understand and use the material presented in this book.

Ismail bin Hashim

ibh1@live.com

Some Book-Keeping Functions That Can Be Done Using Microsoft Excel

Some Book-Keeping Functions That Can Be Done Using Microsoft Excel

Here are some of the book-keeping functions that can be performed using Microsoft Excel, and that will be shown in this book:

To record raw data.
To automate updating of the Trial Balance statement.
To automate updating of the Balance Sheet statement.
To automate updating of the Profit & Loss statement.
To generate and update Debtors Ageing report.
To generate and update Creditors Ageing report.
To generate and update monthly Sales summary report.
To generate and update monthly Purchases summary report.
To generate and update monthly Items purchased summary report.
To generate and update monthly Items sold summary report.
To generate data for preparing Statement of account.
To help in preparation of bank reconciliation.
5 To help in cash flow monitoring.
Filtering data to see only list of data that we want to see.

Are these functions sufficient for your needs?

Many other reports, detailed or summary, can be produced as long as we know how to use Excel's Filtering function, PivotTable function, and formulas.

Requirements

REQUIREMENTS

What are required to successfully implement book-keeping with Microsoft Excel ?

1. Initially, an empty Microsoft Excel file (Excel 95, 97, 2000, XP).

2. A basic book-keeping knowledge (particularly understand the concept of Debit and Credit).

3. A basic knowledge and hands-on experience in Microsoft Excel.

Why use Microsoft Excel for accounting

Reasons :

From staff view : If allowed and wish, he can bring home the work and finish at home, so the next day, there would be more time for him to do other works.

From Head of Dept view : He can ask for a copy of the file and look for it at home. At home, he can jot any query to be presented to his staff in next day.

If the file is in cloud, both persons ie staff and Head of Accounting Dept can see it live.

If the staff is knowledgable in Excel, he can use Excel for accounting work, thus he can save money for the company he works, ie by using Excel fully rather than buying accounting software which cost thousands of Ringgit, and save cost of periodic maintenance of the software.

Table of Content

TABLE OF CONTENT

CHAPTER 1 – BOOK-KEEPING FUNCTIONS THAT CAN BE DONE USING EXCEL
CHAPTER 2 – REQUIREMENTS FOR IMPLEMENTATION OF BOOK-KEEPING WITH EXCEL


BUILDING THE STRUCTURE

CHAPTER 3 – STEP 1 – CREATE A FOLDER (DIRECTORY)
CHAPTER 4 – STEP 2 – CREATE AND SET A FILE (WORKBOOK)
CHAPTER 5 – STEP 3 – SET THE WORKSHEETS IN THE FILE
CHAPTER 6 – STEP 4 – SET THE STRUCTURE IN THE SHEET ‘DATA’
CHAPTER 7 – STEP 5 – SET THE STRUCTURE IN THE SHEET ‘TB’

GUIDE TO INPUTTING DATA

CHAPTER 8 – GUIDE TO INPUTTING DATA IN SHEET ‘TB’
CHAPTER 9 – GUIDE TO INPUTTING DATA IN SHEET ‘DATA’

CREATING REPORTS

CHAPTER 10 – CREATING REPORT – TRIAL BALANCE
CHAPTER 11 – CREATING REPORT – BALANCE SHEET
CHAPTER 12 – CREATING REPORT – PROFIT & LOSS
CHAPTER 13 – CREATING REPORT – DEBT-AGEING
CHAPTER 14 – CREATING REPORT – CREDITORS AGEING
CHAPTER 15 – CREATING REPORT – PURCHASES
CHAPTER 16 – CREATING REPORT – SALES

FILTERING DATA

CHAPTER 17 – FILTERING DATA IN THE SHEET ‘DATA’
CHAPTER 18 – FILTERING DATA IN THE SHEET ‘TB’

SEARCHING, REPLACING AND SORTING DATA

CHAPTER 19 – SEARCHING FOR DATA
CHAPTER 20 – REPLACING DATA
CHAPTER 21 – SORTING DATA

REVIEW OF DATA AND STRUCTURE

CHAPTER 22 – REVIEW OF DATA AND STRUCTURE

USING THE FILE

CHAPTER 23 – HOW TO USE THE FILE
CHAPTER 24 – NEW ACCOUNTING YEAR
CHAPTER 25 – MANAGING INCREASING NUMBER OF FILES

BEYOND DATA ENTRY

CHAPTER 26 – MONITORING CASH BALANCE
CHAPTER 27 – STATEMENT OF ACCOUNT
CHAPTER 28 – GETTING LEDGER DETAILS OF AN ACCOUNT
CHAPTER 29 – BANK RECONCILIATION

OTHERS

CHAPTER 30 – POTENTIAL PROBLEMS
CHAPTER 31 – SHORTCUTS AND TECHNIQUES
CHAPTER 32 – FUNCTIONS USED IN THE FORMULAS
CHAPTER 33 – SEGREGATION OF DUTIES, NETWORKING AND INTERNET
CHAPTER 34 – PIVOTTABLE OPERATION PRIMER
CHAPTER 35 – ADVANCE FILTER PRIMER
CHAPTER 36 – BOOK-KEEPING PRIMER
CHAPTER 37 – THIS SYSTEM AND ITS SUITABILITY
CHAPTER 38 – SAMPLE DISK
CHAPTER 39 – CONTACTING THE AUTHOR

Create a folder (directory)

Here are the steps to develop a book-keeping system with Microsoft Excel.

Step 1- Create A Folder (Directory)

You should be familiar with the Windows computer operating system and know how to create a folder in a disk. Now, create a new folder in an easy-to-access path, such as in the root directory of disk C, not in a sub-folder. Give it an easy-to-remember name, say: AccountsXL. So, the path would look something like this: C:/AccountsXL.

All your accounts files will be saved into this folder.

You can choose another folder or some other name for the folder if you feel that it is more convenient.

Create and Set a new blank file (workbook)

Step 2 - Create And Set A File (Workbook)

After you create a folder, open Microsoft Excel (95, 97, 2000 or XP). Then, create a new blank Microsoft Excel file (menu File/New). In Excel, a file is called a workbook. Save the file into the AccountsXL folder (or some other folder of your choice). Give the file this (suggested) name: 0000 Book-Keeping Sample.xls. You can use some other name if you wish.

This file will become the SAMPLE or MASTER copy file for our book-keeping functions.

NOTE By putting zeros (0000) in the front part of the file name, you ensure that the file will always appear first (topmost) in the folder that it is in. Although one zero will suffice, I use 4 zeros to make it more obvious.

(The disk accompanying this book contains a copy of this sample file).

Initial Sheet in the file (workbook)

- Open a new workbook (This will automatically create a blank workbook).

- Save the new workbook. In the process of saving a new workbook, you will be asked to give a name for the workbook. Give the new workbook a suitable name that reflect what data it will contain. For example, if the name is "AccJan2012.xls" it will contain accounting data for January 2012.

Other than name, you also be asked to state or select which folder you want to put the workbook.

A workbook is same as a file.

A workbook (or a file) consist sheet. Initially you are provided with one sheet. You can add more sheet or delete sheets.

A sheet consist of rectangular boxes. You write into these boxes.

When you firstly open the new file, initially, there is only one sheet in the new file that you have just created.

In Excel, sheet is also called worksheet.

Rename the sheet as : DATA. (This name is used throughout this book and in formulas in the file. Do not use other name unless you are aware of the effect).

Later, when you have more sheets, you can arrange the sheets in any order.

Sheet DATA will contain all your raw transaction data (such as opening balances, sales, purchases, receipts, payments, journals, etc).

TIP Usually we use the mouse to move from sheet to sheet by clicking the intended sheet. You can use a combination of keyboard keys, instead. Use Ctrl+Page Down to move to the sheet on the right (Hold down the Ctrl key, and press the Page Down key. The next sheet will become active.). Use Ctrl+Page Up to move to the sheet on the left. By depending less on the mouse, you can speed up your work, but this requires a good memory.

Structure in sheet Data

Where to enter data

1. Use a dedicated sheet.
2. Name the sheet appropriately. In this book, its name is "Data". This name is used in formulas in this book.

Sample structure

	A	B	C	D	E	F	G	H	I	J
1	SRC	DATE	MTH	SRC. NO.	2ND SRC. NO.	Note 1	Note 2	Note 3	Note 4	Note 5
2
3
4
5
6
7
8
9	Subtotal
10	Checking
11	Cut-off Date		(date)
12	LastRowDATA		(formula)

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

Sample structure

	A	B	C	D	E	F	G	H	I	J
1	SRC	DATE	MTH	SRC. NO.	2ND SRC. NO.	Note 1	Note 2	Note 3	Note 4	Note 5
2
3
4
5
6
7
8
9	Subtotal
10	Checking
11	Cut-off Date		(date)
12	LastRowDATA		(formula)

(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

	Q	R	S	T	U	V	W
1	Debt Age	Debt Age Group	Supplier Inv. Status	Credit Age	Credit Age Group	Due Date	Items
2	(formula)	(formula)		(formula)	(formula)
3
4
5
6
7
8
9
10
11
12

	X	Y	Z	AA	AB	AC	AD
1	Qty	Unit of Measure	Price	BS or PL	Original Order
2
3
4
5
6
7
8
9
10
11
12

Explanation for the structure in sheet Data

Explanation for the sample structure

1. Cells in row 1 from A1 to N1 are to contain headings.
2. Column O serves as the right-side border. Colour its cells as visual indicator.
3. Rows for transaction data starts at row 2.
4. Let the range A2:N7 as the initial Data Area. The Data Area is dynamic as it will expand or contract as we add more row/s or delete row/si.
5. Make the row immediately below the Data Area as the bottom-side border. Colour its cells as visual indicator.
7. The bottom-side border row is also dynamic as it will move downward or upward as we add more rows of data or delete a row above it.
8. Designate a row immediately below the bottom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side border row.
9. Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
10. Designate a row immediately below the Checking Row as Cut-Off row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
11. In the sample, we put a label Last Row Data in cell A13. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C13 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.

Dynamic rows

To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row) :

BottomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA

Sample formulas in use in sheet Data'

Cell	Formula
J2	=ROUND(G2*H2,2)
K2	=IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK")
M2	=IF(J2=J1,M1+L2,L2)
P2	=IF(O2="open",CutOffDate-B2,"NR")
Q2	=IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR")
Z1	=ROW(LastRowTB)
G9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
J9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
M9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
V9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
C12	=ROW(LastRowDATA)

Certain columns need to be formatted with suitable formatting

Column	Heading	Format
A	Source	Text
B	Date	Date (eg dd-mmm-yyyy)
C	Month	Text
D	Source No.	Text
E	Other source and no.	Text
F	Note 1	Text
G	Note 2	Text
H	Note 3	Text
I	Note 4	Text
J	Accounts	Text
K	Checking Accounts	Text
L	Amounts DR/CR	Number
M	Cumulative Amounts	Number
N	Bank Reconciliation marking	Text
O	Sales Invoice Status	Text
P	Debt Age	Number
Q	Debt Age Group	Number
R	Debt/Credit Term	Text
S	Due date	Date
T	Item purchased/Sold	Text
U	Quantity Purchased/Sold	Number
V	Unit of measurement	Text
W	Price	Number
X	BS or PL marking	Text
Y	Original order	Number

Changing the structure

You can design the structure differently as you wish.

To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row) :

BotomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA

Sample formulas in use

Cell	Formula
J2	=ROUND(G2*H2,2)
K2	=IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK")
M2	=IF(J2=J1,M1+L2,L2)
P2	=IF(O2="open",CutOffDate-B2,"NR")
Q2	=IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR")
Z1	=ROW(LastRowTB)
G9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
J9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
M9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
V9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
C12	=ROW(LastRowDATA)

Certain columns need to be formatted with suitable formatting

Column	Heading	Format
A	Source	Text
B	Date	Date (eg dd-mmm-yyyy)
C	Month	Text
D	Source No.	Text
E	Other source and no.	Text
F	Note 1	Text
G	Note 2	Text
H	Note 3	Text
I	Note 4	Text
J	Accounts	Text
K	Checking Accounts	Text
L	Amounts DR/CR	Number
M	Cumulative Amounts	Number
N	Bank Reconciliation marking	Text
O	Sales Invoice Status	Text
P	Debt Age	Number
Q	Debt Age Group	Number
R	Debt/Credit Term	Text
S	Due date	Date
T	Item purchased/Sold	Text
U	Quantity Purchased/Sold	Number
V	Unit of measurement	Text
W	Price	Number
X	BS or PL marking	Text
Y	Original order	Number

Changing the structure

You can design the structure differently as you wish.

Explaination on the sample~1. Cells in row 1 from A1 to N1 are to contain headings.
2. Column O serves as the right-side border. Colour its cells as visual indicator.
3. Rows for transaction data starts at row 2.
4. Let the range A2:N7 as the initial Data Area. The Data Area is dynamic as it will expand or contract as we add more row/s or delete row/si.
5. Make the row immediately below the Data Area as the bottom-side border. Colour its cells as visual indicator.
7. The bottom-side border is also dynamic as it will move downward or upward as we add more rows of data or delete a row.
8. Designate a row immediately below the borrom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
9. Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
10. Designate a row immediately below the Checking Row as Cut-Off row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
11. In the sample, we put a label Last Row Data in cell A13. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C13 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.

Dynamic rows

More explanation for structure in sheet Data Page 6 7 8 9 10

Row 1

In cell Al, type:

TOTAL/SUBTOTAL

This is to indicate that some of the other cells in row 1 are used to accommodate formulas which calculate totals/subtotals.

In cell H1, write this formula:
SUBTOTAL (9, INDIRECT("H5: H30000"))

This formula calculates the total of amounts in cells in column H (from row 5 to row 30,000). (We will see later that cells H5 to H30000 will contain amounts for debited accounts).

In cell J1, write this formula:

=SUBTOTAL(9, INDIRECT("J5:J30000"))

This formula calculates the total of amounts in cells in column J (from row 5 to row 30000). (We will see later that cells J5 to J30000 will contain amounts for credited accounts).

In cell T1, write this formula:

=SUBTOTAL (9, INDIRECT("T5:T30000"))

This formula calculates the total of amounts in the cells in column T (from row 5 to row 30000). (We will see later that cells T5 to T30000 will contain amounts of payment which we still holding, such as cheques we still hold).

In cell Y1, write this formula:

=SUBTOTAL(9, INDIRECT("Y5:Y30000"))

This formula calculates the total of amounts in cells in column Y (from row 5 to row 30000). (We will see later that cells Y5 to Y30000 will contain quantity values).

6

Other cells:

You can use other cells in row 1 for other purpose.

Change of formula:

You can change the formulas if you know the effect.

NOTE Here, you are introduced to the SUBTOTAL function. You can refer to Topic 29 for an explanation of this function as well as other functions used in this book.

Row 2

In cell A2, type Checking. This is to indicate that some of the cells in row 2 are used to accommodate formulas that will check the logic of the results of the other formulas in our file.

For example, cell J2 contains a formula which checks the result of the formulas in cell H1 and J1. The sum of cell H1 and cell J1 should be zero (total Debit amounts minus Total credit amount). The formula is:

=IF(H1+J1=0, "Ok", "Please check")

This formula will show OK if the sum is zero. Otherwise, it will show Please Check to indicate to us to check something. You can read about the IF function in Topic 29.

You can use other cells in row 2 for other purpose.

Row 3

All cells in row 3 should be left blank. This row serves as a border for the data area (rows of data which encompasses row 4 to 30000). If any cell is not blank, some operation will not perform correctly, particularly data filtering and PivotTable functions. We can use data/validation function to help us prevent entry of into the cells. (Refer to Topic 28 on 'Tips And Shortcuts').

7

Row 4

Cells in row 4 are to contain the data titles (or field titles or column titles) for the columns A to AC. The data titles are used to indicate what data should be put in cells under each title.

In cell A4: Write SRC (for SOURCE)
In cell B4: Write DATE
In cell C4: Write NTH (for MONTH)
In cell D4: Write SRC NO (for SOURCE NUMBER)
In cell E4: Write OTHER REF/NO (for OTHER REFERENCE or REFERENCE NUMBER)
In cell F4: Write NOTE
In cell G4: Write ACCOUNT DEBIT
In cell H4: Write RN-DEBIT
In cell 14: Write ACCOUNT CREDIT
In cell 14: Write RM-CREDIT
In cell K4: Write CHECK ACCOUNT DEBIT
In cell L4: Write CHECK ACCOUNT CREDIT
In cell M4: Write BANK RECON (for BANK RECONCILIATION).
In cell N4: Write DEBTOR STTMNT (for DEBTOR STETEMENT).
In cell 04: Write DEBT AGE (days)
In cell P4: Write DEBT AGE GROUP
In cell Q4: Write CREDITOR STTMNT (for CREDITOR STATEMENT).
In cell R4: Write CREDIT AGE (days)
In cell 54: Write CREDIT AGE GROUP
In cell T4: Write HOLD (for PAYMENT/CHEQUE or RECEIPT ON HOLD).
In cell U4: Write TERM (for CREDIT TERM- Ours and Suppliers).
In cell V4: Write DUE DATE
In cell W4: Write ITEM PURCHASED
In cell X4: Write ITEM SOLD
In cell Y4: Write QTY (for QUANTITY)
In cell Z4: Write UNIT (for UNIT OF MEASUREMENT)In cell AA4: Write PRICE
In cell AB4:Write TEMP (for TEMPORARY).
In cell AC4:Write ORIGINAL ORDER.


8

Do not rearrange the order of the titles, because it will affect the formulas and explanations in this book, unless you know the effect.

Do not change the spelling of the titles, because it will also affect the formulas and explanations this book, unless you know the effect.

Row 5

This is where you can start to input data (the first row for data). Data must start at this row.

Some cells in row 5 will contain initial formulas. Later on, when you add more data into cells in rows below it, just copy the formula down.

TIP Use the keyboard key combination of Ctrl+D in a cell to copy into it the contents of the cell above it. (Hold down the Ctrl key and press the D key.)

Be careful not to accidentally delete the cells containing formulas. Keep this book handy for reference.

In cell K5, write this formula:

=IF(ISERROR(VLOOKUP(G5, INDIRECT("TB!A5: A2000"),1,FALSE))," "NOT OK", "OK")

This formula checks whether the account stated in cell G5 below the data title ACCOUNT-DEBIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, you should add the account name into the list, or replace the account name with one that already exists.

Later on, when you add more data into cells in rows below it, just copy the formula down.

You can read more about the functions ISERROR, VLOOKUP and INDIRECT in Topic 29.

9

This formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must change the parameter 2000 in the formula to the actual one.

You should review this column (column K) from time to time to check for non-existent accounts in column G.

In cell L5, write this formula:

=IF(ISERROR(VLOOKUP(IS, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "OK")

This formula checks whether the account stated in the cell below the data title ACCOUNT CREDIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, add the account name into the chart, or replace the account name with one that already exists.

Later on, when you add more data into cells in rows below it, just copy the formula down.

Again, this formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must replace the parameter 2000 in the formula with the actual one.

You should review this column (column L) from time to time for non-existent accounts in column I.

In cell 05, write this formula:

=IF(N5="open", CutOffDate-B5,"NR")

This formula computes the age (in days) of unpaid sales invoice. In cell N5, we will state open if the invoice is still unpaid. The Sales invoice date is supposed to be available in cell B5. Cutoff Date is a name we set for our cut-off date. So, if cells in cell N5 contain open, the formula will subtract the cut-off date from the invoice date. If cell N5 does not contain open, the formula will return HR (for Not Relevant) to the active cell (05).

10

More explanation for structure in sheet Data Page 11

Later on, when you add more data into rows below it, just copy the formula down.

NOTE

We set the name CutoffDate to refer to a cut-off date by using the menu Insert/Name/Define. When we click this menu, the Define Name dialog box shown below will appear. In the box below the label Names in workbook, write the name Cutoff Date. In the box below the label Refers to, write the date it refers to. Be aware that you must precede the date with "-" (equal) sign. (Also remember that in Excel, you must write dates in this format: Month/Day/Year, i.e. the month, followed by the day, and then the year separated by "/" (forward-slash) and enclosed inside (double-quotes). Click Add. Excel will remember the name and what it refers to. Click OK to close the dialog.

Define Name Names in workbook: CutOffDate OK Close Add Delete Refers to: ="4/30/2002"

In cell P5, write this formula:

=IF(N5="open", IF (05<30,"0-30", IF (05<60, "30-60", IF (05-90, "60. 90", IF (05 120, "90-120", ">120")))),"NR")

11

More explanation for structure in sheet Data Page 12 13 14

This formula will check whether cell NS contains the item open. If so, it will return the debt age group (in days) of the corresponding apes (unpaid) sales invoice. (Remember that in column N, we will state open if the invoice is still unpaid). The debt age is expected to be available in column O. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell NS does not contain open the formula will return NR (for Not Relevant) to the active cell (P5)

Later on, when you add data into cells in rows below it just copy the formula down.

In cell R5, write this formula:

=IF(Q5="open", CutOffDate B5. "MR")

This formula computes the age (in days) of unpaid suppliers invoice. (In cell Q5, we will state open if the supplier invoice is still unpaid). The Supplier invoice date is supposed to be available in cell B5 (Cutoffdate is a name we set for our cut-off date. Refer to the Note above on how to set Cutoffdate) So, if cell Q5 contains open, the formula will subtract the cut-off-date from the invoice date. This results in the number of days unpaid. If cell Q5 does not contain open, the formula will return (for Net Relevant) to the active cell (R5).

Later on, when you add more data into cells in rows below it just copy the formula down.

In cell S5, write this formula:

=IF(Q5="open", IF (R5<30 60.="" 90="" if="">120")))), "NR").

This formula computes the group for the credit age (in days) of unpaid suppliers invoice. In cell Q5, we will state open if the invoice is still unpaid. The credit age is supposed to be available in cell R5. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell Q5 does not contain open, the formula will return NR (for Not Relevant) to the active cell.

Later on, when you add data into cells in rows below it, just copy the formula down.

12

In cell V5, write this formula:

=IF(ISNUMBER(U5), B5+U5, "NR").

This formula computes the due date for payment to supplier/by customer. The formula will add the term (in days, in cell U5) to the invoice date in cell B5. This will produce the due date. If cell U5 does not contain a number type, the formula will write NR to the active cell (V5).

Later on, when you add more data into cells in rows below it, just copy the formula down.

Freeze row 5:
You may want to freeze row 5 so that you can still see the data titles in row 4 when you scroll down.

Row 30000

This is the expected last row for the data area. This row number will appear in many formulas. Why 30000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this row number insufficient, you can increase the number in every formula. Similarly, if your find that row 10000 is enough for your needs, you can change the 30000 in each affected formula to 10000.

To easily find and change every number or character (30000 in this case) in a formula, just use menu Edit/Replace (Refer to topic 27 on 'Shortcuts And Techniques For Working Better And Faster').

Data area

Data area is a rectangular area (bounded by a blank row on top, a blank column on the right, a blank row at the bottom and a filled column in the left) which is expected to contain the data. In this book, the data area

13

starts at cell A5 and ends at cell AC30000. Why 300000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this number insufficient, you can increase the number in every formula. To easily change every number or character in a formula, just use the Edit Replace function. Similarly, if your find that row 10000 is enough for you, you can change every formula to 10000.

Formatting

Certain columns need to be formatted with appropriate format to accommodate proper types of data. Usually, data can be a 'text' type, 'date' type or 'number' type. Columns to accommodate 'date' type must be formatted with 'date' formatting. Columns to accommodate 'number' type must be formatted with 'number' formatting. Columns to accommodate text 'type' must be formatted with 'text' formatting.

This book does not show you how to format a cell since the author expects that you already know how to perform this basic task.

Entering transactions Data in sheet Data

Where to enter transactions data

Transactions data must be entered in the Data Area. The Data area begins from row 2 until the row above bottom-side-border row (dynamic).

What are the sources for entering data

The sources for entering data are documents such as sales invoice, supplier invoice, payment voucher, etc.
Upon receiving the source document, you record its particulars into the sheet Data.

What are the particulars we want to record?

Usually the minimum details we want to record are the name of the source, date of the source, the reference number of the source, what accounts to debit, what accounts to credit, the debit amounts, the credit amounts, etc.

Where to start entering data

Start entering data at row 2 from column to column, guided by the column headings as to what particular to enter.

How to enter Opening Balances

Opening balances are list of balances carried forward from end of prevoius accounting period. One item requires only one row.

Preferably all opening balances are grouped together (located in rows adjacent to each other) and put on top portion of the sheet.

Sample structure

	A	B	C	D	E	F	G	H	I	J
1	SRC	DATE	MTH	SRC. NO.	2ND SRC. NO.	Note 1	Note 2	Note 3	Note 4	Note 5
2	OB	1.1.12	0
3	OB	1.1.12	0
4	OB	1.1.12	0
5	OB	1.1.12	0
6	OB	1.1.12	0
7	OB	1.1.12	0
8
9	Subtotal
10	Checking
11	Cut-off Date		(date)
12	LastRowDATA		(formula)

	K	L	M	N	O
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts
2	CAPITAL	-5000	(formula)	(formula)
3	RETAINED PROFITS	-1000
4	SUPPLIER ABC	-2000
5	CUSTOMER EFG	3000
6	FIXED ASSETS	2000
7	CASH IN BANK	3000
8
9		(formula)
10
11
12

Note :

- OB means Opening Balance.

- As to the date of the Opening Balances, you can put the date of the beginning of the accounting period, or you can put the original date. For example, for the item Capital you can put the original date it first get recorded or the date of the beginning of the current accounting period.

- For Opening Balances, put the month (MTH) as zero (0), no matter what date you put in column DATE.

- If one opening balance has potential to be splitted into more detail, you may choose to show more detail rather than only one row. For example, one opening balance for a creditor may consist of 5 outstanding invoices, so you may show all the 5 invoices particulars, each invoice in a separate row.

How to enter Sales Invoices particulars

- Sales invoices is one of the source documents.
- One invoice will involves at least 2 accounts, one debit and one credit. So one invoice affecting 2 accounts will need 2 rows, one row to record the debit particulars and the other row to record the credit particulars. If the invoice affects 3 accounts then we need 3 rows. Most of the particulars are repeated or duplicated in the rows.

Shall we enter source data based on the date order?

No. Ignore the date order at this stage because we can use Excel to later sort data in date order if you want to.

Can we group data according to source?

You can enter data as the source come one by one from row to row. into rows there is no need to group data according to source of transactions

Must we enter data row by row ?

Can we skip row or rows?

Rules

1. Do not allow any blank cell in column A when entering transaction data.
2. Ensure all cells in column Z are blank.
3. Ensure all cells in Bottom-side border are blank.

What to enter below the headings

How to enter Debit and Credit particulars

Use a row to record a debit entry particulars and another row to record a credit entry particulars. Yes, some data on the debit row are duplicated on the credit row.

2. Use minus sign to indicate credit amount (eg -6000).

Insert and Delete Row

You can insert or delete row but be careful not to delete the total row or the LastRowData if you don't want to reinsert the row.

Automatic update

Sample

General guide to entering data in sheet Data

GENERAL GUIDE TO INPUTTING DATA IN SHEET 'DATA'

Once the structure of DATA sheet are ready, you can start inputting data in sheet ‘DATA’.

Data that you want to input is based on the financial transaction document we have on hand, such as invoice, vouchers, journals, etc. Enter the data available in each document in the appropriate cell under an appropriate column title in the same row.

A row containing data of a document in its appropriate column is called ‘Transaction row’. Sometimes, data in a row is called a ‘record’.

Data should start at row 5 and continue until row 30000. If your data exceed row 30000, change all references to “30000” in all formula in all sheets.

Entering data in sheet Data column SRC

Filling cells under column title ‘SRC’

Under the column title 'SRC' (for Source), enter a name for your source of transaction. The source refers to a group of documents having similar function that you have, such as invoices, vouchers, journals etc.

Below are some of the common sources :

1. Opening Balances

If this is the first time you use this system, or in a new accounting year, opening balances is one source of transaction. You may write 'OB' or other term of your choice to identify Opening Balance source.

2. Audit Adjustment (previous accounting year)

It is good to identify Auditor's adjustment as a separate source, rather than group them under journals. You may write 'AUDIT ADJ' or other term to identify Audit Adjustment.

3. Provisions for expenses

It is good to identify provisions for expenses as a separate source. Usually, we use journals to be our source for provisions. In this case, we identify journal for provision separate from other journals. You may write 'PROV' or other term to identify Provisions.

4. Purchase (Supplier) Invoices

This refers to invoices from local suppliers. You may write 'INV' or other term to identify Purchase Invoices.

5. Import Invoices

This refers to invoices from oversea suppliers. You may write 'IMPORT' or other term to identify Import Invoices.

6. Salary-Production

This refers to salary summary for production workers. Accounts staff may get the summary from payroll system.

It is good to obtain separate salary summary for production staff from administrative staff. You may write 'SALARY-P' or other term to identify Salary summary for production staff.

7. Salary-Admin

This refers to salary summary for administrative staff. Accounts staff may get the summary from payroll system. It is good to obtain separate salary summary for administrative staff from production staff. You may write 'SALARY-A' or other term to identify Salary summary for administrative staff.

8. Bonus-Production

This refers to bonus summary for production staff. Accounts staff may get the summary from payroll system. It is good to obtain separate bonus summary for production staff from administrative staff. You may write 'BONUS-P' or other term to identify bonus summary for production staff.

9. Bonus-Admin

This refers to bonus summary for administrative staff. Accounts staff may get the summary from payroll system. It is good to obtain separate bonus summary for production staff from administrative staff. You may write 'BONUS-A' or other term to identify bonus summary for administrative staff.

10. Sales Invoices

This refers to our sales invoices. You may write 'SALES' or other term to identify sales invoices.

11. Sale-Subsidiary XX

This refers to sales invoices to one of our subsidiary with name XX. It is good to identify sales to each subsidiary as separate source. You may write 'SALE-X' or other term to identify sales to company X (a subsidiary).

12. Sale-Associate co. XX This refers to sales invoices to one of our accociate company. It is good to identify sales to each associate company as separate source. You may write 'SALE-X' or other term to identify sales to company X (an associated company).

13. Sale-Holding company

This refers to sales invoices to our holding company. It is good to identify sales to holding company as separate source. You may write 'SALE-H-X' or other term to identify sales to company X (holding company).

14. Petty Cash

This refers to one of petty cash transactions (receipts and payments). It is good to identify each petty cash float as separate source. You may write 'PC-X' or other term to identify Petty Cash float managed by staff X.

15. Banks

This refers to one of banks transactions (receipts and payments). It is good to identify each one bank as separate source. You may write 'BANK X' or other term to identify Bank X transactions.

16. Interest on HP

This refers to interest on hire purchase of one item. It is good to identify each item under hire purchase as a separate source. Usually, we use journals to be our source for interest on Hire Purchase. It is good to identify interest on hire purchase as separate source from general journals. You may write 'INT-HP-X' or other term to identify Hire Purchase Interest for item X.

17. Depreciation

This refers to depreciation of one type or category of fixed assets. It is good to identify depreciation of each one type of assets as separate source rather than lump them all together. You may write 'DEPR-X' or other term to identify Depreciation for item X.

18. Journal

This refers to general journals, ie journal which could not be conveniently identified as separate source. As we see, normally depreciation and interest on hire purchase will lump under journals. But since we can conveniently identify them as separate source, we do separate it. You may write 'JOURNAL' or other term to identify journals.

19. Closing Stock-Raw material

This refers to closing stock of raw material. Accounts staff may get the summary from store system. You may write 'CS-RM' or other term to identify Closing Stock of Raw Material.

20. Closing Stock-Finished Product

This refers to closing stock of finished product. Accounts staff may get the summary from store system staff. You may write 'CS-FP' or other term to identify Closing Stock of Finished Product.

21. Closing Stock-Semi-Finished Product

This refers to closing stock of semi-finished product. Accounts staff may get the summary from store system. You may write 'CS-SF' or other term to identify Closing Stock of Semi-Finished Product. Variances in source identified. You may identify sources differently eventhough the above samples are most common. Below are some of the differences :

Some company file their suppliers invoices in a file for each supplier, rather than ALL supplier invoice in ONE file. If this is the case, it is more appropriate if the source is written such as "INV-SUPPLIER XXX", "INV-SUPPLIER YYY", and so on. The sample in this book assume that ALL supplier invoices are kept in ONE file.

Some company file their sales invoices in the separate file for each customer, rather than ALL sales invoice in ONE file. If this is the case, it is more appropriate if the source is written such as "INV-CUST-XXX", "INV-CUST-YYY", and so on. The sample in this book assume that ALL sales invoices are kept in ONE file.

However, some companies generate more than one internal copies for sales invoice. They keep one copy in a master file and another copy in the respective customer file. If this is the case, you can use either way as source.

Why we want to identify sources ?

Apparently, source can be said best as a group of document that get filed together. Proper identification of source will help you easily refer back and search to your physical (paper) document.

Entering data in sheet Data column SRC Notes on Opening Balance

Notes On Entering Opening Balance Data

No double entry

Note that the opening balance data does not involve double entry (Debit and Credit) transactions. It is merely a listing of balances from closing of previous accounting year.

When you enter a Debit item data, there is no corresponding Credit account and amount to write. So, just leave blank the Credit account column and Credit amount column. For the remaining cells in the row, just write NR (for Not Relevant).

Similarly, when you enter a Credit item, there is no Debit account and amount to write. So, just leave blank the Debit account column and Debit amount column. For the remaining cells in the row, just write NR.

However, instead of a blank, you can create an account name such as OPENING BALANCE.

Put Debit and credit items on different rows

Do not write a Debit item data on the same row as a Credit item data. This is because you will encounter difficulties when you want to add a note (comments) to both items since there is only one note column. Also, if sales invoice is pitted against purchase invoice, both will chase for only one 'due date' column.

However, if you can manage these problems, you can put Debit and Credit items data on the same row. This can save rows.

More details

It is better that the opening balances items data contain more details. For instance, instead of one figure for a customer, list down all unpaid invoices for that customer. Instead of one figure for whole staff advance, list down all unpaid staff advances for every staff. Also, instead of one figure for a supplier, list down all unpaid purchase invoices for the supplier, etc.

If you are transferring data from other system, you may need lots of time to record the details, especially if the system is not compatible with Excel.

If you already using this system, transferring details to the following year file is very easy. This is because you will have a ready list of unpaid invoices etc row by row. It is just a matter of selecting and transferring (or copying and deleting) the data.

Check data

After you finish entering the Opening Balances items data, check that its absolute total debit amounts agree with total credit amounts. Just look at the cells H1 and J1.

42

Entering data in sheet Data column B C D E page 31

Filling cells under column title ‘DATE’ (Column B).

Under the column title 'DATE', enter the date of the transactions as appear on the document. The date of transactions is usually typed on the document.

Remember that to enter date, use / (forward-slash) sign to separate month, day and year. Remember also that Excel expect you to enter month first, followed day and then year. Internally, Excel keep date by month first, followed by day and year. So, if you enter 1/2/2002, it will mean 2nd January. It can avoid mistake by Excel if you enter year in 4 digits rather than 2 digits.

Make sure the cell that contain the date is formatted appropriately. If you choose format 'm/d/yyyy', then you enter date 1/2/2002, it will appear the same order ie 1/2/2002 (Internally, this read as 2 January 2002). But if you choose format 'd/m/yyyy', then you enter date 1/2/2002, you will see different order ie 2/1/2002 (though this still read 2 January 2002, internally). So, be careful with formatting of date.

Filling cells under column title ‘MTH’ (Column C)

Under the column title 'MTH', enter the month of the transactions. Use numbers to indicate month, such as 1, 2, 3 etc. This can save hard disk space.

This column is useful if you later want to analyse data according to month.

You can use formula to extract month from the date in column DATE, but I do not recommend it. This way (not using formula), eventhough the date say 2/28/2002 (Month :February), you may want to include this transaction under month 3 (March) when you analyse data or in your report, for whatever reason.

Use 0 (zero) to indicate month of the opening balances items.

Filling cells under column title ‘SRC NO’ (Column D)

Under the column title 'SRC NO', enter the serial number or reference number of the source document. For example : Sales Invoice number, Purchase Invoice number, journal number, voucher number, etc. If there is no reference number, leave blank or you can replace with some other notes.

Filling cells under column title ‘OTHER REF./ NO.’ (Column E)

(OTHER REF./NO. refers for OTHER REFERENCE OR NUMBER)

Under the column title 'OTHER REF./NO.', enter the other serial or reference number of the source document or other document relevant to the source document. For example : You have enter sales invoice number under SRC NO, so you may want to enter the PO number under OTHER SRC NO. If there is no other reference number, leave blank or you can replace with some other notes.