Tuesday, June 28, 2011

15. More explanation for structure in sheet Data column by column 2

Column L

In cell L2, write this formula:

=IF(ISERROR(VLOOKUP(I2, INDIRECT("TB!A2: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 2 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.

Column R br /> br /> In cell R2, write this formula:

=IF(Q2="open", CutOffDate-B2, "NR")

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

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

Column S

In cell S2, write this formula:

=IF(Q2="open", IF(R2<30, 0, 60="" 90="" if="">120")))), "NR").

This formula computes the group for the credit age (in days) of unpaid suppliers invoice. In cell Q2, we will state open if the invoice is still unpaid. The credit age is supposed to be available in cell R2. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell Q2 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.

Column V

In cell V2, write this formula:

=IF(ISNUMBER(U2), B2+U2, "NR").

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

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

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').

Column AA serves as the right-side border. Colour its cells as visual indicator.






No comments:

Post a Comment