Table of Contents 1

Table of Contents

Part 1: Getting ready

1. Introduction 1
Book-Keeping Functions That Can Be Done Using Microsoft Excel
Requirements

2. Building The Structure 3
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

Part 2: Input Data

3. Input Data In Sheet TB (Trial Balance) 19
Entering Data In The ACCOUNTS Column (Column A)
Entering Data In The BALANCE SHEET-MAIN CATEGORY Column (Column B)
Entering Data in The BALANCE SHEET-SUB CATEGORY Column (Column C)
Entering Data In The PL-CATEGORY Column (Column D) Entering Data In The PL-CATEGORY Column (Column E)
Copying Formula Into Cells in The RM-DEBIT Column (Column F)
Copying Formula Into Cells in The RM-CREBIT Column (Column G)
Copying Formula Into Cells in The RM-NET Column (Column H)
Copying Formula Into Cells in The CHECK DUPLICATE Column (Column I)
Sample

4. Input Data In Sheet DATA 27
Entering Data In The SRC Column (Column A)
Entering Data In The DATE Column (Column B)
Entering Data In The MTH Column (Column C)
Entering Data In The SRC NO Column (Column D)
Entering Data In The OTHER REF./NO. Column (Column E)
Entering Data In The NOTE Column (Column F)
Entering Data In The ACCOUNT-DEBIT Column (Column G)
Entering Data In The RM-DEBIT Column (Column H)
Entering Data In The ACCOUNT-CREDIT Column (Column I)
Entering Data In The RM-CREDIT Column (Column J)
Entering Data In The CHECK ACCOUNT-DEBIT Column (Column K)
Entering Data In The CHECK ACCOUNT-CREDIT Column (Column L)
Entering Data In The BANK RECON Column (Column M)
Entering Data In The DEBTORS STTMNT Column (Column N)
Entering Data In The DEBT AGE Column (Column O)
Entering Data In The DEBT AGE GROUP Column (Column P)
Entering Data In The CREDITOR STTMNT Column (Column Q)
Entering Data In The CREDIT AGE Column (Column R)
Entering Data In The CREDIT AGE GROUP Column (Column S)
Entering Data In The HOLD Column (Column T)
Entering Data In The TERM Column (Column U)
Entering Data In The DUE DATE Column (Column V)
Entering Data In The ITEM PURCHASED Column (Column W)
0 Entering Data In The ITEM SOLD Column (Column X)
Entering Data In The QTY Column (Column Y)
Entering Data In The UNIT Column (Column Z)
Entering Data In The PRICE Column (Column AA)
Entering Data In The TEMP Column (Column AB)
Entering Data In The ORIGNAL ORDER Column (Column AC)
Do Not Write Anything In Column AD
No Blank Cells In SRC Column
No Blank Cells Between Items In Column Titles
When One Transaction Gives Rise To More Than One Account Name
When One Transaction Involves More Than One Item Sold Or Purchased
Group Together Data From The Same Source
Arrange Sources Of Data In Groups of Logical Order
Note On Entering Opening Balance Data
Double Entry For Closing Stock
Unequal Number Of Double Entries In The Audit Adjustment
Double Entry For Payroll
Double Entry For Petty Cash Receipts
Double Entry For Petty Cash Payments
Double Entry For Bank Receipts
Double Entry For Bank Payments
A Sample

5. Review Data And Structure 49
Review Of Data In Sheet DATA
Review Of Data In Sheet TB

Part 3: Create Report

6. Creating Report: Trial Balance 51
Creating "Trial Balance' Report Using Formulas
Creating "Trial Balance' Report Using PivotTable Operation

7. Creating Report: Balance Sheet 55
Creating 'Balance Sheet Report Using Formulas Creating 'Balance Sheet Report Using PivotTable Operation

8. Creating Report: Profit & Loss 65
Creating 'Profit & Loss' Report Using Formulas
Creating 'Profit & Loss' Report Using PivotTable Operation

9. Creating Report: Debt Ageing 74
Creating 'Debt Ageing' Report Using Formulas
Creating 'Debt Ageing' Report Using PivotTable Operation

10. Creating Report: Creditors Ageing 85
Creating 'Creditors Ageing Report Using Formulas
Creating 'Creditors Ageing' Report Using PivotTable Operation

11. Creating Report: Purchases 96
Creating 'Items Purchased' Report Using Formulas
Creating 'Items Purchased' Report Using PivotTable Operation

12. Creating Report: Sales
Creating 'Items Sold' Report Using Formulas
Creating 'Items Sold' Report Using PivotTable Operation