ICAEW.com works better with JavaScript enabled.

Excel Community | Financial modelling series

Intro to Financial Modelling - Part 4: Layout & Structure - Managing Inputs

Author: Andrew Paw

Published: 23 Apr 2020

Hello and welcome to the continuing series from the Excel Community's Financial Modelling committee, in which we will work through the chapters of our Financial Modelling Code and explain how each element translates into practice.

Start from the beginning

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.
Worksheet example
  • Document data sources in the overview sheet and/or in each worksheet using information headers.
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
Visually identify inputs by either keeping inputs in separate clearly labelled worksheets or placing inputs in clearly identified distinct areas of the same worksheet.
  • 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 colour coding standards to differentiate between different data
  • Use separate tabs for mass data
Use separate tabs for mass data
Segregate inputs, calculations and outputs, either by using separate worksheets, or demarcated sections on the same worksheet.
  • 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.
Use of data validation for inputs help to manage and control what inputs are entered in a model.

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

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel
Topics