I prefer clients who do book-keeping by spreadsheet, as do 60% of small businesses. Accounting is a language I understand and the spreadsheet is a common language I can use to communicate with and show a client how to better manage practically everything. Baby steps lead to giant leaps.
What follows encapsulates some ideas I use and is the introduction and application of some concepts. As such I have to limit the text to the subject and not enter too wide an arena.
So then, some “rules of the game”:
The principle in play is “zero base control accounting” this means everything comes back to zero, otherwise it indicates an error state. This makes it a simple and clear matter to see when and where errors exist, persist and show further work is required. Assume no VAT, or if so then “cash accounting”. We can do “Invoice Accounting”, but that’s more complex and I want to demonstrate the ideas, not VAT.
Assume a spreadsheet list of transactions with various columns as needed, covering multiple bank and other accounts such as credit cards and paypal. We do NOT use extended analysis across columns such as “Motor”, “Stationery” etc, that can be produced as a report after the data entry work is complete, if required.
0 – to enter new transactions, insert sufficient number of new rows after the last entry for that source. I usually insert too many rows and then delete the ones not required afterwards. This same effect can be achieved by entering transactions on the end of the data set and then using the sorting (ordering) features of Excel, but great care must be exercised to ensure the original order as per the source records is maintained for each source (when entered separately I have in fact used this technique to collect new entries together with the earlier entries so each source has all its transactions together. Data downloaded can be copied and pasted in to avoid excess typing.
The reason for maintaining the order per the source records is audit trail and control – HMRC may wish to check the records match the sources and we may need to do the same for any number of reasons, for example to find a transaction to be requested from the bank, to correct an error or check our work in the last resort. This is especially true when VAT is involved because the law requires underlying records to match VAT Returns, with associated document numbers: so then we want no duplication of records while keeping our flexibility of reporting for multiple purposes.
1 – I enter the words “close” and “open” in a suitable code or description column, only ONCE per account as defined in the SOURCE column, which describes the sources: e.g. RBS12345678.
The CALC column is a running balance column. The AMOUNT column contains the Gross amount of the transaction.
They must be the first and last entry for the related source.
The Close balance must always be entered the opposite way round to its expected sign so that the CALC column will show the Close balance as zero and the Grand Total of the AMOUNT column will be zero.
The above is the same as they would be if you had a separate sheet for each source (bank account or credit card say). These key words are used by the formula in the CALC column to differentiate between start and end of sources.
If the word “open” is missing, the CALC will not know a different source has commenced.
If the word “close” is missing, the balance at the end of that source will be wrong in the CALC column, because it MUST = zero. Exception: no transactions and/or open and close balances are zero.
2 – to enter a fresh balance you have two choices:
a – copy the previous “close” row to the end of your transactions and delete the original: enter new balance
b – change the old “close” row’s sort number to be the last in the series of transactions you have entered.
3 – given a correctly entered open, close and series of transactions, the calculated balance shown in the CALC column MUST = zero . If it does not there is an entry error or missing transactions.
4 – A combination of the SOURCE column and the SORT column is used to keep all transactions both together by source and in the exact order they appear in that source’s records (statements).
It MUST always be possible to sort (some people call this “ordering”) the data and return it to its original state; this is why I create a fresh SORT column when all data entry is complete and I know the data is in the correct sort order. I usually leave at least the most recent previous version of the SORT column available in case there is some error in the new order. My current SORT column is ALWAYS headed SORT, is the leftmost column and with any earlier versions to its right with name changed. It takes less time to do than read this paragraph. Mac users can use “List Manager” to great effect.
5 – the grand total of the AMOUNT column covering all sources, must always = zero.
I wonder if the above looks complicated. I hope you will find that the reality of the spreadsheeting involved is really quite simple while offering you complete control over your records. I grant some practice may be required to achieve proficiency with the ideas, but I am thinking that experienced spreadsheet users may have no trouble at all.
I am offering this to you as a means of “on the job” training which can be useful to you in perpetuity. Once you have the above we can introduce reporting that can be based on it, which then has endless possibilities for very little effort whether for your personal finances, business tax reporting or HMRC enquires at any time in the future (this last being where huge costs are usually incurred) because (a) the records are usually static and (b) the records are usually incomplete being missing all the private stuff as well as sufficient detail and the ability to report (answer questions) on it. If you do not want to show your accountant your private records you merely give them an extract.
I hope it is apparent this basic spreadsheet work is an investment in your life in a number of very different ways, both past (accounts/tax), present (personal finances/tax) and future (enquiries/business/banking/loan raising reports/sale of business). All year’s spreadsheets can be combined for five year reporting if required, making due diligence and audit records production a matter of ease.
I have used these techniques since 1984 (the PC was invented in 1980) and HMRC has always been satisfied when making enquiries for data and records.
Regards,
Anthony
