Tuesday, June 28, 2011

3. Entering transactions data

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
ABCDEFGHIJ
1SRCDATEMTHSRC. NO.2ND SRC. NO.Note 1Note 2Note 3Note 4Note 5
2OB1.1.120
3OB 1.1.12 0
4OB 1.1.12 0
5OB 1.1.12 0
6OB 1.1.12 0
7OB 1.1.12 0
8
9Subtotal
10Checking
11Cut-off Date(date)
12LastRowDATA(formula)

KLMNO
1ACCOUNTSAmountsCum. AmountsCheck Accounts
2CAPITAL-5000(formula)(formula)
3RETAINED PROFITS-1000
4SUPPLIER ABC-2000
5CUSTOMER EFG3000
6FIXED ASSETS2000
7CASH IN BANK3000
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

No comments:

Post a Comment