Models should be laid out in such a way that users can understand the flow of logic and modify it as appropriate. By building clear and logical sections within a model, users can easily find the components that are relevant to them. Logic should also flow consistently from inputs to calculations to outputs. This consistency will help users understand the logic flows of similar items.

Through native Excel functionality, there are several ways to more easily identify inputs, assumptions, and the sources of information for users and or reviewers.  Outlined below are some examples of structuring model inputs that will help to establish a clear and logical layout for input information. 

• Set up your model to read like a book: with logic flowing from top to bottom within each worksheet, and from left to right both within each worksheet and across sheets within a workbook.

• Document data sources in the overview sheet and/or in each worksheet using information headers.

• Visually identify inputs by either keeping inputs in separate clearly labelled worksheets (with no other calculations performed here) OR Placing inputs in clearly identified distinct areas of the same worksheet. A combination of using cell shading with a cell shading legend can be used to distinguish Inputs from Assumptions

• Use colour coding standards to differentiate between different data - The selection of colours used to display cell and worksheet types are a matter of preference to individuals / teams using the model. What is of importance is that colours used within the model carry consistent meaning when being defined and used.

• Use separate tabs for mass data

• Use of data validation for inputs help to manage and control what inputs are entered in a model. For example, using the validation criteria, users can set specific parameter values such as percentages, define threshold numbers, dates and predefined selection listing. This reduces the risk of incorrectly entering data into the model and helps to prevent incompatible assumptions being entered.

By clearly labelling and visually making inputs easy to find, users should be able to find and manipulate inputs in the model with minimal effort. The model should also prevent incompatible assumptions from being entered inadvertently where inputs are related to one another.

Remember to choose a structure that is appropriate for the circumstances. For example, a model could either contain a single input worksheet, or multiple worksheets if it were deemed necessary to split between time-based and non-time-based inputs.

Separation of inputs from calculations, outputs and other data types creates a logical design that minimises confusion and facilitates ease of maintenance, reducing the likelihood of introducing errors.
Documentation of data sources and clear labelling of input areas helps ensure the data that is entered in a spreadsheet is understood and interpreted correctly.

In the next part of the series, we examine the components of user interface and transparency and what this entails.

Next in the series

Part 5: User interface and transparency