Saturday, July 25, 2026
Friday, July 24, 2026
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
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
Friday, August 30, 2024
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 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.
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 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.
Tuesday, July 30, 2024
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
Thursday, July 25, 2024
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 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.
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 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.
Saturday, July 20, 2024
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.
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.
Friday, July 29, 2011
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.
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.
Thursday, July 28, 2011
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
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.
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).
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).
Wednesday, June 29, 2011
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.
- 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.
Tuesday, June 28, 2011
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
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 on 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
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.
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 inputting 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.
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
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 difference :
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.
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.
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
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 difference :
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 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.
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.
Filling cells under column title ‘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.
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.
Monday, June 27, 2011
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.
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.
Sunday, June 26, 2011
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:
Below is the explanation, row by row.
14
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
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.
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.
Explanation to structure in sheet TB
Row 1
In cell Al, type this label: TOTAL/SUBTOTAL
This is to indicate that some of the cells in row 1 are used to accommodate formulas which calculate totals/subtotals.
In cell F1, write this formula: =SUBTOTAL(9, INDIRECT("F5:F2000"))
This formula calculates the total of the amounts in cells in column F (from row 5 to row 2000). We will see later that cells F5 to F2000 will contain totals of debit amounts for each account in column A.
In cell G1 write this formula: =SUBTOTAL(9, INDIRECT("G5:G2000"))
This formula calculates the total of amounts in cells in column G (from row 5 to row 2000). We will see later that cells G5 to G2000 will contain totals of credit amounts for each account in column A.
In cell H1 write this formula: =SUBTOTAL(9, INDIRECT("H5: H2000"))
This formula calculates the total of the amounts in cells in column H (from row 5 to row 2000). We will see later that cells H5 to H2000 will contain net totals of credit and debit amounts for each account in column A.
Other cells: You can use other cells in row 1 for other purpose.
Change of formulas: You can change the formulas if you know the effect.
15
Why row 5 to 20007
It is expected that row 5 to row 2000 is sufficient to accommodate all accounts names. If you find that you need more rows, then change the 2000 in each affected formula above to the required figure.
Row 2
In coll 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 result of the other formulas in our file.
For example, cell H2 contains a formula which checks the results of the formula in cell G1 and H1. The sum of cell G1 and H1 should be zero (total Debit amounts minus Total credit amount). The formula is:
=IF(61-81-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 the data.
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 rows of data (start at row 4 to 2000). If any cell is not blank, some operation will not perform correctly, particularly the data filtering and PivotTable function.
Row 4
Cells in row 4 are used to show the data titles (or field titles or column titles) for columns A to I. The data titles are used to indicate what data should be put in cells under each title.
16
In cell A4, write ACCOUNTS.
In cell 84, write BALANCE SHEET MAIN CATEGORY.
In cell C4, write BALANCE SHEET SUB CATEGORY.
In cell D4, write PL CATEGORY (for PROFIT & LOSS CATEGORY).
In cell E4, write PL DETAIL (for PROFIT & LOSS DETAIL).
In cell F4, write RM DEBIT (for TOTAL RM DEBIT).
In cell G4, write RM CREDIT (for TOTAL RN CREDIT).
In cell H4, write RM NET (for NET OF DEBIT AND CREDIT).
In cell 14, write CHECK DUPLICATE.
In cell J4 onwards, do not write anything unless you know the effect. Do not rearrange the order unless you know the effect. Do not change the spelling unless you know the effect.
Row 5
This is where you can start to input your accounts names (the first row for data).
Some cells in row 5 will contain initial formulas. Later on, when you add data into rows below it, just copy the formula down. Be careful not to delete the cells containing formula.
In cell F5, write this formula:
=SUMIF(INDIRECT("DATA!G5: G30000"), A5, INDIRECT("DATA! H5: H30000"))
This formula computes the total of the debit amounts of the account in column H in sheet DATA where its name in column G in that sheet is the same as the one in cell A5 in sheet TB.
In cell G5, write this formula:
=SUMIF(INDIRECT("DATA! 15: 130000"), AS, INDIRECT("DATA!J5: J30000"))
This formula totals the credit amounts of the account in column J in sheet DATA where its name in column I in that sheet is the same as the one in cell A5 in sheet TB.
17
In cell H5, write this formula:
=F5+G5
This formula computes the net of the total the credit amounts and the total of debit amounts of the account.
In cell 15, write this formula:
=IF(COUNTIF(INDIRECT("A5: A2000"), A5)>1, "DUPLICATE", "OK")
This formula indicates whether the account name in column A is duplicated. If it is a duplicate, it will return DUPLICATE, otherwise OK.
Freeze row 5:
You may want to freeze this row 5 so that you can still see the titles in row 4 when you scroll down.
Row 2000
This is the expected last row for the data area. This row number will appear in many formulas in sheet TB.
Why 2000? The author expects this number of rows to be sufficient to hold all accounts names. 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 menu Edit/Replace. Similarly, if your find that row 500 is enough for you, you can change every formula to 500, etc.
Formatting
Certain columns need to be formatted with appropriate format to accommodate proper type 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.
18
In cell Al, type this label: TOTAL/SUBTOTAL
This is to indicate that some of the cells in row 1 are used to accommodate formulas which calculate totals/subtotals.
In cell F1, write this formula: =SUBTOTAL(9, INDIRECT("F5:F2000"))
This formula calculates the total of the amounts in cells in column F (from row 5 to row 2000). We will see later that cells F5 to F2000 will contain totals of debit amounts for each account in column A.
In cell G1 write this formula: =SUBTOTAL(9, INDIRECT("G5:G2000"))
This formula calculates the total of amounts in cells in column G (from row 5 to row 2000). We will see later that cells G5 to G2000 will contain totals of credit amounts for each account in column A.
In cell H1 write this formula: =SUBTOTAL(9, INDIRECT("H5: H2000"))
This formula calculates the total of the amounts in cells in column H (from row 5 to row 2000). We will see later that cells H5 to H2000 will contain net totals of credit and debit amounts for each account in column A.
Other cells: You can use other cells in row 1 for other purpose.
Change of formulas: You can change the formulas if you know the effect.
15
Why row 5 to 20007
It is expected that row 5 to row 2000 is sufficient to accommodate all accounts names. If you find that you need more rows, then change the 2000 in each affected formula above to the required figure.
Row 2
In coll 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 result of the other formulas in our file.
For example, cell H2 contains a formula which checks the results of the formula in cell G1 and H1. The sum of cell G1 and H1 should be zero (total Debit amounts minus Total credit amount). The formula is:
=IF(61-81-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 the data.
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 rows of data (start at row 4 to 2000). If any cell is not blank, some operation will not perform correctly, particularly the data filtering and PivotTable function.
Row 4
Cells in row 4 are used to show the data titles (or field titles or column titles) for columns A to I. The data titles are used to indicate what data should be put in cells under each title.
16
In cell A4, write ACCOUNTS.
In cell 84, write BALANCE SHEET MAIN CATEGORY.
In cell C4, write BALANCE SHEET SUB CATEGORY.
In cell D4, write PL CATEGORY (for PROFIT & LOSS CATEGORY).
In cell E4, write PL DETAIL (for PROFIT & LOSS DETAIL).
In cell F4, write RM DEBIT (for TOTAL RM DEBIT).
In cell G4, write RM CREDIT (for TOTAL RN CREDIT).
In cell H4, write RM NET (for NET OF DEBIT AND CREDIT).
In cell 14, write CHECK DUPLICATE.
In cell J4 onwards, do not write anything unless you know the effect. Do not rearrange the order unless you know the effect. Do not change the spelling unless you know the effect.
Row 5
This is where you can start to input your accounts names (the first row for data).
Some cells in row 5 will contain initial formulas. Later on, when you add data into rows below it, just copy the formula down. Be careful not to delete the cells containing formula.
In cell F5, write this formula:
=SUMIF(INDIRECT("DATA!G5: G30000"), A5, INDIRECT("DATA! H5: H30000"))
This formula computes the total of the debit amounts of the account in column H in sheet DATA where its name in column G in that sheet is the same as the one in cell A5 in sheet TB.
In cell G5, write this formula:
=SUMIF(INDIRECT("DATA! 15: 130000"), AS, INDIRECT("DATA!J5: J30000"))
This formula totals the credit amounts of the account in column J in sheet DATA where its name in column I in that sheet is the same as the one in cell A5 in sheet TB.
17
In cell H5, write this formula:
=F5+G5
This formula computes the net of the total the credit amounts and the total of debit amounts of the account.
In cell 15, write this formula:
=IF(COUNTIF(INDIRECT("A5: A2000"), A5)>1, "DUPLICATE", "OK")
This formula indicates whether the account name in column A is duplicated. If it is a duplicate, it will return DUPLICATE, otherwise OK.
Freeze row 5:
You may want to freeze this row 5 so that you can still see the titles in row 4 when you scroll down.
Row 2000
This is the expected last row for the data area. This row number will appear in many formulas in sheet TB.
Why 2000? The author expects this number of rows to be sufficient to hold all accounts names. 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 menu Edit/Replace. Similarly, if your find that row 500 is enough for you, you can change every formula to 500, etc.
Formatting
Certain columns need to be formatted with appropriate format to accommodate proper type 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.
18
Entering data in sheet TB page 19
Input Data In Sheet TB (Trial Balance)
After you set the structure in the sheet TB, you can start adding date in it. It is a good idea to set up your chart of accounts first in this sheet before you enter transaction data in sheet DATA. However, you can choose to do otherwise. No harm in that.
Entering Data In The ACCOUNTS Column (Column Al)
In cells in column A titled ACCOUNTS, you will enter names of accounts you have. The names do not necessarily have to be entered in alphabetical order. You can always sort them later (You have to know how to sort in Excel.)
For accounts that usually appear in the Profit & Loss statement, enter the name of each item (account). For example: Printing & Stationery, Legal tees, Assessments fees. Upkeep of Motor Vehicle, Upkeep of Building, Road Tax, Insurances, Licences, Bank Charges, etc. (one after another).
Here are the specific items which deserve separate treatment:
Salary
This is an item that usually appears in the Profit & Loss statement. It can be broken down into more other, more detailed accounts (such as basic salary, allowance, deductions, net etc). Enter each detailed item. (Later on, you can combine the various accounts into one salary figure in the actual Profit & Loss statement using a formula.)
Fixed Assets and Provisions for Depreciation
Enter each asset category, not each detailed item, because this book expects that a separate system exists for fixed assets. Usually, the categories are Land, Buildings, Plant & Machinery, Office Equipments, Furniture & Fittings, etc.
Enter also the provisions for depreciation accounts for each asset category, which usually are: Prov for Depr for Plant & Machinery, Prov for Depr for Motor Vehicles etc. (Different industries have different categories.)
Closing Stock
Enter each category of closing stock name, not each item of stock, because this book expects that a separate system exists for stock. Usually, the categories are: Raw Material, Semi-Finished Product, and Finished Product. However, different industries have different categories.
Investment
Enter each category name (such as quoted and unquoted), not each item, because this book expects that a separate system exist for investments.
19
After you set the structure in the sheet TB, you can start adding date in it. It is a good idea to set up your chart of accounts first in this sheet before you enter transaction data in sheet DATA. However, you can choose to do otherwise. No harm in that.
Entering Data In The ACCOUNTS Column (Column Al)
In cells in column A titled ACCOUNTS, you will enter names of accounts you have. The names do not necessarily have to be entered in alphabetical order. You can always sort them later (You have to know how to sort in Excel.)
For accounts that usually appear in the Profit & Loss statement, enter the name of each item (account). For example: Printing & Stationery, Legal tees, Assessments fees. Upkeep of Motor Vehicle, Upkeep of Building, Road Tax, Insurances, Licences, Bank Charges, etc. (one after another).
Here are the specific items which deserve separate treatment:
Salary
This is an item that usually appears in the Profit & Loss statement. It can be broken down into more other, more detailed accounts (such as basic salary, allowance, deductions, net etc). Enter each detailed item. (Later on, you can combine the various accounts into one salary figure in the actual Profit & Loss statement using a formula.)
Fixed Assets and Provisions for Depreciation
Enter each asset category, not each detailed item, because this book expects that a separate system exists for fixed assets. Usually, the categories are Land, Buildings, Plant & Machinery, Office Equipments, Furniture & Fittings, etc.
Enter also the provisions for depreciation accounts for each asset category, which usually are: Prov for Depr for Plant & Machinery, Prov for Depr for Motor Vehicles etc. (Different industries have different categories.)
Closing Stock
Enter each category of closing stock name, not each item of stock, because this book expects that a separate system exists for stock. Usually, the categories are: Raw Material, Semi-Finished Product, and Finished Product. However, different industries have different categories.
Investment
Enter each category name (such as quoted and unquoted), not each item, because this book expects that a separate system exist for investments.
19
Entering data in sheet TB page 20 21
Unappropriated profit/loss brought forward
Enter a suitable name.
Holding company
Enter its name.
Subsidiary companies
Enter each name.
Associated companies
Enter each name.
Trade debtor
Enter each debtor's name.
Other debtors
Enter each debtor's name.
Staff loan/advance
Enter the name of each staff concerned.
Prepayments
Enter the suitable name of each prepayment, e.g. Rental prepaid, Assessment prepaid, etc.
Cash in bank
Enter each bank name.
Petty cash
Enter a suitable name to identify each float.
Trade creditor
Enter each creditor's name.
Other creditors
Enter each other creditor's name.
Provisions for expenses
Enter a suitable name for each provision.
20 Bank overdraft
Enter each bank overdraft's name
Loan provider
Enter each loan provider's name.
Income tax payable
Enter a suitable name.
Dividend payable
Enter a suitable name.
Follow the similar trend for other items not listed above. The general guide is that if you don't have a separate system, enter the detailed account name, as detailed as possible.
TIP
You may elect not to write the account name in full. For instance, if the full name is THE BANK OF MALAYSIA GHANA PRIVATE COMPANY LIMITED, you can shorten it to BANK MSIA GHANA. However, make sure the abbreviated name does not confuse you. Use sparingly. The short name will save some disk space and speed up your work.
Accounts name must be entered row by row. Do not allow blank cells in between account names.
Enter a suitable name.
Holding company
Enter its name.
Subsidiary companies
Enter each name.
Associated companies
Enter each name.
Trade debtor
Enter each debtor's name.
Other debtors
Enter each debtor's name.
Staff loan/advance
Enter the name of each staff concerned.
Prepayments
Enter the suitable name of each prepayment, e.g. Rental prepaid, Assessment prepaid, etc.
Cash in bank
Enter each bank name.
Petty cash
Enter a suitable name to identify each float.
Trade creditor
Enter each creditor's name.
Other creditors
Enter each other creditor's name.
Provisions for expenses
Enter a suitable name for each provision.
20 Bank overdraft
Enter each bank overdraft's name
Loan provider
Enter each loan provider's name.
Income tax payable
Enter a suitable name.
Dividend payable
Enter a suitable name.
Follow the similar trend for other items not listed above. The general guide is that if you don't have a separate system, enter the detailed account name, as detailed as possible.
TIP
You may elect not to write the account name in full. For instance, if the full name is THE BANK OF MALAYSIA GHANA PRIVATE COMPANY LIMITED, you can shorten it to BANK MSIA GHANA. However, make sure the abbreviated name does not confuse you. Use sparingly. The short name will save some disk space and speed up your work.
Accounts name must be entered row by row. Do not allow blank cells in between account names.
Entering data in sheet TB - Column B C Page 21 22
Entering Data In The BALANCE SHEET-MAIN CATEGORY Column (Column B)
In the column titled BALANCE SHEET MAIN CATEGORY, identify and enter the main categories into which the accounts (under column ACCOUNTS) will go in the Balance Sheet. Usually, the main categories are Capital, Unappropriated Profit b/f. Investment. Inter Company. Fixed Assets, Current Assets and Current Liabilities. All Profit & Loss accounts should come under Unappropriated Profit b/f.
For example, an account named Bank A in column A would have been categorised into Current Assets in column B.
Do not miss a single account.
Do not allow blank cells in between category names.
21
Entering Data In The BALANCE SHEET-SUB CATEGORY Column (Column C)
In the column titled BALANCE SHEET-SUB CATEGORY, identify and enter the sub-categories into which the accounts (under column ACCOUNTS) will go in the Balance Sheet. Usually, there is only one sub-category level in the Balance Sheet. Some main categories in the Balance Sheet do not have sub-categories, eg. Capital, Unappropriated Profit b/f, Investments and Fixed Assets. In this case, we state the same name of the main category as the sub-category name, i.e. Capital, Unappropriated Profit b/f, Investments and Fixed Assets etc.
Other main categories in the Balance Sheet have sub categories like Inter-Company. Current Assets and Current Liabilities. The sub-categories under Inter-Company are usually Holding company, Subsidiaries and Associated Company. The sub-categories under Current Assets are usually Stock, Trade Debtor, Other Debtors, Cash in Bank, Cash in Hand, etc. The sub-categories under Current Liabilities are usually Trade Creditors, Other Creditors, Term Loan, Bank overdraft, etc.
For example, an account named BANK A in column A would have been categorised into CASH IN BANK in column C.
Do not miss a single account. Do not allow blank cells in between sub-category names
Entering Data In The PL-CATEGORY Column (Column D)
In the column titled PL-CATEGORY (for Profit & Loss Category), identify and enter the categories into which the accounts (under column ACCOUNTS) will go in the Profit & Loss statement. This is relevant only for accounts which usually come into the Profit & Loss statement. Usually the categories are Income, Cost of Sale, Labour cost, Direct cost, Marketing expense Administration expense, Other income/expense, Taxation, Dividends, Reserves etc. For an account in the Balance Sheet category, write NR (for Not Relevant) or leave a blank, but be consistent (use either NR or blank throughout).
Do not miss a single account.
22
In the column titled BALANCE SHEET MAIN CATEGORY, identify and enter the main categories into which the accounts (under column ACCOUNTS) will go in the Balance Sheet. Usually, the main categories are Capital, Unappropriated Profit b/f. Investment. Inter Company. Fixed Assets, Current Assets and Current Liabilities. All Profit & Loss accounts should come under Unappropriated Profit b/f.
For example, an account named Bank A in column A would have been categorised into Current Assets in column B.
Do not miss a single account.
Do not allow blank cells in between category names.
21
Entering Data In The BALANCE SHEET-SUB CATEGORY Column (Column C)
In the column titled BALANCE SHEET-SUB CATEGORY, identify and enter the sub-categories into which the accounts (under column ACCOUNTS) will go in the Balance Sheet. Usually, there is only one sub-category level in the Balance Sheet. Some main categories in the Balance Sheet do not have sub-categories, eg. Capital, Unappropriated Profit b/f, Investments and Fixed Assets. In this case, we state the same name of the main category as the sub-category name, i.e. Capital, Unappropriated Profit b/f, Investments and Fixed Assets etc.
Other main categories in the Balance Sheet have sub categories like Inter-Company. Current Assets and Current Liabilities. The sub-categories under Inter-Company are usually Holding company, Subsidiaries and Associated Company. The sub-categories under Current Assets are usually Stock, Trade Debtor, Other Debtors, Cash in Bank, Cash in Hand, etc. The sub-categories under Current Liabilities are usually Trade Creditors, Other Creditors, Term Loan, Bank overdraft, etc.
For example, an account named BANK A in column A would have been categorised into CASH IN BANK in column C.
Do not miss a single account. Do not allow blank cells in between sub-category names
Entering Data In The PL-CATEGORY Column (Column D)
In the column titled PL-CATEGORY (for Profit & Loss Category), identify and enter the categories into which the accounts (under column ACCOUNTS) will go in the Profit & Loss statement. This is relevant only for accounts which usually come into the Profit & Loss statement. Usually the categories are Income, Cost of Sale, Labour cost, Direct cost, Marketing expense Administration expense, Other income/expense, Taxation, Dividends, Reserves etc. For an account in the Balance Sheet category, write NR (for Not Relevant) or leave a blank, but be consistent (use either NR or blank throughout).
Do not miss a single account.
22
Entering data in sheet TB - Column E F G H Page 23 24
Entering Data In The PL-DETAIL Column (Column E)
Under the column titled PL-DETAIL (for Profit & Loss Detail), enter the actual account names which the accounts (under column ACCOUNTS) will appear in the Profit & Loss statement. This is relevant for accounts which usually come into the Profit & Loss statement. If the accounts come into a Balance Sheet category, write NR (for Not Relevant) or use a blank instead.
Sometimes, the account name you use in the Chart of Accounts is slightly different from what you want to appear in the Profit & Loss statement. For example, in the Chart of Accounts, you use P & S, but in the Profit & Loss statement you want it to appear as Printing & Stationery.
In another example, in the Chart of accounts under the column ACCOUNTS, you have many items related to salary, e.g. Salary-Basic, Salary-Allowance, Salary-EPF etc., but under column PL-DETAIL, you want to group it all under one name ie. Salary since this is the name that will appear in the Profit & Loss statement.
Do not miss a single account.
Copying Formula Into Cells In The RM-DEBIT Column (Column F)
Located immediately under the column titled RM-DEBIT is the cell F5 which contains a formula. This formula will get the total of Debit amounts (in column H in sheet DATA) for every account name in column G (in sheet DATA) which matches the account name that appears in cell A5 (in sheet TB). The formula is:
=SUMIF(INDIRECT("DATA!G5: G30000"), A5, INDIRECT("DATA! HS: H30000"))
Be careful not to delete the formula in this cell.
Copy this formula down to every row where the corresponding cell in column A has an account name. The formula will get the net total of RM-Debit amounts for each account. Remember, you can use Ctrl+D keyboard keys to copy down.
Do not miss copying the formula for a single account.
23
Copying Formula Into Cells In The RM-CREDIT Column (Column G)
Located immediately under the column & titled BM CREDIT is the cell G5 which contains a formula. This formula will total the Credit amounts (in columin I in sheet DATA) for every account name in column I in sheet DATA) which matches the account name that appear in cell A5 (in sheet TB). The formula is :
=SUMIF (INDIRECT("DATAIIS 130000"), AS, INDIRECT("DATA! JS 330000"))
Be careful not to delete the formula in this cell.
Copy this formula down to every row where the corresponding cell in column A has an account name. The formula will get the net total of RM-Credit amounts for each account. Remember, you can use Ctrl+D keyboard keys to copy down.
Do not miss copying the formula for a single account.
Copying Formula Into Cells In The RM-NET Column (Column H)
Located immediately under the column H titled RM-NET is the cell H5 which contains a formula. The formula is:
=F5+G5
This formula will gets the net total of RM-Debit amounts and RM-Credit amounts for account in cell A5.
Copy down this formula for every row which its corresponding cell in column A has an account name.
Do not miss copying the formula for a single account.
24
Under the column titled PL-DETAIL (for Profit & Loss Detail), enter the actual account names which the accounts (under column ACCOUNTS) will appear in the Profit & Loss statement. This is relevant for accounts which usually come into the Profit & Loss statement. If the accounts come into a Balance Sheet category, write NR (for Not Relevant) or use a blank instead.
Sometimes, the account name you use in the Chart of Accounts is slightly different from what you want to appear in the Profit & Loss statement. For example, in the Chart of Accounts, you use P & S, but in the Profit & Loss statement you want it to appear as Printing & Stationery.
In another example, in the Chart of accounts under the column ACCOUNTS, you have many items related to salary, e.g. Salary-Basic, Salary-Allowance, Salary-EPF etc., but under column PL-DETAIL, you want to group it all under one name ie. Salary since this is the name that will appear in the Profit & Loss statement.
Do not miss a single account.
Copying Formula Into Cells In The RM-DEBIT Column (Column F)
Located immediately under the column titled RM-DEBIT is the cell F5 which contains a formula. This formula will get the total of Debit amounts (in column H in sheet DATA) for every account name in column G (in sheet DATA) which matches the account name that appears in cell A5 (in sheet TB). The formula is:
=SUMIF(INDIRECT("DATA!G5: G30000"), A5, INDIRECT("DATA! HS: H30000"))
Be careful not to delete the formula in this cell.
Copy this formula down to every row where the corresponding cell in column A has an account name. The formula will get the net total of RM-Debit amounts for each account. Remember, you can use Ctrl+D keyboard keys to copy down.
Do not miss copying the formula for a single account.
23
Copying Formula Into Cells In The RM-CREDIT Column (Column G)
Located immediately under the column & titled BM CREDIT is the cell G5 which contains a formula. This formula will total the Credit amounts (in columin I in sheet DATA) for every account name in column I in sheet DATA) which matches the account name that appear in cell A5 (in sheet TB). The formula is :
=SUMIF (INDIRECT("DATAIIS 130000"), AS, INDIRECT("DATA! JS 330000"))
Be careful not to delete the formula in this cell.
Copy this formula down to every row where the corresponding cell in column A has an account name. The formula will get the net total of RM-Credit amounts for each account. Remember, you can use Ctrl+D keyboard keys to copy down.
Do not miss copying the formula for a single account.
Copying Formula Into Cells In The RM-NET Column (Column H)
Located immediately under the column H titled RM-NET is the cell H5 which contains a formula. The formula is:
=F5+G5
This formula will gets the net total of RM-Debit amounts and RM-Credit amounts for account in cell A5.
Copy down this formula for every row which its corresponding cell in column A has an account name.
Do not miss copying the formula for a single account.
24
Entering data in sheet TB - Column I (CHECK DUPLICATE column) page 25
Located immediately under the column titled CHECK DUPLICATE is the cell 15 which contains a formula. The formula is:
=IF(COUNTIF(INDIRECT("A5: A2000"),A5)>1, "DUPLICATE", "OK")
This formula will check the range A5:A2000 for any duplicate of the account name in cell A5. If it is a duplicate, it will return DUPLICATE, otherwise OK.
Copy down this formula for every row which the corresponding cell in column A has an account name.
Review this column for DUPLICATE item, and delete the row with a duplicate item.
Do not miss copying the formula for a single account.
25
=IF(COUNTIF(INDIRECT("A5: A2000"),A5)>1, "DUPLICATE", "OK")
This formula will check the range A5:A2000 for any duplicate of the account name in cell A5. If it is a duplicate, it will return DUPLICATE, otherwise OK.
Copy down this formula for every row which the corresponding cell in column A has an account name.
Review this column for DUPLICATE item, and delete the row with a duplicate item.
Do not miss copying the formula for a single account.
25
Sample Trial Balance (TB)
Sample
Below is a sample of part of sheet TB with data.
78.259.733.40
(78.259.733 40)
0.00 OK
TOTAL/SUBTOTAL
Checking row
ACCOUNTS
BALANCE SHEET MAIN CATEGORY
BALANCE SHEET-SUB CATEGORY
PL CATEGORY
PL DETAIL
RM-DEBIT
RM-CREDIT
RM-NET
CHECK DUPLICATE
4
0.00
ACCRUED-EPF
CURRENT LIABILI OTHER CREDI NA
NA
(125.6 10.00)
(125,610.00) OK
0.00
(12.66700)
Below is a sample of part of sheet TB with data.
| 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
Subscribe to:
Posts (Atom)