Financial Modeling Best Practices

Transcript (Edited for Clarity)

Introduction

Hi, and welcome to this video on financial modeling best practices. I’m Ian Schnoor. Over the next hour I will take you through some of my favorite tips and skills for building best-in-class financial models. Financial modeling has become one of the most important skills for finance and accounting professionals, so I’m excited to share practical guidance with you today. Let me share my screen so we can get started.

Let me begin with a quick introduction so you know a bit about me and my background. I’m joining you from Toronto, Canada, where I am based. I am the Executive Director of Financial Modeling Institute, the world’s only financial modeling accreditation organization. I have spent most of my career thinking about modeling, building models, and teaching modeling.

I started in investment banking at BMO and at Citibank. I then started a training firm more than 20 years ago called The Marquee Group, where I taught financial modeling to students all over the world. After 20 years, I sold that training business to Training The Street, a U.S. training firm, and now I am exclusively focused on helping to build FMI globally.

I also had the honor of creating the Financial Modeling Practical Skills module that is now part of the CFA Program and is required at Level I. I am a lecturer at Queen’s University, a large business school in Canada, where I teach Advanced Financial Modeling. Financial modeling has been a consistent focus of my career, and in this video I will get into several critical ideas.

About FMI

FMI is the only financial modeling accreditation body in the world. There are three levels of accreditation, which you can review in these slides or on our website. The first level is the Advanced Financial Modeler, or AFM.

Session roadmap

As we go through, I will share a notes file I will build live and a financial model that we will use to discuss best practices. There are five key takeaways for this session:

1. Model flow
2. Managing assumptions
3. Creating a scenarios page to manage uncertainty
4. Repeat and link
5. Build it and link it

I will cover all five over the next hour.

Financial modeling is a discipline in its own right. It draws on accounting, finance, design, and technical skills, but modeling itself is its own discipline. In this session I will share a range of practical ideas in the notes file and the model you will see on screen.

What is a financial model

A financial model can mean many things, but in finance and accounting it usually means a forecast of a company’s financial statements built in a spreadsheet, typically Microsoft Excel. We forecast the income statement, cash flow statement, and balance sheet. That is the core of it.

How far into the future do people forecast? Usually five to ten years. We refer to timing choices as the model’s periodicity. Five to ten years is common, though some models are shorter or longer.

Why do we build these forecasts? Because financial models have become the most important decision tools in finance. Almost every major decision today is made on the back of a model.

Examples include credit decisions such as whether a company can support more debt or risks default, investment decisions such as whether to buy or sell shares, research forecasts for price targets, standard valuation and security analysis, M&A analysis, LBOs, budgeting and operational planning such as headcount or marketing spend, and risk analysis through sensitivities. There are many other use cases as well. Many types of professionals need good models.

The problem

Although the purpose sounds straightforward, most models are a mess. They are hard to understand and use, often contain errors, and make effective decisions difficult. The goal of this session is to give you ideas that help you build best-in-class models that are powerful decision tools.

Two ways a model must work

Every good model must work in two different ways.

First, it must work electronically. Every formula should make sense. If I email you a model and you start clicking through, each calculation should be logical and understandable. You should not see huge formulas that wrap across the screen. Long formulas are not optimal because no one can understand them.

Second, it must work on paper or as a PDF. You should be able to print it or export it and have it feel like a presentation. A good model is a communication tool. It tells a story about the company you are forecasting. What is the value. Can it take on more debt. Your model should tell that story.

Modeling is also a leadership development tool. People who build strong models become better leaders because they ask good questions, challenge assumptions, design and build clearly, and communicate effectively from the model.

Planning and time allocation

Strong models do not get built by accident. If you open Excel and start typing, you will not end up with an effective model by chance. Planning and model flow are critical. A good modeler often spends no more than 50 percent of the time in Excel. The rest is spent talking to people, asking questions, doing research, challenging assumptions, presenting, and making changes to produce a powerful communication tool.

Slides and model setup

There are slides in the deck, from slide 15 through slide 19, that cover model design, planning, and flow. I will talk about model flow, but first let me show you a financial model.

Henderson Manufacturing sample model

This is the Henderson Manufacturing model, one of the sample models used at Financial Modeling Institute. It is similar to what candidates build on the AFM exam. On the exam, candidates build a full company model from scratch in under four hours. This sample gives you a sense of what that looks like.

The workbook contains a Cover sheet, a Summary (Executive Summary), an Assumptions sheet, a Scenarios sheet, and a Model sheet. The Model sheet contains the calculations and the financial statements. On the Model sheet you will see a Revenue section, cost calculations including Cost of Production, then the Income Statement forecast for five years, followed by the Cash Flow Statement and the Balance Sheet. Below the statements there are supporting schedules and calculations.

How the model looks on paper

I also want you to see how the model looks on paper. Printed to PDF, this example runs 15 pages. The first page is the cover, which sets the stage and makes it feel like a financial presentation. The next page is the Executive Summary with Base, Best, and Worst cases presented up front because clients want the answer immediately.

