Tag Archive for 'spreadsheets'

Guide to Bookkeeping – 3

Published on March 20, 2012 in Manual Cash Books and Spreadsheets. 0 Comments Tags: , , , .

This is a guide to bookkeeping for a business that

  • wants to use manual daybooks and cash books, or computer spreadsheets, such as Excel
  • want to record transactions on a standard bookkeeping basis (i.e. At the date the invoices are raised rather than at the point items are paid for)
  • is VAT registered, and operates its VAT scheme on a Standard Accounting basis

If you have not done so already, it would be worth reading our Introduction to Book Keeping article, before progressing further.

It will also help to follow the instructions and explanations if you can print off the appendices referred to.

~~~~~~~~~~

This bookkeeping method requires a Sales Daybook, a Purchase Daybook and a Cash Book.  It is a system used when VAT is to be calculated on invoice date rather than payment date, what is called Standard VAT accounting.  With this VAT scheme keeping a simple record of cash in and cash out does not give you the correct totals at the end of each VAT quarter to prepare your VAT return.  This method of bookkeeping provides a straight forward method of keeping your VAT records and keeping on top of your cash and bank balances.

The Sales Daybook, Purchase Daybook and Cash Book are each explained below, followed with an explanation of how to prepare the VAT return from them.

Sales Day Book

The is where a business records its sales invoices.  Appendix 6 shows a basic layout for a sales daybook – there would be little reason to vary much from this.

Explanations of each column header are given below.  Obviously, each page needs to be titled with the month and year in question and with the fact that it is Sales Invoices that are being listed.

Date
It it ususal to list your sales invoices in date order.

Detail
Here list the the customer or client who received the sales invoice.

Reference
The sales invoice number is recorded in this column. 

Net
This is the column to record your net sale before you added VAT to it.

VAT
This is the column where you record the VAT that had to be charged on the sale.

Gross
This is the column where you record the invoice total (the net and VAT columns added together).

So, for each invoice you will need to

•          state the date it was raised,
•          state who it was sent to,
•          state the invoice number,
•          show the net, VAT and gross amounts of the invoice in the relevant columns.

At the end of each month you can add up the columns to get totals for the month.  The best check to see if you’ve got the basic arithmetic right is to understand that the totals of the ‘Net’ and ‘VAT’ columns should equal the totals of the ‘Gross’ column.

Purchase Day Book

This is where a business records its supplier or Purchase invoices.  Appendix 7 shows a basic layout for a purchase daybook – there would be little reason to vary much from this.

Explanations of each column header are given below.  Obviously, each page needs to be titled with the month and year in question and with the fact that it is Supplier or Purchase Invoices that are being listed.

Date
It is usual to list your supplier invoices in date order.

Detail
Here list the supplier who gave you the purchase invoice.

Reference
It is sensible give each invoice a reference number so that it can be easily found and referred to if necessary in the future.  So, start with number 1; write number 1 in the top right hand corner of the first invoice you want to record, record “1” in the reference column, and place the invoice in a file.  Pick up the next invoice, reference that as number 2, and so on. This way all of your invoices will be filed in numerical order and easily found in the future.

Note that you do not need to start at number 1 again for the second month; just continue with the next number on.

Gross
This is the column where you record the invoice total (the net and VAT added together).

VAT Column
The VAT included in any of the payments you make needs to be recorded in this column, so that you can claim it back on your VAT return.  Remember that you can only claim back input VAT if you have a supporting VAT invoice or receipt from your supplier.  A VAT invoice or receipt must always have your supplier’s VAT registration number printed or written on it.

Most VAT invoices or receipts will have the amount that you have been charged for goods or services before VAT, the amount of VAT you have been charged, and the grand total.  The VAT charged is the amount to record in the VAT column.

If you have been given a receipt that just shows the amount you have charged, but does not separate out the VAT, then you need to work out how much VAT to claim back.  VAT is currently 20%, therefore, you will need to divide the full amount paid by 6.  For example, if you have paid £30 for petrol and need to work out what VAT is included, then divide £30 by 6.  This gives you £5, and this can be claimed back as input VAT.  You have been charged £25 before VAT for your petrol.  For more information on this click here.  Also remember, that when there is a change in the rate of VAT, the calculation you need to apply to work out the VAT this way will change.

