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.
No comments:
Post a Comment