Category Archives: Techniques

Posts about analytical techniques, methods and theory. Warning: may contain mathematical notation.

Help! My spreadsheet’s derailed

Watching the train franchise debacle unfolding at the Department for Transport, I find myself getting steadily more and more depressed.  Putting aside the politics of it all it seems somebody at DfT screwed up their spreadsheet models.  Models being used to assess the comparative merits of the suiting bidders.  The problems only came to light when the department was railroaded – if you’ll pardon the pun – into hiring PwC to do some due diligence.

I have a deal of sympathy with those original analysts who built the model.  I’ve built many a model myself over the years, and reviewed many others.  Building spreadsheet models is a complex business.  It’s painfully easy to make mistakes.  The research literature suggests the majority of Excel models used in business contain errors, many of which have a material impact.  You need to think of it as a form of software development (which it is really).  Good software is expensive to produce because development has to include significant design and testing phases, and a lot of iteration.  It’s not just a question of a programmer sitting down at a keyboard, bashing out a few hundred lines of code and Bob’s your uncle it all works fine.  Yet those who commission Excel models – and unfortunately a sizeable chunk of those who develop them – too often fail to appreciate this.  If you are building a complex spreadsheet model, one on which a multi-million pound decision hinges, you have to design it carefully and QA it to death.  Government departments have the capability to do this in-house, they don’t need to bring in a consultancy to do it.  What is lacking is the senior civil service and ministerial awareness of what modelling is.  A lack of awareness which I suspect in this case led to either a failure to resource their in-house analytical team correctly for the job and/or a failure to ask the questions which would have exposed the model’s lack of rigour.

Don’t make the same mistake.  Add this to your bed-time reading list:

Spreadsheet Modelling Best Practice.