Excel

Spreadsheets have been around on computers since VisiCalc and before that ever since Man discovered how to write. Indeed before that such matters could only be imagined in the human mind. So the arrangement of information has been around since the dawn of time.

I view all computer accounting “programs” as nothing more than automated spreadsheets where outcomes (a.k.a. “reports”) are pre-ordained whether custom or preset.

N.B. HMRC’s MTD is not a block to spreadsheets, far from it.

Such systems are capable of errors both by means of “bugs” (not operating as documented) and human errors whether input errors or ignorance about either how the particular system works or how bookkeeping and accounting work, in fact a different sort of “bug”. Add to that a distinction between “compliance with the law and GAAP” versus business planning and policies together with taxation considerations for any particular entry none of which is yet built in to developed software.

One day we may/will have A.I. but not yet by any means. Need convincing? Try “talking” to the sublimely stupid “chat bots” that constantlly seek our attention online.

Am I “anti accounting software”? No. I am however anti misleading advertising that makes claims about buyers not needing any expertise because the software can do it all. This has persisted since the beginning of accounting software and is nothing new - so it goes back to around 1980.

Systems have improved out of all recognition, but still they make these claims and now try to “acquire the customer” who they basically sell out to the professionals who have to pay handsomely for these “clients”. They achieve this by advertising, so the tail is now wagging the dog. To this day software success is governed by its advertising budget and skill, not by how suitable it is for any particular business. By the time any of this becomes apparent it is far too late. Some will never discover the true costs of what they are doing.

“Compliance” is cheap to do so looks great in price comparisons (where like with like is being compared which often is not the case, but this is obscured).

“Business strategy” (for want of a better term) has no place, yet is where skilled professionals are needed. However, those who “cannot see why” will remain blind to any wisdom concerning the arrays of what they cannot see.

Note the distinction between “we can do” or “we can teach”. Not both, unless willing to spend significant sums on doing both at the same time. Imagine riding a bike yoruself versus teaching a child to ride a bike; very different time propositions.

Where the line is between using a spreadsheet and software is moot. I know it when I see it. However typically several hundred transactions a year can easily rest in a spreadsheet.

Some businesses have an aspect with thousands of transactions, for example sales and yet the rest is only small. Then the sales ledger aspect can be used to enter combined journals into the spreadsheet. It can well be that after running in spreadsheets for a while, that choosing a system that can offer the same benefits becomes easy.

So spreadsheets as a prototyping machine is the first primary use. This is like establishing where one is on a map, so one can derive the route to where one wishes to be. Indeed all software starts out one way or another as a prototype.

Why not use the same design process for one’s own business? That way lie proper well informed choices. I view “decisions” as “choices made with insufficient information”. Let us be informed. Let us not act in ignorance.

N.B. HMRC’s MTD is not a block to spreadsheets, far from it.

The Spreadsheet world was once lead by Lotus 123 followed by Multiplan and Quattro Pro.

Multiplan morphed into Excel and won the marketing battle, now many years ago. Lotus 123 is now owned by IBM and is largely and sadly dead. Lotus Agenda has never been replaced and was sheer genius.

Wikipedia has extensive covereage of spreadsheets here: https://en.wikipedia.org/wiki/Spreadsheet

including a long list of offerings, not least the online Google Sheets which is free and always evolving as well as autosaving. Indeed it can now do “pivot tables” which are a crucial ingrediant to modern spreadsheet “reporting” because they can eliminate (or highight) errors as well as ensuring confidence of “completeness”.

Pivot tables

Often viewed as a black art or limited in possible uses to certain very narrow aspects. What it can be seen as is a “point and click” database reporting mechanism. As with any software reporting system, what comes out relies on what goes in, so the underlying spreadsheet “tables” have to be suitably designed. “Structured Tables” are a whole other story that move spreadsheets even closer to competing with database systems. Throw in VBA and a 100% custom accounting system shuffles in to view. However, we are not here trying to compete with formal software because re-inventing the wheel is not what we are about, which is to ensure we do not make uninformed leaps in to the dark.

Some bookkeeping basics in spreadsheets

Excel is my reference point

Generic transaction data
Columns (database Fields)

Tx no, Our Ref, Their Ref, Date, Particulars, Folio, Gross, Vat, Net, Balance

“Folio” means a cross reference to another “book”.

“DateDue” is not in the generic list but I use it to allow forecast reporting.

Bank Account(s)
Reconciliation is critical.
The “balance” column becomes a running balance and an extra “memo” column is used to mark whenever it has been agreed to the bank account. This column is also available for any notes.

Additional bank or cash or indeed credit card accounts simply require an extra three columns. Even a loan account or H P account can be accommodated in this way. “Column hide” may become useful.

Purchase and Sales Ledger control accounts
These run just like bank accounts.
You can have a combined account or separate, it is personal preferece. These would be a further three columns to the right of the bank account, or an extra six columns if separated.

Trial Balance

This column pick up the amount from each of the above. It has a running total to ensure it can be seen to be in balance.

VAT

This is in fact the Vat column mentioned in the generics. Several further columns might be used to determine such as one being Inputs Outputs Out of scopes and so on. Another might specify the VAT % to be applied (and amount checked). VAT Stagger groups can have their own column. MTD with “digital links” is no problem via pivot tables.

Analysis

Any number of analysis code columns can be added which is one area formal systems are unable to compete.

Formal systems cannot compete

The limitless flexibility of a spreadsheet leaves formal systems hog tied by their inflexible design because they limit themselves to the fields they designed in to start with. Further analysis actually requires they output their data for further anlaysis in spreadsheets.

I am aware of one that tries to enage in cashflow forecasting, but it cannot flex itself and like all lans, cannot survive real life. It is a game effort nonetheless. By “cashflow forecasting” I do NOT mean those that use percentages and probabilities, these are just guesswork.

STRUCTURED TABLES

These are relatively new to spreadsheets and as their name infers they offer structure and so reduced “error prone” risks. This means greatly improved consistency of calculations. Clearly this is in addition to traditional balancing and control techniques, and takes spreadsheets a step closer to the controlled database world without departing from spreadsheet flexibility.

The future: Arrays, Lambdas and Lets etc

Microsoft are, or will be, rolling out significant improvements to Excel in the use of array formulas, Lamdas and such like. These can remove the need for vast numbers of (error prone) formulae. However, for now these remain in the beta versions for selected users.

COMPLIANCE REPORTING

All the above means that it is easy, indeed trivial, to produce reports for VAT, Corporation Tax, companies house and HMRC accounts production, self assessment income tax, partnerships over multiple accounting periods and tax years simultaneously. At will. Including MTD compliance.

BUSINESS REPORTING

This means business financial position in real time, all the time. Cashflow daily management into whatever future time frame is needed. Creditor and debtor reporting, even some job costing and other “departmental” and “personal” reporting as desired.

GENERALLY

Being a spreadsheet we can build in pretty much anything desired, instantly, including many to many ledger payments, as well as one to many and many to one. The last are facilitated for the first time ever by the new Microsoft functions being made available. Until now “many to many” transaction relationships have always been a “spreadsheet killer”: not any more. They were always possible but really made things too complicated whereas in formal systems they are no problem.

LIMITATIONS

Of course there are limits, but in small businesses it depends on a case by case consideration. It also depends on the level of control desired and the ability to “do accounting” beyond simple compliance as offered by formal systems.