Financial Modeling explained

Financial Modeling: this article provides a practical explanation of financial modeling. After reading, you will understand the basics of this powerful financial management tool.

What is Financial Modeling?

Financial Modeling is the process of developing a financial representation of a company’s performance through modelling. These models take into account conditions and risks, future expectations that are relevant to making future decisions such as raising new capital or valuing companies, and interpreting the impact that these decisions can make.

A financial model is a tool that presents possible solutions to a financial problem. Such a model is often created in Excel, although not every spreadsheet is a financial model. A financial model contains a range of variables, inputs, outputs, calculations, and scenarios.

In addition, the model often contains a series of financial forecasts. These are often based on analyses of, for example, a company’s profit and loss account, balance sheet, and cash flow statement. These variables and inputs make the model a dynamic whole. Changing a single input affects the entire model, calculations, and results.

Financial models almost always examine a company’s future financial situation. The people who create these models often want to know what financial projections look like at a certain point in time.

For example, if an organisation continues to grow at the same pace, what will the total cash flow be in four years time? Because these models are built to make predictions, they are ideally suited for performing scenario and sensitivity analyses. For example, what impact would an interest rate rise have on the company? Which discount factor should be implemented?

What is investment banking?

Investment banking is a specific banking segment that is involved in creating capital for other companies, government agencies, or other entities. Investment banks underwrite debt and equity securities for companies, provide help during the sale of securities, assist in mergers and acquisitions, and act as brokers for both institutions and private investors. Investment banks provide assistance to companies that issue securities on the issue and placement of shares.

Many large investment banks are affiliated with even larger banking institutions. Several well-known names include Morgan Stanley, JP Morgan Chase, Bank of America, Goldman Sachs, etc. These major banks help companies with large, complicated transactions. For example, they advise on how much a company is worth (company valuation), or how best to close a deal if a company is considering a merger, acquisition, or sale. They also assist in the issuing of securities and shares as a way to raise money for clients who want to go public with their company.

Because these scenarios often involve a lot of money, people working in this sector often use financial models. The practice of working with these is called financial modelling. Several of the most common financial models are briefly explained below.

Examples of different financial models

There are different types of financial models. Below are some of the most common models. In practice, however, even more models are used.

Three Statement Model (based on profit and loss account, cash flow statement, balance sheet)

The Three Statement Model links the profit and loss account, balance sheet, and cash flow statement in a dynamic financial model. These combined models in turn form the basis for more advanced models, such as the Discounted Cashflow Analysis Model and the Leveraged Buyout (LBO) model.

Steps in the Three Statement Model

1. Enter historical financial data
2. Determine expectations that drive the forecast
3. Predict income statement
4. Predict capital goods
5. Predict financing activities
6. Predict the balance sheet
7. Complete the cash flow statement

Merger (Mergers & Acquisitions) Model

A Merger Model is an analysis of combining two companies through the M&A process. An acquisition takes place when a company decides to acquire another company. In all cases, both companies merge into a company subject to approval by the shareholders of both organisations. This model is widely used in financial modelling.

Steps in the Merger Model

1. Make assumptions about the acquisition
2. Make projections
3. Value both companies
4. Model the business combination and make pro forma adjustments
5. Deal accretion

Initial Public Offering (IPO) Model

Initial Public Offering (IPO) refers to the process that occurs when a previously unlisted company sells new or existing securities and offers these to the general public for the first time. This is done prior to an IPO. Before a company is listed, it is considered private. The shares are held in limited possession by a few recognised investors, early investors, high net worth individuals, family members, or founders. After the IPO, the issuing company becomes a listed company on a recognised stock exchange. The IPO is also referred to as ‘going public’.

Leveraged Buyout (LBO) Model

An LBO model is created in Excel to evaluate an LBO transaction. This involves the acquisition of a company that is financed with a significant portion of debt. The assets of the acquired company and assets of the acquiring company are then used as collateral for the loans. The purpose of the LBO model is to allow investors to properly assess the transactions.

Steps in the LBO Model

1. Assumptions about the selling price
2. Creating resources and using funds
3. Financial projections
4. Balance sheet adjustments
5. Exit
6. Calculation of IRR on initial investment

Sum of the Parts Model

The Sum of The Parts (SOTP) Model can be used to value a company. The value of each business segment or subsidiary is assessed separately and then added together to get the total value of the company. SOTP is typically used in conjunction with other valuation techniques such as the Discounted Cash Flow (DCF) and comparable business valuation techniques.

Steps in the Sum of The Parts (SOTP) Model

1. Determining business segments
2. Valuation of each segment
3. Sum total

Consolidation Model in financial modeling

A Consolidation Model is created by combining the financial results of multiple business units into a single model. The first sheet of the model often includes diagrams such as monthly and annual income, productivity, profit, costs, etc. Other tabs provide financial data by business unit/segment or production line by year, month, or quarter.

Steps in the Consolidation Model

1. Entering relevant data
2. Calculating financial figures with the data tables
3. Validating data with the financial statements
4. Creating summary
5. Generati graphs and charts for visualisation purposes

