There are a few rules for model design that should be followed when designing the layout of a model. Most experienced modellers will follow these instinctively, as they are generally common sense.
Extract from Using Excel for Business Analysis, Chapter Two
Separate Inputs, Calculations, and Results, Where Possible
Clearly label which sections of the model contain inputs, calculations, and results. They can be on separate worksheets or separate places on a given worksheet, but make sure that the user knows exactly what each section is for. Colour coding can help with ensuring that each section is clearly defined.
See the previous article “The Workbook Anatomy of a Model” for more discussion on whether it is always practical to separate these sections.
Use Each Column for the Same Purpose
This is particularly important when doing models involving time series. For example, in a time-series model, knowing that labels are in Column B, unit data in Column C, constant values in Column D, and calculations in Column E, makes it much easier when editing a formula manually.
Use One Formula per Row or Column
This forms the basis of the best-practice principle whereby formulas are kept consistent using absolute, relative, and mixed referencing, as described in greater detail in Chapter 3, “Best Practice Principles of Modelling.” Keep formulas consistent when in a block of data, and never change a formula halfway through.
Refer to the Left and Above
The model should read logically, like a book, meaning that it should be read from left to right and top to bottom. Calculations, inputs, and outputs should flow logically to avoid circular referencing. Be aware that there are times when left-to-right or top-to-bottom data flow can conflict somewhat with ease of use and presentation, so use common sense when designing the layout. By following this practice, we can avoid having calculations link all over the sheet, which makes it harder to check and update.
Excel will also calculate more quickly if you build formulas in this way, because it calculates left to right, and top to bottom, so not only does it make your model easier to follow, it will calculate more efficiently.
Use Multiple Worksheets
Avoid the temptation to put everything on one sheet. Especially when blocks of calculations are the same, use separate sheets for those that must be repeated to avoid the need to scroll across the screen.
Include Documentation Sheets
A documentation sheet where assumptions and source data are clearly laid out is a critical part of any financial model. A cover sheet should not be confused with an assumptions sheet. A model can never have too much documentation!
Design Issues
Here are four key issues you need to think about before you start a model.
- Time series: Most financial models include a time-series element, and the majority of these will be either monthly, quarterly, or annual. It’s important to get this right from the start, as it’s much easier to summarise a monthly model up to an annual basis than it is to split an annual model down to a monthly basis!
- Data Collection: Often, the majority of time is spent not in building a model, but rather collecting, interpreting, analysing, and manipulating data to put into the model. For example, you could be building an annual model for your company’s fiscal year, which goes from July 1 to June 30, but the survey data you’ve collected and want to include is for the period January 1 to December 31. If you’ve got access to the raw data on a monthly basis, you’ll be able to manipulate the data so that it’s accurate—or else you’ll need to extrapolate.
- Model Purpose: Think about what it is that you want the model to do. What outputs do you expect the model to show? See “Types and Purposes of Financial Models” in Chapter 1 for greater detail on different types of financial models. The outcome that you want the model to show will greatly influence the design of the model. For example, in a business case or project evaluation model, the outcome that we are working toward is the net present value (NPV), and in order to get that, we need cash flow, for which we need a profit-and-loss statement, and this then determines how we build the model.
- Model Audience: Who will be using your model in the future? If it’s for only your use, no need to make it very fancy, but most models are built for others to use. If so, you will need to make your model as user-friendly as possible, and clearly define which cells are input variables and which cannot be changed. If you expect users to have limited knowledge of Excel, the model needs to be as simple to use as possible.
Want to learn more about Financial Modelling? Enrol on a course, or purchase your copy of Using Excel for Business Analysis
