Considering the Layout and Design of Your Excel Financial Model

When you’re building a model for the first time, you may have trouble knowing where to begin. The key elements of a financial model are

• Inputs or source data
• Assumptions documentation
• Calculations
• Outputs

Structuring your financial model: What goes where

• Separate inputs, calculations, and results, where possible. Clearly label which sections of the model contain inputs, calculations, and results. You can put them on separate worksheets or separate places on one worksheet, but make sure that the user knows exactly what each section is for. Color coding can help with ensuring that each section is clearly defined.
• 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. 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, you 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!

Defining inputs, calculations, and output blocks for your financial model

Here are some guidelines of what might be included on each tab in your model:

• Cover sheet: Although not always included, the cover sheet contains many details about the model. Of course, the cover sheet is not much use unless you keep it up to date. If you decide to include a cover sheet, you may add details such as the following:
  • A log of changes and updates to the model with date, author, change details, and their impact on the output of the model, which can help with version control
  • The purpose of the model and how it is intended to be used going forward
  • Who originally wrote the model and who to contact with questions
  • Table of contents
  • Instructions on how to use the model
  • Disclaimers as to the limitations of the model, legal liability, and caveats
  • Global or key assumptions integral to the use of the model

    Cover sheets and instruction pages are rarely used. If you decide not to include a cover sheet, make sure that the model contains explicit instructions regarding operation, purpose, assumptions, source data, and disclaimers.

• Input sheet: This is the only place where hard-coded data should be entered. There may be one or more input sheets if there are large amounts of data, but the input data should be laid out in logical blocks.
• Output, summary, and scenario sheets: These present the final outcomes. They may also contain scenario drop-down boxes or user entry fields that allow the users of the model to generate their own outputs. This section might also contain a dashboard.
• Calculation or workings sheets: Split the calculation sheets logically and then, within each sheet, set them up consistently. If the calculations become long and confusing, it makes sense to split them into logical sections. For example, they can be split by type of service, customers, financial tables, geographical location, or business segments. If calculation sheets are split, ensure that the layout and formatting are as consistent as possible across all sheets.
• Error check sheet: This sheet contains links to all error checks in the model. Error checks should be performed in the calculation section, but a summary of all error checks in one location means that once the model is in use, the modelers can quickly check to see if any of the error checks have been triggered.