After the Executive Summary come the Assumptions. Inputs are grouped clearly, and there are multiple pages as needed. Then the Scenarios page. Next the Revenue forecast, then Costs, the Income Statement, Cash Flow Statement, and Balance Sheet.

Notice the design and flow. Every page shows the company name, a title, and a subtitle. Left and right margins are consistent. Pages are numbered. Font sizes are consistent. It feels like a document and a presentation. That is the goal when you build an effective financial modeling tool.

Optimal flow

In a good model, the flow is as follows:

1. Cover page to set the stage and create a presentation feel.
2. Executive Summary to provide the answer up front. Use tables and charts as helpful.
3. Assumptions so the reader can see how you got there. As soon as someone sees the answer, they want to know how you arrived at it.
4. Scenarios to manage uncertainty and show upside and downside.
5. The Engine that contains schedules, calculations, and the financial statements.

Good models accommodate this flow because it helps tell the story.

Assumptions up front and a powerful Excel skill

Keep all assumptions up front so readers can see inputs before calculations. As soon as you show an answer, the next question is how you got there.

Here is a powerful Excel skill that lets you keep assumptions up front while still monitoring results deep in the model. On the Income Statement, suppose we are watching five years of EBITDA and five years of Net Income. If we change a raw material assumption on the Assumptions sheet, we want to see the impact immediately without jumping back and forth.

Use Excel’s Watch Window. It lives under the Formulas menu. Keyboard shortcut: Alt, M, W.

Open the Watch Window and click Add Watch. Select the first three years of EBITDA and click Add. Do the same for three years of Net Income. You can collect answers from anywhere in the workbook and display them in the Watch Window.

Now when you change an input, for example Raw Materials from 2.26 to 2.50, keep your eyes on the Watch Window and you will see EBITDA and Net Income update instantly. You can add any metrics you care about.

To make the Watch Window clearer, use the Name Box to name key cells, such as EBITDA and Net Income. When you name a cell, the name appears in the Watch Window, which makes tracking even easier. Change the input back to 2.26 and watch the numbers update again. It is a powerful tool that supports optimal flow: keep assumptions up front and still see key outputs in real time. I encourage you to use it in your Excel work.

Managing uncertainty with a Scenarios Page

Every financial model you build should include a Scenarios Page. The purpose is to manage uncertainty and variability, so you can see upside and downside cases.

Some assumptions are easy to forecast. For example, a tax rate of 25 percent will stay at 25 percent until the government changes it, which does not happen often. Depreciation rates are also relatively straightforward.

Other assumptions are difficult to forecast. Examples include cost inflation, interest rates, exchange rates, and commodity prices. These are economic variables that can move unpredictably. We call these difficult assumptions key drivers.

Because of these key drivers, we want to run multiple cases in our model. At a minimum, create a Base Case, a Best Case, and a Worst Case. That way, you can test how results change when key drivers shift.

Three steps to build a Scenarios Page

There are three main steps to build an effective Scenarios Page. Let’s walk through them together in the model.

1. Build large rectangles for each key driver.

Each rectangle has three rows: Base, Best, and Worst. For example, create one rectangle for cost inflation, another for sales prices, and another for sales volumes. These input values are colored blue, consistent with standard modeling practice. Blue cells indicate inputs or assumptions.

How do you choose the values? Do research, talk to your team, consult your manager, and then enter your chosen assumptions into the rectangles.

2. Add a numeric switch.

Create a simple input cell that stores the number 1, 2, or 3. This switch controls which scenario is running. If the switch is set to 1, the Base Case runs. If set to 2, the Best Case runs. If set to 3, the Worst Case runs.

You will see the values from the selected case reappear in a single skinny row above or below the rectangles.

3. Build the live case row.

This skinny row is the live case. It is the row that feeds into the model. It shows only one set of numbers at a time, depending on the switch value.

For example, when the switch is 1, the Base Case values appear in the live row. When the switch is 2, the Best Case values appear. When the switch is 3, the Worst Case values appear. The live row should be black to differentiate it from input cells.

Formulas to power the live row

Many people use nested IF statements to build the live row. For example:

=IF(Switch=1, Base, IF(Switch=2, Best, Worst))

This works, but it creates long, hard-to-read formulas. Nested IFs are not ideal.

A better option is Excel’s CHOOSE function.

