tag:blogger.com,1999:blog-80369247175939017942026-11-10T17:46:27.850+08:00Complete Accounting With Microsoft ExcelI show you how to use Microsoft Excel as complete Accounting system.Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.comBlogger211125tag:blogger.com,1999:blog-8036924717593901794.post-81513991868950390692026-11-08T15:56:15.970+08:002026-11-08T16:01:30.752+08:00Deleted NotesEntering Data In The ITEM PURCHASED Column (Column W)
In the ITEM PURCHASE column, write the names of the items we purchase. Usually this is stated in the supplier's invoice.
If the document is not related to purchase of an item, just write NR (for Not Relevant).
This column will help us to monitor the items we purchase.
Entering Data In The HOLD Column (Column T)
In the HOLD column, Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-34586080634255785032026-07-25T18:12:12.702+08:002026-07-25T18:12:12.703+08:00Not completed yetThis blog is not completed yetMael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-90939694302772834692026-07-24T15:20:00.000+08:002026-10-23T17:35:19.915+08:001. Front coverBOOK-KEEPING WITH MICROSOFT EXCEL
You can use Microsoft Excel for all your book-keeping tasks, from recording raw data to preparation of Trial Balance, Balance Sheet, Profit & Loss statement, and other reports. This book shows you how.
ISMAIL HASHIM
Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-68636410187378324902024-07-30T14:50:00.000+08:002026-10-23T18:03:59.518+08:002. IntroductionIntroduction
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.
In this book, the book-keepng system is based on Microsoft Excel 2000. Later Excels should be able to open its file.
Microsoft Excel has long Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0Taman Sri Muda, Shah Alam, Selangor, Malaysia3.0279061 101.533330699999963.0158871 101.52058619999997 3.0399251 101.54607519999996tag:blogger.com,1999:blog-8036924717593901794.post-34576653495269522242024-07-25T11:00:00.000+08:002026-10-23T18:01:13.402+08:003. Some Book-Keeping Functions That Can Be Done Using Microsoft ExcelSome 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 accounting raw data.
To create the Trial Balance statement and automate updating it.
To create the Balance Sheet statement and automate updating it.
To create the Profit & Loss statement Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-31039712677861116652024-07-20T08:40:00.000+08:002026-11-05T07:33:42.057+08:004. Requirements for smooth implementationRequirements
What are required to successfully implement book-keeping with Microsoft Excel ?
1. Initially, an empty Microsoft Excel file (Excel 95, 97, 2000, XP or later).
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.
Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-49975088819429266102011-07-29T12:15:00.000+08:002026-10-23T18:09:06.345+08:005. Why use Microsoft Excel for accountingReasons :
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 Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-60644231755300058732011-07-28T19:50:00.000+08:002026-10-24T07:23:23.753+08:006a.Table of ContentTABLE 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’
Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-8540916560076827152011-07-28T10:00:00.000+08:002026-10-24T07:23:48.489+08:006b. Table of Contents 1
Table of Contents
1. Introduction 1
Book-Keeping Functions That Can Be Done Using Microsoft Excel
Requirements to successfully use Excel for book-keeping
2. Building The Structure
Step 1 - Create A Folder (Directory)
Step 2- Create And Set A File (Workbook)
Step 3 - Set The Worksheets In The File
Step 4 - Set The Structure In Sheet DATA
Step 5 - Set The Structure In Sheet TB (Trial Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-91072680247991829102011-07-28T09:00:00.000+08:002026-10-24T07:27:26.078+08:006c. Table of Contents 213. Filter Data In Sheet DATA
Using Simple AutoFilter
Using Double AutoFilter
Using Custom AutoFilter
Using Advanced Filter
Filtering On Your Own
Remove Filtering After It Is Done
14. Filter Data In Sheet TB
15. Searching For Data
Introduction
Searching For Dates
Searching For Numbers
16. Replace Data
Replacing Data
17. Sorting Data
How To Sort Data
Sorting Data In The Sheet Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-56481994561773818852011-07-15T16:05:00.000+08:002026-10-24T07:28:41.017+08:007. 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 Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-52020218896346268682011-07-15T16:00:00.000+08:002026-10-24T07:39:23.009+08:008. 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 or later). 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 nameMael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-33129130079322554872011-06-29T23:40:00.000+08:002026-10-24T11:37:43.517+08:009. Initial Sheet in the file (workbook)When you firstly open a new file (or workbook), initially, there is only one sheet in the new file that you have just created.
In Excel, sheet is also called worksheet.
Rename the new 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 Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-50518583802891253512011-06-28T22:00:00.000+08:002026-11-05T18:33:10.742+08:0010. Structure in sheet 'Data'Sample structure
ABCDEF
123456
1SRCDATEMTHSRC. NO.2ND SRC. NO.NOTE
2
3
4
5
6
7
8
9Total/ Subtotal
10Checking
11Cut-off Date(Date)
12Last Row DATA(Formula)
GHI
789
1FC DR/(CR)RATECONVERTED
2(formula)
3
4
5
6
7
8
9
10
11
12
JKLMN
1011121314
1ACCOUNTSCheck AccountsAmountsCum. AmountsBank Recon.
2(Formula)(formula)
3
4
5
6
7
8
9(formula)
10
11
12
OPQRS
1516171819
1Sales Inv. Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0Taman Sri Muda, Shah Alam, Selangor, Malaysia3.0279061 101.533330699999963.0158871 101.52058619999997 3.0399251 101.54607519999996tag:blogger.com,1999:blog-8036924717593901794.post-78550125319089288892011-06-28T18:30:00.000+08:002026-11-05T18:32:52.041+08:0011. Explanation for the structure in sheet Data row by row 1Explanation for the sample structure row by row
Where to enter raw 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.
table { border-collapse: collapse; }
table, th, tr, td { border: 1px solid black; }
1Row 1
Cells in row 1 from column A to Z are to contain headings. More explanation on each Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-79181458761287085132011-06-28T17:30:00.000+08:002026-10-28T17:25:30.648+08:0012. More explanation for structure in sheet Data row by row 2Column H
In cell H9, write this formula:
SUBTOTAL (9, INDIRECT("J2:J"&Row(LastRowData)))
This formula calculates the total of amounts in cells in column J (from row 2 to row obtained by Row(LastRowData) formula.
Column T
In cell T1, write this formula:
=SUBTOTAL (9, INDIRECT("T2:T30000"))
This formula calculates the total of amounts in the cells in column T (from row 2 to row Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-74872094304763434322011-06-28T17:20:00.000+08:002026-10-28T18:05:50.504+08:0013. More explanation for structure in sheet Data row by row 3Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-41747392380984283312011-06-28T17:00:00.000+08:002026-11-05T15:55:56.167+08:0014. More explanation for structure in sheet Data column by column 1Later 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.Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-53962205736226604472011-06-28T16:26:00.000+08:002026-11-09T07:39:14.282+08:0015. More explanation for structure in sheet Data column by column 2
Column L
In cell L2, write this formula:
=IF(ISERROR(VLOOKUP(I2, INDIRECT("TB!A2: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. Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-63828567536038930892011-06-28T16:25:00.000+08:002026-11-09T09:28:18.208+08:0016. A Transaction give rise to Double entryDouble entry is a term exist in book-keeping system.
It arises when a financial transaction occur.
Why double ? Because, in a transaction, it involve at least 2 accounts or double effect ie increase and increase, increase and decrease or decrease and decrease.
For example, in a transaction of payment by cash to buy stationery, the effect is increase in stationery accounts and decrease in Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-5290762561485190222011-06-28T16:19:00.000+08:002026-11-10T12:05:17.928+08:00* Effect of a transaction to the number of rowsEffect of a transaction to the number of rows
A financial transaction may affect two or more accounts.
For example, a cash payment to buy stationery, may affect cash in hand (reduce) and stationery account (increase). So, at minimum, a transaction affects two accounts.
Since this system requires that a cell in a row can contain only one account (in column J), this means another cell in Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-26697504151006771312011-06-28T16:14:00.000+08:002026-11-10T17:46:27.849+08:00Sample of double entry for transactionsWhat are the double entry for each transaction ?
1. Opening Balances
The Opening balances do not involve double entry for the current year.
2. Audit Adjustment (previous accounting year)
The double entry for the auditor adjustments are as given by the auditors.
3. Provisions for expenses
If the transaction is for example, provision for office rental, the double entry is :
Debit : Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-22556869156550840982011-06-28T16:13:00.000+08:002026-11-10T12:02:50.452+08:00Transaction's particulars to be recordedWhat are the particulars we want to record for each transaction ?
It depends on the source.
1. Opening Balances
If the source is Opening Balance, then we want to record these particulars :
Source(A), Date(B), Month(C), ACCOUNTS(J), CHECK ACCOUNTS(K), AMOUNTS(L), CUMULATIVE AMOUNTS(M).
2. Audit Adjustment (previous accounting year)
If the source is Audit Djustment (end of previous Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-750337646365795512011-06-28T16:10:00.000+08:002026-10-30T19:39:09.728+08:00* Columns formatColumn 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 withMael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-24071673891924587952011-06-28T15:50:00.000+08:002026-11-01T12:56:01.718+08:00* Important to identify borders of Data AreaNo Blank Cells In SRC Column (Column A)
Make sure there are no blank cells in the cells below the column title SRC, between the rows of data. This column will serve as the left-side border of the data area. If there is a blank cell under the column title SRC, Excel will most likely use the blank cell to segregate the data area. This will affect the results of data manipulation using Filtering Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0