Temporary Notes

Sample structure

	A	B	C	D	E	F	G	H	I	J
1	SRC	DATE	MTH	SRC. NO.	2ND SRC. NO.	Note 1	Note 2	Note 3	Note 4	Note 5
2
3
4
5
6
7
8
9	Subtotal
10	Checking
11	Cut-off Date		(date)
12	LastRowDATA		(formula)

(formula)

	K	L	M	N	O	P
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts	Bank Recon.	Sales Inv. Status
2				(formula)
3
4
5
6
7
8
9				(formula)
10
11
12

	Q	R	S	T	U	V	W
1	Debt Age	Debt Age Group	Supplier Inv. Status	Credit Age	Credit Age Group	Due Date	Items
2	(formula)	(formula)		(formula)	(formula)
3
4
5
6
7
8
9
10
11
12

	X	Y	Z	AA	AB	AC	AD
1	Qty	Unit of Measure	Price	BS or PL	Original Order
2
3
4
5
6
7
8
9
10
11
12

Explaination on the sample structure

1. Cells in row 1 from A1 to N1 are to contain headings.
2. Column O serves as the right-side border. Colour its cells as visual indicator.
3. Rows for transaction data starts at row 2.
4. Let the range A2:N7 as the initial Data Area. The Data Area is dynamic as it will expand or contract as we add more row/s or delete row/si.
5. Make the row immediately below the Data Area as the bottom-side border. Colour its cells as visual indicator.
7. The bottom-side border is also dynamic as it will move downward or upward as we add more rows of data or delete a row.
8. Designate a row immediately below the borrom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
9. Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
10. Designate a row immediately below the Checking Row as Cut-Off row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
11. In the sample, we put a label Last Row Data in cell A13. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C13 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.

Dynamic rows

To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row) :

BotomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA

Sample formulas in use

Cell	Formula
J2	=ROUND(G2*H2,2)
K2	=IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK")
M2	=IF(J2=J1,M1+L2,L2)
P2	=IF(O2="open",CutOffDate-B2,"NR")
Q2	=IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR")
Z1	=ROW(LastRowTB)
G9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
J9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
M9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
V9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
C12	=ROW(LastRowDATA)

Certain columns need to be formatted with suitable formatting

Column	Heading	Format
A	Source	Text
B	Date	Date (eg dd-mmm-yyyy)
C	Month	Text
D	Source No.	Text
E	Other source and no.	Text
F	Note 1	Text
G	Note 2	Text
H	Note 3	Text
I	Note 4	Text
J	Accounts	Text
K	Checking Accounts	Text
L	Amounts DR/CR	Number
M	Cumulative Amounts	Number
N	Bank Reconciliation marking	Text
O	Sales Invoice Status	Text
P	Debt Age	Number
Q	Debt Age Group	Number
R	Debt/Credit Term	Text
S	Due date	Date
T	Item purchased/Sold	Text
U	Quantity Purchased/Sold	Number
V	Unit of measurement	Text
W	Price	Number
X	BS or PL marking	Text
Y	Original order	Number

Changing the structure

You can design the structure differently as you wish.

Creditors reconciliation

Usually, every month our suppliers (creditors) send us a statement of accounts. The statement of accounts usually show amount we owe them and its details such as invoice number, invoice date, delivery order number and amount.

We want to compare our suppliers (creditors) items in our accounting ledger with the statements received from suppliers/creditors.

Remember, when we receive an invoice from supplier/creditor, we record the particulars in columns in 2 rows (row for debit and credit particulars). The columns are ....

When we receive statement of accounts from supplier, we want to check whether their record is same with ours.

We need a specific column to be used to compare the items in the statement with our record. Give a title to the column, may be CREDIT RECON.

Cells in the column are used to mark whether the item in the statement is also appear in our record. Use same marking throughout. For example, we can use word OK to indicate that our item also appear in the supplier statement.

If an item exist in the statement but not appear in our record, then do not mark anything, leave the cell blank.

Introduction

Here I show how to use Microsoft Excel as a complete accounting tool.

Useful for freelancers, book-keeping service provider, small businesses, students and for those who want to work from home.

VBA is NOT used at all.

1. Initial Steps

- Open a new workbook (Create a blank workbook).

- Save the new workbook. Give the new workbook a suitable name that reflect what data it will contain. For example, if the name is "AccJan2012.xls" it will contain accounting data for January 2012.

