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:

=sum(D2:E2)

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:

=A2+G2

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:

=H2-A2

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

Advertisements

2 responses to “Simple Sales Ledger in Excel I

  1. why is the credit period given and taken columns needed? would these be defined in days?

  2. Credit period given/taken are only required if you are giving credit. So, if your customers pay “cash on delivery” or pay for your services as they use them, then columns G, H, I and J are not needed and can be omitted. Yes, both credit period taken/received are in days. Thursday’s post will explain in a bit more detail, how to use the spreadsheet.