Simple Sales Ledger in Excel IV

To use the sales ledger spreadsheet is easy.  Just record all your sales invoices as you raise them.  Allow one row per invoice.

Enter the total amounts in columns D and E (if VAT registered) and check that the figure in column F is as you have put in your actual sales invoice.

If you give different periods of credit to different customers, record the number of days credit that you have allowed.   Otherwise, just copy the same number down onto all the rows or leave blank if no credit is given.

Record the date that the invoice is paid in column I.  Column J will then calculate how many days of credit were actually taken, which may assist you in future credit control and/or cash flow forecasting.

If you are not giving your customers a credit period before payment falls due, then columns G, H, I and J can be omitted.

The notes column is just a space to record extra detail, for example, if the invoice is under query by the customer, or the details of when you chased your customer for payment.  If you have an accountant, you might use also this column to flag a query with them – for example, if the invoice becomes a bad debt and why.

For example, you wish to record an invoice, dated 6 August 2007 to Bloggs & Co for £117.50 including VAT.  You normally allow 30 days credit.

• column A (Date): 6 August 2007
• column B (Invoice number):xx (your sales invoice number, normally sequential and unique)
• column C (customer): Bloggs & Co
• column D (net amount): 100
• column E (VAT): 17.50
• column F (Gross): 117.50 (automatically filled in)
• column G (Credit period given): 30 (as in 30 days)
• column H (Date Invoice is due): 5 September 2007
• column I (Date Invoice is paid): this is completed when payment is received.  (Make sure that it is a proper date that is entered here, otherwise column J will not calculate properly)
• column J (Credit Days actually taken): this is automatically calculated and over time, will enable you to see which of your customers pay on time and which don’t.

Afterwards, you check that the figure in column F agrees to the amount that you have put on the actual sales invoice.

Always file your sales invoices sequentially.  It will help you to find an invoice in the event of a copy invoice being requested and may help to cut down your accounting fees if you use an accountant.

Suitability

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