Using Spreadsheets for Your Accounts

Using spreadsheets to do your accounts used to be the standard way of doing things until the advent of database type specialised accounts programs, such as Sage, Xero, Quickbooks, FreeAgent, etc.

A spreadsheet is an easy way to get your business transactions to us to do your accounts. We will simply import your spreadsheet into a software accounts programs that is then able to separate the transactions into the various tax categories and prepare the data for your tax return.

Note also that if you’ve got a basic spreadsheet skill set, you can download a CSV file from your bank, complete with all the transactions in place, and modify it to match our recommended stencil. All you have to do is remove a few unnecessary columns, remove any rows that are not relevant, and add the category for each line.

Please see the post: Modifying a Bank CSV File

Here is a guide to the Columns in our Spreadsheet Stencil:

  • Date: The date of the transaction in the format dd/mm/yyyy or dd/mm/yy
  • Type: This is the transaction ‘Type’ code as will be on your bank statements. It’s just to keep a record of how the transaction was paid. If you don’t know any codes, just make up your own. e.g. D/D (Direct Debit), S/O (Standing Order), CSH (Cash), CHG (Charges), DBC (Debit Card), CDC (Credit Card), XFR (Bank Transfer), BAC (Bank Automated Credit), etc. Whatever you put, I’ll be able to work it out …
  • Payee or Payer: This is usually shown as ‘Description’ on your bank statement, but it’s not usually a description of what it is, (although it might also have “ESSO Fuel”); it’s a description of who paid you or who you paid.
  • In: The amount that you received in.
  • Out: The amount that you paid out.
  • Bank Account: If all your transactions are via the same bank account just put the bank account in the top row, no need to fill in all the rows. If all your transactions are via various accounts (including cash), fill in the column to indicate what transactions relate to which bank account.
  • Category: Use one or two words to describe what the transaction is for. e.g. ‘Sales’, ‘Goods’ (for resale), ‘Vehicle Fuel’, ‘Insurance’, ‘Mobile Phones’, ‘Internet Connection’, etc.
  • Group: If you wish you can use the ‘Group’ column to indicate different groups that relate to specific transactions. e.g. If you have income from property, you may want to differentiate what transactions relate to specific properties; or if you do different types of work like roofing, joinery, electrical, etc., you might want to keep a note of what transactions relate to specific job types.

Here is an example of a spreadsheet with boxes filled in:-

Download Spreadsheets

To be as compatible with as many clients as possible, these stencil files are available in MS Excel format (.xlsx) and Open Office format (.ods). Click on the icons to download …

Microsoft Excel Spreadsheet Stencil

Microsoft Excel
Spreadsheet Example

Open Office
Spreadsheet Stencil

Open Office
Spreadsheet Example

See more articles on our Clients Page