Using a Wiki as an Accounting System

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]

17 thoughts on “Using a Wiki as an Accounting System

  1. We’ve used the ODBC driver – that’s how we actually imported all the old data into the wiki. I’m not convinced that creating reports in Access is much better than creating custom SAGE reports, but the biggest problem is still that SAGE can’t store the main information necessary for sensible reporting. We looked at using some of the lesser known and almost never used fields for this, but that was mostly a dead end too.

  2. Tony, Sage has shipped with an ODBC driver for years, sufficient for use with Access for reporting. FWIW I’d rather the Sage binary was still able to run on a 486 than have a (never-quite) complete reporting solution included, that only 1% of its users would ever need.

  3. I also had a stab with Sage and found it absolutly useless. My demands for a system where quite simple but with the added complexity of handling multiple currencies and multiple banks/payment methods. I couldn’t find a good way to make this work in Sage, and certainly no way to automate it.

    In the end I just created my own simple system with a simple PHP/mySQL script. This way all the data is in a database and can be manipulated in any way I want. Far more flexible.

  4. I too found SAGE to be overly complicated. For my needs I found QuickBooks to be excellent. You might also consider SQL-Ledger (http://www.sql-ledger.com/) which, although rather horrible code, is written in perl and therefore hackable towards what you need.

    Having said that, if the wiki works, don’t fix it !

    S.

  5. Pingback: Khaos » Blog Archive » Wikis - for fearless information entry

  6. Pingback: Understanding Nothing » Blog Archive » More on Accounting Wikis

  7. You entirely miss the point about entering periods. You don’t need to do accruals because there’s a month end process – you need to have a month end process to do accruals. If you have no way of assigning costs to a period, then you have to do manual accruals or you’ll be counting everything in the wrong place. If you get quarterly bills of £3000 each, but fail to assign these as £1000 per month, then your P&L for one month each quarter will show overinflated costs, and will be under representing your actual costs in the other two months. If your accounting system doesn’t handle this on your behalf then you’ll either have useless reports or you’ll need to do a lot of manual work to produce them by hand.

    As for the double-entry system, I’m not entirely convinced that there’s not a better way, but even if it’s the best system to represent transactions “under the hood”, software should be able to adequately hide it from users. All business accounts systems I’ve seen fail miserably at this.

  8. Totally agree with you about Sage, but not all modern systems are the same.

    Acceptum Business Software ( http://www.rcl-systems.co.uk ) allows you to do all that you mention Sage cannot –
    a) drill-down on financial report amounts to the underlying figures, right back to source document.
    b) create reports or change standard reports
    c) as no special month end processing is required, accurals and prepayments don’t become an issue.

    The idea of double entry bookeeping goes back to the ancient Egptians, its been around so long and has proven so useful it is a principle that cannot be changed – otherwise you end up with incomplete record accounting which is a nightmare that keeps many accounting firms in business.

    Bob

  9. Pingback: Pierres Service » Blog Archive » using a wiki as an accounting system

  10. Pingback: Understanding Nothing » Blog Archive » Wiki Gardening

  11. I manage an SME in the engineering consultancy sector and I wholeheartedly agree with the sentiments expressed here. So much so that I set up a small company to write my our own integrated project management and accounting software: written for business managers, not just accountants. I’ve been using the software – PrismERP – for 3 years to run my consultancy. Right now we are looking for two or three small companies who would like to help us by trialling the software. We’ll supply the software and support free in return for feedback and help identifying errors. If you are interested, then please contact me at eastlandd@sunadra.com

  12. We are software developers in Australia providing integration capabilities for 3rd party developers to QuickBooks and MYOB. Currently starting work on integrtaion to Sage for a developer in the UK. Sage has left me seriously underwhelmed, to say the least. I thought that the shortcomings of QuickBooks and MYOB were bad, but they have nothing on Sage. But, the as job has to be done, we will have a solution very shortly. It seems to me the best way to make Sage work correctly is to only pass to it what you want it to know when it needs to know – and to use your other system as the real business management system. That way it WILL get it right!

  13. Pingback: What I Want From Personal Finance Software : Understanding Nothing

  14. Pingback: From the Desk of Asif Nawaz » Blog Archive » Accounting: QuickBooks, SAGE, Peachtree or Microsoft Office Accounting? - Part ONE - HP, Quick Play, News Articles & Consutling Services

  15. We are a small IT Solutions and Service company based in the Philippines. A lot of our customers in our services division had the same issues with these entry-to-mid level accounting systems. Additionally, they cannot afford SAP, Oracle and GP.

    So we did a lot of research and found two – Open Bravo and WebERP.

    As we offered the solution in a hosted environment, WebERP was the best choice. Open Bravo was better but then it was just too heavy for a hosted solution in the Philippines.

    We have 3 medium clients now (about 300 employees each) and are happy with our hosted WebERP solution.

    By the way, both software were tested and reviewed by 3 accouting chiefs (CFO, and 2 finance directors). Two of these three work in large MNCs. So we know the solution really worked before we asked our customers to test.

Leave a Reply

Your email address will not be published. Required fields are marked *