Monitoring cash in bank pg 184 185 186 187

22. Monitoring Cash Balance

We can extend the use of the file to monitor cash in bank balances.

We can use a separate sheet for this purpose. So, create a sheet for this purpose. You can name it with any name you think fit.

Here is a suggested structure for the sheet:

	A		B		C		D		E		F		G
1 	SATU DUA (M) SDN BHD
2
3 	CASH BALANCE ESTIMATE
4 	As at:
5 	BANK ACCOUNT NAME..>>>				BANK A		BANK B		BANK C		TOTAL
6 	Current balance (as per ledger)		        (126,655.27)	(778.279.36)	42.117.17	(862.817.46)
7 	Payment/Receipt On Hold				0.00		0.00		0.00		0.00
8 	CASH AVAILABLE  				(126,655.27)	(778,279.36)	42.117.17	(862,817.46)
9و
10 	Expected/recurring receipt/ payment in the foreseeable future:
11
12				NOTE	DUE DATE
13 	SI Rental			Every 2nd
14 	Salary				Every 30th	50,000.00	0.00		0.00		50,000.00
15 	SI-Hire Purchase	        Every 25th	700.00		0.00		0.00		700.00
16 	Short-term loan due	        12 this month	200,000.00	0.00		0.00		200,000.00
17 	LC due				Due 6/5/02	350,000.00	0.00		0.00		350,000,00
18
19 	EXPECTED NET RECEIPT/PAYMENT		        601,700.00	0.00		0.00 		601,700.00
20
21 	EXPECTED BALANCE (EXCESS/DEFICIT)	        475,044.73	(778.279.36)	42.117.17	(261,117.46)
22

This sample shows the current balance of each bank account and its total. The balances are then adjusted for expected payment or receipts in the foreseeable future so that we can forecast our financial state.

The explanation of the structure:

Cell A1: The name of the business.
Cell A2: (Blank)
Cell A3: The title of the statement.
Cell A4: Text As at (date).
Cell A5: Text BANK ACCOUNT NAME ..>>>.
Cell A6: Text Current Balance (as per ledger).
Cell A7: Text Payments/Receipts on hold.
Cell A8: Text CASH AVAILABLE.
Cell A9: (Blank)
Cell A10: Text Expected/recurring payments/receipts in the future.
Cell A11: (Blank)
Cell A12: (Blank)
Cell A13: From this cell downwards, list down the expected/recurring payments/receipts in the future.
Cell A14: (Blank)
Cell A15: (Blank)
Cell A16: (Blank)0
Cell A17: This sample assumes that this is the last cell for the list of the expected/recurring payments/receipts in the future.
Cell A18: (Blank)
Cell A19: Text EXPECTED NET RECEIPTS/PAYMENTS.

Cell A20: (Blank)
Cell A21: Text EXPECTED BALANCE (EXCESS/DEFICIT).

Cell B22: Text NOTE. For each cell below it, write any note for an item appearing on the same row on the cell on the left.

Cell C22: Text DUE DATE. For each cell below it, write the due date for each item appearing on the same row on the cell on the left.

Cell D5: In this cell and cells in subsequent columns, write the account names for each bank. Make sure the spelling is exactly the same with what is available in sheet TB. Cell D6: Contains this formula (in one line):
=SUMIF(INDIRECT("DATA!G5:G30000"), INDIRECT("D4"), INDIRECT("DATA! H5: H30000"))+
SUMIF (INDIRECT("DATA!I5:I30000"), INDIRECT("D4"), INDIRECT("DATA!J5:J30000"))

This formula will compute the current balance of the bank name in cell D6 from data on sheet DATA. That's why it is important to ensure the exact spelling.

185

Copy this formula to the next column until the last column containing the bank name (in this sample, you do so in cell E6 and F6).

Cell D7: Contains this formula (in one line):
=SUMIF(INDIRECT("DATA!G5: G30000"), INDIRECT("D4"), INDIRECT("DATA!05:0 30000"))+
SUMIF(INDIRECT("DATA!15:130000"), INDIRECT("D4"), INDIRECT("D ATA!05:030000"))

This formula will compute the receipt/payments on hold as indicated in the column "HOLD" on sheet DATA.
Copy this formula to the next column until the last column containing the bank name.

Cell D8: Contains this formula:
=D6+D7

This formula will compute the current balance plus payments/receipts on hold. Copy this formula to the next column until the last column containing the bank name.

Cell D13: From this cell downwards, state the amount of the expected/recurring payments/receipts in the future. Repeat for each of the next columns until the last bank name.

Cell D17: In this book, it is assumed that this is the last cell for the amount of the expected/recurring payments/receipts in the future.

Cell D19: Contains formula which computes the total amount of expected payment/receipts. Copy this formula to the next column until the last column containing the bank name.

186

Cell D21: Contains formula which computes the expected balance:
=D8+D19

Copy this formula to the next column until the last column containing the bank name. Assume column G is the last column for a bank name.

Cell G5: Write TOTAL. Cell G6: Write this formula:
=D6+E6+F6.
Copy this formula down until row 21.

Once you understand the formulas, you can change the structure to suit your own needs. The most important thing is how the current balance of each bank is captured using the formulas. You then adjust the balance with items you think is logical.

Segregation of duties and file sharing pg 182 183

21. Segregation Of Duties & File Sharing

Segregation Of Duties

Sometimes, sources of transactions are handled by different personnel. For example, a bank book, petty cash book, purchases, and sales are separately handled by different personnel. Then, there is one person who maintains the ledger and he/she has to get data from the other staff. Each staff is provided with a computer and works with Microsoft Excel in each computer. The person who maintains the ledger will need to import the data from the other sources. He can work faster if each computer is connected via a computer network and each source uses Microsoft Excel and has the same structure for sheet DATA.

Rather than each staff having a separate file with similar structure, you can just keep one file and everybody works on that file, i.e. that file will be shared. Everybody will update the same file.

To enable sharing of one file:

1. Put the file in a folder in a computer in a network, and set the folder as shared and set the computer as accessible by everyone involved.

2. Then, open the file to enable us to set its sharing attributes.

3. Click menu Tools/Share Workbook... The Share Workbook dialog will appear:

Share Workbook

Editing | Advanced

☑ Allow changes by more than one user at the same time. This also allows workbook merging.

Who has this workbook open now:
	Authorised Desktop PC User (Exclusive)-6/11/2002 1:30 PM

	OK		Cancel

182

4. Click the tab Editing.

5. Put a mark in the box on the left of the option Allow changes by more than one user at the same time...

6. Click the tab Advanced. You will see more options.

7. Under Conflicting changes between users, select the option Ask me which changes win.

8. Under Update changes, select the option When file is saved.

9. Click OK.

