Model

8.a. A simplified or idealized description or conception of a particular system, situation, or process, often in mathe­matical terms, that is put forward as a basis for theoretical or empirical under­standing, or for calculations, predictions, etc.; a conceptual or mental represent­ation of something.

— Oxford English Dictionary

Modelling

Every consultancy study involves constructing models that represent one or more aspects of the real-world subject. Sometimes these are just informal thought experiments—mental models—but very often it's worth formalising them into diagrams or computer models.

I first used Microsoft Excel to model the operation and finances of visitor attractions in 1997. Since then I have created more than 100 models covering everything from the running costs of a small visitor centre up to the capital and operational costs and revenues over 20 years of a £80 million acquarium. Some of them concentrate on very specific aspects such as visitor flow or secondary spend.

The general approach I have developed is to build a model on lots of small input assumptions, each of which is exposed to discussion and criticism. This has two big advantages:

Photograph of people working in front of a large screen that shows part of one or my models.
  • It makes it easy for people to relate the assumptions to their real-life experience, which increases their confidence in the modelling.
  • It minimises the effect of any single input on the model's outputs and increases the chance that inaccurate assumptions will cancel each other out.

Workshop Sessions

Usually during a study I arrange workshop sessions with the client where we sit down with the model on a big screen and systematically examine the assumptions and the logic. Click on the picture at left to see part of the Staffing sheet from an actual model, showing positions, salaries, start dates and so on.

What if...?

A good model allows "what-if" analysis, and I have developed a range of techniques to make this as effective as possible.

  • One is the detailed modelling based on many small assumptions. For example, I routinely set up models so they can handle multiple price indexes: this makes it simple to look at the effect of (say) energy costs doubling over the next five years while other prices change much more slowly.
  • Another is future-proofing. For example, most models assume that the VAT rate is constant—an assumption that the government has invalidated three times in as many years. My models allow not only for a VAT rate that changes over time, but also for multiple VAT rates should they be introduced. In addition they can calculate irrecoverable VAT for the typical charitable trust + trading company structure.
  • I have also developed a heavy-duty scenario manager that can be used with any of my models or with your existing spreadsheets. This allows multiple sets of assumptions to be stored in the workbook and switched in and out as desired. It can also generate comparison tables and sensitivity tables.

Quality Control

Research in recent years has shown that 90% of spreadsheets contain errors and that few people appreciate the risks. It has been inferred that reliance by financial institutions on unverified spreadsheets contributed to the crisis that began in 2008. Of the spreadsheets I have audited, all but the very simplest have contained errors that affected the outputs - or would affect them as soon as what-if analysis began.

Against this background it would be hubris to claim that my spreadsheets are 100% free of errors. But through the European Spreadsheet Risk Interest Group I appreciate the problems and keep up with thinking on good practice. I have adopted and developed techniques to reduce the risk of errors, and have a set of software tools for troubleshooting and auditing spreadsheets.

If you have doubts about an important spreadsheet I can audit it for you.