Other Columns (Net Columns)
Once you have recorded the VAT in the VAT column you need to record the ‘net’ (before VAT) expense in one of the other columns.  Choose a column that best suits the expense you are ‘analysing out’ and list the amount there. 

It will also be useful, especially to your accountant, if you could give additional information for some expenses in the column to the far right.  For example, it’s great seeing that this business paid £458 to Aviva and that it is analysed under ‘Insurance’, but in order to know that the transaction is accounted for properly in your year accounts, your accountant will need to know what type of insurance it is and what period was covered by the insurance. 

This additional information isn’t vital if you give all of your purchase invoices to your accountant at the year end as they will be able to find this detail on the relevant invoice.  However, if you send your accounts records to your accountant via email, and do not send in the original documentation, then this additional information will help no end.

So, for each invoice you will need to

•          state the date it was raised,
•          state who it was sent from,
•          state the reference number that it has been given,
•          show the net, VAT and gross amounts of the invoice in the relevant columns.

Again, once the month has been completed the total of the columns needs to be calculated.  And again, the sum of the ‘net’ and ‘VAT’ columns should equal the ‘Gross’ column.

Cash Book

Cash Received

Appendix 8 shows a fairly typical layout for recording the income for a business.  It is usual for income to be split month by month, but not essential, however, it does lend itself more readily to helping you balance your bank and cash each month (more on this later).

Explanations of each column header are given below.  Each page needs to be titled with the month and year in question and with the fact that it is Income that is being listed.

Date
It helps to specify the date on which amounts are received.  It also helps to list them in date order.

Detail
Here list the person, business, or other entity that has paid you the money and/or a brief explanation of what the receipt is for, if considered necessary.

Reference
This example lists the invoice numbers of the invoices being paid by the amounts received.  For amounts received that do not relate to sales invoices, the reference has been left blank and a brief description used in the ‘Detail’ column instead.

Bank
Here list all amounts received that are paid into the bank.  This column should contain all of the deposits that show up on your bank statements.

Cash
Here list all amounts that are received in cash that you do not put into your bank account.

Once a receipt has been listed in either the Bank or Cash columns it then needs to be recorded as income relating to a Sales Invoice, or other income.

Sales receipts
List the amount received in this column if it is payment for one of sales invoices.  The gross amount needs to be recorded here.  The VAT on your sales invoice has been taken care of in the Sales Daybook (see above) and so does not have to be shown again here.

Other Income
List all other income here.  Your accountant will be able to analyse the items in this column over the year, and treat them accordingly in your accounts.

VAT
Other receipts will not ordinarily have VAT to account for; in this example other income is in the form of a tax refund and cash introduced from the proprietor.  These are not subject to VAT. 

However, this example also shows a refund of telephone charges.  This would mean that VAT was also refunded and so would need to be recorded as such.  The VAT would need to be recorded in the VAT column and the net amount recorded in the Other Income column.

So, for each receipt you will need to

•          state the date it was received and provide some detail for it,
•          determine whether it should be shown as being paid into your bank account or held in your cash in hand,
•          record whether the receipt is a payment for a sales invoice or whether it is some other income.

At the end of each month you can add up the columns to get totals for the month.  The best check to see if you’ve got the basic arithmetic right is to understand that the totals of the ‘Bank’ and ‘Cash’ columns should equal the totals of the ‘VAT’, ‘Sales Receipts’ and ‘Other Income’ columns.

Cash and Bank Payments

Appendix 9 shows a fairly typical layout for recording the cash and bank payments for a business.  Again, it is usual for expenditure to be split month by month, but not essential, however, it does lend itself more readily to helping you balance your bank and cash each month (more on this later).

Explanations of each column header are given below.  Each page needs to be titled with the month and year in question and with the fact that it is Expenditure that is being listed.

Date
It helps to specify the date on which payments are made.  It also helps to list them in date order.

Detail
Here list the person, business, or other entity that you have paid the money to.

Reference
In this column, if the payment is in relation to a supplier invoice listed in the Purchase Daybook, then record the reference number that has been allocated to that invoice. 

