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

Anthony MellorOwner
CERTIFIED EXPERT
40 years working with one to two person businesses . In EE my focus is Excel bookkeeping & accounts & banking. (Not VBA)
Published:
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
A principal principle I am applying is what I call “zero base accounting” this means everything comes back to zero, and if it does not then it indicates an error state.
Expected zero results makes it a simple and clear matter to see when and where errors exist, persist flagging that further work is required.
We do NOT use extended analysis across columns such as “Motor”, “Stationery” etc, because such reports can be produced as a (pivot table) report after the data entry work is complete, if still required. In words of one syllable this means you are not allowed more columns across - on pain of a serious increase in professional fees if you do, because it wastes a lot of time and causes grave misunderstandings.
Assumptions
We can do “Invoice Accounting”, but that’s more complex and I want to demonstrate the ideas, not VAT (sales tax/gst/etc).
No VAT (sales tax/gst/etc), or if so then “cash accounting”.

For accountants: my application of debits & credits is strict, to make the final pivot table reporting work.
My use of the minus sign is counter intuitive, designed to minimise its use by applying it generally to income instead of expenditure, because expenditure has large numbers of entries whereas income does not. A high volume of minus signs reduces the readability and thus introduces more risk.
It is not necessary to understand debits & credits to be able to apply the instructions in this article, however of you do understand them you will be able to spot them working.
Imagine a spreadsheet list of bank/cash/credit card transactions with various column headings (shown below) in Row 1 from Column A across about six columns.

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).

SORT start at 1 and add one for every entry, it does not matter how often you re-number as long as when you do the original order is in place.
SOURCE - Bank, a/c number, credit card, cash or whatever is the source
DATE - the full date, 12/12/2012 , not a two digit year or any other subset.
PARTICULARS Who the payment was to/from
WHATFOR What it was for
AMOUNT Amount paid (no exceptions!)
BALANCE The running balance: a calculated column using a formula. Formulae are outside the scope of this article.

With the inclusion of the SOURCE column heading our sheet can include multiple bank and other accounts such as credit cards and paypal etc.
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)
to enter new transactions either type them in, paste them in or import them from csv.
where the sheet is already populated with data insert sufficient number of new rows after the last entry for that SOURCE. I usually insert far too many rows to provide lots of room and then delete the ones not required afterwards.

Advanced tip: 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,

Care must be exercised to ensure the original order as per the source records is maintained for each source: this is what the SORT column is for (for techies: we use it like a database index field, but without binary searches and with the ability to rewrite it edit it and so on.)

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:
 
(a) the records are usually static and
(b) incomplete being missing all the private stuff which is the first thing tax authorities ask for
(c) as well as sufficient detail and the ability to report (answer questions).

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
2
4,728 Views
Anthony MellorOwner
CERTIFIED EXPERT
40 years working with one to two person businesses . In EE my focus is Excel bookkeeping & accounts & banking. (Not VBA)

Comments (5)

CERTIFIED EXPERT
Author of the Year 2011
Top Expert 2006

Commented:
As with your earlier EE Article, this one can be useful in helping us through not only tax time, but keeping our numbers straight throughout the year.

Thank you for submitting it.

"Yes" vote above.
CERTIFIED EXPERT

Author

Commented:
and THANKS for the yes vote!
tigermattStaff Platform Engineer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
"YES" vote above. Very nice article. Thanks for taking the time to submit it!

-Matt
Keith AlabasterEnterprise Architect
CERTIFIED EXPERT
Top Expert 2008

Commented:
Yes for me
CERTIFIED EXPERT

Author

Commented:
thanks guys, very welcome encouragement. I am back here to write a follow on where someone already has the usual spreadsheet I see year after year and is wondering how to convert it to the above concept without having to re-enter a year's worth (or whatever) of data.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.