Financial Modeling Best Practices

This guide will provide readers with information on financial modeling best practices in an easy to follow, step-by-step guide on how to building a financial model in Excel. These tips are adapted from CFI instructors with over 15 years of financial analyst training experience.

Screenshot from CFI’s financial modeling courses.

Key Structure of Model Building

In modeling, it is crucial to apply a structured approach – a robust model contains three core building blocks: inputs, processing, and outputs. It is important to note that the inputs must be clearly separated from the processing and outputs of a financial model.

Inputs should at all times be clearly defined and only be entered once. The processing of inputs to outputs must be not only transparent but also must be broken down into simple steps that are easy to follow. Finally, outputs should be well organized, so users have quick access to the outputs they wish to see or use.

Best Practices in Financial Modeling

Before we examine the building blocks and financial modeling best practices in Excel, it is important to note that model building is not an iterative process. In fact, models that are built on the fly without scrutiny or attention to detail are typically prone to errors.

In order to minimize errors when building your financial models, be mindful of the following five steps:

1. Clarify the business problem
2. Simplify as much as possible
3. Plan your structure
4. Build structural integrity
5. Test the model

1. Clarify the business problem and intended goal

• What problem is this financial model designed to solve?
• Who are the end users?
• What are users supposed to do with this model?

2. Try to keep the model as simple as possible

• What is the minimum number of inputs and outputs required to build a useful model?
• Remember that the more assumptions a model has, the more complex it becomes

3. Plan your model structure

• Plan how the inputs, processing, and outputs will be laid out.
• Try to keep your inputs in one place as possible in order to have a quick overview of all inputs and their impacts on the model.

4. Protect data integrity

• Utilize Excel tools to protect data integrity, including “data validation” and “conditional formatting.”
• This limits other users ability to accidentally “break” the model

5. Use test or dummy data

• Ensure that the model is completely functional and works as expected
• Stress test it by putting in scenarios that should cause the model to run of out cash, grow at a flat rate (no changes) and other scenarios that are easy to sense check

Inherent Tensions in Model Building

In model building, inherent tensions create a spectrum of models, ranging from realistic to robust. While large and realistic models offer users a high degree of detail and precision, due to their complex nature, they are also more challenging to build, follow and audit.

On the other hand, small and robust models are generally easier to build, follow and audit, yet lack the degree of precision necessary for decision-making. The best financial models work to reconcile these opposing forces, hence keeping inputs and outputs as simple as possible, while still providing sufficient details for decision-making.

Model Inputs

When building the inputs of your model, it is important to be mindful of these several factors:

• Input accuracy
• Reasonable data ranges
• Easy to use
• Easy to understand
• Easy to update data

Your model should be structured so that you should only enter each data once. Moreover, all inputs should be differentiated from the outputs by using different colors, highlights, and fonts so they are easily identified. Yellow shading or blue color fonts are often used to indicate inputs.

Finally, it is important to fully utilize existing Excel tools to ensure data integrity. You could use data validation, conditional formatting and comments to help you maintain the integrity of your data and model inputs.

Color coding should be as follows:

• Blue: Inputs, assumptions, and drivers
• Black: Formulas and calculations (references to the same worksheet)
• Green: Calculations and references to other sheets
• Red: References to external links or separate files

Screenshot from CFI’s Financial Modeling coursesr.

Model Processing

Model processing is about translating inputs into outputs. Hiding calculation cells or putting too many calculations into a single cell makes models harder to maintain an audit. Ideally, optimal model processing should be easy to maintain, transparent, and accurate. In order to build an optimal model, users should:

• Break down complex calculations into several steps;
• Use comments and annotations to explain how the model works;
• Use formatting to ensure formulas are not accidentally overtyped; and
• Calculate final figures on your processing worksheets, then link these figures into the final workbook sheets

Image from CFI’s Financial Modeling courses.

Model Outputs

Financial model outputs include balance sheet forecasts, cash flow forecasts, DCFvaluations, and so on. To learn more about DCF valuations, please see our Business Valuation Fundamentals course.

Ideally, a model’s output cells should be easy to understand, unambiguous, and provide key results to aid decision making. In order to build an ideal model, users should:

• Make outputs modular so the end users can choose which outputs they wish to review. For example, one can keep the balance sheet, income and cashflow forecast in separate groups or worksheets.
• Consider creating a summary output sheet that allows users to review the key model outputs without having to go through the entire model.
• Utilize colors to clearly categorize and indicate output formulas and cells.
• Consider protecting your output cells and worksheets to maintain data integrity.

Source: CFI’s Financial Modeling courses.

By Corporate Finance Institute