Every user will open the same file. So, there must be someone who oversees the file to make sure the structure of the file is intact and data is filled properly and there is no undesirable overwriting of data.

File Sharing Through Networking And The Internet

Through networking, this file can be made available to other interested persons e.g. the Accountant and the General Manager. The file can be set for viewing only, i.e. no changes are permitted. However, they can be allowed to make a copy of the data or sheet or file so that they can make analysis of the data on their own rather than wait for their subordinates to provide them.

The current technology also allows an Excel file to be shared via the Internet, either by opening the file directly from Internet Explorer (or some other web browser) or by converting the file into an HTML format (use menu File/Save As Web Page) or sending the file through e-mail (after compression).

183

Not completed yet

This blog is not completed yet

Front cover

BOOK-KEEPING WITH MICROSOFT EXCEL

You can use Microsoft Excel for all your book-keeping tasks, from recording raw data to preparation of Trial Balance, Balance Sheet, Profit & Loss statement, and other reports. This book shows you how.

ISMAIL HASHIM

Cover page

Excel 95, 97, 2000, XP

Microsoft Excel has long been used to help in book-keeping tasks. Many businesses rely on Excel to manage their book-keeping chores.

There are many different ways to implement it. This book shows one way, may be a better way.

In this book, formulas are used to automate updating of Trial Balance, Balance Sheet, Profit & Loss and other reports. Only a few formulas are used. This books shows what are the formulas. No programming at all. With AutoFilter, CustomFilter, AdvanceFilter and PivotTable operations, we can get the desired listings, summaries, sub-totals and totals immediately.

Just follow the guide in the book to build the data structure in an Excel file, set formulas and functions and you are ready to enter data. If you don't want to build from scratch, use a sample file provided with this book. This book is also serve as an extensive manual as your immediate reference while doing your book-keeping work.

Book-keepers, Accounts Assistants, Accounts Executives, Accountants, Accountig students would appreciate this book. Others with basic knowledge in the concept of debit and credit should be able to understand and use this material.

Introduction

Introduction

Here I show how to use Microsoft Excel as a complete book-keeping 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. 

Developing A Book-Keeping System With Microsoft Excel

Microsoft Excel has long been used to help in book-keeping and accounting tasks. Many businesses rely on Excel to manage their book-keeping chores.

There are many different ways to implement it. This book shows one way, perhaps better way It shows how you can use Microsoft Excel to do complete book-keeping tasks. Book-keeping tasks here means recording of data from its sources, preparation of Trial balance, Balance Sheet, Profit & Loss statement, and other reports.

You will see how formulas can be used to automate updating of your Trial Balance, Balance Sheet, Profit & Loss, and other reports. Only a few formulas are used. This book shows you all the formulas. No programming at all is needed. With Excel's AutoFilter, CustomFilter AdvanceFilter and PivotTable operations, we can get the desired listings, summaries, and totals immediately.

Just follow the steps outlined in this book to build the data structure in an Excel file, set the formulas and functions, and you will be ready to enter data. If you do not want to build the file from scratch, you can use the sample file provided with this book. This book also serves as an extensive user's manual which you can use as your immediate reference as you perform your book-keeping tasks.

Book-keepers, Accounts Assistants, Accounts Executives, Accountants, and Accounting students will find this book of interest. Other users, with a basic knowledge of the concept of Debit and Credit should be able to understand and use the material presented in this book.

Ismail bin Hashim

ibh1@live.com

Some Book-Keeping Functions That Can Be Done Using Microsoft Excel

Some Book-Keeping Functions That Can Be Done Using Microsoft Excel

Here are some of the book-keeping functions that can be performed using Microsoft Excel, and that will be shown in this book:

To record raw data.
To automate updating of the Trial Balance statement.
To automate updating of the Balance Sheet statement.
To automate updating of the Profit & Loss statement.
To generate and update Debtors Ageing report.
To generate and update Creditors Ageing report.
To generate and update monthly Sales summary report.
To generate and update monthly Purchases summary report.
To generate and update monthly Items purchased summary report.
To generate and update monthly Items sold summary report.
To generate data for preparing Statement of account.
To help in preparation of bank reconciliation.
5 To help in cash flow monitoring.
Filtering data to see only list of data that we want to see.

Are these functions sufficient for your needs?

Many other reports, detailed or summary, can be produced as long as we know how to use Excel's Filtering function, PivotTable function, and formulas.

Requirements

REQUIREMENTS

What are required to successfully implement book-keeping with Microsoft Excel ?

1. Initially, an empty Microsoft Excel file (Excel 95, 97, 2000, XP).

2. A basic book-keeping knowledge (particularly understand the concept of Debit and Credit).

3. A basic knowledge and hands-on experience in Microsoft Excel.

Why use Microsoft Excel for accounting

Reasons :

From staff view : If allowed and wish, he can bring home the work and finish at home, so the next day, there would be more time for him to do other works.

From Head of Dept view : He can ask for a copy of the file and look for it at home. At home, he can jot any query to be presented to his staff in next day.

If the file is in cloud, both persons ie staff and Head of Accounting Dept can see it live.

If the staff is knowledgable in Excel, he can use Excel for accounting work, thus he can save money for the company he works, ie by using Excel fully rather than buying accounting software which cost thousands of Ringgit, and save cost of periodic maintenance of the software.

Table of Content

TABLE OF CONTENT

CHAPTER 1 – BOOK-KEEPING FUNCTIONS THAT CAN BE DONE USING EXCEL
CHAPTER 2 – REQUIREMENTS FOR IMPLEMENTATION OF BOOK-KEEPING WITH EXCEL


BUILDING THE STRUCTURE

CHAPTER 3 – STEP 1 – CREATE A FOLDER (DIRECTORY)
CHAPTER 4 – STEP 2 – CREATE AND SET A FILE (WORKBOOK)
CHAPTER 5 – STEP 3 – SET THE WORKSHEETS IN THE FILE
CHAPTER 6 – STEP 4 – SET THE STRUCTURE IN THE SHEET ‘DATA’
CHAPTER 7 – STEP 5 – SET THE STRUCTURE IN THE SHEET ‘TB’

GUIDE TO INPUTTING DATA

CHAPTER 8 – GUIDE TO INPUTTING DATA IN SHEET ‘TB’
CHAPTER 9 – GUIDE TO INPUTTING DATA IN SHEET ‘DATA’

CREATING REPORTS

CHAPTER 10 – CREATING REPORT – TRIAL BALANCE
CHAPTER 11 – CREATING REPORT – BALANCE SHEET
CHAPTER 12 – CREATING REPORT – PROFIT & LOSS
CHAPTER 13 – CREATING REPORT – DEBT-AGEING
CHAPTER 14 – CREATING REPORT – CREDITORS AGEING
CHAPTER 15 – CREATING REPORT – PURCHASES
CHAPTER 16 – CREATING REPORT – SALES

