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.