Simple Purchase Ledger in Excel I

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

  • date
  • supplier
  • ref/invoice number
  • 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)
  • date paid
  • check zero
  • stationery (analysis column)
  • postage (analysis column)
  • telephone (analysis column)
  • electricity (analysis column)
  • prof fees (analysis column)
  • [add more analysis columns, as relevant to your business]
  • description
  • 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 the check zero column (which is column H in my example), in the row below the column headings (ie in cell H2), enter the formula:

=sum(I2:M2)-D2

This will act as another check to catch data input errors when you start to use the spreadsheet.

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.

simple-purchase-ledger-screenshot-1doc.jpg

Copying cells

Using the menus

  • select cell F2 and then choose Edit|Copy (from the menu bar).
  • select cell F3 and then while holding down the left mouse button, drag the selection box down to row 50 (so that a narrow rectangle is highlighted)
  • release the left mouse button and then choose Edit|Paste (from the menu bar)

Using the toolbar icons

  • if familar with the icons on the standard toolbar, use these instead of choosing Edit|Copy and Edit|Paste from the menu bars

Using the mouse/right click menu

  • select cell F2 and then click the right mouse button.
  • choose copy from the mini menu (by clicking on it)
  • highlight the cells to be copied into, as before then click the right mouse button again
  • chose paste from the mini menu

Dragging the original cell

  • select cell F2
  • move the mouse over to the bottom right corner of that cell until the pointer turns to a cross
  • hold down the left mouse button and drag it downwards so that the required cells (to be copied into) are highlighted
  • release the mouse button
Advertisements

Comments are closed.