tag:blogger.com,1999:blog-80369247175939017942024-03-09T07:46:10.113+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.comBlogger35125tag:blogger.com,1999:blog-8036924717593901794.post-87871862620985048822012-08-01T15:52:00.001+08:002012-08-01T15:52:40.317+08:00Temporary Notes
Sample structure
ABCDEFGHIJ
1SRCDATEMTHSRC. NO.2ND SRC. NO.Note 1Note 2Note 3Note 4Note 5
2
3
4
5
6
7
8
9Subtotal
10Checking
11Cut-off Date(date)
12LastRowDATA(formula)
KLMNOP
1ACCOUNTSAmountsCum. AmountsCheck AccountsBank Recon.Sales Inv. Status
2(formula)(formula)
3
4
5
6
7
8
9(formula)
10
11
12
QRSTUVW
1Debt AgeDebt Age GroupSupplier Inv. StatusCredit AgeCredit Age GroupDue DateItems
2(Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-5933860848235383642012-02-01T15:33:00.001+08:002014-10-30T17:49:43.994+08:00Creditors reconciliationUsually, 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 Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-68636410187378324902011-06-30T14:50:00.000+08:002011-07-15T09:39:29.843+08:00IntroductionHere I show how to use Microsoft Excel as a complete 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.
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-33129130079322554872011-06-28T23:59:00.001+08:002014-10-30T17:20:58.728+08:001. Initial Steps- Open a new workbook (Create a blank workbook).
- Save the new 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.
Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-50518583802891253512011-06-28T23:59:00.000+08:002012-08-01T17:16:02.120+08:002. Data entry structureWhere to enter data
1. Use a dedicated sheet.
2. Name the sheet appropriately. In this book, its name is "Data".
Sample structure
ABCDEFGHIJ
1SRCDATEMTHSRC. NO.2ND SRC. NO.Note 1Note 2Note 3Note 4Note 5
2
3
4
5
6
7
8
9Subtotal
10Checking
11Cut-off Date(date)
12LastRowDATA(formula)
KLMNO
1ACCOUNTSAmountsCum. AmountsCheck AccountsBank Recon.
2(formula)
3
4
5
6
7
8
9(formula)
10
11
12
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-74231840659461815152011-06-28T23:56:00.000+08:002012-11-05T12:12:55.656+08:003. Entering transactions dataWhat 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 theMael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-46620130873411583322011-06-27T09:41:00.000+08:002011-09-27T16:51:10.381+08:00Handling 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 to record the amounts in the foreign currency.
3. To get the sum of all amounts of foreign currency for a particular Accounts, useMael 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-20888274843465447122011-06-26T23:00:00.000+08:002012-07-30T14:29:14.800+08:00Trial Balance and Chart of Accounts1. Use a dedicated sheet for the Trial Balance and Chart of Accounts.
2. The Trial Balance and Chart of Accounts are combined.
3. Design the structure for the Trial Balance and Chart of Accounts as you like.
4. Use SUMIF function to sum total figures from data entry sheet.
5. Use COUNTIF function to detect duplicate of Accounts name.
Sample structure of TB/Chart
ABCDEFGHIJK
1Acc.BS or 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-29991101297152992282011-06-25T23:00:00.000+08:002012-07-30T14:15:30.346+08:00Balance SheetWhere to put the Balance Sheet
Use a dedicated sheet for the Balance Sheet.
A sample structure of Balance Sheet
Design the structure for the Balance Sheet as you like.
Sample structure :
A B C D E
1 ABC SDN BHD
2 BALANCE SHEET FOR PERIOD. ..
3 PARTICULARS NOTE YEAR TO DATE LAST YEAR
4
5 CAPITAL
6 RETAINED EARNINGS
7 TOTAL
8
9 Represented byMael 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-33239999631911088082011-06-25T22:30:00.000+08:002012-01-15T09:35:34.328+08:00Profit and Loss - cumulativeWhere 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 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-68817731494530440312011-06-25T22:00:00.000+08:002012-01-15T09:40:02.068+08:00Profit and Loss - monthlyWhere 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 functionMael 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-36182414532565196532011-06-24T14:45:00.004+08:002012-01-11T15:23:15.743+08:00Viewing a single ledgerMethod 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 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-45899572341559424602011-06-24T14:44:00.002+08:002012-01-11T15:25:40.075+08:00Generating all ledger AccountsMethod 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 methodsMael 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-62872805055956480612011-06-24T14:43:00.006+08:002011-06-27T09:12:08.755+08:00View transaction listingUse Filtering operation or PivotTable operation.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-54076948901431192842011-06-24T14:43:00.005+08:002011-06-27T09:11:15.084+08:00View all ledgersUse PivotTable operation.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-89153046645846018802011-06-24T14:42:00.002+08:002011-06-24T14:42:16.129+08:00Other analysisOnce
you familiar with using formula, PivotTable operation and Filtering
operation, you will be able to generate many analysis with ease.Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-87662333394053842932011-06-24T14:39:00.003+08:002011-06-27T09:15:17.109+08:00Cash Flow reportDesign the structure of the report as you like.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-48500920473715626432011-06-24T14:38:00.003+08:002011-06-27T09:16:10.761+08:00Fixed Assets scheduleDesign the structure of the schedule as you like.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-69216436637617253322011-06-24T14:37:00.003+08:002012-08-01T14:54:58.455+08:00Bank reconciliation1. 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.
KLMNOP
1ACCOUNTSAmountsCum. AmountsCheck AccountsBank Recon.Sales Inv. Status
2(formula)(formula)
3
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-50556020839440212232011-06-24T14:36:00.003+08:002011-06-27T09:18:05.259+08:00Debtors ListingUse PivotTable operation.Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0tag:blogger.com,1999:blog-8036924717593901794.post-89457337053987344962011-06-24T14:35:00.003+08:002011-06-27T09:18:55.407+08:00Debtors Ageing analysisUse PivotTable operation.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-41022211628253430462011-06-24T14:34:00.003+08:002011-06-27T09:20:17.980+08:00Creditors listingUse PivotTable operation.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-37214740274628437872011-06-24T14:33:00.003+08:002011-06-27T09:21:07.547+08:00Creditors Ageing analysisUse PivotTable operation.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-51610088977777271812010-06-23T11:51:00.000+08:002011-06-23T11:52:41.023+08:00List of Payments due dateUse PivotTable operation or Filtering operation.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-29048567475036333382010-06-23T11:50:00.000+08:002011-06-23T11:50:36.992+08:00Opening balances for new accounting yearUse the balances in the closing Trial Balance.Mael Hashimhttp://www.blogger.com/profile/12112000937690547018noreply@blogger.com0Taman Sri Muda, Shah Alam, Selangor, Malaysia3.0279061 101.533330699999963.0158871 101.52058619999997 3.0399251 101.54607519999996