2. Data entry structure

Where to enter data

1. Use a dedicated sheet.
2. Name the sheet appropriately. In this book, its name is "Data".

Sample structure

	A	B	C	D	E	F	G	H	I	J
1	SRC	DATE	MTH	SRC. NO.	2ND SRC. NO.	Note 1	Note 2	Note 3	Note 4	Note 5
2
3
4
5
6
7
8
9	Subtotal
10	Checking
11	Cut-off Date		(date)
12	LastRowDATA		(formula)

	K	L	M	N	O
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts	Bank Recon.
2				(formula)
3
4
5
6
7
8
9				(formula)
10
11
12

Explaination on the sample structure

1. Cells in row 1 from A1 to N1 are to contain headings.
2. Column O serves as the right-side border. Colour its cells as visual indicator.
3. Rows for transaction data starts at row 2.
4. Let the range A2:N7 as the initial Data Area. The Data Area is dynamic as it will expand or contract as we add more row/s or delete row/si.
5. Make the row immediately below the Data Area as the bottom-side border. Colour its cells as visual indicator.
7. The bottom-side border is also dynamic as it will move downward or upward as we add more rows of data or delete a row.
8. Designate a row immediately below the borrom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
9. Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
10. Designate a row immediately below the Checking Row as Cut-Off row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.
11. In the sample, we put a label Last Row Data in cell A13. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C13 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.

Dynamic rows

To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row) :

BotomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA

Sample formulas in use

Cell	Formula
J2	=ROUND(G2*H2,2)
K2	=IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK")
M2	=IF(J2=J1,M1+L2,L2)
P2	=IF(O2="open",CutOffDate-B2,"NR")
Q2	=IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR")
Z1	=ROW(LastRowTB)
G9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
J9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
M9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
V9	=SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
C12	=ROW(LastRowDATA)

Certain columns need to be formatted with suitable formatting

Column	Heading	Format
A	Source	Text
B	Date	Date (eg dd-mmm-yyyy)
C	Month	Text
D	Source No.	Text
E	Other source and no.	Text
F	Note 1	Text
G	Note 2	Text
H	Note 3	Text
I	Note 4	Text
J	Accounts	Text
K	Checking Accounts	Text
L	Amounts DR/CR	Number
M	Cumulative Amounts	Number
N	Bank Reconciliation marking	Text
O	Sales Invoice Status	Text
P	Debt Age	Number
Q	Debt Age Group	Number
R	Debt/Credit Term	Text
S	Due date	Date
T	Item purchased/Sold	Text
U	Quantity Purchased/Sold	Number
V	Unit of measurement	Text
W	Price	Number
X	BS or PL marking	Text
Y	Original order	Number

Changing the structure

You can design the structure differently as you wish.

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

	A	B	C	D	E	F	G	H	I	J
1	SRC	DATE	MTH	SRC. NO.	2ND SRC. NO.	Note 1	Note 2	Note 3	Note 4	Note 5
2	OB	1.1.12	0
3	OB	1.1.12	0
4	OB	1.1.12	0
5	OB	1.1.12	0
6	OB	1.1.12	0
7	OB	1.1.12	0
8
9	Subtotal
10	Checking
11	Cut-off Date		(date)
12	LastRowDATA		(formula)

	K	L	M	N	O
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts
2	CAPITAL	-5000	(formula)	(formula)
3	RETAINED PROFITS	-1000
4	SUPPLIER ABC	-2000
5	CUSTOMER EFG	3000
6	FIXED ASSETS	2000
7	CASH IN BANK	3000
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

Handling Foreign Currency

1. Sometimes transactions occur in foreign currency. We record the double entry amounts in local currency (after conversion) but we still interested to know the transaction history of amounts in the foreign currency.

2. Use a dedicated column in data entry sheet to record the amounts in the foreign currency.

3. To get the sum of all amounts of foreign currency for a particular Accounts, use SUMIF function.

4. To view rows of transaction for a particular Accounts having foreign currency, follow the following steps. 

4.1 Make a copy of the data entry sheet.
4.2 Sort the data according to Accounts, then by Date.
4.3 Put the data in Filtering mode.
4.4 Select the Accouns that we interested.


5. We can delete the copy sheet once done because we can easily rebuild.

Trial Balance and Chart of Accounts