For amounts that are paid by standing order and do not relate to a specific invoice, we suggest the abbreviation ‘SO’ be used.

Some payments may not have a reference as such. 

Bank
Here list all amounts that are paid out of the bank.  This column should contain all of the withdrawals that show up on your bank statements.

Cash
Here list all amounts that are paid in cash and not from your bank.

Once a payment has been listed in either the Bank or Cash columns we then need to indicate what it was payment for. 

Purchase Daybook (PDB) Column
Most payments made will be payments for the invoices already listed in the Purchase Daybook.  All of the necessary information for these expenses will already be taken care of in the Purchase Daybook.  Therefore, these payments will just need to to listed in the PDB column.

Other Columns
Although most of the business’s costs will be detailed in the Purchase Daybook there will still be other expenses that appear for the first time in the Cash Book.  In this example, monthly utility standing orders, bank charges and drawings are all payments that do not cover invoices listed in the PDB.  In this case, further analysis columns are required.

Payments such as drawings, council tax and bank charges are not subject to VAT, and as such can be listed directly under relevant column headings.

VAT Column
Some payments may include VAT but may not relate directly to a specific invoice (usually utility bills such as gas and electricity).  In this case you will be sent paperwork by the supplier detailing the VAT that you can claim on each payment.  In this case this VAT can be split out into the VAT column and the remaining, or net, amount can be recorded in the relevant ‘other’ column.

Again, once the month has been completed the total of the columns needs to be calculated.  The ‘Bank’ and ‘Cash’ columns should equal the sum of all the other columns.

Bank and Cash reconciliations
For details of how to prepare bank and cash reconciliations at each month end please refer to the notes in Guide 1.

Guide to Bookkeeping – 2

Published on January 6, 2012 in Manual Cash Books and Spreadsheets. 0 Comments Tags: , , , , .

This is a guide to bookkeeping for a business that

  • want to record transactions on a cash basis (i.e. at the point that items are paid for, rather that the date invoices are raised)
  • wants to use a manual cash book, or use computer spreadsheets, such as Excel
  • is registered for VAT, and operates its VAT on a Cash Accounting basis

If you have not done so already, it would be worth reading our Introduction to Book Keeping article, before progressing further.

It will also help to follow the instructions and explanations if you can print off the appendices referred to.

~~~~~~~~~~

Bank and Cash transactions can be recorded on the same page of a manual cash book or on the same spreadsheet.  The best way to split them is to show bank and cash receipts together, and bank and cash payments together.

INCOME

Appendix 4 shows a fairly typical layout for recording the income for a business.  It is usual for income to be split month by month, but not essential, however, it does lend itself more readily to helping you balance your bank and cash each month (more on this later).

Explanations of each column header are given below.  Obviously, each page needs to be titled with the month and year in question and with the fact that it is Income that is being listed.

Date
It helps to specify the date on which amounts are received.  It also helps to list them in date order.

Detail
Here list the person, business, or other entity that has paid you the money and/or a brief explanation of what the receipt is for, if considered necessary.

Reference
This example lists the invoice numbers of the invoices being paid by the amounts received.  For amounts received that do not relate to sales invoices, the reference has been left blank and a brief description used in the ‘Detail’ column instead.

Bank
Here list all amounts received that are paid into the bank.  This column should contain all of the deposits that show up on your bank statements.

Cash
Here list all amounts that are received in cash that you do not put into your bank account.
Once a receipt has been listed in either the Bank or Cash columns it then helps to split income into VAT, sales receipts and other receipts.

VAT

The VAT that is included in the amount received needs to be recorded in the VAT column.
When you raised your sales invoices they will most likely have shown the amount that you charged your customer for goods or services before VAT, then the amount of VAT charged, and then the total.  The amount of VAT shown on your sales invoice needs to be recorded here.

Sales receipts
List receipts here if they relate to one of your sales invoices, or some other paid work that you have done.  The net before VAT is the amount to record.

Other Income
List all other income here.  Your accountant will be able to analyse the items in this column over the year, and treat them accordingly in your accounts.

So, for each receipt you will need to

  • state the date it was received and provide some detail for it,
  • determine whether it should be shown as being paid into your bank account or held in your cash in hand,
  • split the overall income between VAT, whether it is a receipt generated by sales or whether it has come from elsewhere.

