Spreadsheet Modelling- A Reminder!

October 12, 2009

Blue-Plate consultants often find themselves advising on or building spreadsheet models on behalf of clients. Such models are almost ubiquitous in the financial management arena. Sometimes, however, the advice is more along the lines of “use the systems that already exist within the organisation”, spreadsheets are not always the best solution – but that is another debate. If you use spreadsheets in anger then you already know the rules but this posting intends to act as a reminder on some of the rules we should be following even though it is tempting to take shortcuts:

  • Don’t just start building the model, a third of the time should be spent on the specification and design, a third on building and a third on testing.
  • Put every variable on a variables sheet, all of them! It may be handy to have a 10% overhead variable just next to the results table so it can be changed quickly but don’t, someone else will come along and miss it.
  • And plan for that ‘someone else’ it’s not ‘your’ model for ever, someone else needs to know how it works, including you when you don’t use it for six months! Create a simple flowchart in the front sheet of the model and produce an operating manual – and allow plenty of time for this important activity. If the time is not available then, again, maybe a spreadsheet model is not the most appropriate tool for the job.
  • Of course the flowchart will be easy to do because your spreadsheet obeys the principle of having inputs, calculations and results on separate sheets. To be clear on the last point – don’t perform any calculations on your input or results sheets, and it goes without saying that your results sheets are just that, nothing else.
  • Don’t have any hard coded values in your formulae – there’s a VAT change coming so just change the standard rate of VAT variable once and every formula that requires the application of VAT looks to that cell or the range name called ‘VAT’.
  • VLOOKUP and HLOOKUP are difficult to audit and you can be caught out when you insert a column or row in the source tables at a later date. If you do use them then use the COLUMN or ROW functions to create a dynamic link to the source data or, better still, use a combination of INDEX and MATCH functions.
  • Make your timescale dynamic. Have the start period as a variable and have the column headings as functions. And have the start period in the same column on every spreadsheet.

There is lots of guidance on spreadsheet modelling best practice; one of the most widely recognised is the document of the same name by Nick Read and Jonathan Batson published by ICAEW and easily accessible through the internet. You can also call Blue-Plate but whatever you do, obey the rules and don’t take shortcuts – you’ll be rewarded in the long term.

Stephen Lockwood