Bookkeeping with Excel, “how much is mine?” -  use the power of pivot tables

Anthony MellorPrincipal
CERTIFIED EXPERT
40 years working with one to two person businesses . In EE my focus is Excel bookkeeping & accounts & banking. (Not VBA)
Published:
Edited by: Andrew Leniart
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.


Bookkeeping with Excel, “how much is mine?” -  Pivot Table books


Anthony Mellor

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


  • who want to use pivot tables AND double entry bookkeeping 
  • who prefer to be able to see everything instead of software obscuring half the entries


ADVANCED USE


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.   


DEVELOPMENT


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


MOTIVATION


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


Concepts


Paper bookkeeping as has gone on since time immemorial.

Debits and Credits as invented 1494 - hundreds of years ago, long before modern computers.

https://en.wikipedia.org/wiki/Debits_and_credits


Assumptions


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 -


K2000-ALPHA1.xlsx


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.

 

SETUP


None. 


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


LAYOUT


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. 


COLUMN HEADS


  • SORT Unique numeric value to allow return of sorted/ordered data back to original; crucial.
  • VATQ The VAT Quarter or Month or Year for sales tax reporting. (VAT being an EU sales tax)
  • VATBOX The “box number” for VAT reporting.
  • TAXYEAR Enter as text so ‘2019 not 2019 without the apostrophe, this facilitates reports.
  • ACCYEAR Same as TAXYEAR but for the business’ accounting year end (if different).
  • DM I use this when I want to mix and match annual and monthly reporting. 
  • InvMTH Calculated.
  • InvDate Input
  • Duedate Input.
  • DatePAID Input
  • Flow Calculated - splits time into weeks in to the future from Today.
  • DueMTH Calculated.
  • Type What type of transaction is it? Journal, invoice etc
  • SRC Bank, Cash, Visa, Whatever, add as many extras as desired.
  • OUR Our reference, for example internal invoice number or cheque number etc
  • THEIR The customer or supplier reference; used to draw together like items.
  • Payee.  Per the bank, to make sure the records contain the legal data about who it was.
  • Description Anything you like, often who it was when the “who” is different from the “legal payee”.
  • TAGS Anything you like.
  • BOOK Sales Ledger SL or Purchase Ledger PL or Nominal Ledger NL. If on credit then it is PL or SL, if “paid cash” (meaning bank, credit card or actual cash) then it’s NL.
  • BANKA. Bank account 1
  • CASH Cash. 
  • VISA Credit card
  • LEDGER If the BOOK column has PL or SL in it then also enter the transaction value here.
  • DAYBOOK  Credit transitions whether Sales or Purchases go here (assumption of mutual exclusivity)
  • DISCOUNT Cash discounts allowed/received or other adjustments.
  • GROSS Total amount of the transaction
  • VAT The VAT (Sales Tax) entered by direct input OR use a calculation or both. 
  • NET GROSS minus VAT - but NOT minus discounts, those are deducted at the end of the period.
  • TB Trial Balance cumulative.
  • BP Balance Sheet or Profit & Loss, (or anything else for such reports)
  • OUTPUTS VAT Reporting makes VAT reports easier by separating broad categories such as “outputs” and “inputs”, though may duplicate the VATBOX column depending on how you use it.
  • CODE Whatever you like, categories etc
  • WHO Again whatever you like, can be used to allocate transactions to who was responsible for it etc.
  • P&L Calculated 
  • B/S Calculated 
  • DIV Calculated 
  • SUM Calculated  - sums the previous three columns to facilitate checking all is balanced. THIS is how reliability is derived, how we avoid that claim that spreadsheets are “error-prone”. We make sure everything balances and reconciles just like we did before computers were invented. Same goes for all our other reporting. It can be time-consuming, but when we are finished we know it is right and more importantly we know and understand exactly where every one of our figures comes from. No “I don’t know” or “the computer does that” surprises. 
  • Cflows - Calculated. This is the outstanding balance (if any) on each credit transaction, in other words, it is (hoped for or expected) future cashflows.


To learn more about me, please check my Experts Exchange profile page.


0
491 Views
Anthony MellorPrincipal
CERTIFIED EXPERT
40 years working with one to two person businesses . In EE my focus is Excel bookkeeping & accounts & banking. (Not VBA)

Comments (0)

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.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community