Category Archives: Sales Ledger

This Blog has Moved


Subscribe to our free newsletter


Sales Invoicing

If you give your customers credit (and most businesses do), it’s important to get your invoicing and customer payments systems in order.  The obvious benefit is to aid cash flow and it is the first step to good credit control and fewer bad debts.

Some while ago, I posted about some basic details that need to be included on a sales invoice.  In addition, if the business is a limited company, the sales invoice must include:

  • the correct full company name (as written on the certificate of incorporation)
  • any “trading as” name or business name.
  • the names of the directors may also be included – but it is a case of naming all the directors or none at all.

Sole traders must give:

  • the business name, if the surname is not being used.
  • an address.

If the business is VAT registered, the VAT number must also be given.

It’s also important to send the invoices out as promptly as possible, particularly at month-end when many companies have a “cut-off” point and invoices received later are processed in the following month, resulting in later payment.

Settlement Discounts

It is quite common to offer a settlement discount to customers who pay within a certain timescale.  The main reason for offering such a discount is to aid cash flow.  However, do not miss the sales/marketing advantage that this also offers:  Make sure that all your customers are aware that you are offering a settlement discount as part of your marketing, inclusion in your standard terms and conditions and don’t forget to mention it when chasing up payment.

Also,, do not fall into the trap of honouring the settlement discount for those customers who fail to meet the required discount.

Simple Sales Ledger in Excel III

To finish off this simple spreadsheet, it might be helpful to make it look slightly more presentable.Select Row 1 (by clicking on the 1) and then bring up the Format Cells dialog box (either by Menu|Format, keyboard shortcut or right mouse button menu) and then you can make the text bold (Font tab), colour in the background (Patterns tab), select borders (Borders tab) or alternatively using the icons shortcuts (on the toolbar menu) or by using keyboard shortcuts.

So, for example:

  • To make the font bold, by highlighting the entire row, selecting (Menu|Format|Cells) and the font tab and selecting Bold in the font style
  • To make give the cells a firm outline (or border), click on the Border tab and then click on both the outside and inside presets near the top
  • To shade the cells, click on the Patterns tab and then click on the colour of your choice from the color section near the top.

Also, adjust the column widths to suit.  This can be done by selecting the column and then choosing Menu|Format|Column|Width and adjusting the number in the little dialog box.  You could also choose the auto-fit selection.

You might also wish to “wrap” the text, if you want narrower columns.  Do this by highlighting Row A selecting (Menu|Format|Cells) and the Alignment tab and check the wrap text box.  Doing this will enable you to make columns narrower whilst still enabling you to read the column title.  (Probably especially relevant for columns B, G, H, I and J)

Finally, freeze the top row to make the spreadsheet easier to use once there’s a lot of entries on it, by:

  • select cell A2
  • from the menu bar: menu|window|freeze panes

Or to freeze the top row and the first two columns, instead of just the top row:

  • select cell C2
  • from the menu bar: menu|window|freeze panes

As with the Simple Purchase Ledger in Excel, none of the formatting is essential; the spreadsheet will work just as well without, it just makes the spreadsheet easier to use.

Simple Sales Ledger in Excel II

To format the numbers (in columns D,E, & F) so that they all have 2 decimal places, select these columns (by clicking at the top (where the column letter is) and then chosing Format|Cells and then (on the number tab) click number and make sure that there is a 2 in decimal places.

I also check the use 1000 separator (,) because it makes reading bigger numbers easier (and makes finding errors slightly easier).

Then click OK.

The Format Cells dialog box could also be reached using the right mouse button menu and keyboard shortcut ([CTRL] and 1), or by using the toolbar icon, if you have chosen to put it on your toolbar.

Format the numbers in columns G and J so that they have no decimal places.  (customers are normally only given a credit period consisting of whole days)

Format the dates in columns A, H & I in a similar way to above, choosing options Format|Cells and then (on the number tab) click date.  Select the date format you prefer (choose one that doesn’t include the time)

Simple Sales Ledger in Excel I

First begin by heading up the columns, in cell A1:

  • date of invoice
  • invoice number (the number that you assign, since these are your sales invoices)
  • customer 
  • net amount (if registered for VAT, if not registered for VAT, then head this column “gross amount”)
  • VAT (omit this if not registered for VAT)
  • gross amount
  • credit period given (if applicable)
  • date invoice is due
  • date invoice paid
  • credit period actually taken
  • notes

If registered for VAT, then in the gross amount column (which is column F in my example), in the first row below the column headings (ie, in cell F2) enter the formula:


which will simply add the net (before VAT) amount and VAT amount, which should then be the same as the gross amount.  This should be the same as the gross amount on the invoice and is a simple check for data input errors.

In cell H2, enter the formula:


The formulae in column F and column H should be copied down for a few rows (may be to row 50, as a start).  This is done a number of different ways.  Please refer to Simple Purchase Ledger in Excel I if you need to know how to do this.

In cell J2, enter the formula:


and, as above, copy the formula down for a few rows.