Understanding financial modeling types through examples

Mergers and acquisitions are complex financial transactions with numerous levels and stages. With that in mind, M&A Community aims to provide business leaders and company executives with the most relevant M&A insights, as well as assist them in conducting complex M&A deals for the first time. 

The following article from M&A Community covers financial modeling as part of M&A deals. 

Deal valuation is among the top factors for M&A success, according to Statista. That only highlights the importance of correct financial modeling, as a tool for deal assessment. 
After reading this article, you’ll know the definition of financial modeling and its benefits, as well as explore financial models examples.

What is financial modeling: Basics of financial modeling

Financial modeling is the process of building a quantified understanding of a company’s financial activity in the past, present, and forecasted future. Financial models are usually spreadsheet-based.

Financial models are often used as decision-making tools, such as when a company’s executives expect to assess a new project or acquisition’s potential costs, risks, and profits.

Financial modeling and business valuation also aim to decide on budgets, allocate corporate finance resources, define the cost of upcoming projects, and forecast the impact of a specific future event on the company’s stock.

Note: Learn how to do stock pitch in our dedicated article.

Below are the key components of financial modeling:

• Assumptions and drivers
• Income statement
• Charts and graphs
• Cash flow statement
• Sensitivity analysis
• Balance sheet
• Supporting schedules
• Valuation

What is financial modeling used for?

Company executives can use financial modeling to decide on:

• Capital allocation
• Financial analysis
• Selling business units and assets as a part of the divestiture strategy
• Growing the business organically (entering new markets, exploring new locations, opening new stores)
• Planning potential cash flows for upcoming years
• Valuing the business
• Management accounting
• Making acquisitions and deciding on business acquisition financing

Different types of financial models

There are many different types of financial modeling, but let’s look at the 10 most frequently used financial modeling types.

1. Three-statement 

This is the basic financial modeling setup. Such financial models always include three financial statements: income statement, balance sheet, and cash flow statement. There are also supporting schedules. As a rule, these financial statements are dynamically linked to Excel files.

With these financial models, you can plan your company’s expenses and revenue. 

• The income statement

Includes specifics about the revenues, expenses, and taxes of a company over some time as well as its net income.

• The balance sheet

Showcases the company’s resources or assets that are to deliver future benefits.

• The cash flow statement

Presents the reconciliation between net income and generated cash.

2. Discounted cash flow (DCF) 

Discounted cash flow analysis builds on the three-statement financial model. It calculates the value of a target company and its free cash flow based on the net present value (NPV) of the business’s future cash flows.

By opting for discounted cash flow analysis, the company’s owners can find out whether its stock is undervalued or overvalued. This type of financial model is usually used in equity research and other capital market areas. 

DCF modeling determines the attractiveness of the potential investment opportunity.

3. Merger and acquisition (M&A)

M&A financial model is used to forecast the profits or losses of a potential merger or acquisition. It primarily aims to figure out the effect on the earnings per share (EPS) of the combined company after closing the deal.

If EPS increases as a result of the merger, then the deal is considered accretive. And if EPS decreases, the transaction is regarded as dilutive.

When building financial models of this type, financial analysts generally use a single-tap model for each company, where Company A + Company B = Merged Co.

Drawing from our experience, investment banking or corporate finance specialists usually build M&A financial models.

4. Initial public offering (IPO)

An IPO financial model is used when a company wants to assess its business in advance of going public. Such financial models aim to make a comparative analysis of the company and assume the cost potential investors would be willing to pay for it. 

The assessment in an IPO financial model includes an IPO discount to ensure good stock trading in the secondary market.

The IPO financial model is frequently used in the sphere of corporate development and investment banking.

5. Leveraged buyout (LBO)

In a leveraged buyout deal, one company acquires another company with borrowed (debt) money. Due to this, LBO financial models are more advanced and usually are built on complex formulas. 

This financial structure always requires modeling a complicated debt schedule. Its main goal is to figure out the amount of profit a certain company can generate from such a deal.

Based on our observations, LBO financial models are rarely used outside of private equity and investment banking.

6. Consolidation

A ‘consolidation’ financial model suggests multiple businesses uniting into one single model.

As a rule, this financial model includes a separate tab for each business, with a consolidation tab that sums up all organizational units. 

The consolidation financial model allows for calculating the possible revenue growth after the companies’ consolidate.

7. Budget

The budget financial model aims to forecast the company’s budget over the next few years. 

Budget financial models imply the analysis of the company’s figures over the last month or quarter and mainly target the income statement.

Budget modeling helps to better understand your company’s financials and plan the budget for the upcoming period more effectively. 

