Row 1
Cells in row 1 are to contain the data titles (or field titles or column titles) for the columns A to AA. The data titles are used to indicate what type of data should be put in cells under each title.
In cell A1: Write SRC (for SOURCE)
In cell B1: Write DATE
In cell C1: Write MTH (for MONTH)
In cell D1: Write SRC NO (for SOURCE NUMBER)
In cell E1: Write SRC NO 2 (for OTHER REFERENCE or Additional REFERENCE NUMBER)
In cell F1: Write NOTE
In cell G1: Write FC (for Foreign Currency)
In cell H1: Write RATE (for Rate of Currency Exchange)
In cell I1: Write CONVERTED (to RM)
In cell J1: Write ACCOUNTS
In cell K1: Write CHECK ACCOUNTS
In cell L1: Write RM-DEBIT/(CREDIT)
In cell M1: Write CUM. AMOUNTS (for Cumulative Amounts)
In cell N1: Write BANK RECON (for BANK RECONCILIATION).
In cell 01: Write DEBTOR INV. STATUS (for DEBTOR Invoice Status).
In cell P1: Write DEBT AGE (days)
In cell Q1: Write DEBT AGE GROUP
In cell R1: Write DEBT TERM
In cell S1: Write DEBT DUE DATE
In cell T1: Write CREDITOR INV. STATUS (for CREDITOR Invoice Status).
In cell U1: Write CREDIT AGE (days)
In cell V1: Write CREDIT AGE GROUP
In cell W1: Write CREDIT TERM
In cell X1: Write CREDIT DUE DATE
In cell Y1: Write BS or PL
In cell Z1: Write ORIGINAL ORDER.
In cell AA1: Leave blank (It serves as right-side border)
Row 2
This is where you can start to input raw data (the first row for data). Data must start at this row.
Row 9
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 14 downwards 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 10
In cell A10, 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 J10 contains a formula which checks the result of the formulas in cell H10 and J10. The sum of cell H10 and cell J10 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 0 (zero). Otherwise, it will show "Please Check" to indicate to us to check something. You can read about the IF function in Topic 29.
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').
Column AA
All cells in column AA should be left blank. This column serves as a right-side border for the data area (rows of data which encompasses column A to Z). 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 into the cells. (Refer to Topic 28 on 'Tips And Shortcuts').
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 in this book, unless you know the effect.
Some cells in row 2 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
No comments:
Post a Comment