Financial Modeling in Excel Considering the Layout and Design of Your Model
Considering the Layout andDesign of Your Model
The problem that needs to be solved and the output required often influence the layout and design of the model. So, it’s not a bad idea to spend some time thinking about the layout before you get started. Not only do the layout and structure of the model relate to the logic and functionality, but they can also influence the look and feel of the model’s interface and how users navigate through the 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
When starting to build your model, start by laying out each of these elements in four separate tabs and think about what will go in each section. Separate each of these elements clearly. Although every model should contain these elements, not every financial model is structured in the same way. Unless a model is very small — like the bus company case study earlier in this chapter — there should be a dedicated tab for each major component of the model.
STEPS TO BUILDING A MODEL
Here are seven steps to follow when starting to build a fi l model:
-
Design the high-level structure.
You won’t know exactly what the layout of the model will be until you actually start building the calculations, but you should have some idea of the tabs. Start by assembling the data you have so far into the broad categories described in the “Defi inputs, calculations, and output blocks” section later in this chapter.
-
Design outputs — summaries, charts, and reports.
Because you already know the problem that your fi model needs to solve, you should have an idea of what that answer might look like. For example, if you’re making a decision about investing in a new product, the output might be the result- ing cash fl and a net present value (NPV). By thinking about the output of your model early on in the process, you’ll be more focused and will ensure that all your calculations work toward the desired end result.
-
Design inputs.
Set up where the inputs and source data will go. Even if you don’t have all the infor- mation yet, set it up so that it can be dropped in at a later date. This can help you make sure that you ask for or gather the data in the right format, as well as design the model correctly. For example, do we need data for the calendar year or the
fi year? Will you need to use the same assumption for every month/year or it is going to change?
-
Start calculating.
Start with a tab labeled “workings” or “calculations,” but keep in mind that this will probably expand as the model grows. Link the formulas to your inputs, but break larger problems into smaller ones and don’t try to attempt too much at once. You might begin by thinking that all expenses can go on one tab, but if depreciation, for example, begins getting rather complicated, you might decide that depreciation needs a tab of its own.
-
Connect outputs.
Link your calculations to the outputs page. Charts are a great way of visualizing and presenting the output of your model. As you’re building the outputs, test at every stage to make sure that the model makes sense, and adjust as necessary.
Structuring your 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
When designing the layout of a model, most experienced modelers follow these rules:
make sure that the user knows exactly what each section is for. Color coding can help with ensuring that each section is clearly defi
» 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 4.
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 fl logically to avoid circular referencing. Be aware that there are times when left-to-right or top-to-bottom data fl can confl 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 effi
» 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 fi model. A cover sheet should not be confused with an assumptions sheet. A model can
never have too much documentation!
Defi inputs, calculations, and output blocks
Typically, modelers work from back to front when building their models. The out– put, or the part they want the viewer or user to see, is at the front, calculations are in the middle, and source data and assumptions are at the back. Like the executive summary, a board paper, or another report, the first few pages should contain what casual viewers need to see at a glance. If they need further information, they can dig deeper into the 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
In my experience, 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 fi outcomes.
They may also contain scenario drop-down boxes or user entry fi lds 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, fi ial 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
