Basic Accounting Template: Step-by-Step Guide

Initial Setup

  1. Enter your business name at the top of the Setup sheet
  2. Set the first financial year and reporting year
  3. Select your year-end month
  4. Customize sales tax codes and percentages if needed
  5. Add bank codes for your different accounts

Setting Up Chart of Accounts

  1. Review the default accounts on the TB (Trial Balance) sheet
  2. Customize account descriptions as needed
  3. Add new accounts by inserting rows and using the correct account number format
    • Format: IS/BS-XX99 (IS for income statement, BS for balance sheet)

Entering Opening Balances

  1. Enter balance sheet opening balances in column C of the TB sheet
    • Use positive values for assets, negative for liabilities/equity
    • Ensure total equals zero
  2. Record opening bank balances on the Income sheet as positive values
  3. Enter opening trade debtors on the Income sheet with actual invoice dates
  4. Enter opening trade creditors on the Expenses sheet with actual invoice dates

Recording Income Transactions

  1. Navigate to the Income sheet
  2. Enter:
    • Invoice number
    • Invoice date
    • Customer name
    • Description
    • Tax inclusive amount
    • Sales tax code(s) if applicable
    • Bank code
    • Account number
  3. When payment is received, add:
    • Payment amount
    • Payment date

Recording Expense Transactions

  1. Navigate to the Expenses sheet
  2. Enter:
    • Document date
    • Supplier name
    • Reference number
    • Description
    • Tax inclusive amount
    • Sales tax code(s) if applicable
    • Bank code
    • Account number
  3. When payment is made, add:
    • Payment date

Bank Transfers

  1. Record both sides of any bank transfer on the Expenses sheet
  2. For the withdrawal: positive amount from source bank
  3. For the deposit: negative amount to destination bank
  4. Allocate both to account BS-0799 (Cash Transfer Control)

Journal Entries

  1. Record journal entries on the Expenses sheet
  2. Use bank code JC for all journal entries
  3. Enter debits as positive amounts
  4. Enter credits as negative amounts

Viewing Financial Reports

  1. Trial Balance (TB sheet): Select month at top of sheet
  2. Income Statement (IS sheet): Shows 12 monthly periods based on reporting year
  3. Cash Flow Statement (CFS sheet): Auto-generated from transaction data
  4. Balance Sheet (BS sheet): Shows monthly positions based on reporting year

Analysis Tools

  1. Bank sheet: View daily/monthly bank movements by selecting bank code and month
  2. SalesTax sheet: Analyze sales tax by selecting tax type and code

Troubleshooting Balance Sheet Errors

If “error” message appears on balance sheet:

  1. Check for error codes on Income and Expenses sheets
  2. Ensure all transactions have bank codes and account numbers
  3. Verify all dates use correct format
  4. Confirm all bank accounts are added to TB and BS sheets
  5. Check opening balances total zero
  6. Review TB sheet for any red highlighted items

Rolling Forward

To move to a new financial year, simply change the reporting year on the Setup sheet – all reports will automatically update.

Leave a Comment