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