Please read about the paper system before reading this page. It is suggested that you practice with the paper-based system for at least a couple of weeks before re-entering everything on the spreadsheet system. Essentially all you will be doing is making a copy of the paper system on a spreadsheet with some assistance from the computer.
When you start up the spreadsheet, you will be warned that the spreadsheet contains macros, which indeed it does. You may run the spreadsheet with macros disabled if you wish, and it is entirely useable just like the paper system. However you will get the most benefit if you allow macros to be enabled, and here it is assumed that you have done this.
On the first page of the spreadsheet, please enter your business name, the period end date as date/month/year, and the opening cash and bank balances. The start of period date is the date of the first day of the week immediately preceding the start of the period, but you can change this later. The VAT rates are locked so you cannot change them easily.
Looking through the cash and bank pages, you will see that everything is marked up so the business name and week starting date appear on every page. The closing cash and bank balances are propagated through the spreadsheet, but of course they will change in typical spreadsheet fashion as you make new entries. Here is a full size cash page:
Before continuing with this guide, you might like to watch videos of the spreadsheet being used if you have not seen them already. You may see data being entered by the row if you click here. You may also see data being entered by the column if you click here. This spreadsheet has features which are intended to be helpful and obvious.
On the first cash page, just type in the dates as the day number, and the month and year will be autocompleted for you since they are obvious from the context. To enter the description “Takings” just type T and this will also be autocompleted. If you try typing C then this will be autocompleted as “Capital Introduced”. Typing B will be autocompleted as Banking, while typing W will be autocompleted as Withdrawal. Note that since bankings and withdrawals are internal transfers, they will be shown in italics and in colour to indicate their special status.
Then type in the number as Cash in or Cash out. As each number is typed in, the predicted cash balance is automatically updated to match. When you are finished with a cash page, count the contents of the cash box, and if different then enter the actual cash counted in the blue box at bottom right. Next week the spreadsheet will automatically pick up the actual cash counted if a figure has been entered.
Here is a full size bank page:
On the bank pages, the Balance or running total will be worked out for you and you cannot overtype it. All you need to do is to produce something which looks like a facsimile of the bank statement. Several bank statements in one month should be spread over several successive bank pages.
You will often see Excel’s autocomplete system helping out with descriptions. However, we would warn you that if you type in the narrative “Bank charges”, then this will tend to spoil the usefulness of being able to type B for Banking as well because the autocomplete system cannot help until it is clear what you intend to type. It is suggested that to enter a bank charge, you start typing “se…” to get “Service Charge (Bank Charge)” as the description.
VAT can be entered just by typing it in the box. Output tax will be shown automatically as a bold green positive amount. Input tax will be shown as a red negative amount. There is no need to type in a minus sign, and it will just be ignored if you do. Alternatively, you may enter VAT amounts by clicking on a button at the bottom right hand side of the screen. If you click on the VAT at 20.0% button for an amount of £100, you will see the figure 16.67 appear in the VAT column and the VAT rate will be shown as 20.0%. This signifies that the net value before VAT is £83.33, and £16.67 divided by £83.33 is 20%. If you happen to be looking at an invoice where the VAT amount is shown as 16.68 or 16.66, you can adjust the VAT amount with the VAT + 1p and VAT – 1p buttons. There is a VAT at 5% button as well if you have an invoice with this rate of VAT on it.
These buttons also appear at the bottom right of the bank pages. In addition there are some buttons to enable bank transactions to be moved around. The objective of the bank pages, as we said, is to produce a facsimile of the bank statements. However, if you write a few large cheques, you might want to know at once how much is left in your bank account. You can enter the cheques as you write them, and you will get an idea. When the new bank statement arrives, you can use the Up and Down buttons to move cheque payments to roughly where they appear on the bank statement, and then to enter intermediate transactions such as bank charges and standing orders so that you end up with a facsimile of the bank statement.
Sometimes a cheque will not get cashed by the payee for a while, and there is a Next page button to move the cheque to the next page in anticipation of the bank statement to be received next month. Such cheques are known as unpresented cheques or uncollected cheques. As well as the usual bank pages, there is a Bank Extra page to hold cheques still unpresented at the end of the quarter.
Likewise, if you pay in cheques or credit card vouchers, then these might not yet appear on the bank statement before the end of the quarter, and these items should also be moved onto the Bank Extra page using the Next page button. Such items are known by the rather old-fashioned name of outstanding lodgements and they are a fact of life. If you change your mind at any time about using the Next page button, then there is a Previous page button to reverse its effect.
You may decide not to record bank transactions until you see the bank statement, or you may provisionally enter cheque payments and then delete them, or you may enter cheque payments and then move them around. You have plenty of choice. The end result should be a series of bank pages which look identical to the bank statements with matching running totals, with unreconciled or leftover items parked on the Bank Extra page.
After entering transactions you will need to save the spreadsheet. If you try to exit without saving it, then you will get a reminder. You should save it or you will lose your work. If you print off the spreadsheet, then each page will print to fit a standard A4 page.
At the end of the quarter, just e-mail the spreadsheet to us. We will read it into our main processing system and use it to generate a VAT return. You will be sent an approval copy of the return, with lists of outputs and inputs, before submission. A check will be made to see if it is more beneficial for you to use the Flat Rate Scheme if this is possible based on your turnover. We will also store everything in readiness for the annual accounts.
When cheque payments are recorded, you should take the name of the payee from the cheque book stub as the description. It is also possible that you will have made some direct transfers from your bank account, where again the payee’s name should appear. Many payees will have charged you some input VAT which you can enter from the invoices they send you. Note that you do normally need an invoice as a basis for reclaiming input VAT. Often you can enter the input VAT from the invoice using the VAT at 20% button followed by the VAT + 1p or VAT – 1p buttons to make adjustments.
As you use this spreadsheet, you will see that you can only move the cursor onto cells where data entry is possible. The rest of the spreadsheet is deliberately made inaccessible so that you cannot accidentally delete an essential formula. The spreadsheet continually presents a running total, a closing balance, or a VAT percentage, so it is self-checking at every stage. The spreadsheet prints true if you want to print it out. Being in a standard format, the spreadsheet is readable by an automatic system such as we use. Many people devise their own spreadsheet systems of varying levels of quality. Well, you might as well use ours since it costs nothing extra if you engage us as your accountants, and in fact you get a discount to reflect the merits of our spreadsheet.
This bookkeeping spreadsheet does not produce invoices and statements. If you have a requirement to produce invoices, then we suggest that you read what we say at
http://www.deadlineadvisor.co.uk/debt-collection
HOME PAPER SYSTEM TOP OF PAGE PARALLEL WORKING WHAT WE DO QUICK DATA ENTRY