What is Financial Modeling?

Can you predict the future using your company’s current financial data? Financial modeling attempts to do just that.

Financial modeling is a representation of a company’s revenue and expenses, typically in the form of a spreadsheet. Using various “what if” conditions, the financial model can predict the impact of a future event or decision based on its calculations.

Its power lies in its ability to give reasonable forecasts of company performance based on given variables or assumptions.

On what factors are the forecasts based? Two things: the company’s historical performance and future assumptions.

Basics of Financial Modeling

Developing a financial model starts with a company’s financial statements, particularly the income statement, balance sheet, and cash flow statement, and supporting schedules.

There are various kinds of financial models. These may vary in their results, depending on the inputs and assumptions given. Its primary purpose is to assist in decision making and strategic planning in the following areas:

Financial Modeling Examples

Financial models can be applied in many different ways. Here are some specific examples:

Who builds and uses financial models?

Financial professionals such as analysts, corporate development, CFOs, VPs of Finance and others develop financial models. They are applied in various industries, including:

Banks

Commercial Banking, Sales & Trading, Equity Research, Investment Banking.

Institutions

Research, Private equity, portfolio management.

Corporate

Financial planning and analysis (FP&A), Treasury, Investor relations, Corporate development.

Public Accounting

Due diligence, transaction advisory, valuations.

Types of Financial Models

Corporate finance professionals and others can use a variety of financial models. Here are different types.

Three Statement Model

Under this basic model, the three financial statements – income statement, balance sheet, and cash flow – are linked together with formulas in the spreadsheet. Accounts are set up and connected. Inputting a set of assumptions drives the financial model.

Discounted Cash Flow (DCF) model

Using the three statement model, the DCF model evaluates a company based on the NPV (net present value) of the company’s future cash flow. It takes the cash flow financial statement, makes the necessary adjustments, and then uses the discounting function (XNPV function in Excel) to discount them back to the business’ current day’s Weighted Average Cost of Capital (WACC).

Merger Model (M&A)

This model is more advanced than the “three statement” and DCF models. It is used to assess the pro forma accretion and dilution of a merger or acquisition. In the spreadsheet, each tab represents a company. The consolidation of all companies results in the “merged company.”

Initial Public Offering (IPO)

IPO models are used to value the business before going public in the stock market. It involves comparative company analysis, based on assumptions on how much investors will be willing to pay for the company under assessment. Valuation in this model includes an IPO discount – this will ensure that the stock will trade well even in secondary markets.

Leveraged Buy-Out Model (LBO)

The LBO model is an advanced form of financial modeling. Since it is very detailed, this model is the most challenging. Multiple layers of financing have circular references and need cash flow waterfalls.

Sum of the Parts Model

This model consolidates various DCF models. Then, there is the addition of business components that may not be suitable for DCF analysis. Marketable securities are not ideal for DCF analysis since they are valued based on the market.

Summing up the parts will result in the Net Asset Value.
Net Asset Value = Value of business unit (A) + Value of business unit (B) + Investments (C) – Liabilities (D)

Consolidation Model

The consolidation model is similar to the “sum of the parts model,” wherein many business units are additions into a single model. In the spreadsheet, each business unit has its tab, and a consolidation tab sums up all business units.

Budget Model

This model’s purpose is to create a budget for the future year(s). The budget model is dependent on the income statement and is designed based on monthly or quarterly figures.

Forecasting Model

Users compare the results of the forecasting model and the budget model. They are either combined in one worksheet or separate spreadsheets.

Option Pricing Model

Factors determining the value of an option include current stock price, the intrinsic value, when the option will expire, also known as time value, dividends paid and interest rates. The option pricing model commonly uses complex formulas such as binomial or trinomial trees or the Black-Scholes formula.

Financial Modeling Best Practices

There is no one formula in creating the right financial model. Why? Because a model is dependent on its purpose. Therefore, understanding the intent in generating the model is the key to creating an optimal structure.

Before a model’s creation, it is critical to know beforehand its level of detail – a concept called “granularity.” The more granular a model, the more difficult it will be to understand. Moreover, granular models will increase the possibility of errors.

Aside from granularity, the structure also depends on how flexible it should be. How frequent will the model be used? How many will use it? Will the same model be used for various purposes?

For models used for one specific reason or a single company, the model will be less flexible. However, a model that will be frequently reused (like a template) needs more flexibility.

A good model allows users to change and simulate different scenarios. The information should also be adaptable to various formats.

Another consideration is transparency. When a model is too complicated, it becomes less transparent. Here are some “best practices” to improve transparency.

Formatting

One way of improving the format is by color-coding cells to differentiate between hardcoded numbers and formula-derived numbers. Formulas that link to other worksheets and cells that connect to data services can also be color-coded differently.

Use comments for footnotes and to give specifics on the data.

Be consistent with sign conventions – the usage of positive or negative signs to represent income and expenses.

Formulas

Formulas should be intact – avoid encoding hard-coded numbers into a cell reference.

Use the roll-forward forecasting approach to connect the current period’s forecasts to those of prior periods.

Break down a complicated formula into multiple cells.

Referencing cells

Do not encode the same content in different places. For example, once you have encoded the company name in the first worksheet, there is no need to re-type the name in other sheets. Reference the first worksheet instead.

Calculate instead of hardcoding values. The only exception is when using “straight-lining” base period assumptions.

Another thing to avoid is formulas referencing multiple worksheets. Bring in data from other spreadsheets into the active sheet with calculations.

Avoid linking files. Accidental moving or deletion of worksheets frequently happen. Moreover, people may not have the authority to access some data. If linking is necessary, color code instead.

Worksheets

Long worksheets mean endless scrolling with reduced visibility of sections. The standard solution is to use multiple spreadsheets. However, doing so increases the possibility of linking errors. Therefore, the preference is still using long worksheets. Users are advised to utilize the split-screen functionality, maintain clear headings, and link from a cover sheet or table of contents.

Avoid hidden rows. These may be missed out and even pasted over by mistake.

For models with high granularity, keep the assumptions (also called inputs) separate from calculations and outputs. Having only one place to modify or remove assumptions.

Lastly, make use of elevator jumps, especially in long worksheets. Reserving the leftmost column for placing an “x” (or another unique character) will make navigation among sections easier.

Periodicity

Models can either be quarterly or annual or even both. When deciding on a model’s periodicity format, there are two best practices.

First, the model’s setup must be with the smallest period wanted, with extended periods rolled up from these shorter periods.

Secondly, the quarterly and annual dates should be in separate worksheets to facilitate audit and maintain consistency.

Circularity

When a cell refers to itself, either directly or indirectly, this is called “circularity.” Using this feature causes errors in Excel. However, circularity can be intentionally used, depending on the purpose of the model. To address potential mistakes, the use of a circuit breaker is advisable. A circuit breaker is a central place that resets any cell containing circularity.

Also, avoid using macros. Aside from the fact that not everyone is familiar with macros, opening files might be a problem. Only print macros are allowable in financial models.

Error checking

Have built-in error checks to alert users to mistakes.

Use direct calculations with error checks instead of “plugs.” Plugs do not highlight errors when there is mislinking.

Consolidate all error checks in an “error dashboard.”.

Flexible models or templates often contain areas that may not be useful now but may be necessary for the future. Excel can tag these areas as errors. The solution is to use functions for trapping errors.

Presentability

To improve readability, include cover pages with the company, project name, model description, author, and team contact details. The table of contents is also helpful for large worksheets.