However, such financial modeling is more about short-term operational planning and control when compared to long-term financial planning models. The latter, in turn, are more about setting strategic direction and ensuring the organization’s long-term viability and success. 

8. Forecasting

The forecasting financial model is often combined with the budget financial model, as they have a similar goal: to compare budgets and make forecasts for the upcoming year or years. 

It’s typically used in financial planning and analysis and helps specialists understand the company’s attractiveness to potential investors, make informed decisions, and effectively manage risks.

Among the main forecasting methods used to perform this type of financial modeling are the straight-line method, moving average, simple linear regression, and multiple linear regression. 

The straight-line and moving average methods are typically based on the assumption that the organization’s historical data will be consistent with future results. While regression models forecast results based on the relationship between two or more variables.

9. Option pricing

Option pricing models aim to define the theoretical value of an options contract. This financial model is based on mathematical formulas and complex calculations and is mostly used by investors to determine the true value of an option.

There are three types of option pricing financial modeling:

10. Sum-of-the-parts

A sum-of-the-parts financial model presupposes taking several DCF models and summing them up. 

This is especially advantageous when valuing a huge conglomerate. Financial analysts value each unit separately and then add them together to get the valuation of the whole conglomerate.

This financial model helps to understand the business’s net asset value and the true value of the company, which is especially important in investment banking.

Financial modeling examples

Now, let’s briefly review some financial modeling case studies to illustrate the real-world application of various financial models.

Three-statement

Let’s say you want to evaluate the financial performance of an organization with the help of the three-statement model. For this, you’ll need to follow these steps:

1. Gather data and assumptions. Collect historical financial statements, market data, and any relevant industry or economic forecasts. Then, gather assumptions from management or stakeholders regarding future revenue growth, expense levels, capital expenditures, and financing activities.
2. Build the income statement. To do this, you should first forecast revenues based on historical trends, market analysis, and growth projections. Then, estimate the cost of goods sold (COGS) and operating expenses. After this, calculate operating income and adjust for non-operating items such as interest and taxes to determine net income.
3. Construct the balance sheet. It starts with estimating opening balances for assets, liabilities, and equity-based on historical data. The next step is to project changes in assets and liabilities due to such factors as capital expenditures, depreciation, and changes in working capital. Ensure the balance sheet is accurate by reconciling assets with liabilities and equity.
4. Prepare the cash flow statement. Start with net income from the income statement. Then, adjust for non-cash items like depreciation and amortization and account for changes in working capital, including accounts receivable, inventory, and accounts payable. Incorporate cash flows from investing activities (purchases of property, plant, or equipment) and financing activities (debt issuance or dividends paid).
5. Integrate and validate. Ensure consistency and accuracy among the three statements by cross-referencing related items. Also, confirm the model by comparing forecasted financial statements to historical data and assessing rationality against industry and economic conditions.
6. Perform sensitivity analysis. Test the sensitivity of the model to key assumptions by adjusting inputs and observing the impact on financial outcomes. 

The screenshot below demonstrates the balance sheet section of a three-statement single worksheet financial model. The model includes an income statement, cash flow statement, supporting schedules, and assumptions. Each section can be expanded or contracted to view each model separately.

DCF

Let’s say you want to assess an investment opportunity in a certain company. These are the typical steps to perform: 

1. Define cash flows. First, you need to assess the organization’s future cash flows that will be generated by the investment in a certain period (for example, a year). Such an assumption can be based on growth projections, historical sales, and current market conditions.
2. Determine the discount rate. The next step is to determine the required rate of return based on the investment-associated risk. This rate indicates the minimum return you’d accept to invest in that company instead of the other, which might be less risky.
3. Calculate the present value of cash flows. With the discount rate, discount future cash flows to their present value. It implies dividing each cash flow by (1 + discount rate) raised to the power of the number of years in the future that the cash flow will be received.
4. Calculate the sum of the present value of the cash flow. For this, add up the present values of each year’s cash flows.
5. Compare the result to the initial investment. To determine the profitability of investment, compare the total present value of investment to the initial investment.

The following DCF financial model screenshot consists of a balance sheet, free cash flow statement, assumptions and drivers, income statement, supporting schedules, and discounted cash flow model sheet. The latter shows historical data and forecasted results.

Mergers and acquisitions

Let’s say you’re a part of the management team of Company A that is considering acquiring Company B. 

To assess the feasibility of an investment and analyze the potential financial impact of this acquisition with the help of M&A financial modeling, follow these steps:

