Bookkeeping with Excel, “how much is mine?” - Pivot Table books
40 years working with one to two-person businesses. In EE my focus is Excel bookkeeping & accounts & banking. (No VBA)
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - so here I do it with Pivot Tables. This article is intended as a teaching and/or self-learning tool that can be used in real life for a small business.
Bookkeeping with Excel: Pivot Table books
By an ancient accountant
1. Why this subject?
“Spreadsheets for accounting and bookkeeping” are getting a lot of bad press. All of it that I see is perpetrated by those who either or both are trying to sell their own software and/or have no idea how to use a spreadsheet properly for bookkeeping, so their work and their claims are all about spreadsheets being full of errors (I.e. “error-prone” or “not fit for purpose”). I beg to differ.
I grew up (meaning my early career) with no computers at all, we had only a pencil and paper and I mean literally. “Final” reports were typed from handwritten documents (in my case written by me) and then “called over” which means two people checked the typed version by one reading aloud the handwritten original and the other reading and initialing the typed version to give it the “ok”. Multiple copies having been produced by using carbon paper. The clean and lovely top copy being for the client, the second for the tax office and the third for our (paper!) files.
Typists were highly skilled and very fast. My point being that pencil and paper was never challenged as unreliable or not fit for purpose and since a spreadsheet is a facsimile of pencil and paper (and eraser!), done right, neither should it be so. Pencil and paper demanded certain skill and so does a spreadsheet - something I think is missed, using Excel cannot imbue skills a person does not have. So let's acquire them, the only requirement is patience.
In addition, this demonstrates Debits and Credits in action for any student wanting to literally see how they work or test their understanding - but in this case, they are implied and not explicit, so perhaps not for beginners in that regard, see my “5 column cashbook” article for that.
2. Who is this for?
This is for people with spreadsheet skill, but not necessarily yet book-keeping skills, which with this “starter template” will give you the lists headings and a few fairly simple calculations you need to get going. When you have the hang of that it will also pivot table report anything you need, including cashflow management so you can see “how much is mine” in real time. No need to wait for annual, quarterly or any other periodic reports that are always out of date by the time you see them. This is real-time business management; all that is required is some patience at the beginning, then it will operate faster and faster as you understand and practice.
This file will best suit spreadsheet aficionados, those who are fast with the Excel keyboard, but those who wish to learn should catch on soon enough with some patience.
WHO WANT TO CREATE BOOKKEEPING RECORDS and to REPORT FROM THEM
And by the by also:
- who want to understand debits and credits; perhaps students who need to understand this apparently black art and have some understanding already, this is the next step from theory into practice.
- who are at home using at least the basics of Excel and are curious how Debits and Credits are still relevant
Used with ultimate skill this file could produce and hold many complete years of bookkeeping data and produce reports on current and historic data simultaneously. Perhaps that is for a later article, just that at this point you are aware you do NOT have to switch to a fresh file every change of financial year - note the ACCYEAR and TAXYEAR list headings, this is where they come in.
It is intentional that as much of the data to input is "manual", to promote thought about how to derive the content for each column (much of which is optional). However, you could use lookup tables and other complex formulae across much of it - perhaps nearly every column, but every one of those could be an article subject in itself. For example to automate the "coding" (which means "categories" in this context, not programming).
Indeed if you were to use VBA this could be the basis of a full-blown accounting system, which it is even without any of that because of the design of it to facilitate pivot tables. As we know, 80% or even 90% of the development time in an application is making it "simple to use" or "doing everything for you", which is exactly what I am trying NOT to do, so that when you get the hang of this, you will truly "know what you are doing" in terms of accountancy.
3. Where? Country:
This will work for anyone anywhere, written in English. Any spreadsheet can create the input data, but you will need to be able to create pivot tables for reporting so I use Excel.
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 detailed knowledge, though copy inserting rows and knowing how NOT to break a table range may be advantages if you start playing with the attached work file.
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.
You do not need to know debits and credits for this, but it would make life clearer for you if you do because that will explain the “why” things are entered as they are rather than just being “plusses” and “minuses”.
I challenge the programming world that thinks it knows accounting better than accountants especially programmers who are 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 and slavish reliance on “the computer”.
INTRODUCTION OF CONCEPTS & ASSUMPTIONS
Paper bookkeeping as has gone on since time immemorial.
Debits and Credits as invented 1494 - hundreds of years ago, long before modern computers.
You want to know “how much is mine?” in the context of your business. Every day all day. That you really want to understand what you are doing with your cashflow, to literally see it in action so you can create records and manage them to run your business, your cashflow and so your life under your direct control without anyone else standing between you and your money. (And for any professionals reading this I am alluding to “charts of accounts” being a “mechanism of interference”, happy and interested to debate this idea, but perhaps not just here as it will confuse beginners.)
So here we go.
File here -
if you spot the unique idea buried in this file do let me know.
also; if you want help let me know either by asking EE questions or directly (EE preferred). If you use EE (which I encourage) maybe let me know as I come and go over time.
BUT: the first time you enter each type of transaction it will require some thought for each column, usually pretty obvious such as “DATE”. After the first time use “row copy” and “insert copied cells” for all future similar transactions changing only the necessary data such as usually the amount and dates, as such a new transaction should take no more than 4 or 5 seconds to enter. No coding is essential, though would be copied anyway, but can all be changed later and is NOT needed for cashflow management.
Note: After data entry do refresh all the calculated columns (use fill down/copy/paste and it should take mere seconds).
Sets of columns:
LEFT - A series of information columns about the transaction
CENTRE - The transaction value itself
RIGHT - Reporting data, such as “codes” or other tags and flags for reports.
And as a bonus:
EXTREME RIGHT - columns that replicate the very old fashioned idea of “extended analysis”; really not required as they are “redundancy” in that results here are “reports” without any extra work, “instant” if you like, though a pivot table reports in an instant too, but sometimes we want information always present in real time, so here it is illustrated in the example case using balance sheet and profit and loss categorizations.
To learn more about me, please check my Experts Exchange profile page.
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.