1. Use a dedicated sheet for the Trial Balance and Chart of Accounts.
2. The Trial Balance and Chart of Accounts are combined.
3. Design the structure for the Trial Balance and Chart of Accounts as you like.
4. Use SUMIF function to sum total figures from data entry sheet.
5. Use COUNTIF function to detect duplicate of Accounts name.

Sample structure of TB/Chart

	A	B	C	D	E	F	G	H	I	J	K
1	Acc.	BS or PL	BS-Main Cat.	BS-Sub Cat.	PL-Main	PL-Label	RM	Check Dup.	FC	Has Sttmnt?
2
3
4
5
6
7
8
9
10	Subtotal						Subtotal

Short explaination of the sample structure

1. Cells in Row 1 (column A to J) contain headings.
2. Range A2:J8 is the initial data area.
3. Row 9 is the initial bottom border.
4. Row 10 is the initial Total/Subtotal row.
5. Column K is the right-side border of the data area.
6. Give a name to cell A9 to indicate it represent te bottom-border row.
7. Cell G10 is the initial cell that contain Subtotal formula to total the amounts in column G.
8. There is a formula in cell I10 to total the amounts in column I.

What to fill under the the column headings

Column A - Put all available Accounts names in this column, row by row.

Column B - For each Accounts in column A, identify whether it is a Balance Sheet (BS) item or a Profit and Loss (PL) item.

Column C - For each Accounts in column A, state the Main Category into which the Accounts will come under in the BS (Eg Capital, Fixed Assets, Retained Earnings, Current Liabilities, Current Assets, etc).

Column D - For each Accounts in column A, state the Sub-Category into which the Accounts will come under in the BS (Eg Cash in Bank, Cash in Hand, Trade Creditors, Trade Debtors, etc. If there is no sub-category, repeat the Main category).

Column E - If the Accounts in column A is a PL item, state the Category into which this Accounts will go into the PL (eg Revenue, Cost of Sales, Admin Expenses, Other Incomes etc). If the Accounts is not a PL item, leave the corresponding cell blank.

Column F - If the Accounts in column A is a PL item, state the again the Accounts name in this column. Later, in the PL Statement we will use formula to extract Accounts name here into the PL Statement. If the Accounts is not a PL item, leave the corresponding cell blank.

Column G - For each Accounts in column A, we will put formula in column G to get the sum of amounts from column L in sheet Data.

Column H - For each Accounts in column A, we will put formula in column H to check that we do not enter Accounts name more than once.

Column I - If the Accounts in column A involve Foreign Currency, we will put formula in column I to get the sum of amounts from column G (Foreign Currency) in sheet Data.

Column J - For each supplier in column A, we will put marking in column J to indicate whether the supplier usually send Statements of Acounts to us.

How to fill the structure

1. Enter data row by row.
2. Leave every cell in the bottom-border row blank.
3. Leave every cell in the right-side-border column blank.

Sample formula in cell G10

=SUBTOTAL(9,INDIRECT("F2:F"&(ROW()-1)))

Sample formula in cell F2

=SUMIF(INDIRECT("DATA!J2:J"&ROW(LastRowDATA)-1),A2,INDIRECT("DATA!L2:L"&ROW(LastRowDATA)-1))

Sample formula in cell G2

=COUNTIF(INDIRECT("A2:A"&(ROW(LastRowTB)-1)),A2)

Is the Trial Balance balance ?

If the total in cell G10 is 0 (zero), it means the Trial balance is balance.
If the total in cell G10 is not 0 (not zero), it means the Trial balance is not balance.

What to do if the Trial Balance is not balance ?

1. Check that the raw data is balance.
2. Check for duplicate Accounts in the Trial Balance.
3. Check for Accounts not listed in the Trial Balance.
4. Check for formula in column G in the Trial Balance.

Balance Sheet

Where to put the Balance Sheet

Use a dedicated sheet for the Balance Sheet.

A sample structure of Balance Sheet

Design the structure for the Balance Sheet as you like.

Sample structure :

	A	B	C	D	E
1	ABC SDN BHD
2	BALANCE SHEET FOR PERIOD. ..
3	PARTICULARS	NOTE	YEAR TO DATE	LAST YEAR
4
5	CAPITAL
6	RETAINED EARNINGS
7	TOTAL
8
9	Represented by :
10
11	FIXED ASSETS
12	INVESTMENTS
13
14	CURRENT ASSETS
15	STOCK
16	DEBTORS
17	CASH IN BANK
18	Total
19
20	CURRENT LIABILITIES
21	TRADE CREDITORS
22	OTHER CREDITORS
23	Total
24
25	NET CURRENT ASSETS
26
27	Total
28

