In the UK, the de facto accounting system for small business is SAGE. This is most unfortunate, as it is an appalling piece of software for many, many reasons. I can rant for hours on its many shortcomings, but today I’ll mention two of the biggest ones, and how we’ve been able to work around these using a wiki.

Firstly, its reporting is terrible. It can, of course, give you all the traditional accounting reports – P&L, balance sheet, cash flow, actual vs forecast, debtors list, creditors list, etc. etc.. In recent years they’ve even added the ability to produce these in HTML. However, there is no way to interrogate the output or investigate things further. When I see a financial report I want to be able to click on any piece of data on it to get further information. This is a classic case of a 90% solution. There are really complex tools available to let you slice and dice data any way you want, but in a small business, just making each number a link to a screen that shows you what makes up that total is generally both necessary and sufficient. SAGE fails miserably here. I’ve encountered many companies who went used to go through the process of getting their bookkeeper or accountant to print these reports on a regular basis, but never really understood what they were seeing, so eventually reverted to the traditional “gut feel” approach that most small business owners prefer, leaving formal reporting for their annual accounts and their semi-regular reports to the bank manager.

SAGE does allow you custom build your own reports, but as far as I’m aware there’s no facility to allow for the creation of “interactive” reports. It’s also highly likely that some enterprising company has built some sort of add-on to do this, as there’s an entire third-party ecosystem surrounding SAGE, but I’ve never found one.

This reporting problem falls into the “generally annoying, but I guess I can live with it category”. Historically my general approach has been to export the pertinent information as CSV and manipulate in Excel – what most companies seem to do when they get big enough to move up from having a simple bookkeeper to actually hiring an accountant.

The much bigger problem however — the one that makes me tear my hair out when having to work with SAGE — is how it deals with accruals. This is an accounting concept whereby you are aware of income or expenses that will apply to an given accounting period, but for which an invoice has not yet been raised. The classic example is a bill that is issued quarterly in arrears. A quarterly bill for £60 should really be entered as £20 for each of the three months. But when you go to close April’s accounts, that quarter’s bill hasn’t arrived yet, so obviously hasn’t been entered on the system. As such your accounts don’t accurately reflect the costs incurred in the month. So you have to accrue a £20 charge that can be reconciled when the bill finally arrives. Similarly, if the bill is quarterly in advance, you don’t want to just charge the full bill against the month when it arrives, so you assign parts of it to each of the next two months. SAGE makes this remarkably complicated. Bookkeepers and accountants have gotten so used to that they don’t really think about it, but watching the confusion of a new admin assistant learning how to do it drives me insane. As a result, many small businesses just don’t bother doing this and their monthly accounts swing wildly in the months where the bigger invoices hit.

One of my standard rants to accountants is about how, yes, double entry bookkeeping was a great invention in 12th Century, but it’s no longer a useful metaphor for users of a computerised accounting system that should be able to take care of such things by itself. Business owners and managers want to enter sales and purchases, note when these are paid, and then check it all off against the bank statements each month to make sure nothing untoward has happened. They don’t care, and shouldn’t need to care, about journals and ledgers and trial balances.

But there seems to be no such thing as “modern” accounting software. They’re all just computerised versions of an ancient system. Some manage to hide more of the underlying mechanisms more than others, but it doesn’t take much scratching to discover what’s really under the surface.

When we took over the running of Ireland’s oldest ISP a few years back, the accounts were a mess. Customers were billed monthly, quarterly, six monthly, and/or annually – some in advance, some in arrears, some in the middle of their billing cycle. How they were billed also bore little relation to how they actually paid – many of the customers who got annual bills actually paid monthly, for example. Suppliers, in turn, tended to invoice quarterly in advance. Large software development projects also carried a substantial Work in Progress element. It was a huge amount of work at each month end to enter all the necessary accruals and movements, and the resulting P&L and balance sheet were mostly incomprehensible due to the wild swings involved in booking or releasing money from customer pre-payments, often received months earlier, but which have to be treated as a liability until that month’s services have been delivered.

The simple solution to this problem is of course to just allow each invoice (whether sales or purchase) to have an associated ‘period’. A phone bill charges for the previous quarter’s calls, and the following quarter’s line rental. Most companies account for these separately so already split the bill and assign each a different nominal code. The trick is to also add the relevant dates to each: calls from April 06 – June 06, line rental from July 06 – September 06. The software can then do all the calculations it needs for accruals each month without the need for a hideously complex manual month-end process. SAGE, however, doesn’t allow you to do this. They’re not alone. Every accountant I’ve spoken to is completely unaware of any low-end financial software that does. But they all agree that it would make the accounting process much, much simpler for most companies. I can’t believe that no-one else has ever thought of doing this, so I’m left with the assumption that it’s just a giant conspiracy to create unnecessary jobs for millions of accountants and bookkeepers.

So, in frustration, we decided to use a wiki for our accounts instead. Mediawiki with the Semantic extensions makes for a very interesting data store, as I’ve written about previously. For basic accounting information there are only really a few key concepts: purchase invoice, sales invoice, payment made, payment received, bank lodgement, bank statement.

Each gets its own Category, and has a set of relationships and attributes defined. Each invoice has attributes for net, VAT, and gross amounts, a nominal code, an issue date, and a from date/to date pair for the period it applies to. Each also has a Supplier/Customer relationship which of course links to the page on the wiki where we’re already storing information for that company. A cheque received relates to one or more invoices, has attributes for the amount, and is related to a Bank Lodgement, which in turn is an amount on a date to a bank etc. A provisional bank statement can then be generated through an <ask> query for each transaction applying to that bank in a given period. This can then be visually cross-referenced against the physical statement when it arrives to make sure that nothing is missing or erroneous. Most small companies are on cash accounting for VAT, so the bank statement query can also be set to also include the VAT portion for each transaction, and a simple sum provides all the information necessary for the quarterly VAT return.

Producing a P&L is trickier. The syntax isn’t really expressive enough to do this yet, and I’m not sure it ever will be. Even if it allowed the full Sparql syntax, as has been mooted, I suspect the computations to properly assign invoices over various accounting periods might be too complex. At the minute we produce these by querying the wiki’s underlying database directly with a Perl script that does all these calculations. This also means, of course, that each piece of data on the resulting report can be a link to a page explaining how it’s made up.

The process is, of course, error-prone. But it’s much, much easier to correct errors in a wiki than in SAGE. Mediawiki, of course, maintains a full revision history for every page, so there’s a built in audit trail that’s much easier to examine than the one in most financial systems. We’ve created a few scripts that run regularly to look for obvious errors, such as invoice items where the net + vat doesn’t equal the gross. The syntax is still too simplistic for some of the reporting we’d like, so until it provides aggregate functions, or until we learn how to use RDF better, we still have to do too much digging at the raw data with SQL. It’s very far from a complete financial system, as we haven’t really needed to deal with things like share capital (although asset purchases are simply a purchase invoice with a very long from date/to date range, so the bulk of a balance sheet is there). And it’s built on a fast changing base – Semantic Mediawiki is still in the early stages of development. So it’s certainly not something everyone could, or even should, do. But it has allowed us to remove a huge amount of recurrent admin work, see much more clearly what the financial status of the company is in real time, and drill down into that data where something seems puzzling.

All we need to do now is edit the ten years worth of historic data that we imported to assign the correct periods to each invoice…

[Update: More on this]