At the end of each month you can add up the columns to get totals for the month.  The best check to see if you’ve got the basic arithmetic right is to understand that the totals of the ‘Bank’ and ‘Cash’ columns should equal the totals of the ‘VAT’, ‘Sales Receipts’ and ‘Other Income’ columns.

EXPENDITURE

Appendix 5 shows a fairly typical layout for recording the expenditure for a business.  Again, it is usual for expenditure to be split month by month, but not essential, however, it does lend itself more readily to helping you balance your bank and cash each month (more on this later).

Explanations of each column header are given below.  Obviously, each page needs to be titled with the month and year in question and with the fact that it is Expenditure that is being listed.

Date
It helps to specify the date on which payments are made.  It also helps to list them in date order.

Detail
Here list the person, business, or other entity that you have paid the money to.

Reference
It is common for people to file their purchase invoices and receipts on a file, giving each one a reference, usually just in simple numerical order.  This example lists the given ‘number reference’ applied to each invoice/receipt.

For amounts that are paid by standing order and do not relate to a specific invoice, we suggest the abbreviation ‘SO’ be used.

Bank
Here list all amounts that are paid out of the bank.  This column should contain all of the withdrawals that show up on your bank statements.

Cash
Here list all amounts that are paid in cash and not from your bank.
Once a payment has been listed in either the Bank or Cash columns it then helps to split the expenditure into the various types of expenditure that you incur.  This is achieved by creating a series of columns with headings relevant to your business.  Appendix 5 shows common headings used.

VAT Column

The VAT included in any of the payments you make needs to be recorded in this column, so that you can claim it back on your VAT return.  Remember that you can only claim back input VAT if you have a supporting VAT invoice or receipt from your supplier.  A VAT invoice or receipt must always have your supplier’s VAT registration number printed or written on it.

Most VAT invoices or receipts will have the amount that you have been charged for goods or services before VAT, the amount of VAT you have been charged, and the grand total.  The VAT charged is the amount to record in the VAT column.

If you have been given a receipt that just shows the amount you have charged, but does not separate out the VAT, then you need to work out how much VAT to claim back.  VAT is currently 20%, therefore, you will need to divide the full amount paid by 6.  For example, if you have paid £30 for petrol and need to work out what VAT is included, then divide £30 by 6.  This gives you £5, and this can be claimed back as input VAT.  You have been charged £25 before VAT for your petrol.  For more information on this click here.  Also remember, that when there is a change in the rate of VAT, the calculation you need to apply to work out the VAT this way will change.

Other Columns

Once you have recorded the VAT in the VAT column you need to record the ‘net’ (before VAT) expense in one of the other columns.  Choose a column that best suits the expense you are ‘analysing out’ and list the amount there.

It will also be useful, especially to your accountant, if you could give additional information for some expenses in the column to the far right.  For example, it’s great seeing that this business paid £458 to Aviva and that it is analysed under ‘Insurance’, but in order to know that the transaction is accounted for properly in your year end accounts, your accountant will need to know what type of insurance it is and what period was covered by the insurance.

This additional info isn’t vital if you give all of your purchase invoices to your accountant at the year end as they will be able to find this detail on the relevant invoice.  However, if you send your accounts records to your accountant via email, and do not send in the original documentation, then this additional information will help no end.

Again, once the month has been completed the total of the columns needs to be calculated.  And again, the sum of the ‘Bank’ and ‘Cash’ columns should equal the sum of all the other columns.

Appendix 3 shows examples of a bank and cash controls and reconciliations.

Guide to Bookkeeping 1

Published on August 31, 2011 in Manual Cash Books and Spreadsheets. 0 Comments Tags: , , , .

           This guide to book keeping is for a business that

  • wants to record transactions on a cash basis (i.e. at the point that items are paid for, rather than the date invoices are raised)
  • wants to use a manual cash book, or use computer spreadsheets, such as Excel
  • is not registered for VAT

If you have not done so already, it would be worth reading our Introduction to Book Keeping article, before progressing further.

It will also help to follow the instructions and explanations if you can print off the appendices referred to.

