Trial Balance and Chart of Accounts in Sheet TB

1. Use a dedicated sheet for the Trial Balance (TB) 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.