Monday, 25 June 2012

Getting my sums right

I have spent a large part of my day sat at my computer trying to get my accounts to balance ... and I finally managed it!

My day started with a trip to the branch of the bank that holds my business account ... and before anyone asks, it is part of the banking group that has been having all the problems with its computer system!

(For the benefit of regular blog readers from outside the UK, one of the major banking groups in the UK – the NatWest Bank, the Royal Bank of Scotland, and the Ulster Bank – has been unable to get its computer system to process all account transactions for five days. This has left businesses unable to pay their staff, people unable to get money out of ATMs, and – in some cases – not be able to move house on the day they were supposed to. The banks have stayed open for extended hours since the problem began and even opened on Sunday morning to try to help customers who were having problems.)

I eventually managed to pay money into the business's bank account (something that took far longer than normal) and after returning home for some lunch, I began to sort out my business and personal accounts and – more importantly – the end-of-year accounts for Wargame Developments. They are all set up on MS Excel spreadsheets, and it usually takes less than an hour to do the lot ... but not today!

It took me less than thirty minutes to get my business and personal accounts up to date and reconciled, but for some reason I just could not get the Wargame Developments accounts to balance. They were 45p out.

Now most people would not worry about 45p ... but my father trained as an accountant after his service in the Army, and he drummed into me from an early age that accounts must balance. As the amount was 45p – and was thus divisible by 9 – it was likely that I had transposed two digits when entering an amount. A cursory look through the accounts showed no obvious errors, so I had to go through every single transaction since the last reconciliation to find the mistake. The problem was that I could not find it.

After two hours of fruitless work I took a break, but even coming back to the problem somewhat refreshed did not immediately yield an answer. I finally decided to check each calculation manually ... something that should not be necessary if the spreadsheet has been set up correctly.

I found the answer after almost two more hours of looking ... and it was something that I had never come across before. One of the calculations on the spreadsheet was producing an incorrect result, but when I checked the formula I could find nothing obviously wrong with it. I deleted the existing formula and typed it in again ... and the correct answer appeared. I have no idea why this happened ... but it did ... and the 45p error had disappeared.

8 comments:

  1. I too have fallen victim to the RBS issues - I transferred a sum of money from another account last week and tried to make a payment from the RBS account today, but guess what? RBS can't locate the money!

    ReplyDelete
  2. Tim Gow,

    I wish that I could say that I shocked by your story ... but I am not.

    I understand from a news item I saw today that the problem arose with the CA-7 batch scheduling software that is used by RBS Group. It had been updated ... and it was the update that caused the problems.

    'Yer pays yer money (in this case it is rumoured to be rupees) and yer gets what yer paid for.'

    All the best,

    Bob

    ReplyDelete
  3. Happily I haven't been affected by this but a few friends are living on whatever they can find in the back of the fridge until payday. We get paid monthly in most jobs here, so they're really stretching to make ends meet.

    Re: accounting problems.

    Doing all that work prevents the accounts being out by 45 or 4,500 or 4,500,000 POUNDS somewhere down the line. This is a small scale example of someone avoiding the problems RBS are having. Also, having worked for MS in the past, "sometimes it just happens" is apparently a valid explaination for most of this stuff.

    ReplyDelete
  4. Arquinsiel,

    The problems caused by this 'glitch' with the RBS Group's computer system has affected whole swathes of society ... and I suspect that it has made many people even more wary of this bank group in particular and banks in general. The ordinary people at the bottom end of the banking 'food chain' are the ones who will suffer most ... and are likely to get the least when it comes to any form of compensation.

    My own accounting problems also seem to have been the result of some form of ‘glitch’ … and I am still at a loss to understand why. In retrospect the only thing that I can think may have caused the ‘glitch’ is the fact that the original spreadsheet was created in an earlier version of MS Excel and that a recent automatic upgrade of MS Office may have triggered the problem. I will never know … but it is the sort of thing that sometimes happens with software.

    All the best,

    Bob

    ReplyDelete
  5. Hi Bob

    Having used Excel from before it was generally released the most likely explanation for your miscalculated formula would be a non-printing character inadvertantly inserted which was handled differently after the MS upgrade.

    I'm surprised MS never added a check for non-printing characters in formulae as standard. I've seen it many a time.

    Now that you may have experienced it it will probably never happen to you again.

    Jim


    Jim

    ReplyDelete
  6. Jim Duncan,

    Your explanation certainly fits the bill, and would be yet another prime example of MS software 'behaving as expected'.

    Now it has happened once I will be on the lookout for it if it ever happens again.

    All the best,

    Bob

    PS. For some reason best known to Blogger, it placed your comment in the 'Spam' folder!

    ReplyDelete
  7. While I was in MS there was a disturbing amount of times I was told that a relatively simple procedure was impossible due to some form of magic. It's almost like common sense is abandoned when entering the building.

    ReplyDelete
  8. Arquinsiel,

    I have had numerous incidents in the past (in particular when using MS Access) when things that were supposed to work just did not do so in the way that was expected. In some instances we never found out why.

    I suspect that it is all a result of Clarke's Third Law coming into play.

    All the best,

    Bob

    ReplyDelete