Why standardise how we do spreadsheet models?

Back to Blog home

Is the model right?

As Matthew said in a recent blog, “It is often explained that all models are wrong, just some are less wrong than others.” I agree wholeheartedly, and believe that this is necessarily so. The modeller’s task is to represent reality with sufficient fidelity to ensure that decisions based on the model are sound.

It is immaterial whether the cost to completion of a multi-million pound project is accurate to the last £1, and effort expended trying to achieve such accuracy would be wasted. Unfortunately there are many instances when insufficient effort has been devoted to making sure that a spreadsheet’s answers are fit for purpose: EuSpRIG contains many such examples. Omitted from that collection is a report by the eminent Harvard economists Reinhart and Rogoff, which appeared to show that national debt above 90% of GDP severely reduces prospects for economic growth. Widely adopted as justification for austerity budgets, the effect was later found to have been greatly exaggerated by a spreadsheet error.

So how can we make sure that the spreadsheet is fit for purpose?

Are we building the right model?

When someone asks for a spreadsheet model it is easy to assume that they know what they want. In practice that is often just the start of the story, as an increasing realisation of what the model could / should do leads to repeated expansion of the modelling requirement, and piecemeal implementation. Such developments easily descend into chaos, so it behoves the modeller to maintain order. Establish who will be the sponsor and users of the model, agree with them the business questions the model will help to answer, and get their approval for the initial scope and later specification of the model. Only then do we have the information which allows us to work out how the model should be built. We cannot prevent changed requirements by standardising the process, but we can minimise and control them.

Are we building the model right?

Complex situations may necessitate complex models, but we can strive to make them as simple as possible. Some of the principles which help this are:

• Adhere to a standard layout to facilitate navigation around the model.
• Separate inputs, calculations and outputs onto different sheets’
• Use colour formatting to differentiate types of cell contents.
• Maintain a logical data flow, left to right and top to bottom.
• Each line of data should have a meaningful (and preferably unique) title.
• Avoid obscure or complex nested formulae.

It’s all about enabling you (and others) to understand what is going on, which is usually an essential preliminary to determining whether it is right. Appropriate testing is essential, first by the model builder, but then by an independent tester.

Why should we standardise our modelling?

The objective of adopting these principles, and others there is not space for here, is not simply to produce ‘good’ models. Primarily it is mitigation of business risk. Minimisation of modelling errors gives your enterprise – and its customers – confidence in the decisions based on those models (and keeps you from adding to the ‘Eusprig’ list). There are other benefits, too:

• The models meet the business needs, enabling informed decision-making.
• Modellers’ efforts are used more efficiently, reducing the cost of modelling.
• Clarity aids understanding, and people will use models they understand,
  • and will adapt them to changing requirements, instead of starting afresh,
  • and may re-use elements of a good model on a later project.

Despite careful planning, most model developments will be iterative, and good design makes modifications easier.

How do we set about this?

There are many established ‘standards’ for spreadsheet modelling. They are all different, most are good, and they share many common principles (which have been advocated in this blog). Many are the ‘branded’ techniques espoused by specialist modelling consultancies. The ICAEW recommends ‘Twenty principles for good spreadsheet practice’ which are a good starting point, but would need a lot of work to be turned into an organisation’s full-blown modelling standard.

For most individuals and organisations the sensible starting point is ‘classroom’ training based on one of the existing (non-proprietory) standards. Aspire Consulting Limited, in conjunction with Faneuil Management Limited, intends to make such a two day course available during 2017. Please let us know if you would like to be kept informed of developments.

Back to Blog home