Category Archives: Excel

This Blog has Moved

to www.HappyAccountant.com

Subscribe to our free newsletter

Advertisements

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 of credit to different customers, record the number of days credit that you have allowed.   Otherwise, just copy the same number down onto all the rows or leave blank if no credit is given.

Record the date that the invoice is paid in column I.  Column J will then calculate how many days of credit were actually taken, which may assist you in future credit control and/or cash flow forecasting.

If you are not giving your customers a credit period before payment falls due, then columns G, H, I and J can be omitted.

The notes column is just a space to record extra detail, for example, if the invoice is under query by the customer, or the details of when you chased your customer for payment.  If you have an accountant, you might use also this column to flag a query with them – for example, if the invoice becomes a bad debt and why.

For example, you wish to record an invoice, dated 6 August 2007 to Bloggs & Co for £117.50 including VAT.  You normally allow 30 days credit.

  • column A (Date): 6 August 2007
  • column B (Invoice number):xx (your sales invoice number, normally sequential and unique)
  • column C (customer): Bloggs & Co
  • column D (net amount): 100
  • column E (VAT): 17.50
  • column F (Gross): 117.50 (automatically filled in)
  • column G (Credit period given): 30 (as in 30 days)
  • column H (Date Invoice is due): 5 September 2007
  • column I (Date Invoice is paid): this is completed when payment is received.  (Make sure that it is a proper date that is entered here, otherwise column J will not calculate properly)
  • column J (Credit Days actually taken): this is automatically calculated and over time, will enable you to see which of your customers pay on time and which don’t.

Afterwards, you check that the figure in column F agrees to the amount that you have put on the actual sales invoice.

Always file your sales invoices sequentially.  It will help you to find an invoice in the event of a copy invoice being requested and may help to cut down your accounting fees if you use an accountant.

Suitability

The spreadsheet described this week is particularly suitable for a sole trader, a consultant trading through a limited company or a start up enterprise.

Simple Sales Ledger in Excel

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:

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

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

The description is just a space to record extra detail, such as a date range of a magazine subscription or more specific description of the goods or services you have bought.  The notes column is more of an accounting note, for example, note if you have a query with the invoice.  If you have an accountant, you might use this column to flag a query with them – for example, if you are not sure whether the item is a tax-deductible business expense.

For example, you wish to record an invoice from Office Bods, for £117.50 including VAT.  £40 relates to new files, paper, envelopes, £40 is for pay-as-you go mobile phone top ups and £20 is for stamps.  This would be recorded as:

  • column A (Date):the date of purchase
  • column B (Supplier): Office Bods
  • column C (Invoice number): (the sequential number that you allocate to this purchase, so start the year at no.1)
  • column D (net amount): 100
  • column E (VAT): 17.50
  • column D (Gross): 117.50 (automatically filled in)
  • column I (Stationery): 40
  • column J (Postage): 20
  • column K (Telephone): 40

Afterwards, you check that there is a zero in column H, the check zero column, to make sure that you have allocated the entire invoice cost to a particular type of expenditure.

Always keep your receipts and invoices and write the ref/invoice number on it so that you can cross reference the original source document to your spreadsheet record.  Not only will this will also help to cut down your accounting fees if you use an accountant, it will also help in the event of having a tax enquiry or investigation.

Suitability

The spreadsheet described this week is particularly suitable for a sole trader, a consultant trading through a limited company or a start up enterprise.