Banking, Bookkeeping & Excel - in practice, lesson 1. Excel skill level: everyone. Accounts level: novice

By an accountant

1. Why this subject?

 Most Accountants "speak spreadsheet": it's a common language you can use to talk with us. If you can take on board our "dialect", the "way we do things" so much the better. It's not complicated, in fact typically more simple than clients use for themselves, but it does have a few layout rules. Given the chance we can show a client how to better manage practically everything.

2. Who is this for?

I prefer clients who do bookkeeping by spreadsheet, which means 60% of small businesses. Baby steps lead to giant leaps.

3. Where? Country:

This will work for anyone anywhere, but the occasional VAT/Sales tax comments are taken from UK rules, though I reckon the principles apply everywhere, and I exclude delving into details so as to maintain the general bookkeeping applicability for everyone.

4. What you need to know:

What follows is my attempt to encapsulate some practical ideas I use while introducing the application of some layout concepts. All this is all about is entering a few column headings and then entering some data under each one according to some definitions which I have called "The Rules". remember I am an accountant so the way I write reflects this. If you wrote this yourself, it would read differently yet have the same information. So let us see if we can understand one another.  

MOTIVATION

Over the years I have learned that teaching this material is more of a challenge than I at first thought so with that in mind I have to limit myself to the subject and not enter too wide an arena. Suffice it to say I am attempting to draw together the accounting principles of debits & credits with the basic requirements necessary to allow the use of pivot table reporting in Excel - I use these techniques every working day, all day. Whether my writing manages to communicate effectively you will have to be the judge. Pivot table reporting itself is NOT part of this article: perhaps a later one. Indeed once entered you could report on the data in a number of other ways: extensive use of SUMIF() being just one.

INTRODUCTION OF CONCEPTS & ASSUMPTIONS

Concepts
Assumptions


Here we go, time to type.

TYPE THE FOLLOWING COLUMN HEADINGS IN TO A BLANK SHEET

Start at A1 and move across heading by heading (or field by field if your mind works that way).


If you are familiar with pivot tables you can probably see where all this is going.

and now, as I mentioned at the beginning of this article

THE RULES

1.

DATA ENTRY Entering transactions (a book subject in itself)

The reason for maintaining the order per the source records is document audit trail and control – HMRC (tax authorities)  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. This is especially true when VAT is involved because the law requires underlying records to match VAT Returns, with associated document numbers: so we want no duplication of records while keeping our flexibility of reporting for multiple purposes.

[step="2 " title=""] SOURCES SEPARATION (i.e. Which account is which) For starting and ending balances I enter the words “OPEN” and "CLOSE" in the WHATFOR column, only ONCE per account as shown in the SOURCE column, which describes the sources: e.g. RBS12345678. The BALANCE column starts and stops summing the AMOUNT column  based on the presence of "Open" (start summing) and "Close" (stop summing) in the WHATFOR column.  Naturally, but with stating: "Open" and "Close" must be the first and last entry for the related source so as to separate the different sources (banks, cards, cash, paypal). The final BALANCE on the row where "Close" is entered in the WHATFOR column must equal zero if there are no errors.

Finally the "Close" balance must always be entered in the AMOUNT column the opposite way round to its expected sign so that the BALANCE column will show the "Close" balance as zero and the Grand Total of the AMOUNT column will be zero. This is one of the "tricks" - and a trap if you forget it.

The above is the same as they would be if you had a separate sheet for each source (bank account or credit card etc). "Open" and "Close" are key words used by the formula in the BALANCE column to differentiate between start and end of sources' transactions. If you understand this you can pretty much stop reading here and give it a try.

If the word “open” is missing, the BALANCE 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 BALANCE column, because it MUST = zero after the last transaction for each source.

Exception: there no transactions and/or open and close balances are zero.This is required where the BALANCE column contains an IF formula that looks for the open/close "key" words: there are other ways of doing this e.g. SUMIF(), but that gives out of context answers when the balance column is not relevant (in different sort orders). Sorting is outside the scope of this article.[/step]

To recap: remember the rule here is to ensure the first and last transactions for each SOURCE are respectively the opening and closing balances for that source, with the closing balance in the AMOUNT column having its sign reversed so the running BALANCE column drops to zero - then you know it is correct because the closing balance is hard typed in as if it were itself a transaction: subtract the closing balance from the running balance and it has to be zero. If it is not zero, you have errors to find to the value of the non zero amount.

3.

– 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). Sorting is outside the scope of this article.
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  refresh the SORT column's numbers when all data entry is complete and I know the data is in the correct sort order. I usually leave at least a copy of the most recent previous version of the SORT column available (e.g. SORT2) 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.

CONCLUSION

I wonder if the above looks complicated. I hope you will find that the reality of the spreadsheeting involved is not complex (much easier to do than describe)  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 - you just need to be given a clue and away you go.

PROTECT THE FUTURE

I am offering these ideas as a means of “on the job” training which can be useful to you in perpetuity. Once you have the above set(s) of transactions (all it amounts to is some lists) one 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 tax enquiries at any time in the future (this last being where huge costs are usually incurred) because:
 

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/matrimonial/alimony/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 (tax authority) has always been satisfied when making enquiries for data and records.

Regards,

Anthony

Chartered Accountant
United Kingdom