Discounted Cash Flow (DCF) Model

The Discounted Cash Flow (DCF) is a valuation method used to estimate a company’s value based on forecasts of future cash flows. The DCF analysis tries to determine the current value of a company based on forecasts of how much money the company will generate in the future.

The DCF analysis determines the present value of expected future cash flows using a discount rate. This is the percentage used to measure future cash flows. In calculations, this value is often referred to as interest or return. The exact percentage depends on many factors and may vary widely.

Components of the Discounted Cash Flow Model

The DCF model contains the following three components:

1. Cash Flow (CF). The cash flow represents a so-called free cash flow without costs.
2. Discount rate (r). For valuation purposes, the discount rate is usually the weighted average cost of capital of a company, referred to as WACC (Weighted Average Cost of Capital)
3. Period (n). Each cash flow is linked to a specific time period. Time periods are usually expressed in months, quarters, or years.

Best practices Financial Modeling

Below are several best practices when it comes to financial modelling.

Excel tips

• Make sure you know how to work with the most important Excel formulas and features
• Use the CHOOSE function to create scenarios
• Restrict or eliminate the use of the mouse. Use keyboard shortcuts instead
• Keep formulas easy to understand and divide complex calculations into steps

Format

It is important that there is a clear distinction between inputs and outputs. The inputs represent expectations/ assumptions, and the outputs are the calculations. These are drawn up on the basis of layout conventions. The colour blue stands for input. Black is used for formulas. Other conventions may also be applied, such as shadow cells or borders.

Design

It is critical that the financial model is structured in a logical and easy to follow way. This means that the model is usually created in one Excel worksheet, and groupings are implemented to create different sections. In this way, it is easy to increase or decrease the size of the model later.

Financial modeling vs financial forecasts

Financial forecasts are used to ensure that (financial) resources end up in the right places. Forecasts can also help a company identify the assets or liabilities needed to achieve objectives and priorities.

A common example of a forecast is the prediction of a company’s sales. A company’s financial statements are almost always linked to expected sales figures, which is why financial forecasting can help make sound financial decisions that support the realisation of objectives. If the turnover increases, there will also be additional costs. This is because it costs money to produce additional sales. Each forecast therefore has an impact on the overall financial position of a company.

On the other hand, financial modelling is the process by which companies build their financial representations. A financial model is used to make business decisions. Financial models are often mathematical company models in which variables are linked. The modelling process includes creating a summary of the financial information in the model.

For example, if a company expects an increase in sales, the company should also anticipate the resulting increase in raw materials or inventory costs. If the company requires new machines, the cost of purchase or lease must also be estimated. Credit needs can also be predicted in this way. In this situation, a company can increase its credit limit with a bank.

Who uses financial models?

Because of the wide applications of financial models, they are used by many different types of professionals. These include:

• Investment banks
• Stock market analysts
• Accountants
• Analysts for business development

Within the context of a modern company, the individuals working on a company’s financial planning and analysis are most likely to create and use models to steer the direction of the company. The FP&A team therefore plays a crucial role in senior management. It is the financial team in the company that draws up a budget and creates financial forecasts. These are used to help the CEO and senior management team understand the company’s financial situation. In addition to budgeting and forecasting, the financial team is also responsible for supporting decision-making and special projects such as process optimisation and market research.

Conclusion of Financial Modeling

Financial Modelling is an important activity for the finance team of a large organisation. Accurate financial models help to better understand a company’s financial position and make better strategic decisions based on recent data.

There is no simple solution or recommendation when it comes to using financial models. There is no financial model that can be applied in every situation, which is why different situations require different financial models and expertise.

Financial models are often created in Excel. It take a lot of time to learn how to create these models yourself. Instead, advanced financial applications are available. This software helps users to remove all the brainwork from the models. That way, more time can be spent on a company’s strategy and next steps.

Now it’s your turn

What do you think? Are you familiar with the explanation of financial modeling? Have you ever worked on a financial model? Have you valued your own company? What other financial models do you know? What do you know about the working methods of large financial institutions? Do you have any other tips or additional comments?

Share your experience and knowledge in the comments box below.

More information

1. Beatty, R. P., & Ritter, J. R. (1986). Investment banking, reputation, and the underpricing of initial public offerings.
2. Benston, G. J., & Harland, J. (1990). Separation of Commercial and Investment Banking. Springer.
3. Fields, L. P., & Fraser, D. R. (1999). On the compensation implications of commercial bank entry into investment banking. Journal of Banking & Finance, 23(8), 1261-1276.
4. Tankov, P. (2003). Financial modelling with jump processes. Chapman and Hall/CRC.

How to cite this article:
Janse, B. (2020). Financial Modeling. Retrieved [insert date] from Toolshero: https://www.toolshero.com/financial-management/financial-modelling/

Original publication date: 04/06/2020 | Last update: 08/29/2023

Add a link to this page on your website:
<a href=”https://www.toolshero.com/financial-management/financial-modelling/”>Toolshero: Financial Modeling</a>