How to fill the data

The figures in column C is derived from sheet Trial Balance. Use the SUMIF function to extract amounts from sheet Trial Balance into the relevant cells in the column C of the Balance Sheet.

Sample formula

Sample formula at cell C5 :

=-(SUMIF(INDIRECT("TB!C2:C"&ROW(LastRowTB)-1),A5,INDIRECT("TB!F2:F"&ROW(LastRowTB)-1)))

Note :

1. TB is the name of sheet that hold the Trial Balance.
2. LastRowTB is the name given to a cell that mark the last row of data area in Trial balance sheet.
3. A5 is the cell in sheet Trial Balance that contain the item same as cell C5 in sheet Balance Sheet.

Automatic update

With the use of the formulas, any change in the amounts in the Trial Balance, the figures in the Balance Sheet is automatically updated.

Does the Balance Sheet balance ?

Based on the sample structure, if amount in cell C7 is the same with the amount in cell C27 then it means the Balance Sheet is balance.

Why the Balance Sheet does not balance

The Balance Sheet may not balance if :

1. The total amounts in column RM in sheet DATA does not balance.

2. The Trial Balance does not balance.

3. The items in the Balance Sheet may be less than or more than what it should be.

4. There is error or are errors in some of the formulas that extract the amounts from the Trial Balance.

5. There is error or are errors in some of the formulas that do the totalling of the amounts.

Comparative figure

How to get the comparative figures ?

Write it manually. They are not many and once there they can be used for many years.

Checking for error in the Balance Sheet

Profit and Loss - cumulative

Where to put the Profit & Loss Statement

Use a dedicated sheet for the Profit & Loss Statement.

A sample structure of a Profit & Loss Statement

Design the structure for the Profit and Loss Statement as you like.

Explaination on the sample structure of the Profit & Loss Statement

How to fill the structure

Formulas in use

Comparative figures

Checking for error in the Profit & Loss Statement

Checking the final amount of Profit or Loss

3. Use SUMIF function to sum total figures from Trial Balance sheet.

Profit and Loss - monthly

Where to put the monthly accounts

Location 1 - In a separate sheet, all by itself.
Location 2 - In a separate sheet, together with other months analysia.
Location 3 - In the same sheet with the cumulative Profit & Loss Statement.

sample structure

Special attention to items

Opening Stock
Closing Stock
Cumulative Profit and Loss Brought Forward

Formulas in use

Use SUMPRODUCT function

Viewing a single ledger

Method 1 :

Use PivotTable operation.

Method 2 :

1. Make a copy of the data entry sheet.
2. Perform the following actions on the copy sheet.
2.1 Sort the data based on column Accounts, then by column Date.
2.2 Make the data in filtering mode.
2.3 Select a single Accounts.
3. You can delete the sheet because you can always rebuild easily.

Method 3 :

1. Do the following operation on the data entry sheet.
1.1 Make the data in filtering mode.
1.2 Select a single Accounts.
2. With this method, the data is not sorted, so rows will appear in its original order.
3. Don't forget to remove the Filtering mode.

Selecting the ledger for copying

If error is found in the ledger

Potential error in the ledger

Generating all ledger Accounts

Method 1 :

Use PivotTable operation.

Method 2 :

1. Make a copy of the data entry sheet.
2. Sort the data based on column Accounts, then by column Date.
3. Do Subtotalling operation.
4. You can collapse/ expand the subtotal tree.
5. You can delete the sheet because you can rebuild easily.

Advantage or disadvantage of each methods

View transaction listing

Use Filtering operation or PivotTable operation.

View all ledgers

Use PivotTable operation.

Other analysis

Once you familiar with using formula, PivotTable operation and Filtering operation, you will be able to generate many analysis with ease.

Cash Flow report

Design the structure of the report as you like.

Fixed Assets schedule

Design the structure of the schedule as you like.

Bank reconciliation