~~~~~~~~~~

Bank and Cash transactions can be recorded on the same page of a manual cash book or on the same spreadsheet.  The best way to split them is to show bank and cash receipts together, and bank and cash payments together.

INCOME

Appendix 1 shows a fairly typical layout for recording the income for a business.  It is usual for income to be split month by month, but not essential, however, it does lend itself more readily to helping you balance your bank and cash each month.

Explanations of each column header are given below.  Obviously, each page needs to be titled with the month and year in question and with the fact that it is Income that is being listed.

Date
It helps to specify the date on which amounts are received.  It also helps to list them in date order.

Detail
Here list the person, business, or other entity that has paid you the money and/or a brief explanation of what the receipt is for, if considered necessary.

Reference
This example lists the invoice numbers of the invoices being paid by the amounts received.  For amounts received that do not relate to sales invoices, the reference has been left blank and a brief description used in the ‘Detail’ column instead.

Bank
Here list all amounts received that are paid into the bank.  This column should contain all of the deposits that show up on your bank statements.

Cash
Here list all amounts that are received in cash that you do not put into your bank account.

 Once a receipt has been listed in either the Bank or Cash columns it then helps to split income into sales receipts and other receipts.

Sales receipts
List receipts here if they relate to one of your sales invoices, or some other paid work that you have done.

Other Income
List all other income here.  Your accountant will be able to analyse the items in this column over the year, and treat them accordingly in your accounts.

So, to recap, for each receipt you will need to

  • state the date it was received and provide some detail for it,
  • determine whether it should be shown as being paid into your bank account or held in your cash in hand,
  • split the overall income between whether it is a receipt generated by sales or whether it has come from elsewhere.

At the end of each month you can add up the columns to get totals for the month.  The best check to see if you’ve got the basic arithmetic right is to understand that the totals of the ‘Bank’ and ‘Cash’ columns should equal the totals of the ‘Sales Receipts’ and ‘Other Income’ columns.

 EXPENDITURE

Appendix 2 shows a fairly typical layout for recording the expenditure for a business.  Again, it is usual for expenditure to be split month by month, but not essential, however, it does lend itself more readily to helping you balance your bank and cash each month.

Explanations of each column header is given below.  Obviously, each page needs to be titled with the month and year in question and with the fact that it is Expenditure that is being listed.

Date
It helps to specify the date on which payments are made.  It also helps to list them in date order.

Detail
Here list the person, business, or other entity that you have paid the money to.

Reference
It is common for people to file their purchase invoices and receipts on a file, giving each one a reference, usually just in simple numerical order.  This example lists the given ‘number reference’ applied to each invoice/receipt.

For amounts that are paid by standing order and do not relate to a specific invoice, we suggest the abbreviation ‘SO’ be used.

Bank
Here list all amounts that are paid out of the bank.  This column should contain all of the withdrawals that show up on your bank statements.

Cash
Here list all amounts that are paid in cash and not from your bank.

Once a payment has been listed in either the Bank or Cash columns it then helps to split the expenditure into the various types of expenditure that you incur.  This is achieved by creating a series of columns with headings relevant to your business.  Appendix 2 shows common headings used.  Choose a column that best suits the expense you are ‘analysing out’ and list the amount there. 

It will also be useful, especially to your accountant, if you could give additional information for some expenses in the column to the far right.  For example, it’s great seeing that this business paid £458 to Aviva and that it is analysed under ‘Insurance’, but in order to know that the transaction is accounted for properly in your year accounts, your accountant will need to know what type of insurance it is and what period was covered by the insurance. 

This additional info isn’t vital if you give all of your purchase invoices to your accountant at the year end as they will be able to find this detail on the relevant invoice.  However, if you send your accounts records to your accountant via email, and do not send in the original documentation, then this additional information will help no end.

Again, once the month has been completed the total of the columns needs to be calculated.  And again, the sum of the ‘Bank’ and ‘Cash’ columns should equal the sum of all the other columns.

Now learn more about Bank and Cash Controls and Reconciliations.

Any feedback on the usefulness of this guide will be much appreciated.

Book Keeping – Manual Cash Book and Spreadsheets – An Introduction