FILTERING DATA

CHAPTER 17 – FILTERING DATA IN THE SHEET ‘DATA’
CHAPTER 18 – FILTERING DATA IN THE SHEET ‘TB’

SEARCHING, REPLACING AND SORTING DATA

CHAPTER 19 – SEARCHING FOR DATA
CHAPTER 20 – REPLACING DATA
CHAPTER 21 – SORTING DATA

REVIEW OF DATA AND STRUCTURE

CHAPTER 22 – REVIEW OF DATA AND STRUCTURE

USING THE FILE

CHAPTER 23 – HOW TO USE THE FILE
CHAPTER 24 – NEW ACCOUNTING YEAR
CHAPTER 25 – MANAGING INCREASING NUMBER OF FILES

BEYOND DATA ENTRY

CHAPTER 26 – MONITORING CASH BALANCE
CHAPTER 27 – STATEMENT OF ACCOUNT
CHAPTER 28 – GETTING LEDGER DETAILS OF AN ACCOUNT
CHAPTER 29 – BANK RECONCILIATION

OTHERS

CHAPTER 30 – POTENTIAL PROBLEMS
CHAPTER 31 – SHORTCUTS AND TECHNIQUES
CHAPTER 32 – FUNCTIONS USED IN THE FORMULAS
CHAPTER 33 – SEGREGATION OF DUTIES, NETWORKING AND INTERNET
CHAPTER 34 – PIVOTTABLE OPERATION PRIMER
CHAPTER 35 – ADVANCE FILTER PRIMER
CHAPTER 36 – BOOK-KEEPING PRIMER
CHAPTER 37 – THIS SYSTEM AND ITS SUITABILITY
CHAPTER 38 – SAMPLE DISK
CHAPTER 39 – CONTACTING THE AUTHOR

Create a folder (directory)

Here are the steps to develop a book-keeping system with Microsoft Excel.

Step 1- Create A Folder (Directory)

You should be familiar with the Windows computer operating system and know how to create a folder in a disk. Now, create a new folder in an easy-to-access path, such as in the root directory of disk C, not in a sub-folder. Give it an easy-to-remember name, say: AccountsXL. So, the path would look something like this: C:/AccountsXL.

All your accounts files will be saved into this folder.

You can choose another folder or some other name for the folder if you feel that it is more convenient.

Create and Set a new blank file (workbook)

Step 2 - Create And Set A File (Workbook)

After you create a folder, open Microsoft Excel (95, 97, 2000 or XP). Then, create a new blank Microsoft Excel file (menu File/New). In Excel, a file is called a workbook. Save the file into the AccountsXL folder (or some other folder of your choice). Give the file this (suggested) name: 0000 Book-Keeping Sample.xls. You can use some other name if you wish.

This file will become the SAMPLE or MASTER copy file for our book-keeping functions.

NOTE By putting zeros (0000) in the front part of the file name, you ensure that the file will always appear first (topmost) in the folder that it is in. Although one zero will suffice, I use 4 zeros to make it more obvious.

(The disk accompanying this book contains a copy of this sample file).

Initial Sheet in the file (workbook)

- Open a new workbook (This will automatically create a blank workbook).

- Save the new workbook. In the process of saving a new workbook, you will be asked to give a name for the 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.

Other than name, you also be asked to state or select which folder you want to put the workbook.

A workbook is same as a file.

A workbook (or a file) consist sheet. Initially you are provided with one sheet. You can add more sheet or delete sheets.

A sheet consist of rectangular boxes. You write into these boxes.

When you firstly open the new file, initially, there is only one sheet in the new file that you have just created.

In Excel, sheet is also called worksheet.

Rename the sheet as : DATA. (This name is used throughout this book and in formulas in the file. Do not use other name unless you are aware of the effect).

Later, when you have more sheets, you can arrange the sheets in any order.

Sheet DATA will contain all your raw transaction data (such as opening balances, sales, purchases, receipts, payments, journals, etc).

TIP Usually we use the mouse to move from sheet to sheet by clicking the intended sheet. You can use a combination of keyboard keys, instead. Use Ctrl+Page Down to move to the sheet on the right (Hold down the Ctrl key, and press the Page Down key. The next sheet will become active.). Use Ctrl+Page Up to move to the sheet on the left. By depending less on the mouse, you can speed up your work, but this requires a good memory.

Structure in sheet Data

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

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

Explanation for the structure in sheet Data

Explanation for 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.

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.

Explaination on the sample~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

More explanation for structure in sheet Data Page 6 7 8 9 10

Row 1

In cell Al, type:

TOTAL/SUBTOTAL

This is to indicate that some of the other cells in row 1 are used to accommodate formulas which calculate totals/subtotals.

In cell H1, write this formula:
SUBTOTAL (9, INDIRECT("H5: H30000"))

This formula calculates the total of amounts in cells in column H (from row 5 to row 30,000). (We will see later that cells H5 to H30000 will contain amounts for debited accounts).

In cell J1, write this formula:

=SUBTOTAL(9, INDIRECT("J5:J30000"))

This formula calculates the total of amounts in cells in column J (from row 5 to row 30000). (We will see later that cells J5 to J30000 will contain amounts for credited accounts).

In cell T1, write this formula:

=SUBTOTAL (9, INDIRECT("T5:T30000"))

This formula calculates the total of amounts in the cells in column T (from row 5 to row 30000). (We will see later that cells T5 to T30000 will contain amounts of payment which we still holding, such as cheques we still hold).

In cell Y1, write this formula:

=SUBTOTAL(9, INDIRECT("Y5:Y30000"))

This formula calculates the total of amounts in cells in column Y (from row 5 to row 30000). (We will see later that cells Y5 to Y30000 will contain quantity values).

6

Other cells:

You can use other cells in row 1 for other purpose.

Change of formula:

You can change the formulas if you know the effect.

NOTE Here, you are introduced to the SUBTOTAL function. You can refer to Topic 29 for an explanation of this function as well as other functions used in this book.

Row 2

In cell A2, type Checking. This is to indicate that some of the cells in row 2 are used to accommodate formulas that will check the logic of the results of the other formulas in our file.

For example, cell J2 contains a formula which checks the result of the formulas in cell H1 and J1. The sum of cell H1 and cell J1 should be zero (total Debit amounts minus Total credit amount). The formula is:

=IF(H1+J1=0, "Ok", "Please check")

This formula will show OK if the sum is zero. Otherwise, it will show Please Check to indicate to us to check something. You can read about the IF function in Topic 29.

You can use other cells in row 2 for other purpose.

Row 3

