Best Practices for Financial Modeling

How to create a reliable financial model in Excel?

A successful business must be backed by calculations and numbers. You need to forecast expenses and earnings to support current and future decisions.

What is a financial model and what is it for?

A financial model is a set of mathematical calculations aimed at translating hypotheses about the behavior of a business project into financial parameters. The financial parameters are then used to evaluate this business project. The purpose of the financial model is to draw conclusions and make business decisions. Therefore, the financial model is usually executed in an analytical tool such as spreadsheets in MS Excel.

A company's financial model typically forecasts financial statements, which are income statement, balance sheet, cash flow, and other reports such as company valuation models. A financial model can be very detailed and complex, prepared by professionals working for investment institutions or relatively modestly prepared by business owners, and then it is more robust. The best models are those that are detailed enough to make decisions and are easy to understand. Therefore, some trade-offs should be made in terms of complexity and resources invested in developing the model.

Before working on a financial model, it is important to understand the purpose of it. You should ask yourself the following questions:

  1. What will it be used for?

  2. What kinds of decisions will be made?

  3. Who will be the recipient?

These are important questions to ask before starting work on a financial model. You need to know in advance what data needs to be collected and what to present as a model outcome.

If the intention is for the financial model to be part of the business plan then it is natural that both documents must match. In fact, the business plan should present a convincing story of the project and the financial model should convey it in the form of meaningful financial numbers.

How to rationally make a financial model?

Once the purpose of the model is clear, it's time to structure it. The sensible way is to draw your model's flowchart on a piece of paper. It should have 3 well-defined parts as follows:

  1. Inputs - data assumptions

  2. Calculations - various financial calculations

  3. Outputs - reports and financial conclusions

The type of input data, including data sources, should be agreed in advance. The model should be as simple as possible and clear enough for other people to understand it and its calculations. You shouldn't make a black box out of it that only you understand. If this happens, people will distrust the results and hesitate to make decisions.

The model should be structured in such a way that the input data is entered only once in a dedicated data entry worksheet. Input cells should be clearly marked with a color or a special font format. Then the worksheets with model calculations should have no hard coded numbers. The inputs for those calculations should be linked to data entry worksheet only. This rule will be especially important when updating the model or running various scenarios. Sticking to this rule will pay off, even if you are tempted to cut corners when time is pressing.

Use comments and annotations to explain how the model works and where the input data comes from. Add explanatory titles for individual worksheets, put correct headings in columns, and write clear descriptions in rows. Use consistent number formatting and use the same terminology for variables throughout the model. If necessary, use the password protection features in the program to prevent unintentional changes to the cells.

The calculations themselves should be grouped on separate worksheets and have a clear logical flow. The best way to do a calculation is to start at the top of the worksheet and make your calculations step by step until you reach the bottom of the worksheet. Don't reference one cell to another cell that is somewhere on the bottom of the sheet, and then reference that cell back to the cell on the top of the worksheet. It makes calculations hard to follow and see the logic. It also makes error checking difficult. So, keep your calculation flow clear from top to bottom and left to right. If you must use a database as input, make sure that processing of data such as PivotTables is done on a separate worksheet. This is to distinguish the original input data from the computational process.

Write formulas in a single cell as simply as possible or break your calculations into several steps to show the flow. However, if you write complex and long formulas in one cell, or use several layers of logical formulas such as "IF" or "AND", then validating the calculation will be difficult, especially for your audience. Also, don't hide cells with calculations. If you think that hiding some columns or rows would be more understandable from a presentation point of view, show the reader that fact. MS Excel has good grouping functions to help you manage large worksheets and present data in a nice and neat way.

Make sure you check your calculations at every step of model development, especially when moving data from one worksheet to another. Check even simple calculations like adding columns and rows. It is useful to use MS Excel's Formula Audit tools such as Trace Dependents and Trace Precedents to check links throughout the workbook.

Another way to maintain model integrity is to introduce cells with checkpoints that will perform the reconciliation calculations. The calculations can be very simple, such as adding a few cells in one place and comparing the result with the values elsewhere in the worksheet. Checkpoints should highlight errors immediately, for example by turning cell color from green to red. Conditional formatting can also be used to validate calculations by analyzing trends and patterns. You can even dedicate a separate worksheet with checkpoints grouped from across the workbook, but it usually makes more sense to put them next to calculations on your worksheets. The presence of control points adds credibility to the entire model.

The outputs and reports should be placed on separate worksheets with clear titles such as: Income Statement, Balance Sheet, Cash flow, Valuation. This will make it easier for readers to navigate the model and make the presentation easier to understand, but make sure your reports reconcile with each other.

It is important to plan how to present your financial model. You should have an idea of what data the recipient would like to see first and then go into detail if necessary. Therefore, the model should have an opening worksheet, called the "Executive Summary", containing all the key assumptions and results of the model supported by comments and infographics. It is a good practice to create a dynamic form of Executive Summary and visualize different scenarios, for example: pessimistic, moderate, expected, optimistic. The format of this presentation should be as understandable to the audience as possible without or very limited vocabulary that is understandable only to experts in the field.

So, when you have a financial model ready, make sure to test it against a different set of assumptions. Perform stress tests with high or low values and see how the model performs in extreme conditions. Then send it to a few people who can give honest feedback in terms of clarity and completeness. Ask them if they think your model is telling them a compelling story and what questions they may have for the model. You can also contact me about your model and I will be happy to help.