Wall Street Prep Financial Modeling Best Practices

[Desiderato Chouinard]

Byforecasting the operating and financial performance of a company (or project), financial models are practical for various use-cases and guide decision-making, such as in the context of performing a valuation or capital budgeting analysis.

For example, if your task was to build a discounted cash flow (DCF) model to be used in a preliminary pitch book as a valuation for one of 5 potential acquisition targets, it would likely be a waste of time to build a highly complex and feature-rich model.

If your model is a key decision-making tool for financing requirements in a potential recapitalization of Disney, a far higher degree of accuracy is crucial. The differences in these two examples might involve things like:

If the purpose of the model is to analyze the potential acquisition of Disney by Apple, you would build in far less functionality than if its purpose was to build a merger model that could handle any two companies.

For instance, if an investment banking analyst submits a valuation model to their direct supervisor, or an associate, the process of auditing the model should be relatively easy, assuming the model was built properly and abides by the standard modeling best practices and industry conventions.

Since virtually all financial models will help decision-making within various assumptions and forecasts, an effective model will allow users to easily modify and sensitize various scenarios and present information in various ways.

Just about everyone agrees that color-coding cells based on whether the cell contains a hard-coded number or a formula are critical. Without color coding, it is extremely difficult to visually distinguish between cells that should be modified and cells that should not (i.e. formulas).

A properly built financial model will further distinguish between formulas that link to other worksheets and workbooks, as well as cells that link to financial data services, like Capital IQ and FactSet.

Our recommendation is Convention 1. The reduced likelihood of error from easier subtotaling alone makes this our clear choice. In addition, one of the most common mistakes in modeling is forgetting to switch the sign from positive to negative, or vice versa when linking data across financial statements. Convention 1, by virtue of being the most visibly transparent approach, makes it easier to track sign-related mistakes.

Financial data should be presented from left to right. The right of the historical columns are the forecast columns. Hence, the left section font color is blue, while the projection section on the right is black.

There are several excellent alternatives to IF that top-notch modelers frequently use. They include using Boolean logic along with various reference functions, including MAX, MIN, AND, OR, VLOOKUP, HLOOKUP, and OFFSET.

While both formulas are challenging to audit, the formula using IF statements is more difficult to audit and is more vulnerable to getting completely out of hand with additional modifications. It uses nested (or embedded) IF statements, with which our feeble human brains have a hard time once there are more than one or two.

For this, go ahead and daisy chain. The reason is that straight-lining base period assumptions are an implicit assumption, that can change, making it possible for certain years in the forecast to ultimately end up with different assumptions than other years.

Instead, use a clean reference =input!C7 and a separate cell for the calculation. While this creates a redundant cell reference, it preserves the visual auditability of the model tab and reduces the likelihood of error.

One reason is simply poor practice. Some models would benefit from an input/calculation/output separation but are often built with no forethought given to structure. Imagine building a house without any pre-planning.

Most investment banking models are either quarterly or annual. For example, a U.S. equity research earnings model will always be a quarterly model because one of its key purposes is to forecast upcoming earnings, which are reported by firms quarterly.

Similarly, a restructuring model is usually a quarterly model (or even a monthly or weekly model) because a key purpose of this model is to understand the cash flow impact of operational and financing changes over the next 1-2 years.

However, attaching a DCF valuation to the combined merged companies may also be desirable. In this case, a possible solution is to roll up the quarters into an annual model and extend those annual forecasts further out.

Circularity refers to a cell referring to itself (directly or indirectly). Usually, this is an unintentional mistake. In the simple example below, the user accidentally included the sum total (D5) in the sum formula. Notice how Excel becomes confused:

While the underlying logic for wanting to incorporate a circularity into a model may be valid, circularity problems can lead to minutes, if not hours, of wasted auditing time trying to locate the source(s) of circularity to zero them out.

Unlike software specifically designed to perform a particular set of tasks (i.e. real estate investment software, bookkeeping software), Excel is a blank canvas, which makes it easy to perform extremely complicated analyses and quickly develop invaluable tools to help in financial decision-making.

This creates room for error because Excel is dealing with blank values. Formulas like IFERROR (and ISERROR), ISNUMBER, ISTEXT, and ISBLANK are all useful functions for trapping errors, especially in templates.

Scenario analysis can be performed in financial modeling by creating a toggle switch that changes the model assumptions based on the active case (e.g. Base Case, Upside Case, Downside Case), whereas sensitivity analysis can be conducted via building data tables that sensitize the key variables with the most influence on the model output.

Because financial modeling requires a great deal of spreadsheet work, most often in Microsoft Excel, I wanted to take the time to highlight some important features of many financial models that can be found on Wall Street and in Corporate America. A few of these items, common to most financial models that you will come across, revolve around proper color-coding (for ease-of-use) and dealing with circularity problems (for proper functionality). While there are many other topics of discussion regarding financial modeling, such as scenario/sensitivity and IRR returns analysis (for evaluating and interpreting the value of a firm or security), we will save those for future articles to come.

Above is an example of the use of color-coding in a financial model. We have historical revenues for the years 2004-2006 manually inputted into the model, and this is reflected in the use of blue text in the cells and yellow shading in the background. This color combination makes it very easy for a financial model user to identify what has been manually typed into the model and locate what other cells may need to be changed in order to adjust projections and assumptions, such as cells F4 through H4 predicting revenue growth rates. This blue text with a yellow background is a standard practice across Wall Street and should be incorporated into any financial model. Corresponding with this is the practice of using black text font and a clear background to identify formulas in a financial model. Cells D4 to E4 and F3 to H3 are examples of this practice, where historical growth rates are being calculated as well as future revenue amounts. Below are some general guidelines when it comes to cell color-coding and how to apply this formatting.

Effective financial modeling requires applying best practices and the two mentioned above (color-coding and handling circularity) are two of the most important. A dynamic, functioning model is very useful when trying to create financial projections or to evaluate investment opportunities, but only to the extent that the model is easily understood and easy to navigate. Incorporating these best practices will allow you to save time and headaches in the future, and make it possible for others to review your work and fix the model when you are not around.

This course teaches trainees how to construct comprehensive LBO models used to support real transactions and gives insiders a walkthrough of the nuances of the private equity deal process and the non-modeling day-to-day responsibilities of a private equity professional.

This course was created by former sales and trading professionals focused on what interns, new hires, and early career analysts need to know on the job. Designed following extensive discussion with hiring managers at major investment banks, we built our Fixed Income program around practical requirements like Bloomberg, with Excel exercises to demystify how Bloomberg calculates Yield and Duration on a bond.

Everything you need to build and interpret real estate finance models. Led by real estate private equity expert Aaron Hancock, you'll not only learn modeling best practices across the different real estate property types, you'll learn how real estate investors actually evaluate opportunities.

This program breaks down everything you need to build and interpret project finance models for a transaction. Learn project finance financial statement modeling, debt sizing mechanics, running upside & downside cases and more.

This module introduces the FP&A profession and its role in businesses of various sizes across different industries. We then examine common FP&A work products, evaluate current and emerging tools of the trade, and introduce a key skill set for FP&A professionals: strategic cost accounting.

We review best practices for long-range planning and effectively managing the five phases of the annual budgeting process. The module covers how to get more out of your planning process by collaborating closely with the cross-functional team.

You will learn the forecasting techniques that are used today by major firms, including time series analysis, driver-based planning, sales pipeline forecasting, zero-based budgeting, and scenario planning methods. We cover methods for accurately projecting sales, expenses, cash flow, headcount, and capex.

3a8082e126