All cells in row 3 should be left blank. This row serves as a border for the data area (rows of data which encompasses row 4 to 30000). If any cell is not blank, some operation will not perform correctly, particularly data filtering and PivotTable functions. We can use data/validation function to help us prevent entry of into the cells. (Refer to Topic 28 on 'Tips And Shortcuts').

7

Row 4

Cells in row 4 are to contain the data titles (or field titles or column titles) for the columns A to AC. The data titles are used to indicate what data should be put in cells under each title.

In cell A4: Write SRC (for SOURCE)
In cell B4: Write DATE
In cell C4: Write NTH (for MONTH)
In cell D4: Write SRC NO (for SOURCE NUMBER)
In cell E4: Write OTHER REF/NO (for OTHER REFERENCE or REFERENCE NUMBER)
In cell F4: Write NOTE
In cell G4: Write ACCOUNT DEBIT
In cell H4: Write RN-DEBIT
In cell 14: Write ACCOUNT CREDIT
In cell 14: Write RM-CREDIT
In cell K4: Write CHECK ACCOUNT DEBIT
In cell L4: Write CHECK ACCOUNT CREDIT
In cell M4: Write BANK RECON (for BANK RECONCILIATION).
In cell N4: Write DEBTOR STTMNT (for DEBTOR STETEMENT).
In cell 04: Write DEBT AGE (days)
In cell P4: Write DEBT AGE GROUP
In cell Q4: Write CREDITOR STTMNT (for CREDITOR STATEMENT).
In cell R4: Write CREDIT AGE (days)
In cell 54: Write CREDIT AGE GROUP
In cell T4: Write HOLD (for PAYMENT/CHEQUE or RECEIPT ON HOLD).
In cell U4: Write TERM (for CREDIT TERM- Ours and Suppliers).
In cell V4: Write DUE DATE
In cell W4: Write ITEM PURCHASED
In cell X4: Write ITEM SOLD
In cell Y4: Write QTY (for QUANTITY)
In cell Z4: Write UNIT (for UNIT OF MEASUREMENT)In cell AA4: Write PRICE
In cell AB4:Write TEMP (for TEMPORARY).
In cell AC4:Write ORIGINAL ORDER.


8

Do not rearrange the order of the titles, because it will affect the formulas and explanations in this book, unless you know the effect.

Do not change the spelling of the titles, because it will also affect the formulas and explanations this book, unless you know the effect.

Row 5

This is where you can start to input data (the first row for data). Data must start at this row.

Some cells in row 5 will contain initial formulas. Later on, when you add more data into cells in rows below it, just copy the formula down.

TIP Use the keyboard key combination of Ctrl+D in a cell to copy into it the contents of the cell above it. (Hold down the Ctrl key and press the D key.)

Be careful not to accidentally delete the cells containing formulas. Keep this book handy for reference.

In cell K5, write this formula:

