Building a Model

The first and most important thing to do before anything else is to think about model design, structure, layout, and planning. These steps play a key role in the quality and success of modeling project.

For finance-related modelers, designing can sometimes be the most difficult part of building a financial model. A well-designed model will be so straightforwardly logical that it will simply speak for itself. Also, the layout and structure of the model relate to the look and feel of the model, and how users navigate through the model. Let’s say you are creating a high-level five-year sales forecasts. We expect that number to increase by 5 percent every year.

So there are a few golden rules for model design in my experience.

• Separate 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 is also another solution.
• Use each Column for the same purpose. This is extremely important when dealing with time series data.
• Use one formula per Row or Column. This forms the basis of the best-practice principle in order to keep formulas consistent when in a block of data.
• Refer to the Left and Above. The model should read logically, like a book. Calculations, inputs, and outputs should flow logically to avoid circular referencing.
• Use multiple worksheets. Avoid putting everything on one sheet.
• Include documentation sheets. A documentation sheet is where assumptions and source data are stored. Also, a model should not have too much documentation.

There are also 4 key issues we need to think about before starting a model:

• Time series. Time series data are the most popular type in most financial models. We need to pay attention heavily on the frequency of the data.
• Data collection. Often the majority of time is spent not in building a model but rather collecting, interpreting, analyzing and manipulating data to put into the model.
• Model purpose. Think about what outputs we expect the model to show.
• Model audience. No need to build a fancy model for you. In otherwise, you should make a really user-friendly model as possible.

Unless a model is very small, there should be a dedicated tab worksheet for each major components of the model.

• Cover sheet contains many details about the model. Some details as shining examples are:

  • Flow chart of the model structure
    • Table of contents
    • Instructions on how to use the model
    • The purpose of the model
    • Key assumptions integral to the use of the model.

• Input sheet is the place where hard-coded data is permissible.

  • Output, summary, and scenario sheets present the final outcomes.
  • Calculation or working sheets should be split logically and set up consistently.
  • Error check sheet contains links to all error checks in the model. It should include a sheet of the summary of all error checks so that the modelers can quickly check to see if any of the error checks have been triggered.

Although planning in advance is very important for successful modeling, it is unrealistic to expect a detailed project plan prior to starting to build it. The first question is how long or the time it takes to build a financial model. And certainly, the answer is never straightforward. It depends on how much time the modeler has (in most cases, there is never enough time :)) ) and how much detail the user(s) needs. And the second is how to use the permitted time wisely and efficiently. Do not waste time on validating minor assumptions that are not material to the outcome of the model is one of my tips for time-saving.

Besides, for BIG models that will be viewed or reviewed by external parties, a flow chart that maps the model’s structure and how it solves the problem might be useful. Not only does this assist in building the model, it also helps users to better understand and follow the model’s logic, design, purpose, and can be used as a presentation tool when explaining the model. One flow chart that I have recently obtained from the Internet is an illustrative example as follow:

Once the aforementioned factors have been determined, we can begin to create our model. If you are working as the sole financial modeler then we might be able to follow a less formal process, called The Streamlined Version by some practitioners. If you are a member of a team of financial modelers, the steps shall be more detailed, as it is more important to follow a documented and structured process, called The Team Version.

There are mainly 7 steps in the streamlined version¹ as follow:

• Design the high-level structure:
• Design outputs: summaries, charts, and reports
• Design inputs
• Plan calculations by breaking larger problems into smaller ones
• Finalize outputs
• Design sensitivities and scenario analysis
• Assumptions documentation

For the team version [1], we have 12 steps which have been split into 2 categories: planning and building. The planning stage should include:

1. Scope out the project: purpose, problem we need to solve, time frame
2. Assign project tasks based on specific skills
3. Determine the users of the model
4. Design the high-level structure
5. Create a data collection plan

The building stage should include:

6. Build inputs
7. Build calculations and working
8. Build outputs: summaries, charts, and reports
9. Peer and Client review of draft model
10. Design sensitivities and scenario analysis
11. Formal model quality assurance check: includes stress test, check some Excel errors like #DIV/0!, sensitivity tests of outputs
12. Maintain and/or Update the model

In reality, several necessary inputs rely solely on information from other parties, whether internal or external to the organization. This leads to the fact that the receipt of timely and accurate information is much important. Therefore, you may consider creating a formalized information request procedure.

For more efficiency, you should also design the file structure where you can find:

• Models: a list of the model with version-control nomenclature applied.
• Project planning: documents relating to the project plan, resource allocation, and timelines.
• Source data

The best example might be shown as follow:

Next chapter, I will show you some best practice principles of modeling which I have been collecting for several years.

Please leave some comments if there are any questions or recommendations. Thank you!