Type =CHOOSE(, then click on the switch cell, lock it with F4, and list the three options: Base, Best, and Worst. The CHOOSE function will select the appropriate case depending on the switch value. If the switch is 1, it selects the first option. If the switch is 2, it selects the second. If the switch is 3, it selects the third.

The CHOOSE function is simple, clean, and easy for clients to understand. It does not allow you to enter a range, so you must select each cell separately, but this actually keeps the formula transparent.

Other formulas can work as well, such as INDEX, OFFSET, XLOOKUP, or VLOOKUP. There are many ways to design the live row, but CHOOSE is highly recommended for clarity.

Creating a user-friendly switch

One issue with the numeric switch is that the number itself (1, 2, or 3) does not mean anything to a client or manager. To make it more user-friendly, build a drop-down control that lets the user pick Base, Best, or Worst directly.

Here’s how:

1. Enable the Developer tab in Excel. By default, it is hidden. To turn it on, go to File > Options > Customize Ribbon, and check Developer.
2. On the Developer tab, click Insert, then choose the Combo Box from Form Controls.
3. Draw the Combo Box on the sheet.
4. Right-click the Combo Box and select Format Control. Enter two items:
   • Input range: the list of scenario names (Base, Best, Worst).
   • Cell link: the numeric switch cell.
5. Now, when you choose an item from the drop-down, Excel writes a 1, 2, or 3 into the switch cell.

This makes the Scenarios Page intuitive and professional. You can move and resize the Combo Box and position it neatly above the switch.

Summary of the Scenarios Page process

• Step 1: Build large rectangles of blue input cells for each driver.
• Step 2: Add a numeric switch cell.
• Step 3: Use a formula such as CHOOSE to create the live case row.
• Optional: Add a Combo Box to make the switch user-friendly.

This structure allows you to run Base, Best, and Worst cases seamlessly. It helps you manage key drivers and present variability clearly.

Repeat and Link

The next important best practice is what I call Repeat and Link.

The concept is simple: whenever you need to create a formula in a model, first repeat the values that will go into the formula, then link them.

Why is this important? Because we never want long, complicated formulas in financial models. Long formulas are difficult to understand, difficult to audit, and easy to break. Instead, make each formula short and transparent.

Let’s look at the Revenue Schedule in the Henderson Manufacturing model. The schedule has three sections: sales price, sales volume, and revenue.

Gross revenue is simply price multiplied by volume, divided by 1,000 to express the result in millions. That is straightforward.

But notice the structure of the pricing section. This company has both a gross price and a net price. Many businesses do. Between gross and net there is usually a deduction. In this case, Henderson pays for freight and warehousing.

Here’s how it is set up:

• Gross price is repeated from the Scenarios Page.
• Inflation is also repeated from the Scenarios Page.
• Freight and warehousing is calculated as the prior year’s cost times one plus inflation.
• Net price is then simply gross price minus freight and warehousing.

Because the gross price and inflation were repeated first, each formula is short and easy to follow. Anyone reviewing the model can see exactly how the numbers flow.

This is the essence of Repeat and Link. Pull required inputs close to where they are used, then build simple formulas from them.

The problem with long formulas

Many modelers create long formulas that reference values scattered across different sheets. For example, someone might write a formula that divides a number from one sheet, multiplies it by a number from another sheet, and subtracts a number from a third sheet, all in one cell.

This is a disaster. Such formulas are almost impossible to audit or understand.

The better approach is to insert rows and repeat each required input locally. Then create one simple formula that references those repeated values. You get the same result, but the formula is clean, auditable, and confidence-inspiring.

Yes, this makes the model longer. But clarity and reliability are far more important than compactness.

Build it and Link it

The final best practice is what I call Build it and Link it.

This means you should never build complex formulas directly in the financial statements. Instead, build supporting schedules for revenue, costs, depreciation, and other items. Then link the totals from those schedules into the statements.

Let’s look at the Henderson model again.

On the Income Statement, the revenue line is not a formula. It is simply a link to the bottom of the Revenue Schedule. The cost of goods sold line is a link to the bottom of the Cost Schedule. Depreciation is a link to the Depreciation Schedule.

Totals such as EBITDA can, of course, be simple calculations (revenues minus costs, for example). But each input line on the Income Statement is just a link.

The Cash Flow Statement is built the same way. Each line is either a link or a straightforward subtotal. Net income is a link from the Income Statement. Cash at the bottom links back up into the Balance Sheet.

The Balance Sheet itself is also structured this way. Each line is a link or subtotal, with no long formulas buried in the statements.

This approach creates alignment and consistency. The schedules do the heavy lifting. The statements summarize the results. Anyone who opens the file can trace values easily and understand exactly how they were derived.

Summary of Repeat and Link, Build it and Link it

• Repeat values before using them in formulas. Avoid long, multi-sheet formulas.
• Keep each formula short, simple, and easy to follow.
• Build supporting schedules for detailed calculations.
• Link those totals into the financial statements.

Following these practices makes models easier to build, easier to audit, and much more reliable as decision-making tools.

Summary of the five best practices

We have now covered the five critical best practices for building strong financial models. Let’s review them.

1. Model flow matters.

The order of sheets and how the model is presented are important. Every good model should begin with a Cover page, followed by an Executive Summary, then Assumptions, Scenarios, and finally the Engine with schedules and statements.

2. Keep assumptions up front.

Place all inputs clearly at the beginning of the model. This makes it easy to review and update. Use Excel’s Watch Window to monitor outputs in real time while adjusting inputs.

3. Build a Scenarios Page.

Identify key drivers that are difficult to forecast. Enter Base, Best, and Worst case values in blue input cells. Use a numeric switch and a live row powered by a simple formula such as CHOOSE. Add a Combo Box for user-friendly control. This structure makes it easy to test upside and downside.

4. Repeat and Link.