This Content Was Last Updated on March 22, 2023 by Jessica Garbett
This spreadsheet is intended to form the backbone of a simple transaction recording system for a small company.
Its use is not compulsory, but is strongly recommended as (a) it helps us to give you the best possible service and (b) it provides a solid base for you to
keep on top of your business finances. It is also fully supported by us.
The spreadsheet contains the following parts:
- Company information
- VAT summaries and rates
- Five quarterly worksheets
- Planning questionnaire
- Year end questionnaire
- Performance summary
- Whitefield internal use page
The intention is that clients should maintain this workbook and e-mail it to us either annually or, if preferred, each quarter so we can then make any amendments and return it to you prior to you filing your vat return. If there are no amendments, then we will e-mail you a confirmation that all is in order,
The cycle each quarter is therefore:
- You complete the sheet
- E-mail to us if you wish
- If sent to use we check/correct, e-mail back if necessary
- Where vat registered you file your return
Important: After it has been e-mailed to us for checking/amendments, and before it is returned to you, it should not be further updated or changed by yourself, as these changes will, of course, not be reflected in the version we hold. When the document is e-mailed to us we endeavour to return it as soon as possible, bearing in mind work flow in our office. Once we have confirmed the completeness of the sheet please do not make any retrospective changes to it. Any late discoveries of errors/omissions can be corrected in the next quarter sheet.
File names: When sending your spreadsheet to us, can you please use a file name of:
wherennn = your client number (a three or four digit number on all our
correspondence with you – ask us if you are not sure)
yyyy = the relevant tax/accounts year, eg 1213 for 2012/2013
It is very important that this naming format is followed, else we find our selves deluged with helpfully named spreadsheets like “accounts.xls” and no immediate identifier for them!
If you wish us to check your vat we suggest you complete the sheet for the quarter, e-mail the whole document to us, and then wait for us to return it with amendments (or a confirmation of no amendments needed). Once you have the sheet back you can then complete your vat return using the vat summary provided and make the applicable payment.
This sheet is structured to work on cash accounting for vat. If you have previously used invoice accounting then a adjustment will be needed in the quarter of change – let us know and we will enter this for you. Please be aware that online VAT returns must be submitted by the 7th of the next but one month (eg 31 March qtr end is due by 7 May). Although we try to process all vat paperwork as soon as possible, we do have a peak of paperwork at each quarter end. It therefore assists us if you can have the sheet to us within 10 days of the quarter end (unless we quote a different checking regime e.g. when we have staff holidays).
The vat summaries and rates tab can be used to specify normal vat, flat rate VAT or will turn off vat if you are not registered.
TAB: COMPANY INFORMATION
This section allows you to enter basic information on the company name, contact details and the date ranges for each quarter.
For the quarter date ranges any date format is acceptable so long as it is consistent:
eg AMJ 12, JAS 12 or, if preferred,
1 April 2012 to 30 June 2012, 1 July to 30 September 2012, or
April, May, June 2012, July, August, September 2012, etc
The information entered here replicates throughout the workbook.
TAB: VAT SUMMARIES AND RATES
This tab provides an at a glance quarterly vat summary, along with a % vat to sales figure enabling an assessment of whether the flat rate scheme would be better for you.
See our VAT guide for more information about Flat Rate
Important: You must apply to HMRC before changing to or from the FRS – you cannot do it of your own volition.
Against vat status (greyed boxes) enter Y for normal vat, F for Flat Rate Scheme (and enter percentage below) or N for not vat registered.
TAB: QUARTER 1 THROUGH QUARTER 5
These are the main areas for data entry, intended to be a sheet for each quarter.
NB: five are provided to allow for in year changes of vat quarter or a “long” first accounts year – most clients however will not need to use sheet 5.
The first section of the sheet is contact details, replicated from the company information tab, and contract details for you to fill in (where, agent, client, sites, dates, etc). This is a quick reference for future use.
Balance at start of quarter should be self evident, and is a carry forward on columns d, f and g from the previous quarter (and is formulated as such for q2, 3, 4 and 5).
The income and expenses sections are for recording of income – columns are:
– a Date
– b Payer/payee details
- c Cheque number/reference (broadly what ever is most
convenient for you)
– d,e,f,g Account source columns – business current account,
cash/personal account (including personal credit card), business credit card, business deposit accounts (headings can be changed to suit individual requirements)
– h,i,j,k VAT content column (VAT is automatically calculated from the drop down selection in the vat analysis column K)
– l Details column – for extra details and information.
NB columns H/I/J formulate automatically and should not be written over.
NB use the drop down sections from column K to select analysis of each transaction. If the drop down box becomes corrupt, please send to us for correction. (If you are not using excel, these may not work, in which case unhide the hidden rows at the foot of the page and copy the descriptions from there).
Income should be entered as a positive, expenses as a negative. If you have a negative income item, eg sales refund, or a negative expense item, eg a refund from a supplier, then it does not matter greatly whether it goes into income or expenses section, so long as the positive/negative aspect is correct.
Extra rows can easily be inserted using the inset function on your spreadsheet. You should insert them above the end of section marker, and then copy formats down using either ctrl-C or, in Excel, Edit|paste special|formula. If formats are not copied when inserting rows then you will lose the automatic vat calculation functions.
VAT is calculated automatically from the information in the vat analysis column (k). If the calculated vat is slightly different to the vat on the invoice/receipt you are entering then it is suggested you leave the calculated amount unchanged. If the difference is significant, then override the calculated vat in column (k).
The vat status set on the vat summaries and rates tab determines whether normal vat is applied, flat rate vat or no vat (not registered).
Use of credit cards: A personal credit card is one in your own name, normally settled from personal funds (or from business funds but with a mixture of private and business items thereon). Settlement of a personal credit card should be coded to directors account. A business credit card is one in the name of the business, usually settled by direct debit to the business account, and used mainly for business items. Settlement should be shown by inter account transfer so that the settlement balances transactions entered (possibly including a previous months transactions). If you are unsure whether a credit card is business or personal then it does not make a great deal of difference so long as the treatment is consistent each quarter – if a card is treated as business, then all transactions, including private items and card charges must be entered, whereas if a card is private then only transactions being claimed for tax need to be entered.
Error trapping: – the spreadsheet has inbuilt error traps to identify if a calculation goes wrong – normally because a formula has been deleted in error. When an error message comes up in the error box, it should be easy enough to correct, but if you are unsure e-mail a copy to us and we will advise.
Transfers between accounts: eg from current account to deposit, or from current account to settle business credit card are entered as a positive in one of the source columns and a negative in another, to balance. This means the entries are in columns d, e, f or g, and do not impact on columns H/I/J/K.
Auto-calculation: entries for mileage and nights away are automatically entered at the last line of the expense section.
Balance at end of quarter: is an auto calculation. For columns d, f and g a comparison to statement is required to ensure that all entries are included.
Ideally you should arrange for your bank/business credit card statements to be made up to the quarter end, and the resultant statement balances entered. The statement balances should match the calculated balance, else an error will be shown.
However if your statements do not tie up with the quarter end, it is suggested that you take the nearest statement and add back or on adjusting items as necessary to give an adjusted quarter end balance, and enter that here.
It is very important that the statement balances are matched to the balances on the spreadsheet, else the integrity of your accounts is wholly lost.
NB the intention is this sheet mirrors the bank statements – if you are used to an accounting system where outstanding items are accounted for by date, then note that on this system no such adjustments are made.
Mileage etc: is a section for entering car mileage (two cars are provided for), cycle and motorcycle mileage, car passenger miles (each passenger other than the driver) and nights away (UK and non UK).
For the car and motorcycle mileage there is the option to select the engine size/fuel type for a vat claim on fuel at approved rates, using the drop down box.
NB if you have mileage reimbursed to you by a client, then this should be included in sales income in the same way as your main contract/professional service income. The total business mileage for the quarter, reimbursed and non reimbursed should then be claimed in this section.
VAT return: is a mirror of the entries to go on your vat return. Once your spreadsheet has been checked/amended by us, this data should be transferred to your vat return and the appropriate payment made.
NB If you ever need to correct an error after a quarters vat has been calculated, this should be done by way of a reversing entry in the next quarter. You should never change a quarter after the vat return has been done.
TAB: YEAR END INFORMATION
This page is designed for use at your year end to let us have the annual information we need to complete your year end accounts.
It does not need to be completed each quarter.
TAB: PLANNING QUESTIONNAIRE
This page is a pre year end questionnaire, designed to help us review your taxes every Spring. Please send it to us after completion of the December quarter end.
It does not need to be completed each quarter.
TAB: PERFORMANCE SUMMARY
This section is designed to give a summary of profit and loss performance and movements on the directors account during the year.
There are various greyed sections which require user input – each section has a pop up note to advise what is needed. If the quarterly sheet entries are maintained correctly, the performance summary will provide an estimate of the accruing corporation tax liability.
Three examples are given – normal vat, flat rate and not vat registered, all on the same data.
TAB:WHITEFIELD OFFICE USE
This tab is for office use by Whitefield, relating to logging use of the sheet.