=IF(ISERROR(VLOOKUP(G5, INDIRECT("TB!A5: A2000"),1,FALSE))," "NOT OK", "OK")

This formula checks whether the account stated in cell G5 below the data title ACCOUNT-DEBIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, you should add the account name into the list, or replace the account name with one that already exists.

Later on, when you add more data into cells in rows below it, just copy the formula down.

You can read more about the functions ISERROR, VLOOKUP and INDIRECT in Topic 29.

9

This formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must change the parameter 2000 in the formula to the actual one.

You should review this column (column K) from time to time to check for non-existent accounts in column G.

In cell L5, write this formula:

=IF(ISERROR(VLOOKUP(IS, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "OK")

This formula checks whether the account stated in the cell below the data title ACCOUNT CREDIT already exists in the Chart of Accounts. (The Chart of Accounts is in the sheet TB). If the account already exists, the formula will return OK. Otherwise, it will return NOT OK. This may indicate that the account does not exist in the Chart of Accounts (in the TB sheet). So, add the account name into the chart, or replace the account name with one that already exists.

Later on, when you add more data into cells in rows below it, just copy the formula down.

Again, this formula expects that the list of accounts is in column A, from row 5 to row 2000, in sheet TB. If the actual list exceeds row 2000, you must replace the parameter 2000 in the formula with the actual one.

You should review this column (column L) from time to time for non-existent accounts in column I.

In cell 05, write this formula:

=IF(N5="open", CutOffDate-B5,"NR")

This formula computes the age (in days) of unpaid sales invoice. In cell N5, we will state open if the invoice is still unpaid. The Sales invoice date is supposed to be available in cell B5. Cutoff Date is a name we set for our cut-off date. So, if cells in cell N5 contain open, the formula will subtract the cut-off date from the invoice date. If cell N5 does not contain open, the formula will return HR (for Not Relevant) to the active cell (05).

10

More explanation for structure in sheet Data Page 11

Later on, when you add more data into rows below it, just copy the formula down.

NOTE

We set the name CutoffDate to refer to a cut-off date by using the menu Insert/Name/Define. When we click this menu, the Define Name dialog box shown below will appear. In the box below the label Names in workbook, write the name Cutoff Date. In the box below the label Refers to, write the date it refers to. Be aware that you must precede the date with "-" (equal) sign. (Also remember that in Excel, you must write dates in this format: Month/Day/Year, i.e. the month, followed by the day, and then the year separated by "/" (forward-slash) and enclosed inside (double-quotes). Click Add. Excel will remember the name and what it refers to. Click OK to close the dialog.

Define Name Names in workbook: CutOffDate OK Close Add Delete Refers to: ="4/30/2002"

In cell P5, write this formula:

=IF(N5="open", IF (05<30,"0-30", IF (05<60, "30-60", IF (05-90, "60. 90", IF (05 120, "90-120", ">120")))),"NR")

11

More explanation for structure in sheet Data Page 12 13 14

This formula will check whether cell NS contains the item open. If so, it will return the debt age group (in days) of the corresponding apes (unpaid) sales invoice. (Remember that in column N, we will state open if the invoice is still unpaid). The debt age is expected to be available in column O. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell NS does not contain open the formula will return NR (for Not Relevant) to the active cell (P5)

Later on, when you add data into cells in rows below it just copy the formula down.

In cell R5, write this formula:

=IF(Q5="open", CutOffDate B5. "MR")

This formula computes the age (in days) of unpaid suppliers invoice. (In cell Q5, we will state open if the supplier invoice is still unpaid). The Supplier invoice date is supposed to be available in cell B5 (Cutoffdate is a name we set for our cut-off date. Refer to the Note above on how to set Cutoffdate) So, if cell Q5 contains open, the formula will subtract the cut-off-date from the invoice date. This results in the number of days unpaid. If cell Q5 does not contain open, the formula will return (for Net Relevant) to the active cell (R5).

Later on, when you add more data into cells in rows below it just copy the formula down.

In cell S5, write this formula:

=IF(Q5="open", IF (R5<30 60.="" 90="" if="">120")))), "NR").

This formula computes the group for the credit age (in days) of unpaid suppliers invoice. In cell Q5, we will state open if the invoice is still unpaid. The credit age is supposed to be available in cell R5. The age groups are: 0-30, 31-60, 61-90, 91-120 and 120. If cell Q5 does not contain open, the formula will return NR (for Not Relevant) to the active cell.

Later on, when you add data into cells in rows below it, just copy the formula down.

12

In cell V5, write this formula:

=IF(ISNUMBER(U5), B5+U5, "NR").

This formula computes the due date for payment to supplier/by customer. The formula will add the term (in days, in cell U5) to the invoice date in cell B5. This will produce the due date. If cell U5 does not contain a number type, the formula will write NR to the active cell (V5).

Later on, when you add more data into cells in rows below it, just copy the formula down.

Freeze row 5:
You may want to freeze row 5 so that you can still see the data titles in row 4 when you scroll down.

Row 30000

This is the expected last row for the data area. This row number will appear in many formulas. Why 30000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this row number insufficient, you can increase the number in every formula. Similarly, if your find that row 10000 is enough for your needs, you can change the 30000 in each affected formula to 10000.

To easily find and change every number or character (30000 in this case) in a formula, just use menu Edit/Replace (Refer to topic 27 on 'Shortcuts And Techniques For Working Better And Faster').

Data area

Data area is a rectangular area (bounded by a blank row on top, a blank column on the right, a blank row at the bottom and a filled column in the left) which is expected to contain the data. In this book, the data area

13

starts at cell A5 and ends at cell AC30000. Why 300000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this number insufficient, you can increase the number in every formula. To easily change every number or character in a formula, just use the Edit Replace function. Similarly, if your find that row 10000 is enough for you, you can change every formula to 10000.

Formatting

Certain columns need to be formatted with appropriate format to accommodate proper types of data. Usually, data can be a 'text' type, 'date' type or 'number' type. Columns to accommodate 'date' type must be formatted with 'date' formatting. Columns to accommodate 'number' type must be formatted with 'number' formatting. Columns to accommodate text 'type' must be formatted with 'text' formatting.

This book does not show you how to format a cell since the author expects that you already know how to perform this basic task.

Entering transactions Data in sheet 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

General guide to entering data in sheet Data

GENERAL GUIDE TO INPUTTING DATA IN SHEET 'DATA'

Once the structure of DATA sheet are ready, you can start inputting data in sheet ‘DATA’.

Data that you want to input is based on the financial transaction document we have on hand, such as invoice, vouchers, journals, etc. Enter the data available in each document in the appropriate cell under an appropriate column title in the same row.

A row containing data of a document in its appropriate column is called ‘Transaction row’. Sometimes, data in a row is called a ‘record’.

Data should start at row 5 and continue until row 30000. If your data exceed row 30000, change all references to “30000” in all formula in all sheets.

Entering data in sheet Data column SRC

Filling cells under column title ‘SRC’

Under the column title 'SRC' (for Source), enter a name for your source of transaction. The source refers to a group of documents having similar function that you have, such as invoices, vouchers, journals etc.

Below are some of the common sources :

1. Opening Balances

If this is the first time you use this system, or in a new accounting year, opening balances is one source of transaction. You may write 'OB' or other term of your choice to identify Opening Balance source.

2. Audit Adjustment (previous accounting year)

It is good to identify Auditor's adjustment as a separate source, rather than group them under journals. You may write 'AUDIT ADJ' or other term to identify Audit Adjustment.

3. Provisions for expenses

It is good to identify provisions for expenses as a separate source. Usually, we use journals to be our source for provisions. In this case, we identify journal for provision separate from other journals. You may write 'PROV' or other term to identify Provisions.

4. Purchase (Supplier) Invoices

This refers to invoices from local suppliers. You may write 'INV' or other term to identify Purchase Invoices.

5. Import Invoices

This refers to invoices from oversea suppliers. You may write 'IMPORT' or other term to identify Import Invoices.

6. Salary-Production

This refers to salary summary for production workers. Accounts staff may get the summary from payroll system.

It is good to obtain separate salary summary for production staff from administrative staff. You may write 'SALARY-P' or other term to identify Salary summary for production staff.

7. Salary-Admin

This refers to salary summary for administrative staff. Accounts staff may get the summary from payroll system. It is good to obtain separate salary summary for administrative staff from production staff. You may write 'SALARY-A' or other term to identify Salary summary for administrative staff.

8. Bonus-Production

This refers to bonus summary for production staff. Accounts staff may get the summary from payroll system. It is good to obtain separate bonus summary for production staff from administrative staff. You may write 'BONUS-P' or other term to identify bonus summary for production staff.

9. Bonus-Admin

This refers to bonus summary for administrative staff. Accounts staff may get the summary from payroll system. It is good to obtain separate bonus summary for production staff from administrative staff. You may write 'BONUS-A' or other term to identify bonus summary for administrative staff.

10. Sales Invoices

This refers to our sales invoices. You may write 'SALES' or other term to identify sales invoices.

11. Sale-Subsidiary XX

This refers to sales invoices to one of our subsidiary with name XX. It is good to identify sales to each subsidiary as separate source. You may write 'SALE-X' or other term to identify sales to company X (a subsidiary).

12. Sale-Associate co. XX This refers to sales invoices to one of our accociate company. It is good to identify sales to each associate company as separate source. You may write 'SALE-X' or other term to identify sales to company X (an associated company).

13. Sale-Holding company

This refers to sales invoices to our holding company. It is good to identify sales to holding company as separate source. You may write 'SALE-H-X' or other term to identify sales to company X (holding company).

14. Petty Cash

This refers to one of petty cash transactions (receipts and payments). It is good to identify each petty cash float as separate source. You may write 'PC-X' or other term to identify Petty Cash float managed by staff X.

15. Banks

This refers to one of banks transactions (receipts and payments). It is good to identify each one bank as separate source. You may write 'BANK X' or other term to identify Bank X transactions.

16. Interest on HP

This refers to interest on hire purchase of one item. It is good to identify each item under hire purchase as a separate source. Usually, we use journals to be our source for interest on Hire Purchase. It is good to identify interest on hire purchase as separate source from general journals. You may write 'INT-HP-X' or other term to identify Hire Purchase Interest for item X.

17. Depreciation

This refers to depreciation of one type or category of fixed assets. It is good to identify depreciation of each one type of assets as separate source rather than lump them all together. You may write 'DEPR-X' or other term to identify Depreciation for item X.

18. Journal

This refers to general journals, ie journal which could not be conveniently identified as separate source. As we see, normally depreciation and interest on hire purchase will lump under journals. But since we can conveniently identify them as separate source, we do separate it. You may write 'JOURNAL' or other term to identify journals.

19. Closing Stock-Raw material

This refers to closing stock of raw material. Accounts staff may get the summary from store system. You may write 'CS-RM' or other term to identify Closing Stock of Raw Material.

20. Closing Stock-Finished Product

This refers to closing stock of finished product. Accounts staff may get the summary from store system staff. You may write 'CS-FP' or other term to identify Closing Stock of Finished Product.

21. Closing Stock-Semi-Finished Product

This refers to closing stock of semi-finished product. Accounts staff may get the summary from store system. You may write 'CS-SF' or other term to identify Closing Stock of Semi-Finished Product. Variances in source identified. You may identify sources differently eventhough the above samples are most common. Below are some of the differences :

Some company file their suppliers invoices in a file for each supplier, rather than ALL supplier invoice in ONE file. If this is the case, it is more appropriate if the source is written such as "INV-SUPPLIER XXX", "INV-SUPPLIER YYY", and so on. The sample in this book assume that ALL supplier invoices are kept in ONE file.

Some company file their sales invoices in the separate file for each customer, rather than ALL sales invoice in ONE file. If this is the case, it is more appropriate if the source is written such as "INV-CUST-XXX", "INV-CUST-YYY", and so on. The sample in this book assume that ALL sales invoices are kept in ONE file.

However, some companies generate more than one internal copies for sales invoice. They keep one copy in a master file and another copy in the respective customer file. If this is the case, you can use either way as source.

Why we want to identify sources ?

Apparently, source can be said best as a group of document that get filed together. Proper identification of source will help you easily refer back and search to your physical (paper) document.

Entering data in sheet Data column SRC Notes on Opening Balance

Notes On Entering Opening Balance Data

No double entry

Note that the opening balance data does not involve double entry (Debit and Credit) transactions. It is merely a listing of balances from closing of previous accounting year.

When you enter a Debit item data, there is no corresponding Credit account and amount to write. So, just leave blank the Credit account column and Credit amount column. For the remaining cells in the row, just write NR (for Not Relevant).

Similarly, when you enter a Credit item, there is no Debit account and amount to write. So, just leave blank the Debit account column and Debit amount column. For the remaining cells in the row, just write NR.

However, instead of a blank, you can create an account name such as OPENING BALANCE.

Put Debit and credit items on different rows

Do not write a Debit item data on the same row as a Credit item data. This is because you will encounter difficulties when you want to add a note (comments) to both items since there is only one note column. Also, if sales invoice is pitted against purchase invoice, both will chase for only one 'due date' column.

However, if you can manage these problems, you can put Debit and Credit items data on the same row. This can save rows.

More details

It is better that the opening balances items data contain more details. For instance, instead of one figure for a customer, list down all unpaid invoices for that customer. Instead of one figure for whole staff advance, list down all unpaid staff advances for every staff. Also, instead of one figure for a supplier, list down all unpaid purchase invoices for the supplier, etc.

If you are transferring data from other system, you may need lots of time to record the details, especially if the system is not compatible with Excel.

If you already using this system, transferring details to the following year file is very easy. This is because you will have a ready list of unpaid invoices etc row by row. It is just a matter of selecting and transferring (or copying and deleting) the data.

Check data

After you finish entering the Opening Balances items data, check that its absolute total debit amounts agree with total credit amounts. Just look at the cells H1 and J1.

42

Entering data in sheet Data column B C D E page 31

Filling cells under column title ‘DATE’ (Column B).

Under the column title 'DATE', enter the date of the transactions as appear on the document. The date of transactions is usually typed on the document.

Remember that to enter date, use / (forward-slash) sign to separate month, day and year. Remember also that Excel expect you to enter month first, followed day and then year. Internally, Excel keep date by month first, followed by day and year. So, if you enter 1/2/2002, it will mean 2nd January. It can avoid mistake by Excel if you enter year in 4 digits rather than 2 digits.

Make sure the cell that contain the date is formatted appropriately. If you choose format 'm/d/yyyy', then you enter date 1/2/2002, it will appear the same order ie 1/2/2002 (Internally, this read as 2 January 2002). But if you choose format 'd/m/yyyy', then you enter date 1/2/2002, you will see different order ie 2/1/2002 (though this still read 2 January 2002, internally). So, be careful with formatting of date.

Filling cells under column title ‘MTH’ (Column C)

Under the column title 'MTH', enter the month of the transactions. Use numbers to indicate month, such as 1, 2, 3 etc. This can save hard disk space.

This column is useful if you later want to analyse data according to month.

You can use formula to extract month from the date in column DATE, but I do not recommend it. This way (not using formula), eventhough the date say 2/28/2002 (Month :February), you may want to include this transaction under month 3 (March) when you analyse data or in your report, for whatever reason.

Use 0 (zero) to indicate month of the opening balances items.

Filling cells under column title ‘SRC NO’ (Column D)

Under the column title 'SRC NO', enter the serial number or reference number of the source document. For example : Sales Invoice number, Purchase Invoice number, journal number, voucher number, etc. If there is no reference number, leave blank or you can replace with some other notes.

Filling cells under column title ‘OTHER REF./ NO.’ (Column E)

(OTHER REF./NO. refers for OTHER REFERENCE OR NUMBER)

Under the column title 'OTHER REF./NO.', enter the other serial or reference number of the source document or other document relevant to the source document. For example : You have enter sales invoice number under SRC NO, so you may want to enter the PO number under OTHER SRC NO. If there is no other reference number, leave blank or you can replace with some other notes.

Entering data in sheet Data column ‘NOTE’ (Column F G H)

Filling cells under column title ‘NOTE’ (Column F)

Under the column title 'NOTE', enter any note you want regarding the transaction. If there is no note to write, you can leave blank.

Filling cells under column title ‘ACCOUNT-DEBIT’ (Column G)

Under the column title 'ACCOUNT-DEBIT', enter the account name that you want it to be debited. The account name must already exist in the Chart of account (in TB sheet). If the account name not yet exist, update the Chart of account (in TB sheet).

Excel is very sensitive about spelling. A single dot or a single space can mean a different item. For example, 'My World" with "My World" is considered different, because of more spaces in the latter. "MyWorld" with "My.World" is also different, because of a dot in the latter. Another example, 'My World" with "My World " is considered different because of the additional space after the end of the latter word . This is difficult to trace since we cannot see the space character.

To check whether there is an additional space at the end of the account name, activate the cell and press F2. Look at the position of the caret. You will notice whether there is any space before the caret.

Or, the formula in cell under column title 'CHECK ACCOUNT-DEBIT' will flag you if the account does not yet exist (may be due to slight different in spelling). However, small letter or capital letter is not treated as different.

Filling cells under column title ‘RM-DEBIT’ (Column H)

Under the column title 'RM-DEBIT', enter the amount of the account name that you debited. For example, 1000. If you format appropriately, it will appear as "1,000.00" or "RM 1,000.00" etc.

Entering data in sheet Data column ACCOUNT CREDIT (Column I)

Entering Data In The ACCOUNT-CREDIT Column (Column I)

In the ACCOUNT CREDIT column, enter the account names that you want to be credited. The account name must already exist in the Chart of accounts (in TB sheet). If an account name does not yet exist, update the Chart of accounts (in TB sheet).

As explained at the top of the page, excel is very particular about spelling. A single dot or a single space can mean a different item.

33
Entering Data In The RM-CREDIT Column (Column J)

In the RM-CREDIT column, enter the amounts of the account names that you credited. For example, -1000. Precede the credit amount with minus sign. The minus sign indicates a credit amount. If you have formatted the column appropriately, it will appear as "(1,000.00)" or "(RM 1,000.00)" etc.

Entering Data In The CHECK ACCOUNT-DEBIT Column (Column K)

In the CHECK ACCOUNT DEBIT column, cell K5 should already contain the following formula:

=IF(ISERROR(VLOOKUP(G5, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "OK")

If there are more entries in the cells below it, just copy down the formula. The formula will return NOT OK if the account name in the column ACCOUNT DEBIT is not listed in sheet TB. The formula will return OK if the account name in column ACCOUNT-DEBIT is listed in column A in sheet TB.

Review this column to check for non-existent account names.

Entering Data In The CHECK ACCOUNT-CREDIT Column (Column L)

In the CHECK ACCOUNT CREDIT column, cell 15 should already contain the following formula:

=IF(ISERROR(VLOOKUP(IS, INDIRECT("TB!A5: A2000"), 1, FALSE)), "NOT OK", "ОК")

If there are more entries in the cells below it, just copy down the formula. The formula will return NOT OK if the account name in the column ACCOUNT CREDIT is not listed in sheet TB. The formula will return OK if the account name in column ACCOUNT CREDIT is listed in sheet TB

Review this column to check for non-existent account names.

34

Entering data in sheet Data column BANK RECON (Column M) Page 35

Entering Data In The BANK RECON Column (Column M)

In the BANK RECON column, state the month in which each amount of bank receipt and payment appear in the statement received from bank. For example, you can write 1, 2, 3 etc. or jan, feb, mar, etc

If the amount of Debit or Credit is not related to a bank account (and not subject to reconciliation), state NR (for Not Relevant or other term you want to use).

If the amount of Debit or Credit is related to a bank account (and subject to reconciliation), state open (until it is reconciled).

This column will help us prepare a Bank reconciliation statement.

Entering Data In The DEBTORS STTMNT Column (Column N)

In the DEBTOR STTMNT column, put a mark to indicate whether our sales invoice has been paid. Use pd to mark the paid invoice. Use open to mark invoice still unpaid. If the source is not related to our sales invoice (and not subject to our monitoring of payment), mark NR (for Not Relevant).

This column is useful to find out paid and unpaid invoices by our customers.

Entering Data In The DEBT AGE Column (Column O)

In the DEBT AGE (days) column, cell 05 should already contain the following formula:

=IF (NS="open", CutOffDate-85,"NR")

Just copy the formula to the cells below it.

Please refer to the note on page 11 on how to set the 'Cut OFF Date'.

35

Entering data in sheet Data page 36

Entering Data In The DEBT AGE GROUP Column (Column P)

In the DEBT AGE GROUP column, cell P5 should already contain the following formula

=IF(N5="open",IF(O5<30,"0-30",IF(O5<60,"30-60",IF(O5<90,"60-90",IF(O5<120,"90-120",">120"))))),"NR")

Just copy the formula to the cells below.

Entering Data In The CREDITOR STTMNT Column (Column Q)

In the CREDITOR STTMNT column, put a mark to indicate whether our suppliers invoice has been paid. Use pd to mark the paid invoice. Use open to mark invoice still unpaid. If the source is not related to our suppliers invoice (and not subject to our monitoring of payment), mark NR (for Not Relevant) or leave blank.

This column will help us locate unpaid suppliers invoices.

Entering Data In The CREDIT AGE Column (Column R)

In the CREDIT AGE (days) column, cell R5 should already contain the following formula:

=IF(Q5="open", CutOffDate-B5, "NR")

Just copy the formula to the cells below.

Entering Data In The CREDIT AGE GROUP Column (Column S)

In the CREDIT AGE GROUP column, a cell S5 should already contain the following formula:

=IF(Q5="open",IF(R5<30,"0-30",IF(R5<60,"30-60",IF(R5<90,"60-90",IF(R5<120,"90-120",">120"))))),"NR")

Just copy the formula to the cells below it.

36

Entering data in sheet Data page 37

Entering Data In The HOLD Column (Column T)

In the HOLD column, state the amounts of payments or receipts already recorded in the column RM-DEBIT or RM-CREDIT but not effectively released or received.

If the amount of Debit or Credit is not related to bank receipt or payment, or not subject to holding, just mark NR (for Not Relevant).

This column will help determine the actual true cash still available in hand or in bank.

Entering Data In The TERM Column (Column U)

In the TERM column, state the credit terms given to our customers or by our suppliers. The term should be stated in term of days, e.g. 30, 60, 90 etc. If data is not related to credit term, write NR (for Not Relevant).

Entering Data In The DUE DATE Column (Column V)

In the DUE DATE column, cell Q5 should already contain the following formula:

=IF(P5="NR","NR", B5+P5)

Just copy the formula to the cells below.

This column will help us to monitor dates of payment/receipt due.

Entering Data In The ITEM PURCHASED Column (Column W)

In the ITEM PURCHASE column, write the names of the items we purchase. Usually this is stated in the supplier's invoice.

If the document is not related to purchase of an item, just write NR (for Not Relevant).

This column will help us to monitor the items we purchase.

37

Entering data in sheet Data page 38

Entering Data In The ITEM SOLD Column (Column X)

In the ITEM SOLD column, write the names of the items we sell. Usually this is stated in our sales invoice.

If the document is not related to selling of an item, just write MR (for Not Relevant).

This column is useful if we want to monitor items we sell.

Entering Data In The QTY Column (Column Y)

In the QTY column, write the quantities of the items we sell or purchase.

If the document is not related to item that we sell or purchase, just write HR (for Not Relevant).

This column will help us to monitor the quantities of items we sell and items we purchase.

Entering Data In The UNIT Column (Column Z)

In the UNITT column, write the measurement unit of the quantity of the item we sell or purchase.

If the data is not related to item that we sell or purchase, just write NR (for Not Relevant).

This column serve to indicate the unit of measurement we use to quantify the items (eg KG, MT, etc)

Entering Data In The PRICE Column (Column AA)

In the PRICE column, write the prices of the items we sell or purchase. If the data is not related to item that we sell or purchase, write NR (for Not Relevant).

This column will help us to monitor the prices of items we sell and items we purchase.

38

Entering data in sheet Data page 39

Entering Data In The TEMP Column (Column AB)

You can leave cells in the TEMP (for Temporary) column blank. It is a multi-purpose column. You can use for any other temporary task. After you use this column, you can delete its content at any time without affecting the accounting data.

Entering Data In The ORIGINAL ORDER Column (Column AC)

In case you want to sort your data, use this column to record the original order of rows before sorting so that you can revert to the original order. However, you may choose to perform sorting on a copy of the sheet.

Entering data in sheet Data Important Things page 39

Do Not Write Anything In Column AD

Do not write anything in the cells in column AD because they serve as a the right border of the data area. So this column must be left blank, unless you know the effect. You can write anything in cells after column AD, i.e. column AE onwards.

No Blank Cells In SRC Column

Make sure there are no blank cells in the cells below the column title SRC, between the rows of data. This column will serve as the left border of the data area. If there is a blank cell under the column title SRC, Excel will most likely use the blank cell to segregate the data area. This will affect the results of data manipulation using Filtering and PivotTable functions.

No Blank Cells Between Items In Column Titles

Make sure there are no blank cells in between column titles in row 4. This row will serve as the top border of the data area. If there is a blank cell separating column titles, Excel will be confused as to the area of your data. This will affect the results of data manipulation using Filtering and Pivot Table.

39
When One Transaction Gives Rise To More Than One Account Name

One transaction may result in more than one accounts name. In cases like this, you need to use as many number of rows to accommodate the number of accounts involved. For example, one claim voucher may affect two accounts; say Travelling account and Stationery account. In this case we need two rows the first row to accommodate data for Travelling account and the second row to accommodate data for the Stationery account However, both rows must be on top of one another - do not allow rows with other data to separate them.

Make all details for both rows the same except for the account name (under columns ACCOUNT-DEBIT and ACCOUNT-CREDIT) and amounts (under RM-DEBIT and RM-CREDIT). Normally, we will fill the data of the first account first, then copy the details into the subsequent row below it. You can use the key combination Ctrl+D to easily copy down.

When One Transaction Involves More Than One Item Sold Or Purchased

One transaction may involve more than one item sold or purchased. In cases like this, we need to use as many number of rows to accommodate the number of unique item names. For example, one sales invoice may affect say two items i.e. Item A and Item B. In this case we need two rows the first row to accommodate data for Item A and the second row to accommodate data for Item B. Both rows must be close to one another do not allow rows with other data to separate them.

Logically, all data for both rows are the same except for the item name and quantity. Usually we will fill the data of the first item first, then copy down the data into the subsequent row below it. Use the key combination Ctrl+D to easily copy down.

40

Entering data in sheet Data important things page 39 40 41

Group Together Data From The Same Source

Keep data from the same source close to each other. When you receive a new accounting document and want to enter data of the new document, try to enter the new data into a row immediately below the existing data from the ésame source. This is to make sure that data relating to the same source are kept close to each other. This will be helpful when you need to search for the data later on.

If the target row is already occupied by data of a different source, insert a new tow (menu Insert/Row) to accommodate the new data. When we insert a new row, all the existing data of the different source will be pushed down a row automatically.

You may think it is a good idea to just enter data in the blank row NOTE immediately below the last filled row irrespective of source group, and then sort the data according to source group. You can do it this way if as long as you don't mind having the Opening Balance data appear at the bottom of your list, i.e. in an illogical order.

Arrange Sources Of Data In Groups Of Logical Order

Grouping the data of the same source together is one thing, and arranging the sources according to a logical order is another matter. The suggested order of data groups (from top row to bottom row) is as follows (based on the column SRC):

Opening Balance
Audit Adjustments (previous yeat)
Provisions
Local Supplier Invoice
Import Invoice
Salary
Bonus
Sales
Petty Cash
Bank Book
Journals
Stock
Audit adjustments (current year)

Although this order is not compulsory, it is preferable. It is based on the logic that Opening Balance should appear at the top of a report, liabilities should appear before payments, debt should appear before payments, etc. This will be reflected in a report (eg. ledger) generated from the data.

You may have sources different from the above list. You are free to arrange them as you see fit.

41

Review Of Data and structure In Sheet DATA

Review Data And Structure in sheet Data

A review of the data and its structure in sheet DATA, from time to time, is necessary to detect any inconsistencies in our data. Inconsistencies in our data will render formulas, filtering, sorting and PivotTable functions inaccurate, producing unexpected results.

We should take notice of the following matters in the sheet named DATA:

1. Row 1 - Make sure formulas in some of the cells in row 1 are still intact.
2. Row 3 All cells must remain blank.
3. Row 4-Cells from column A to AC must contain data titles. There should be no blank cells in between the cells. Spelling must not change.
4. Row 5 Some cells contain initial formulas. Make sure formulas are not deleted. This row is the starting data area.
5. Rows 5 to 30000 - This is the Data area. Data must be filled row by row. Make sure there are no blank cells in columns A, G, H, I, and J, in between rows of data. Other columns may contain blank cells.
6. The absolute total amount of RM-DEBIT and RM-CREDIT should be the same.
7. Review items in column CHECK ACCOUNT DEBIT for non-existent accounts name.
8. Review items in column CHECK ACCOUNT CREDIT for non-existent accounts name.
9. The last filled row in columns A, G, H, I and J must be the same.

48

Handling Opening balances for new accounting year

Use the balances in the closing Trial Balance.

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.

Setting the structure in Sheet TB

Sheet TB (short for Trial Balance) will function as our List of Accounts (or Chart of Accounts) and Trial Balance.

You may think it a bit strange that a Chart of Accounts can simultaneously function as the Trial Balance. But with Excel, it can be done.

Set The Structure In Sheet TB

The next step is to set the structure in sheet TB (for Trial Balance). This sheet is used to contain our List of accounts (Chart of Accounts). It will also serve as the Trial Balance.

Sample structure

Here is a sample of the data structure in sheet TB:

ACCOUNTS	BALANCE SHEET MAIN CATEGORY	BALANCE SHEET CATEGORY	PL CATEGORY	PL DETAIL	RM DEBIT/ (CREDIT)	CHECK DUPLICATE
TOTAL/SUBTOTAL					0.00
Checking row					OK

Below is the explanation, row by row.

14

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.