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 [...]

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 [...]

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 [...]

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 [...]

Back Again

Well, the VAT returns have been done and it’s back to the blog, which has been sorely neglected of late.
I have also been reminded of how handy Excel is as a fallback, even though it isn’t necessarily the best tool for the job, it’s relatively painless, reasonably straightforward and fairly easy to follow the “audit trail”.

Simple Purchase Ledger in Excel IV

To use the purchase ledger spreadsheet is easy.  Just record all purchase invoices, receipts and other payments on the spreadsheet.  Allow one row per invoice/receipt.
Enter the total amounts in columns D/E/F (allowing for the VAT split if VAT registered) and then allocate the relevent amounts in the appropriate analysis columns (column I to N in [...]

Simple Purchase 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 [...]

Simple Purchase Ledger in Excel II

To format the numbers (in columns D,E,F,H onwards) 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 [...]

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 [...]

Daily Dose of Excel

I don’t normally have a lot of time to surf the net between work and family committments, but I have noticed Daily Dose of Excel, who have been kind enough to include the Happy Accountant in their extensive blogroll.
The Daily Dose has been running since the start of 2004 and has lots of great tips and [...]

Simple Formulae

All formulae in Excel begin with an = sign.
Suppose you wish to add the contents of cell A1 and A2 and display the answer in cell A3, put the formua into cell A3:
=A1+A2
To add a column of numbers from A1 down to A10, and put the total in cell B1, you could put
=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10
in cell B1, but [...]

Cell References

Excel references each cell, co-ordinate style: With absolute references, letters denote columns and numbers are used for rows.  Hence B4, is the second column along (from the left) and the fourth row down.
(There is another style of cell referencing, called relative referencing)
To reference a range of cells (for example, the column of cells starting aown [...]