Simple Purchase Ledger in Excel IV

To use the purchase ledger spreadsheet is easy.  Just record all purchase invoices, receipts and other payments on the spreadsheet.  Allow one row per invoice/receipt.

Enter the total amounts in columns D/E/F (allowing for the VAT split if VAT registered) and then allocate the relevent amounts in the appropriate analysis columns (column I to N in my example).

The description is just a space to record extra detail, such as a date range of a magazine subscription or more specific description of the goods or services you have bought.  The notes column is more of an accounting note, for example, note if you have a query with the invoice.  If you have an accountant, you might use this column to flag a query with them – for example, if you are not sure whether the item is a tax-deductible business expense.

For example, you wish to record an invoice from Office Bods, for £117.50 including VAT.  £40 relates to new files, paper, envelopes, £40 is for pay-as-you go mobile phone top ups and £20 is for stamps.  This would be recorded as:

  • column A (Date):the date of purchase
  • column B (Supplier): Office Bods
  • column C (Invoice number): (the sequential number that you allocate to this purchase, so start the year at no.1)
  • column D (net amount): 100
  • column E (VAT): 17.50
  • column D (Gross): 117.50 (automatically filled in)
  • column I (Stationery): 40
  • column J (Postage): 20
  • column K (Telephone): 40

Afterwards, you check that there is a zero in column H, the check zero column, to make sure that you have allocated the entire invoice cost to a particular type of expenditure.

Always keep your receipts and invoices and write the ref/invoice number on it so that you can cross reference the original source document to your spreadsheet record.  Not only will this will also help to cut down your accounting fees if you use an accountant, it will also help in the event of having a tax enquiry or investigation.


The spreadsheet described this week is particularly suitable for a sole trader, a consultant trading through a limited company or a start up enterprise.


2 responses to “Simple Purchase Ledger in Excel IV

  1. You mention the invoice number and say “so start the year at no.1”. Does this mean every financial year the invoices generated should begin at 1?

  2. Oh, I should have been slightly clearer: no,it doesn’t – I was trying to emphasise the need for clear, sequential numbering (plus, I made an assumption that you would set up such a spreadsheet at the start of the year, which, in reality, might not be the case). The idea behind numbering your purchase invoices is so that you have a cross-reference between the line on your spreadsheet and the actual invoice or receipt (on which you would have also written the sequential number and filed it accordingly) so that you can find the original document easily, should you need to and not account for the same invoice twice.