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)
- 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
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.