Published on August 31, 2011 in Bookkeeping and Manual Cash Books and Spreadsheets. 0 Comments Tags: , , , , .

When starting in business everyone is given advice about opening a business bank account and about the kind of paperwork and documentation they should keep.  This is all very well, but once all of this paperwork starts to build up, the next question is, “What exactly should I be doing with it?”

 These guides are a tool towards helping you understand the basic principles of bookkeeping, thus ensuring that you

  • can make sense of your records so that they act as a reliable record of your business history,
  • know exactly where you stand with regards to your available cash at any one time,
  • comply with the basic requirements by law regarding business records,
  • present your business records to your accountant in a manner that will enable them to prepare your year end accounts easily, thus keeping your bill down.

First things to consider
It’s all very well devising your own system that you can follow and that gives you all the information you need throughout the year, but that doesn’t mean that it will give your accountant all of the information they will need to prepare your accounts.  Obviously, this guide is being written from an accountant’s point of view, but a system that suits you but not your accountant will only result in your accountant having to re-invent the wheel, as it were, and your accountancy bill will soar.  So it makes sense to stick to a system that suits both your needs.

Perhaps it would help if, at this point, we explain a bit more about what your accountant needs to be able to do in order to put together your figures for the year.

 Bank transactions
The first thing that your accountant will do is put together what is called a ‘bank control account’.  This means that they need to identify everything that comes into, or is withdrawn from the bank over the year, and make sure that the final balance per your records agrees with the balance on your bank statement at your year end.  If your records already provide all of this information in a manual cash book or spreadsheet, then great, but if not, then it’s a long process of trawling through your bank statements, identifying which items are recorded in your records, picking up items on the bank statements that aren’t recorded, and more than likely coming back to you with a list of queries. This is quite costly.

 Cash transactions
Your accountant will then need to try to do the same with your cash.  This is never such a definite process as preparing the bank control.  At least, when balancing your bank figures, your accountant has a third party verification of what went on at the time, in the form of your bank statements.  With your cash, there is no such back up, so once gaps in the records appear, it is almost impossible to backtrack to get to the right answer. 

Leaving your accountant in this position, results in them having to take a judgement and treating a ‘difference’ in such a way that may not be completely accurate.  A very common example of this is that small businesses often shows that cash has come in to the business with no real explanation of how it’s all been spent.  Accountants often treat this as the ‘drawings’ figure that the proprietor or director has taken to live on throughout the period.  This makes sense as business owners must live on something and often do not record drawings as, to their thinking, they are not a business expense.  The Inland Revenue (as was) used to accept this as a valid treatment at one time, but not so much anymore.  They now prefer to see all payments out recorded as they occur; failure to do this can result in questions from a tax inspector that you could well do without.

Also, it’s worth bearing in mind, that a figure such as this could hide the fact that legitimate business expenses have not been recorded and, ultimately, these won’t be able to be claimed against your profits.

The following book keeping guides are aimed at helping you find the best way to record your business activity, depending on your requirements.  More are to follow so if you don’t see one that fits the bill, keep checking, or better still, drop us an email, and let us know what you are waiting for.

Guide to Bookkeeping – Manual Cash Book and Spreadsheets – 1
   This is a guide to bookkeeping for a business that
      a) wants to record transactions on a cash basis (i.e. at the point that items are paid for, rather that the date invoices are raised)
      b) is not registered for VAT

Guide to Bookkeeping – Manual Cash Book and Spreadsheets – 2
  This is a guide to bookkeeping for a business that
      a) want to record transactions on a cash basis (i.e. at the point that items are paid for, rather that the date invoices are raised)
      b) wants to use a manual cash book, or use computer spreadsheets, such as Excel
      c) is registered for VAT, and operates its VAT on a Cash Accounting basis

Guide to Bookkeeping – Manual Cash Book and Spreadsheets – 3
  This is a guide to bookkeeping for a business that
      a) wants to use manual daybooks and cash books, or computer spreadsheets, such as Excel
      b) want to record transactions on a standard bookkeeping basis (i.e. At the date the invoices are raised rather than at the point items are paid for)
     c) is VAT registered, and operates its VAT scheme on a Standard Accounting basis

Please leave any comments below.