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 data to preparation of the 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 data to preparation of the 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 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 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
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
Saturday, June 25, 2011
Balance Sheet
Where to put the Balance Sheet
Use a dedicated sheet for the Balance Sheet. In this book, the sheet is named BS. This name is used in many formulas. So, if you change the name of this sheet, you must also change the names of this sheet in formulas.
A sample structure of Balance Sheet
Design the structure for the Balance Sheet as you like. However a sample structure is provided in this book.
Sample structure :
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
Use a dedicated sheet for the Balance Sheet. In this book, the sheet is named BS. This name is used in many formulas. So, if you change the name of this sheet, you must also change the names of this sheet in formulas.
A sample structure of Balance Sheet
Design the structure for the Balance Sheet as you like. However a sample structure is provided in this book.
Sample structure :
| A | B | C | D | ||
| 1 | ABC SDN BHD | ||||
| 2 | BALANCE SHEET FOR PERIOD. .... | ||||
| 3 | PARTICULARS | NOTE | YEAR TO DATE | LAST YEAR | |
| 4 | |||||
| 5 | CAPITAL | ||||
| 6 | RETAINED EARNINGS | ||||
| 7 | Grand Total | A | |||
| 8 | |||||
| 9 | Represented by : | ||||
| 10 | |||||
| 11 | FIXED ASSETS | ||||
| 12 | INVESTMENTS | ||||
| 13 | Sub-Total | B | |||
| 14 | |||||
| 15 | CURRENT ASSETS | ||||
| 16 | STOCK | ||||
| 17 | DEBTORS | ||||
| 18 | CASH IN BANK | ||||
| 19 | Sub-Total | C | |||
| 20 | |||||
| 21 | CURRENT LIABILITIES | ||||
| 22 | TRADE CREDITORS | ||||
| 23 | OTHER CREDITORS | ||||
| 24 | Sub-Total | D | |||
| 25 | |||||
| 26 | NET CURRENT ASSETS | E | |||
| 27 | |||||
| 28 | Grand Total | F | |||
| 29 |
How to fill the data in column A
How to fill data in column B
The figure in cell B7 is obtained by this formula : =B5+B6.
How to fill data in column C
The figures in column C is derived from sheet TB (Trial Balance). Use the SUMIF function to extract amounts from sheet Trial Balance into the relevant cells in the column C of the Balance Sheet.
Sample formula at cell C5 :
=-(SUMIF(INDIRECT("TB!C2:C"&ROW(LastRowTB)-1),A5,INDIRECT("TB!F2:F"&ROW(LastRowTB)-1)))
Note :
1. TB is the name of sheet that hold the Trial Balance.
2. LastRowTB is the name given to a cell that mark the last row of data area in Trial balance sheet.
3. A5 is the cell in sheet Trial Balance that contain the item same as cell C5 in sheet Balance Sheet.
Automatic update
With the use of the formulas, any change in the amounts in the Trial Balance, the figures in the Balance Sheet is automatically updated.
Does the Balance Sheet balance ?
Based on the sample structure, if amount in cell C7 is the same with the amount in cell C27 then it means the Balance Sheet is balance.
Why the Balance Sheet does not balance
The Balance Sheet may not balance if :
1. The total amounts in column RM in sheet DATA does not balance.
2. The Trial Balance does not balance.
3. The items in the Balance Sheet may be less than or more than what it should be.
4. There is error or are errors in some of the formulas that extract the amounts from the Trial Balance.
5. There is error or are errors in some of the formulas that do the totalling of the amounts.
How to fill data in column D (Comparative figure)
How to get the comparative figures ?
Write it manually. They are not many and once there they can be used for many years.
Checking for error in the Balance Sheet
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
Friday, June 24, 2011
Viewing a single ledger
Method 1 :
Use PivotTable operation.
Method 2 :
1. Make a copy of the data entry sheet.
2. Perform the following actions on the copy sheet.
2.1 Sort the data based on column Accounts, then by column Date.
2.2 Make the data in filtering mode.
2.3 Select a single Accounts.
3. You can delete the sheet because you can always rebuild easily.
Method 3 :
1. Do the following operation on the data entry sheet.
1.1 Make the data in filtering mode.
1.2 Select a single Accounts.
2. With this method, the data is not sorted, so rows will appear in its original order.
3. Don't forget to remove the Filtering mode.
Selecting the ledger for copying
If error is found in the ledger
Potential error in the ledger
Generating all ledger Accounts
Method 1 :
Use PivotTable operation.
Method 2 :
1. Make a copy of the data entry sheet.
2. Sort the data based on column Accounts, then by column Date.
3. Do Subtotalling operation.
4. You can collapse/ expand the subtotal tree.
5. You can delete the sheet because you can rebuild easily.
Advantage or disadvantage of each methods
Other analysis
Once
you familiar with using formula, PivotTable operation and Filtering
operation, you will be able to generate many analysis with ease.
Bank reconciliation
1. Design the reconciliation statement as you like.
2. Use a column in the data entry sheet to mark bank entry that has it corresponding entry in the statement provided by the bank.
3. Ummarked cell means unreconciled items.
4. Bring the unmarked item details into the reconciliation statement. (formula)
2. Use a column in the data entry sheet to mark bank entry that has it corresponding entry in the statement provided by the bank.
3. Ummarked cell means unreconciled items.
4. Bring the unmarked item details into the reconciliation statement. (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 |
Creditors reconciliation
Usually, every month our suppliers (creditors) send us a statement of accounts. The statement of accounts usually show amount we owe them and its details such as invoice number, invoice date, delivery order number and amount.
We want to compare our suppliers (creditors) items in our accounting ledger with the statements received from suppliers/creditors.
Remember, when we receive an invoice from supplier/creditor, we record the particulars in columns in 2 rows (row for debit and credit particulars). The columns are ....
When we receive statement of accounts from supplier, we want to check whether their record is same with ours.
We need a specific column to be used to compare the items in the statement with our record. Give a title to the column, may be CREDIT RECON.
Cells in the column are used to mark whether the item in the statement is also appear in our record. Use same marking throughout. For example, we can use word OK to indicate that our item also appear in the supplier statement.
If an item exist in the statement but not appear in our record, then do not mark anything, leave the cell blank.
We want to compare our suppliers (creditors) items in our accounting ledger with the statements received from suppliers/creditors.
Remember, when we receive an invoice from supplier/creditor, we record the particulars in columns in 2 rows (row for debit and credit particulars). The columns are ....
When we receive statement of accounts from supplier, we want to check whether their record is same with ours.
We need a specific column to be used to compare the items in the statement with our record. Give a title to the column, may be CREDIT RECON.
Cells in the column are used to mark whether the item in the statement is also appear in our record. Use same marking throughout. For example, we can use word OK to indicate that our item also appear in the supplier statement.
If an item exist in the statement but not appear in our record, then do not mark anything, leave the cell blank.
Wednesday, June 23, 2010
Comparative figure
There are two (2) ways to get comparative figures (usually comparative figures are previous year figures) into the current period report or statements :
1. Manually enter the relevant amounts, or,
2. Copy the relevant sheet into the file and use formula to extract the figures.
Usually comparative figures are included in the following main reports or statements of current accounting period :
1. Balance Sheet.
2. Profit & Loss statement.
1. Manually enter the relevant amounts, or,
2. Copy the relevant sheet into the file and use formula to extract the figures.
Usually comparative figures are included in the following main reports or statements of current accounting period :
1. Balance Sheet.
2. Profit & Loss statement.
How many rows are needed ?
Excel XP has 65,535 rows.
Excel later than XP has 1,036,000 rows.
Estimated, a one year (12 months) transactions require how many rows ?
Sales invoices : 5 invoices per day X 300 working days X 2 rows = 3,000 rows.
Debit/Credit Notes : 2 per month X 12 months X 2 rows = 48 rows.
Supplier invoices : 10 invoices per day X 300 working days X 2 rows = 6,000 rows.
Journals : 100 per year X 2 rows = 200 rows
Cash/bank transactions : 200 per month X 12 months X 2 rows = 4,800 rows.
Total = 14,048 rows.
So, one Excel XP sheet can easily accomodate a one whole year transactions, and occupies only about 20 % of the space.
Excel later than XP has 1,036,000 rows.
Estimated, a one year (12 months) transactions require how many rows ?
Sales invoices : 5 invoices per day X 300 working days X 2 rows = 3,000 rows.
Debit/Credit Notes : 2 per month X 12 months X 2 rows = 48 rows.
Supplier invoices : 10 invoices per day X 300 working days X 2 rows = 6,000 rows.
Journals : 100 per year X 2 rows = 200 rows
Cash/bank transactions : 200 per month X 12 months X 2 rows = 4,800 rows.
Total = 14,048 rows.
So, one Excel XP sheet can easily accomodate a one whole year transactions, and occupies only about 20 % of the space.
Statement of Accounts to Customers
Method 1 : Using copy and paste
1. Design a Statement of Accounts format in a separate sheet.
2. Make a copy of the data entry sheet.
3. Highlight the relevant data of the customer we interested.
4. Copy the relevant data and paste into the Statement of Accounts format.
Method 2 : Using formula
1. Design a Statement of Accounts format in a separate sheet.
2. Make a copy of the data entry sheet.
3. Sort the data according to Accounts and followed by Date.
3. Use formula to extract relevant data into the Statement of Accounts format.
Note :
- One customer by one customer.
Closing Cumulative Monthly Accounts
1. Initially, when we started using Excel as our system, we use one file (workbook). In this first file, we record data of day to day transactions from the beginning of the period until todate. This file is the up-to-date file, or, the most current file.
2. If we want to close the file (or the accounts) to up to a certain date (closing date) (usually up to end of a particular month), which is a date usually earlier than the current date, we must make a copy of the current file, then in the copy file we remove rows of transaction data that having date later than the closing date, so that the copy file contains only the transaction data from the beginning of the period until the closing date.
3. To remove the rows of transaction data that having date later than the closing date in the copy file, we have 2 methods :
3.1 Method 1 : Identify the relevant row one by one and delete the row one row by one row.
3.2 Method 2 : Filter the data so that we see only rows of transaction data that having date later than the closing date. Select the visible rows in one go (use menu Edit --> Go To --> Specials --> Visible Cells Only --> OK. Delete the visible rows.
4. Once the above is done, automatically the Trial Balance, Balance Sheet, Profit Loss and other analysis are updated.
5. Name or Rename the copy file appropriately.
6. Continue updating the current data in the current file, not in the copy file.
2. If we want to close the file (or the accounts) to up to a certain date (closing date) (usually up to end of a particular month), which is a date usually earlier than the current date, we must make a copy of the current file, then in the copy file we remove rows of transaction data that having date later than the closing date, so that the copy file contains only the transaction data from the beginning of the period until the closing date.
3. To remove the rows of transaction data that having date later than the closing date in the copy file, we have 2 methods :
3.1 Method 1 : Identify the relevant row one by one and delete the row one row by one row.
3.2 Method 2 : Filter the data so that we see only rows of transaction data that having date later than the closing date. Select the visible rows in one go (use menu Edit --> Go To --> Specials --> Visible Cells Only --> OK. Delete the visible rows.
4. Once the above is done, automatically the Trial Balance, Balance Sheet, Profit Loss and other analysis are updated.
5. Name or Rename the copy file appropriately.
6. Continue updating the current data in the current file, not in the copy file.
Closing The Current Accounting Period Accounts
Closing the current accounting period accounts means : Making sure the transaction data consist only the transactions for the current accounting period - remove data irrelevant to the current accounting period and make sure only data related to current accounting period are taken in.
Please note that the term Accounting period is used instead of Accounting Year because an accounting period may span over two years eg from 1 July 2020 to 30 June 2021.
Once the data in sheet DATA conform to the above, automatically the Trial Balance, Balance Sheet, Profit Loss and other sheets are updated.
Make a copy of the current period file. So, after the copying, there are 2 files containing the same transaction data. First, the current period file, and secondly the file for the next accounting period.
Example name of current accounting period file is accjandec2020.xls, and example name of next accounting period is accjandec2021.xls. It is easier to find the files if they are saved into the same folder.
It is a good practice if you make a copy of the current file only after you decide to close the current file. Close current accounting period file means you will not add or delete anymore row to the file.
Any changes to the current accounting period data is done only before auditor adjustment.
After the copying, the current accounting period file will still contain data for the next accounting period file. Those data meant for the next accounting period need to be removed (ie the relevant rows need to be deleted), leaving only data for the current accounting period. So, your Trial Balance, Balance Sheet, Profit & Loss statements are closed.
After the copying, the next accounting period file will still contain data for the current accounting period. Those data meant for the current accounting period need to be removed (ie the relevant rows need to be deleted), leaving only data for the next accounting period. So, your Trial Balance, Balance Sheet, Profit & Loss statements are closed.
How to remove the data in the current accounting period with date outside the accounting period?
Identify rows in column DATE which contain the irrelevant date.
You can identify one by one.
You can use filtering method.
After the copying, if you make adjustments or addition or deletion to the current accounting period, it will affect the figures brought forward to the next accounting period, so make sure that the affected accounts in the next accounting period file is adjusted accordingly.
After the copying, if the auditor make adjustments or addition or deletion to the current accounting period, it will affect the figures brought forward to the next accounting period, so make sure that the affected accounts in the next accounting period file is adjusted accordingly. So auditor adjustments should be written to both files ie current accounting period file and next accounting period file.
Please note that the term Accounting period is used instead of Accounting Year because an accounting period may span over two years eg from 1 July 2020 to 30 June 2021.
Once the data in sheet DATA conform to the above, automatically the Trial Balance, Balance Sheet, Profit Loss and other sheets are updated.
Make a copy of the current period file. So, after the copying, there are 2 files containing the same transaction data. First, the current period file, and secondly the file for the next accounting period.
Example name of current accounting period file is accjandec2020.xls, and example name of next accounting period is accjandec2021.xls. It is easier to find the files if they are saved into the same folder.
It is a good practice if you make a copy of the current file only after you decide to close the current file. Close current accounting period file means you will not add or delete anymore row to the file.
Any changes to the current accounting period data is done only before auditor adjustment.
After the copying, the current accounting period file will still contain data for the next accounting period file. Those data meant for the next accounting period need to be removed (ie the relevant rows need to be deleted), leaving only data for the current accounting period. So, your Trial Balance, Balance Sheet, Profit & Loss statements are closed.
After the copying, the next accounting period file will still contain data for the current accounting period. Those data meant for the current accounting period need to be removed (ie the relevant rows need to be deleted), leaving only data for the next accounting period. So, your Trial Balance, Balance Sheet, Profit & Loss statements are closed.
How to remove the data in the current accounting period with date outside the accounting period?
Identify rows in column DATE which contain the irrelevant date.
You can identify one by one.
You can use filtering method.
After the copying, if you make adjustments or addition or deletion to the current accounting period, it will affect the figures brought forward to the next accounting period, so make sure that the affected accounts in the next accounting period file is adjusted accordingly.
After the copying, if the auditor make adjustments or addition or deletion to the current accounting period, it will affect the figures brought forward to the next accounting period, so make sure that the affected accounts in the next accounting period file is adjusted accordingly. So auditor adjustments should be written to both files ie current accounting period file and next accounting period file.
Friday, July 24, 2009
This system and its suitability
THIS SYSTEM AND ITS SUITABILITY
Factors to consider : Size of business.
This file is suitable for small and medium size businesses. As long as the number of rows to be used does not exceed 65,536 in a year, you can use it. The type of industry is also not important – it can be used in trading company, manufacturing, services etc.
Will you reach 65,536 rows in a year : Very unlikely.
How many rows will you use in a year : Estimation using Simple calculation :
500 sales invoice per month X 12 months = 6,000 rows 500 suppliers invoice per month X 12 months = 6,000 rows 500 receipts + payments per month X 12 months = 6,000 rows 50 petty cash receipts + payments per month X 12 months = 600 rows Opening balances individual customer accounts = 200 rows Opening balances individual suppliers accounts = 200 rows Opening balances individual fixed assets categories = 10 rows Opening balances individual other accounts = 100 rows Salary journals : 20 items X 12 months = 240 rows Other journals items : 20 items X 12 months = 240 rows Contingency rows = 10,000 rows TOTAL = 29,590 rows.
Most likely, one file will be more than enough to accommodate one year transactions.
Factors to consider : Size of business.
This file is suitable for small and medium size businesses. As long as the number of rows to be used does not exceed 65,536 in a year, you can use it. The type of industry is also not important – it can be used in trading company, manufacturing, services etc.
Will you reach 65,536 rows in a year : Very unlikely.
How many rows will you use in a year : Estimation using Simple calculation :
500 sales invoice per month X 12 months = 6,000 rows 500 suppliers invoice per month X 12 months = 6,000 rows 500 receipts + payments per month X 12 months = 6,000 rows 50 petty cash receipts + payments per month X 12 months = 600 rows Opening balances individual customer accounts = 200 rows Opening balances individual suppliers accounts = 200 rows Opening balances individual fixed assets categories = 10 rows Opening balances individual other accounts = 100 rows Salary journals : 20 items X 12 months = 240 rows Other journals items : 20 items X 12 months = 240 rows Contingency rows = 10,000 rows TOTAL = 29,590 rows.
Most likely, one file will be more than enough to accommodate one year transactions.
Tuesday, June 23, 2009
One Workbook for one accounting period data
1. Use one workbook (file) for only one accounting period. For example, file name AccJanDec2011.xls contains data for acounting period 1 January to 31 December 2011.
2. Why ? (1) Too many data and formulas will slow down calculations. (2) Better files organization.
3. Though usually one accounting period (12 months) equal to one accounting year (12 months), but in some cases, one accounting period may span over two years eg 1 July 2010 to 30 June 2011 (12 months).
2. Why ? (1) Too many data and formulas will slow down calculations. (2) Better files organization.
3. Though usually one accounting period (12 months) equal to one accounting year (12 months), but in some cases, one accounting period may span over two years eg 1 July 2010 to 30 June 2011 (12 months).
Monday, June 22, 2009
Printing
PRINTING
Printing of transaction data and reports can be done as normally. This chapter will only show how to print all ledger balances at one go. We know that in sheet ‘Ledger’, we can get a single account details and balance at a time, thus printing all ledger balances will take a long time and require more work and hundreds of clicks of mouse. If we can combine all ledger details into one sheet, we can print them by just one printer setting and only a few mouse clicks. This is the aim of this chapter, ie to show how to combine all ledger balances into a separate sheet so that we can print them all at once rather than one by one. This is useful for printing for the hard copy after the year end.
Follow these steps :
1. Create a new sheet (Use menu ‘Insert/ Worksheet’).
2. Leave its name at default or you can give it a different name.
3. Copy data titles in column A to column F in sheet ‘Data’ to the new sheet .You can copy to cell A1. The titles are SRC, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE. Add 2 new titles ie ACCOUNT and RM in column G and H respectively.
4. Copy data in column A to column F in sheet ‘Data’ to the new sheet in immediately below the data titles. The titles are SRC, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE.
5. Copy the same data in column A to column F in sheet ‘Data’ to the new sheet in immediately below the data already copied in step 4 above. (we are actually make a copy twice, one below the other).
6. Copy data in column G (ACCOUNT-DEBIT) and H (RM-DEBIT) in sheet ‘Data’ to the new sheet immediately below the data titles ACCOUNT and RM respectively.
7. Copy data in column I (ACCOUNT-CREDIt) and J (RM-CREDIT) in sheet ‘Data’ to the new sheet immediately below the data already copied in step 6 above.
8. Sort the data according to data title ACCOUNT then DATE. (Use menu ‘Data/ Sort’. A dialog with title ‘Sort’ appears. In the dialog, under ‘Sort by’ label, select ‘ACCOUNT’, and under ‘Then by’ label, select ‘DATE’. Click OK).
9. Get the total of each accounts. (Use menu ‘Data/ Subtotals’. A dialog with title ‘Subtotal’ will appear. Under the label ‘At each change in ;’, select ‘ACCOUNT’. Under the label ‘Use function’, select ‘Sum’. Under the label ‘Add Subtotal to’, select only ‘RM’. Make sure ‘Summary below data’ box is checked. Do not check the ‘Page Break between groups’ box so that Excel will make it print continuously, otherwise it will print one account per page. Click OK. Excel may take a few seconds to produce the result).
10. The above step 9 will produce the accounts balances and its details. The data is in ‘Subtotal-mode’. You should see 3 buttons on the upper-left side of the data with label 1, 2, 3 respectively. These buttons are useful to contract and expand the data according to the subtotals.
11. You should see also that the text in ‘total’ lines is in bold and with word ‘Total’ in addition to the account names. You may want to format these total lines further. You can use menu ‘Format/ Conditional formatting’. Let’s say you want to colour the ‘total’ figures with light yellow, so follow these steps : Select all data in column RM because the total figure is in this column. Click ‘Format/ Conditional formatting’. A dialog with title ‘Conditional Formatting’ will appear. In a box under label ‘Condition 1 is’, select ‘Formula is’. Then on the box on its right, write a formula, say : =RIGHT(G1,5)=”Total”. Click ‘Format…’ button. Click ‘Pattern’ tab. Select a colour (Light Yellow). Click ‘OK’. Click ‘OK’ again. You should find that the cells with total figures are now in light yellow.
12. Now you can print the ledger as usual (Use menu ‘File/ Print’).
Printing of transaction data and reports can be done as normally. This chapter will only show how to print all ledger balances at one go. We know that in sheet ‘Ledger’, we can get a single account details and balance at a time, thus printing all ledger balances will take a long time and require more work and hundreds of clicks of mouse. If we can combine all ledger details into one sheet, we can print them by just one printer setting and only a few mouse clicks. This is the aim of this chapter, ie to show how to combine all ledger balances into a separate sheet so that we can print them all at once rather than one by one. This is useful for printing for the hard copy after the year end.
Follow these steps :
1. Create a new sheet (Use menu ‘Insert/ Worksheet’).
2. Leave its name at default or you can give it a different name.
3. Copy data titles in column A to column F in sheet ‘Data’ to the new sheet .You can copy to cell A1. The titles are SRC, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE. Add 2 new titles ie ACCOUNT and RM in column G and H respectively.
4. Copy data in column A to column F in sheet ‘Data’ to the new sheet in immediately below the data titles. The titles are SRC, DATE, MTH, SRC NO., OTHER REF./ NO., NOTE.
5. Copy the same data in column A to column F in sheet ‘Data’ to the new sheet in immediately below the data already copied in step 4 above. (we are actually make a copy twice, one below the other).
6. Copy data in column G (ACCOUNT-DEBIT) and H (RM-DEBIT) in sheet ‘Data’ to the new sheet immediately below the data titles ACCOUNT and RM respectively.
7. Copy data in column I (ACCOUNT-CREDIt) and J (RM-CREDIT) in sheet ‘Data’ to the new sheet immediately below the data already copied in step 6 above.
8. Sort the data according to data title ACCOUNT then DATE. (Use menu ‘Data/ Sort’. A dialog with title ‘Sort’ appears. In the dialog, under ‘Sort by’ label, select ‘ACCOUNT’, and under ‘Then by’ label, select ‘DATE’. Click OK).
9. Get the total of each accounts. (Use menu ‘Data/ Subtotals’. A dialog with title ‘Subtotal’ will appear. Under the label ‘At each change in ;’, select ‘ACCOUNT’. Under the label ‘Use function’, select ‘Sum’. Under the label ‘Add Subtotal to’, select only ‘RM’. Make sure ‘Summary below data’ box is checked. Do not check the ‘Page Break between groups’ box so that Excel will make it print continuously, otherwise it will print one account per page. Click OK. Excel may take a few seconds to produce the result).
10. The above step 9 will produce the accounts balances and its details. The data is in ‘Subtotal-mode’. You should see 3 buttons on the upper-left side of the data with label 1, 2, 3 respectively. These buttons are useful to contract and expand the data according to the subtotals.
11. You should see also that the text in ‘total’ lines is in bold and with word ‘Total’ in addition to the account names. You may want to format these total lines further. You can use menu ‘Format/ Conditional formatting’. Let’s say you want to colour the ‘total’ figures with light yellow, so follow these steps : Select all data in column RM because the total figure is in this column. Click ‘Format/ Conditional formatting’. A dialog with title ‘Conditional Formatting’ will appear. In a box under label ‘Condition 1 is’, select ‘Formula is’. Then on the box on its right, write a formula, say : =RIGHT(G1,5)=”Total”. Click ‘Format…’ button. Click ‘Pattern’ tab. Select a colour (Light Yellow). Click ‘OK’. Click ‘OK’ again. You should find that the cells with total figures are now in light yellow.
12. Now you can print the ledger as usual (Use menu ‘File/ Print’).
Saturday, June 20, 2009
What are good things
1. Portable (because of its small in size).
2. Networking-enabled.
3. No need to buy a separate software for accounting.
∙4. Safe cost in the long run.
∙5. Easy trouble-shooting.
∙6. Web-enabled.
∙7. Potential for working from home.
8. Can shere with head of department.
2. Networking-enabled.
3. No need to buy a separate software for accounting.
∙4. Safe cost in the long run.
∙5. Easy trouble-shooting.
∙6. Web-enabled.
∙7. Potential for working from home.
8. Can shere with head of department.
Saturday, January 24, 2009
Potential problems
POTENTIAL PROBLEMS
Excel may get slower
You may find Excel is getting slower in its reaction if you have entered data in thousands of rows, say more than 15,000 rows. You can notice that it is getting slower that when you press 'Enter' key, the mouse cursor does not move to another cell immediately, but takes more than 1 second. You may also notice that the status bar shows that Excel is still doing some calculation for 2 or 3 seconds. Some people can tolerate delay of 3 or 4 seconds. The delay is caused by so many calculations Excel has to make since it contain so many data which affect the recalculations of formulas.
If you can't stand the little delay, you can avoid it by setting the calculation mode to 'manual'. Steps :
1. Click menu 'Tools/Option'.
2. A dialog box with title 'Options' will appear.
3. Click on 'Calculation' tab.
4. Select 'Manual'.
5. Click 'OK'.
From this point onwards, Excel will not do the calculation automatically. It means, all formulas will not get calculated or updated.
To force calculation of all formulas after you make the above setting , press key 'F9' on the keyboard.
Another way to force calculation when it is set as 'manual' is by using ‘Calculate Now’ button. Follow this step :
1. Click menu 'View/ Toolbars/ Customize'.
2. A dialog box with title 'Customize' will appear.
3. Activate the 'Commands' tab.
4. Under 'Categories' list, select 'Tools'.
5. Under 'Commands' list, find 'Calculate now' button.
6. Drag the 'Calculate now' button into the toolbar area and release at the location you want.
7. The button will stay there (you can remove it later by dragging back in to the dialog box).
8. Click OK.
9. From now on, you can click on that button to force formulas calculations.
This manual calculation affects all other Excel files, not just the file which you set the mode. If you share your computer with others, make sure you reset the calculation to ‘automatic’ calculation, unless you know that they know about ‘manual’ calculation.
Excel may get slower
You may find Excel is getting slower in its reaction if you have entered data in thousands of rows, say more than 15,000 rows. You can notice that it is getting slower that when you press 'Enter' key, the mouse cursor does not move to another cell immediately, but takes more than 1 second. You may also notice that the status bar shows that Excel is still doing some calculation for 2 or 3 seconds. Some people can tolerate delay of 3 or 4 seconds. The delay is caused by so many calculations Excel has to make since it contain so many data which affect the recalculations of formulas.
If you can't stand the little delay, you can avoid it by setting the calculation mode to 'manual'. Steps :
1. Click menu 'Tools/Option'.
2. A dialog box with title 'Options' will appear.
3. Click on 'Calculation' tab.
4. Select 'Manual'.
5. Click 'OK'.
From this point onwards, Excel will not do the calculation automatically. It means, all formulas will not get calculated or updated.
To force calculation of all formulas after you make the above setting , press key 'F9' on the keyboard.
Another way to force calculation when it is set as 'manual' is by using ‘Calculate Now’ button. Follow this step :
1. Click menu 'View/ Toolbars/ Customize'.
2. A dialog box with title 'Customize' will appear.
3. Activate the 'Commands' tab.
4. Under 'Categories' list, select 'Tools'.
5. Under 'Commands' list, find 'Calculate now' button.
6. Drag the 'Calculate now' button into the toolbar area and release at the location you want.
7. The button will stay there (you can remove it later by dragging back in to the dialog box).
8. Click OK.
9. From now on, you can click on that button to force formulas calculations.
This manual calculation affects all other Excel files, not just the file which you set the mode. If you share your computer with others, make sure you reset the calculation to ‘automatic’ calculation, unless you know that they know about ‘manual’ calculation.
Friday, January 23, 2009
Who can use
Who can use
Someone with basic book-keeping knowledge and good familiarity with Microsoft Excel.
Someone with basic book-keeping knowledge and good familiarity with Microsoft Excel.
Thursday, January 22, 2009
What are lacking ?
What are lacking ?
This system is not :
∙ an integrated invoicing system with ledger.
∙ an integrated fixed assets system with ledger.
∙ an integrated investment system with ledger.
∙ an integrated stock system with ledger.
∙ an integrated payroll system with ledger.
Those above can be overcome through use of Excel-VBA programming.
This system is not :
∙ an integrated invoicing system with ledger.
∙ an integrated fixed assets system with ledger.
∙ an integrated investment system with ledger.
∙ an integrated stock system with ledger.
∙ an integrated payroll system with ledger.
Those above can be overcome through use of Excel-VBA programming.
Wednesday, January 21, 2009
Does this system work in other spreadsheet ?
Does this system work in other spreadsheet ? Other than Microsoft Excel, there are other spreadsheets under other Office suites, such as Lotus123, OpenOffice, StarOffice, Microsoft Works, etc. The formulas and functions in this book have not been tested in those spreadsheets. However, most probably, you can use this method on other spreadsheet, as long as the other spreadsheet has the formulas, PivotTable operation and Filtering operation capabilities similar to Microsoft Excel. May be there are small variations on how the functions and formula should be constructed or difference in syntax. Most spreadsheets have functions similar to AutoFilter and PivotTable. You can check by opening Excel files using those spreadsheets applications and see whether the formulas are still intact and get the expected results. If not, then the application is not fully compatible with Microsoft Excel.
Tuesday, January 20, 2009
Advance Filter Primer
ADVANCE FILTER PRIMER
We use AdvanceFilter function when we want to filter our data in data area (database) based on more than one column titles. If we want to filter our data in data area (database) based on one column title only, we use AutoFilter.
We can access the AdvanceFilter function by clicking menu 'Data/Filter/AdvanceFilter'.
Before we use this function, we must have data in a proper data area (database). A data area is a rectangular area of adjacent cells boundaried by a row on top, a row at the bottom, a column in the left and a column in the right. A data area is proper and ready for use by the function if it has the following characteristics :
The cells in the first row of the data area must be used as data titles. Cells used as data titles must not have a blank cell between each other. (You can start data area from any row, not necessarily the first row of the sheet).
Actual data must appear in rows below the data titles row.
There must not be a row which all its cells are blank in each row below the data titles. (You must have at least one cell in a row which is not blank. Other cells can be blank in that row). If there is a row which all its cells are blank, this row is considered as the last row of the data area.
When we want to use AdvanceFilter, we need to identify what columns titles (or data titles) we want to use as basis of filtering. We identify them by writing the relevant data titles in another area (preferably in other sheet). In that other sheet, we write the data titles in a separate cell in a row (any row) and must be adjacent to each other. In cells below each titles, write the item that we want to see as a result of the filtering. (It could be more than one cell filled with item under each title). The item must be one that is actually available in cells below that title in the actual data area.
The area where we write the selected data titles and the selected items below it is called a 'CRITERIA'.
So, a simple Criteria looks like this :
A B C 1 Title A Title B Title C 2 xxxx Yyyy aaaa 3 bbbb kk 4 hhh
If we fill the Criteria that look like this :
A B 1 Title A Title B 2 xxxx Yyyy 3 4
This read we want to see rows where each row has item 'xxx' below a data title A AND has item 'yyyy' below data title B. Only rows having these 2 items will get selected, the rest will be hidden.
If we fill the Criteria like this :
A B 1 Title A Title B 2 xxxx 3 4
This means we want to see rows where each row has item 'xxx' below a data title A AND has any other item under data title B. Only rows having item 'xxx' under title A will get selected (Data under title B can consist anything). The rest will be hidden.
If we fill the Criteria like this :
A B 1 Title A Title B 2 Xxx Yyyy 3 Aa 4
This means we want to see rows where each row has item 'xxx' below a data title A AND has item 'yyyy' below data title B AND ALSO each row which has item 'aa' below a data title A AND has any other item below data title B . Only rows satisfying these conditions will get selected. The rest will be hidden.
If we fill the Criteria like this :
A B 1 Title A Title B 2 xxxx 3 aa Yyyy 4
This means we want to see rows where each row has item 'xxx' below a data title A AND has any item below data title B AND ALSO each row which has item 'aa' below a data title A AND has item 'yyyy' below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden. If we fill the Criteria like this :
A B 1 Title A Title B 2 Xxx 3 aa Yyyy 4
This means we want to see rows where each row has item 'xxx' below a data title B AND has any item below data title A AND ALSO each row which has item 'aa' below a data title A AND has item 'yyyy' below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.
If we fill the Criteria like this :
A B 1 Title A Title B 2 xxxx 3 Yyyy 4
This means we want to see rows where each row has item 'xxx' below a data title A AND has any item below data title B AND ALSO rows which each row which has item 'yyyy' below a data title B AND has any item below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.
By now, you would see that a blank cell in the ‘Criteria’ block represents ANY. A row in the Criteria represents a row in the actual data area.
After you set the Criteria then you can start using the menu 'Data/Filter/AdvanceFilter'.
You can set more than one Criteria in one sheet to serve different filtering.
After the Criteria is ready, activate the sheet which contain the data you want to filter. Click menu 'Data/ Filter/ AdvanceFilter'. You will see a dialog with title 'Advance Filter'.On the right of 'List range' label is your data area automatically identified by Excel. (Check this range. If wrong, click cancel and correct you data setting to follow the rules, and start again). Click on the 'Criteria range' box. Click at the sheet where the criteria that you want to use reside. Select the criteria range. Automatically the 'Criteria range' box will be filled with the address of your selection area (criteria range). Click OK. Your data in the data area will get filtered accordingly (You see only data that you want to see).
Summary
To filter data based on one item in one column, use AutoFilter operation. To filter data based on more than one item from one column, use 'Custom' in the AutoFilter menu. To filter data based on more than one item from more than one column, use AdvanceFilter operation.
We use AdvanceFilter function when we want to filter our data in data area (database) based on more than one column titles. If we want to filter our data in data area (database) based on one column title only, we use AutoFilter.
We can access the AdvanceFilter function by clicking menu 'Data/Filter/AdvanceFilter'.
Before we use this function, we must have data in a proper data area (database). A data area is a rectangular area of adjacent cells boundaried by a row on top, a row at the bottom, a column in the left and a column in the right. A data area is proper and ready for use by the function if it has the following characteristics :
The cells in the first row of the data area must be used as data titles. Cells used as data titles must not have a blank cell between each other. (You can start data area from any row, not necessarily the first row of the sheet).
Actual data must appear in rows below the data titles row.
There must not be a row which all its cells are blank in each row below the data titles. (You must have at least one cell in a row which is not blank. Other cells can be blank in that row). If there is a row which all its cells are blank, this row is considered as the last row of the data area.
When we want to use AdvanceFilter, we need to identify what columns titles (or data titles) we want to use as basis of filtering. We identify them by writing the relevant data titles in another area (preferably in other sheet). In that other sheet, we write the data titles in a separate cell in a row (any row) and must be adjacent to each other. In cells below each titles, write the item that we want to see as a result of the filtering. (It could be more than one cell filled with item under each title). The item must be one that is actually available in cells below that title in the actual data area.
The area where we write the selected data titles and the selected items below it is called a 'CRITERIA'.
So, a simple Criteria looks like this :
A B C 1 Title A Title B Title C 2 xxxx Yyyy aaaa 3 bbbb kk 4 hhh
If we fill the Criteria that look like this :
A B 1 Title A Title B 2 xxxx Yyyy 3 4
This read we want to see rows where each row has item 'xxx' below a data title A AND has item 'yyyy' below data title B. Only rows having these 2 items will get selected, the rest will be hidden.
If we fill the Criteria like this :
A B 1 Title A Title B 2 xxxx 3 4
This means we want to see rows where each row has item 'xxx' below a data title A AND has any other item under data title B. Only rows having item 'xxx' under title A will get selected (Data under title B can consist anything). The rest will be hidden.
If we fill the Criteria like this :
A B 1 Title A Title B 2 Xxx Yyyy 3 Aa 4
This means we want to see rows where each row has item 'xxx' below a data title A AND has item 'yyyy' below data title B AND ALSO each row which has item 'aa' below a data title A AND has any other item below data title B . Only rows satisfying these conditions will get selected. The rest will be hidden.
If we fill the Criteria like this :
A B 1 Title A Title B 2 xxxx 3 aa Yyyy 4
This means we want to see rows where each row has item 'xxx' below a data title A AND has any item below data title B AND ALSO each row which has item 'aa' below a data title A AND has item 'yyyy' below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden. If we fill the Criteria like this :
A B 1 Title A Title B 2 Xxx 3 aa Yyyy 4
This means we want to see rows where each row has item 'xxx' below a data title B AND has any item below data title A AND ALSO each row which has item 'aa' below a data title A AND has item 'yyyy' below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.
If we fill the Criteria like this :
A B 1 Title A Title B 2 xxxx 3 Yyyy 4
This means we want to see rows where each row has item 'xxx' below a data title A AND has any item below data title B AND ALSO rows which each row which has item 'yyyy' below a data title B AND has any item below data title B. Only rows satisfying these conditions will get selected. The rest will be hidden.
By now, you would see that a blank cell in the ‘Criteria’ block represents ANY. A row in the Criteria represents a row in the actual data area.
After you set the Criteria then you can start using the menu 'Data/Filter/AdvanceFilter'.
You can set more than one Criteria in one sheet to serve different filtering.
After the Criteria is ready, activate the sheet which contain the data you want to filter. Click menu 'Data/ Filter/ AdvanceFilter'. You will see a dialog with title 'Advance Filter'.On the right of 'List range' label is your data area automatically identified by Excel. (Check this range. If wrong, click cancel and correct you data setting to follow the rules, and start again). Click on the 'Criteria range' box. Click at the sheet where the criteria that you want to use reside. Select the criteria range. Automatically the 'Criteria range' box will be filled with the address of your selection area (criteria range). Click OK. Your data in the data area will get filtered accordingly (You see only data that you want to see).
Summary
To filter data based on one item in one column, use AutoFilter operation. To filter data based on more than one item from one column, use 'Custom' in the AutoFilter menu. To filter data based on more than one item from more than one column, use AdvanceFilter operation.
Subscribe to:
Posts (Atom)