Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Bookkeeping & Excel: Debits & Credits and the 5 Column Cashbook
Also for Pivot Table reporting
By an ancient accountant
1. Why this subject?
The “Three Column Cashbook” is famous (if you have an accountant’s background) and the concept of the number of columns can, in fact, be expanded as desired. This makes a very old concept particularly apt for a very new technology: the pivot table.
In addition, this demonstrates Debits and Credits in action for any student wanting to literally see how they work or test their understanding.
2. Who is this for?
This is for people:
- who want to understand debits and credits; perhaps students who need to understand this apparently black art
- who are at home using at least the basics of Excel and are curious how Debits and Credits are still relevant
I ASSUME the reader knows the theory of double entry, even if you do not understand it. Google it if not. This article is practical, not theoretical, but here is the shortest possible reminder:
Debit: an increase in an asset or a reduction of a liability
Credit: an increase in a liability or a reduction of an asset.
3. Where? Country:
This will work for anyone anywhere, written in English. Any spreadsheet can do this: pivot tables are not obligatory.
4. What you need to know:
Ledgers and what they are for - though it may be self-explanatory. They are, after all, just virtual books.
You need very little spreadsheet knowledge, though inserting and deleting rows and columns and knowing how NOT to break a table range may be advantages if you start playing with the attached work file. Understanding range names help but is not fundamental. There is one named range called DATA, delete it if you wish.
Note: You break a table range if you add a column or row off the end of the existing range because those are not already part of the range. Microsoft has tried to add assistance for this. It's easy enough to fix, just reset the table range coordinates.
What you do need to know is what debits and credits (are supposed to) mean and at least some theory about what they are for, because this article demonstrates the theory in practice, rather than attempting to explain it. Granted it is simplified practice, for example, transactions have no dates because this is not relevant to these basics, neither is sales tax (such as VAT).
I challenge the programming world that thinks it knows accounting better than accountants. Obscuring half the world of debits and credits from users means not that software simplifies, but that it obscures, such that users become dependent on software and literally do not know what they are doing, which leads to misunderstood results.
INTRODUCTION OF CONCEPTS & ASSUMPTIONS
Debits and Credits as invented 1494 - hundreds of years ago, long before modern computers.
That when you see debits and credits you do not glaze over immediately and that you are willing to puzzle out what you see because when you figure out the puzzle you will have understood and be able to create your own, which will be a proper bookkeeping record you could develop anywhere.
So here we go.
It is as simple as this, just one little screen covers all the basics:
Before reading on, spend a little time to see if you understand what the screenshot is showing you, for it is the 5 Column Cashbook, reduced to a minimum so as to illustrate its necessities not least also to allow pivot table reporting.
To explain I will discuss the layout and then each column.
On the left are the text columns used to describe the data in various ways. OPEN means an opening balance b/d, similarly CLOSE is a balance c/d or c/f.
On the right are the numbers columns used to hold the debits and credits, suitably annotated as such and NOT as plusses and minuses.
There is no such thing as a “minus credit” or a “plus debit”, yet I understand that might be being a bit too esoteric so Credits are all in red, which if you really must you can imagine are negatives for the purpose of the additions/balancing. This is where we differ with the programming community and binary thought - though granted a debit and a credit ARE binary! Not surprising then that that discussion can ebb and flow.
On the extreme right are two memoranda columns that are used to note discounts allowed or received. Not mandatory, but useful in real life when reconciling ledger totals accounts. You can ignore them for the sake of simplicity if you wish. I have put them there in case future discussion requires them when reconciliations are in hand, involving much larger volumes of data than we have here.
TRX - Transaction Index serial numbers, unique, one per entry. Needed in some pivot tables or when using the sort commands to return to original order.
PARTICULARS is for the name of the supplier or customer for example.
FOLIO is an old-fashioned word and refers to the page in another ledger (e.g purchase/payables or sales/receivables ledger) I use it here to enter a codified version of the ledger. So, for example, FRED is the name of Frederick's account. Because we are using a computer we no longer need an actual page in the ledger because we can use Excel to gather together all FRED’s transactions into a summary that reproduces what would have been entered into Frederick's paper ledger pages. Auto Filter is of course “the trick” or a pivot table to reproduce traditional reports.
LEDGER is the name of the “book of account” where we used to have actual paper books for the sales ledger, the purchases ledger, the sales day book, the purchases day book, the cashbook (misleadingly being bank and cash) and these days we could easily add a “credit card account ledger”, though being more modern items they do not have a history, yet can be treated just like a bank account, merely an extra column headed perhaps AMEX for example. In other words, the Ledger column is the transaction SOURCE Column.
NLCODE is the Nominal Ledger Code for the journal entry that would be made to the Nominal Ledger. You may observe the column looks like it contains ALL the Nominal Ledger entries, but there is a trick and it does not. Entries between the other ledgers are missing because they are present in those other ledgers. For example look at entries that appear in both the BANK and PLC columns. See how they cancel out and only any difference appears in the NL-JNL column? In the full ledger either both those entries would be recorded or none at all and only the periodic summary totals (from total Control Accounts). So understanding this I had to call it something that is not simply “NL” (sometimes called the GL or General Ledger).
Cash-Jnl Amounts paid or received by cash.
Bank-Jnl Amounts paid or received by bank.
PLC-Jnl Purchase Ledger Control entries - including Purchase Day Book entries. A Day Book entry example is an invoice, whereas a Ledger Control entry is Discount Received. In the old days these were separate books, but now we have Excel and we can use its powers of a summary to reproduce the old books from one record.
SLC-Jnl Sales Ledger Control entries - including Sales Day Book entries. Same as above PLC-Jnl.
NL-Jnl Nominal Ledger Journal entries - this column is automatic (=-SUM) based on entries in the above four columns, hence the 5 column Cashbook. It is this column that software "hides", and then uses it for prime reporting.
To repeat: On the extreme right are two memoranda columns that are used to note discounts allowed or received. Not mandatory, but useful in real life when reconciling ledger total accounts. You can ignore them for the sake of simplicity if you wish. I have put them there in case future discussion requires them when reconciliations are in hand, involving much larger volumes of data than we have here.
The TOTALS row contains this formula (Row 22): =SUBTOTAL(9,F2:F21) so totals each column and if auto filter is in use shows the filtered totals. The "9" means "include in the Subtotal SUM only what we can see on screen". Roughly.
This row is NOT part of the table area and not included in pivot table reports. A Grand Total row is optional.
The area of the data is Insert Name Define-name as “DATA” and currently covers: =Records!$A$1:$J$21 So K and L are excluded but can be included if desired.
Pivot NL-Jnl sheet contains a pivot table based on the NL-Jnl column. Note that only the final Grand Total column is expected to sum to zero whereas the other columns may (will) contain what amount to totals control account entries that would traditionally have been entered in the Nominal Ledger total control accounts. In other words, this pivot table is an introduction to total control accounts. If you understand this then you have understood the purpose of this article.
The spreadsheet file attached can be used for real life bookkeeping in a simple business without sales taxes (VAT). How large you make it is up to you. In theory, you could run a business with many thousands of transactions, include sales tax (VAT) and report to trial balance. You will at that point have a simple large spreadsheet. I say simple because while the data may be viewed as complex, it will contain no more than one column of SUM equations and maybe a vat calc column. To make myself clear, what you will not have is a complex large spreadsheet which is arguably not a good thing. Large and simple is good, small and complex is good, but not large and complex.
We avoid complexity by using Sorting, Filtering and Pivot Tables. The TEXT columns I describe above are also in pivot table terms “helper columns”. You can add as many as you like and report as many pivot tables as you like.
Once you grasp debts and credits the sky is the limit on your spreadsheet bookkeeping.
It may need to be large, but it does not need to be complex in spreadsheet terms (i.e complex formulae and the like).
SOME BASIC REMINDERS:
Accuracy is paramount, always balance and control so that you are not exposed to the accusation that spreadsheets are prone to error and inaccuracy; use proper accountancy techniques and you will be as bulletproof as we were before computers existed. Also, use proper spreadsheet techniques such as =round(A1,2) and never rely on display amounts without checking them. Be sure that the sum of a column does, in fact, add up to what the displayed total says it is. Cross cast everything!
Clearly, I have written far less than more. I view this content a “baby step” and yet for those who do not yet understand, a “giant leap” for when the light dawns, all is revealed. Enjoy.
This isn’t about Excel expertise, it is about using Excel for one of its primary purposes, which is the making of lists and then engaging the real power with Pivot Tables (another article of which there are many) and Sorting and Filtering. You might think bookkeeping is just like shopping lists and you would be right.
Questions welcome and if you have feedback let me know and I will consider how to improve this article.
Dare I say it is much harder to write when you already know the answer.
When I learned Debits and Credits the computer did not exist and it was all handwritten on paper so this is my little offering to TNG.
May you always be in balance and reconciled.
p.s the inspiration for this article was an AAT exam test I did the other day to see what sort of thing they ask.