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