Building models that are easy to check and modify

Download a pdf copy of this help file  here

See also: Model design introduction, Building models that are efficient

The better a model is explained and the better it is laid out, the easier it is to check. Model building is an iterative process, which means that you should construct your model to make it easy to add, remove and modify elements. A few basic rules will help you do this:

• Dedicate one sheet of the workbook to recording the history of changes to the model since conception with emphasis on changes since the previous version;

• Document the model logic, data sources, etc.  during the model build. It may seem tedious, especially for the parts you end up discarding, but writing down what you do as you go along ensures the documentation does get done (otherwise we move onto the next problem, the model remains a black box to others, etc) and also gives you a great self-check on your approach;

• Avoid megaformulae if possible unless it is a formula you use very often;

• Avoid writing macros that rely on model elements being at specific locations in the workbook or in other files;

• Break down a complex section into its constituent parts. This may best be done in a separate area of the model and the result placed into a summary area;

• Use a single formula for an array (e.g. column) so that only one cell need be changed and the formula copied across the rest of the array;

• Keep linking between sheets to a minimum. For example, if you need to do a calculation on a data set residing in one sheet, do it in that sheet, then link the calculation to wherever it needs to be used. This saves huge formulae that are difficult to follow, like: =VoseCumulA('Capital required'!G25,'Capital required'!G26,'Capital required'!G28:G106,'Capital required'!H28:H106);

• Create conditional formatting and alerts that tell you when impossible or irrelevant values occur in the model;

• Use the Data/Validation tool in Excel to format cells so that another user cannot input inappropriate values into the model;

• Use the Excel Tools/Protection/Protect_Sheet function together with the Tools/Protection/Allow_Users_to_Edit_Ranges function to ensure other users can only modify input parameters (not calculation cells);

• In general, keep the number of unique formulae as small as possible;

• Colour code the model elements;

• Make good use of range naming.

Read on:  Building models that are efficient