1. Collect the financial information. Gather the financial data from both companies (buyer and seller): income statements, balance sheets, and cash flow statements. This data will be needed for the pro forma financial statements’ creation.
2. Estimate synergies and cost savings. Define the potential synergies and cost-saving this transaction can bring. Synergies can include reduced costs, improved market share, or streamlined supply chain management.
3. Build pro forma financial statements. For this, connect the financial data of both companies and then adjust for any synergies and cost savings.
4. Define purchase price and deal structure. Determine the price you’re ready to pay for the target company and think of a deal structure (stock, cash, or cash and stock).
5. Assess the accretion and dilution. Determine whether the deal is going to be accretive or dilutive to the acquiring company’s earnings per share (EPS).
6. Assess the deal. Now, based on the pro forma financial statements and the dilution/accretion analysis, evaluate the acquisition’s potential and whether it aligns with Company A’s goals.

LBO

Let’s say you are a private equity company planning a leveraged buyout of a small restructuring company.  These are the steps to take to perform an LBO financial modeling for the assessment of potential investment:

1. Forecast the cash flows of the target company. The first step is to assess the company’s future cash flows for the investment period (for example, 4 years). The assessment can be based on growth projections, historical financial performance, and market conditions and trends.
2. Define the financing structure. Decide how much debt and equity you’ll use to finance the LBO. The chosen financing structure can depend on the target company’s cash flows, acceptable level of risk, and availability of financing.
3. Assess the schedule of debt repayment. Knowing the financing structure, you can now calculate the annual debt repayments a company will need to make. As a rule, borrowed money is paid back with the target company’s cash flows.
4. Calculate the exit value. Now, estimate the value of the target company by the time the investment period ends. Typically, this is done by applying a valuation multiple to the projected financial metrics of the company.
5. Calculate the potential return on investment (ROI). By considering the initial investment, the debt repayment, and the exit value, determine the potential ROI. This helps to assess the attractiveness of the investment.
6. Evaluate the maximum purchase price. Based on the expected rate of return, you can calculate the maximum purchase price you can afford to pay for the selling company. 

The following leveraged buyout model screenshot demonstrates the fully developed financial statements, credit metrics, debt modeling, multiple operating scenarios, cash-on-cash and IRR, and sensitivity analysis.

Budget and forecasting

Though budget and forecasting financial modeling pursue different purposes, the steps of performing such types of financial projection are similar. We describe them below:

1. Allocate financial data. Gather all the financial data of your company. It includes such historical financial data as expenses, cash flows, and revenues.
2. Analyze industry trends and other external factors. Look for certain trends in your company’s performance and other external market factors that can impact it.
3. Set revenue and expense assumptions. Based on the findings from the latter analysis, set assumptions for future revenues and expenses. These could include anticipated operating expenses or expected sales growth.
4. Build the budget. Create a detailed budget for a certain period (upcoming year, for example) based on the revenue and expense assumptions. Such a financial scenario should include all the expected income and expenses.
5. Create the forecast. Based on the created budget and any other extra data not mentioned there, make a forecast for the desired period (upcoming year, for example). It’s important to regularly update the forecast to address all the changes in market conditions or assumptions.

Choosing the right financial model

Now, let’s take a brief look at the factors to consider when deciding on the financial model to use for a specific financial evaluation: 

• Financial question being addressed

Ensure the chosen financial model directly addresses the specific question or problem under analysis. For example, for valuation purposes, the DCF method might fit the best, while for budget forecasting purposes, budget and forecasting models would be the most appropriate.

• Data availability

Evaluate the accessibility of the required data for the chosen model, and consider if there are any data limitations or gaps that may affect the choice of model. For instance, models requiring extensive historical data may not be suitable if such data is lacking.

• Model complexity

Match the complexity of the model with the analysis requirements, considering transparency and resource constraints. Also, ensure there’s a balance between the model’s complexity and the time and resources required to prepare the model and conduct the valuation.

Key takeaways

Let’s briefly summarize the key points from this article:

• Financial modeling is a process of building a numerical illustration of a company’s financial activity in the past, present, and forecasted future. 

• Financial modeling is typically used for budget allocation, financial analysis, business evaluation, planning potential cash flows for the upcoming years, making acquisitions, and more.

• The most commonly used financial models include the three statements, DCF, LBO, M&A, IPO, consolidation, budget, forecasting, option pricing, and sum-of-the-parts.

FAQ

What are the four main components of financial modeling? 

The four main components of financial modeling include income statement, cash flow statement, balance sheet, and debt schedule.

Who builds financial models?

Typically, these are professionals from the investment banking, corporate finance, equity research, and accounting sectors.