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". This name is used in formulas in this book.

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 row is also dynamic as it will move downward or upward as we add more rows of data or delete a row above it.
8. Designate a row immediately below the bottom-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 above bottom-side border 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 above bottom-side-border 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 above bottom-side-border 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 above bottom-side-border 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 above bottom-side-border row) :

BottomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA

Sample formulas in use in sheet Data'

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

Where to enter transactions data

Transactions data must be entered in the Data Area. The Data area begins from row 2 until the row above bottom-side-border row (dynamic).

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

Sample structure

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.

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 (sheet Data) to record the amounts in the foreign currency. In this book, column used to record amounts in foreign currency is column E in sheet Data.

3. Once we record the amounts in the foreign currency, we need to format the amounts to a format which show the foreign currency sign such as USD, JPY, etc.

4. If we have more than one foreign currency, we may want to see the rows containing a particular foreign currency only. This can be done via filtering operation. The total amounts of a particular foreign currency will be shown in row Total.

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

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

6.1 Make a copy of the data entry sheet.
6.2 On the copy sheet, sort the data according to Accounts, then by Date.
6.3 Still on the copy sheet, put the data in Filtering mode.
6.4 Select the Accounts that we interested.


7. 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. In this book, the sheet is named TB (for Trial Balance). This name is used in formulas in this book. So, if you change the name of this sheet, make sure you also change the name of the sheet in the relevant formula that is affected by the change.
3. The Trial Balance and Chart of Accounts are combined into one sheet, ie sheet TB serves both as Trial Balamce and Chart of Accounts.
4. Design the structure for the Trial Balance and Chart of Accounts as you like. However, a sample structure is provided in this book. The formulas in this book are based on the structure of this sample.


Sample structure of TB/Chart

	A	B	C	D	E	F	G	H	I	J	K
1	Accounts	BS or PL	BS-Main Category	BS-Sub Category	PL-Main	PL-Label	RM	Check Duplicate	FC	Has Sttmnt?
2
3
4
5
6
7
8
9
10	Subtotal						(Formula)

Short explanation for the sample structure

1. Cells in Row 1 (column A to J) contain headings.
2. Range A2:J8 is the initial data area. This area is dynamic as we insert or delete rows in this area.
3. Row 9 is the initial bottom border. This row is dynamic as we insert or delete rows in data area.
4. Row 10 is the initial Total/Subtotal row.This row is dynamic as we insert or delete rows in data area.
5. Column K is the right-side border of the data area.
6. Give a name to cell A9 to indicate it represent the 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 here 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 Balance Sheet (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 Balance Sheet (Eg Cash in Bank, Cash in Hand, Trade Creditors, Trade Debtors, etc. If there is no sub-category, repeat the Main category item).

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

Column F - If the Accounts in column A is a Profit and Loss item, state again the Accounts name in this column. Later, in the Profit and Loss Statement we will use formula to extract Accounts name here into the Profit and Loss Statement. If the Accounts is not a Profit and Loss 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)

Does 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. In this book, the sheet is named BS. This name is used in many formulas. So, if you change the name of this sheet, you must also change the names of this sheet in formulas.

A sample structure of Balance Sheet

Design the structure for the Balance Sheet as you like. However a sample structure is provided in this book.

Sample structure :

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

How to fill the data in column A

How to fill data in column B

The figure in cell B7 is obtained by this formula : =B5+B6.

How to fill data in column C

The figures in column C is derived from sheet TB (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 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.

How to fill data in column D (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

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.

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

There are two (2) ways to get comparative figures (usually comparative figures are previous year figures) into the current period report or statements :

1. Manually enter the relevant amounts, or,
2. Copy the relevant sheet into the file and use formula to extract the figures.

Usually comparative figures are included in the following main reports or statements of current accounting period :

1. Balance Sheet.
2. Profit & Loss statement.

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 are needed ?

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

Estimated, a one year (12 months) 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 The Current Accounting Period Accounts

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

Please note that the term Accounting period is used instead of Accounting Year because an accounting period may span over two years eg from 1 July 2020 to 30 June 2021.

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

Make a copy of the current period file. So, after the copying, there are 2 files containing the same transaction data. First, the current period file, and secondly the file for the next accounting period.

Example name of current accounting period file is accjandec2020.xls, and example name of next accounting period is accjandec2021.xls. It is easier to find the files if they are saved into the same folder.

It is a good practice if you make a copy of the current file only after you decide to close the current file. Close current accounting period file means you will not add or delete anymore row to the file.

Any changes to the current accounting period data is done only before auditor adjustment.

After the copying, the current accounting period file will still contain data for the next accounting period file. Those data meant for the next accounting period need to be removed (ie the relevant rows need to be deleted), leaving only data for the current accounting period. So, your Trial Balance, Balance Sheet, Profit & Loss statements are closed.

After the copying, the next accounting period file will still contain data for the current accounting period. Those data meant for the current accounting period need to be removed (ie the relevant rows need to be deleted), leaving only data for the next accounting period. So, your Trial Balance, Balance Sheet, Profit & Loss statements are closed.

How to remove the data in the current accounting period 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.


After the copying, if you make adjustments or addition or deletion to the current accounting period, it will affect the figures brought forward to the next accounting period, so make sure that the affected accounts in the next accounting period file is adjusted accordingly.

After the copying, if the auditor make adjustments or addition or deletion to the current accounting period, it will affect the figures brought forward to the next accounting period, so make sure that the affected accounts in the next accounting period file is adjusted accordingly. So auditor adjustments should be written to both files ie current accounting period file and next accounting period file.

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 period data

1. Use one workbook (file) for only one accounting period. 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.

3. Though usually one accounting period (12 months) equal to one accounting year (12 months), but in some cases, one accounting period may span over two years eg 1 July 2010 to 30 June 2011 (12 months).

To Do list

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