1. Design the reconciliation statement as you like.
2. Use a column in the data entry sheet to mark bank entry that has it corresponding entry in the statement provided by the bank.
3. Ummarked cell means unreconciled items.
4. Bring the unmarked item details into the reconciliation statement. (formula)

	K	L	M	N	O	P
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts	Bank Recon.	Sales Inv. Status
2				(formula)
3
4
5
6
7
8
9				(formula)
10
11
12

Debtors Listing

Use PivotTable operation.

Debtors Ageing analysis

Use PivotTable operation.

Creditors listing

Use PivotTable operation.

Creditors Ageing analysis

Use PivotTable operation.

List of Payments due date

Use PivotTable operation or Filtering operation.

Opening balances for new accounting year

Use the balances in the closing Trial Balance.

Comparative figure

Two (2) ways :
1. Manually enter the amounts, or,
2. Copy the relevant sheet into the file and use formula.

Deferred Tax computation

Design the structure of the report as you like.

Tips and tricks

1. Use Crtl + D.
2. Use Home key.

How many rows ?

Excel XP has 65,535 rows.
Excel later than XP has 1,036,000 rows.

Estimated, a one year transactions require how many rows ?

Sales invoices :  5 invoices per day X 300 working days X 2 rows = 3,000 rows.
Debit/Credit Notes : 2 per month X 12 months X 2 rows = 48 rows.
Supplier invoices : 10 invoices per day X 300 working days X 2 rows = 6,000 rows.
Journals : 100 per year X 2 rows = 200 rows
Cash/bank transactions : 200 per month X 12 months X 2 rows = 4,800 rows.

Total = 14,048 rows.

So, one Excel XP sheet can easily accomodate a one whole year transactions, and occupies only about 20 % of the space.

Statement of Accounts to Customers

Method 1 : Using copy and paste

1. Design a Statement of Accounts format in a separate sheet.
2. Make a copy of the data entry sheet.
3. Highlight the relevant data of the customer we interested.
4. Copy the relevant data and paste into the Statement of Accounts format.

Method 2 : Using formula

1. Design a Statement of Accounts format in a separate sheet.
2. Make a copy of the data entry sheet.
3. Sort the data according to Accounts and followed by Date.
3. Use formula to extract relevant data into the Statement of Accounts format.

Note :

- One customer by one customer.

Closing Cumulative Monthly Accounts

1. Initially, when we started using Excel as our system, we use one file (workbook). In this first file, we record data of day to day transactions from the beginning of the period until todate. This file is the up-to-date file, or, the most current file.

2. If we want to close the file (or the accounts) to up to a certain date (closing date) (usually up to end of a particular month), which is a date usually earlier than the current date, we must make a copy of the current file, then in the copy file we remove rows of transaction data that having date later than the closing date, so that the copy file contains only the transaction data from the beginning of the period until the closing date.

3. To remove the rows of transaction data that having date later than the closing date in the copy file, we have 2 methods :

3.1 Method 1 : Identify the relevant row one by one and delete the row one row by one row.

3.2 Method 2 : Filter the data so that we see only rows of transaction data that having date later than the closing date. Select the visible rows in one go (use menu Edit --> Go To --> Specials --> Visible Cells Only --> OK. Delete the visible rows.

4. Once the above is done, automatically the Trial Balance, Balance Sheet, Profit Loss and other analysis are updated.

5. Name or Rename the copy file appropriately.

6. Continue updating the current data in the current file, not in the copy file.

Closing Yearly Accounts

Closing the year accounts means : Making sure the transaction data consist only the transactions for the year - remove data irrelevant to the accounting period and make sure all data related to accounting period is taken in.

Once the data in sheet DATA conform to the above, automatically the Trial Balance, Balance Sheet, Profit Loss and other sheets are updated.

How to remove the data with date outside the accounting period?

Identify rows in column DATE which contain the irrelevant date.
You can identify one by one.
You can use filtering method.

Using on other spreadsheet

This method has not been tested on other spreadsheet. However, most probably, you can use this method on other spreadsheet, as long as the other spreadsheet has the formulas, PivotTable operation and Filtering operation capabilities similar to Microsoft Excel.

One Workbook for one accounting year data

1. Use one workbook(file) for only one accounting year. For example, file name AccJanDec2011.xls contains data for acounting period 1 January to 31 December 2011.

2. Why ? (1) Too many data and formulas will slow down calculations. (2) Better files organization.

To Do list

Use a separate sheet for list of things to do, outsanding items, etc.