Financial modelling

1. 3-Statement Model

A 3-Statement Model is one of the most common types of financial models that businesses use to predict a company’s financial future. As the name suggests, the model projects the company’s three financial statements : Income statement, balance sheet, and cash flow statement for 5-10 years or more. It helps analysts study financial data and ratios and make informed decisions.

How to Build?

Add historical financial data and make future revenues, expenses, and capital expenditures assumptions. Then create schedules like revenue built-up, cost sheet, depreciation schedule, debt schedule, etc.

Once all schedules are ready, link the projected numbers from these schedules to the three financial statements. This linkage creates an integrated 3-statement financial model that simplifies the financial analysis. You can learn to build the 3-statement model in our financial Modeling course.

Recommended Course: Financial Modeling Courses

Who Creates 3-Statement Models?

• Financial Analysts: To predict a company’s financial future.
• Entrepreneurs: To plan and pitch their ideas.
• Investors: To make smart investment choices.
• Project Managers: To check project or venture finances.

Inputs for 3-Statement Model

• Company’s Historical Data: Use the data from past and current income statements , balance sheets, and cash flow statements.
• Growth Assumptions & Estimates: Revenue , direct and indirect expenses, capital expenditure, etc., are added too.

Output from 3-Statement Model

• Financial Ratios: This model includes financial ratios that cover different aspects of a company’s performance, including profitability, liquidity, turnover, solvency, and earnings ratios.
• Projected Financial Statements: Projected income statement, balance sheet , and cash flow statement for 5 to 10 years or more.

Example:

(Image Source: Tesla Financial Modeling Course)

ADVERTISEMENT

CORPORATE FINANCE - Specialization | 29 Course Series | 16 Mock Tests

?6999 ?21999 65% OFF

100+ Hours of HD Videos | 29 Courses | 16 Mock Tests & Quizzes | Verifiable Certificate of Completion | Lifetime Access 4.6

2. Discounted Cash Flow (DCF) Model

Discounted Cash Flow (DCF) Model helps you find a stock or investment’s intrinsic or actual value (share price in the case of securities). It lets the investors compare the intrinsic value with the current market prices to check if the investment is undervalued or overvalued. Analysts can also perform sensitivity analysis to see how changing the assumptions (WACC, growth rates) will impact the price.

How to Build?

Analysts project the stock’s future cash flows and calculate the terminal value (the value beyond the projection period) and the discount rate (WACC). Analysts then use this discount rate to discount the projected cash flows and terminal value to their present value. The result gives us the stock’s appropriate intrinsic value (share price).

Recommended Course: Business Valuation Course

Who Creates DCF Models

• Financial Analysts: They use it to find enterprise, equity, and intrinsic value for businesses’ investments.
• Valuation Experts: They apply it to determine fair values for businesses or assets.
• Investors: They use it to decide if investment in a particular stock is a good idea.
• Entrepreneurs/Business Owners: They create it to plan for the future financial health of their company.

Inputs for Discounted Cash Flow (DCF) Model

• Projected Cash Flows.
• Weighted Average Cost of Capital (WACC).

Outputs from Discounted Cash Flow (DCF) Model

• Present Value of Cash Flows.
• Target Share Price (Intrinsic Value) of the Stock.

Example:

3. Comparable Company Analysis

Comparable Company Analysis (CCA) is a valuation method that assesses the value of a company by comparing its performance to similar public companies in the same industry.

How to Build?

Start by building a spreadsheet model where you enter the financial data of the target company and its comparable firms. Calculate relevant ratios, such as the P/E and EV/EBITDA ratios, and analyze the differences in metrics. Finally, arrive at a valuation range based on the multiples of comparable companies.

Who Creates Comparable Company Analysis Models?

• Financial Analysts: To provide insights about the target company’s valuation based on industry peers’ financial metrics.
• Investment Bankers: To assist in pricing IPOs, mergers, and acquisitions by comparing the target company’s value to market competitors.
• Valuation Professionals: To determine how much a company should be worth by comparing how well it’s doing to similar companies in the same industry.

Inputs for Comparable Company Analysis Models

• Financial Data: Historical financial statements of the target company and its comparable peers.
• Market Data: Stock prices, trading multiples, and relevant industry benchmarks.

Outputs from Comparable Company Analysis Models

• Valuation Metrics: Price-to-Earnings (P/E) ratios, Price-to-Book (P/B) ratios, and Enterprise Value-to-EBITDA ratios show how the target company’s valuation compares to similar companies in the industry.

Example:

4. Merger (M&A) Model

Analysts working in Investment Banks build Merger Models to understand how potential mergers or acquisitions can affect a company’s finances. They use the model to assess various aspects of the M&A deal, such as synergies, valuation adjustments, financing structures, and post-transaction financials.

How to Build?

Start by collecting historical financial data from acquiring and the target companies. Afterward, they project their future financial statements, combine them, and conduct accretion/dilution analysis. Also, study key measures like EPS (earnings per share) and debt ratios.

ADVERTISEMENT

CRYPTOCURRENCY - Specialization | 9 Course Series

?3999 ?15900 65% OFF

54+ Hours of HD Videos | 9 Courses | Verifiable Certificate of Completion | Lifetime Access 4.5

Recommended Course: ?Mergers and Acquisitions Course

Who Creates M&A Models?

• Investment Bankers: They build it to analyze potential mergers or acquisitions.
• Private Equity Analysts: They use it to assess the feasibility and potential returns of acquiring a business.
• Due Diligence Teams: They use it to thoroughly examine the firm’s financial details before completing a merger or acquisition.

Inputs for Merger Model (M&A) Model

• Historical Data: Past financial data of the merging companies.
• Financing Terms: Debt and equity components used in financing the deal.
• Purchase Price Allocation: Allocation of the purchase price to assets and liabilities of the target company.
• Synergy Estimates: Assumptions about potential cost savings, increased revenue, and other synergies.

Outputs from Merger Model (M&A) Model

• Combined and projected financial statements after the merger (income statement, balance sheet, and cash flow statement).
• Accretion/dilution analysis to analyze if the merger will increase or decrease earnings per share (EPS) for the acquiring company.

Example:

?5. Leveraged Buyout (LBO) Model

The LBO Model helps businesses assess if acquiring a company using debt will be beneficial. The LBO Model evaluates if the target company’s future cash flows will be able to cover the debt’s interest payments as well as provide positive returns.

How to Build?

To build an LBO Model, analysts start by projecting the target company’s financial statements and determining the capital structure and financing details. After using methods like EBITDA multiples to estimate the exit value, they also calculate returns metrics such as internal rate of return (IRR) and equity multiple. Finally, they perform sensitivity analyses to gauge the model’s sensitivity to changes in key assumptions.

Recommended Course: LBO Modeling Course

Who Creates LBO Models?

• Private Equity Analysts: They construct it to evaluate if the purchase of a company using a significant amount of debt can be profitable.
• Investment Professionals: They use it to assess the financial feasibility of acquiring a company and improving its operations to generate profits.
• Financial Consultants: They create it to analyze potential returns and risks of a leveraged buyout and accordingly advise their clients.

Inputs for Leveraged Buyout (LBO) Model

• Acquisition Price & Debt Structure: The cost of acquiring the target company and details of debt financing, including types of debt and interest rates.
• Operating Projections: Forecasts of the target company’s future revenues, expenses, and cash flows.
• Valuation Multiples: Factors used to estimate the purchase price based on EBITDA, EBIT, or other metrics.

Outputs from Leveraged Buyout (LBO) Model

• Equity Returns: Calculated returns on the equity investment after accounting for debt and other costs.
• Internal Rate of Return (IRR): The annualized rate of return generated by the investment.
• Debt Repayment Schedules: Timelines for repaying the debt used to finance the acquisition.

Example:

6. Initial Public Offering (IPO) Model

A private company uses the IPO Model when it wants to go public and wants to determine the right offer price. The IPO model checks the potential valuation of the company as well as the effects of the IPO on the company’s financial situation.

How to Build?

You must collect financial data and information about the company. Then, project future financial performance and estimate underwriting fees and expenses. Also include the share price, offering size, investor demand, and potential post-IPO changes in the model.

Who Creates IPO Models?

• Investment Bankers: They use it to plan and price a company’s initial public offering (IPO).
• Valuation Experts: They create IPO models to ensure the offering price aligns with the company’s fundamentals, industry standards, and market conditions.
• Consulting Firms: They create it to advise companies planning to go public.

Inputs for Initial Public Offering (IPO) Model

• Historical Financials: Past financial information and information about market conditions, industry trends, etc.
• Offer Size & Price: Number of shares allotted for the offering and their expected price.

Outputs from Initial Public Offering (IPO) Model

• Projected Financials Post-IPO: Estimated financial performance after the company goes public.
• Valuation Ranges: Potential market capitalization and share price ranges.

Example:

ADVERTISEMENT

PRIVATE EQUITY - Specialization | 26 Course Series | 3 Mock Tests

?5999 ?19998 65% OFF

97+ Hours of HD Videos | 26 Courses | 3 Mock Tests & Quizzes | Verifiable Certificate of Completion | Lifetime Access 4.5

7. Budget & Forecasting Model

Businesses use a budget & forecasting model to plan their budget and forecast their financial performance over a specific period. They create this to make resource allocation and strategic decision-making easier.

How to Build?

Start by creating a spreadsheet and filing information about the company’s historical data. Also include its revenue and expense projections, growth assumptions, and cost estimates. Finally, use formulas to calculate future financials, analyze differences, and adjust strategies accordingly.

Recommended Course: Financial Budgeting Course

Who Creates Budget & Forecasting Models?

• Financial Analysts: To provide insights into a company’s financial performance and assist in strategic decision-making based on accurate projections.
• Finance Managers: To allocate resources effectively, monitor financial health, and meet the organization’s financial goals.
• Business Planners: To develop plans that align with the company’s objective while ensuring the firm maintains its financial stability.

Inputs for Budget & Forecasting Models

• Historical Data: Past financial statements and performance metrics.
• Cost Estimates: Projected costs related to operations, investments, and expansion.
• Growth Assumptions: Estimates for revenue growth, expenses, and market conditions.

Outputs from Budget & Forecasting Models

• Budget: Detailed plan of expected revenues and expenditures for the upcoming period.
• Forecast: Projection of future financial performance based on assumptions.
• Variance Analysis: Comparison of actual performance against forecasts to find any differences and potential areas for improvement.

Example:

8. Sum of the Parts Model

The “Sum of the Parts Model” is a financial model where we value individual business segments or divisions separately. It helps financial experts derive a comprehensive valuation for a company with diverse business segments or operating in multiple industries.

How to Build?

First, gather financial data and performance metrics for each business segment to build a Sum of Parts Model. After that, estimate the segment’s future cash flows or earnings and find its value using an appropriate valuation method. Finally, combine each segment’s values to find the company’s overall valuation.

Who Creates the Sum of Parts Models?

• Financial Analysts: Analysts use SOP models to provide detailed insights into the value of various parts of a company.
• Investment Bankers: They create these models to guide clients through spin-offs, mergers, or divestitures.
• Corporate Finance Teams: Companies create SOP models to understand better the value of their different business units or assets to make strategic decisions.
• Private Equity Firms: They use this type of financial model to assess the value of various portfolio companies they own, especially when planning exits.

Inputs for Sum of the Parts Model

• Segment Financial Data: Financial performance metrics of individual business segments.
• Market Data: Relevant market information and industry benchmarks.
• Growth Rates: Forecasts for revenue and cash flow growth for each segment.

Outputs from the Sum of the Parts Model

• Individual Segment Valuations: Value of each business division.
• Total Conglomerate Valuation: Total firm valuation by combining all individual segments’ value.

9. Option Pricing Model

Analysts prepare Option Pricing Models to estimate the value and assess the risk of financial options, such as stock options or derivatives. Option Pricing Models estimate the value using factors like underlying asset price, volatility, and time to expiration.

How to Build?

The process of building an Option Pricing Model involves finding the underlying asset price, option strike price, time to expiration, implied volatility, and risk-free rate. The analysts input these data into the model and then calculate the option’s value. Additionally, analysts may calculate various option Greeks, such as delta, gamma, theta, vega, and rho, to understand how the option’s value changes in response to changes in underlying variables.

Who Creates Option Pricing Models?

• Traders/Investors: To calculate the value of financial options and make trading decisions.
• Financial Analysts: Analysts in investment banks, hedge funds, and financial institutions use option pricing models to evaluate and make decisions about investment in options.
• Derivatives and Risk Management Specialists: They create these models to understand the complexities of options and manage financial risks.

Inputs for Option Pricing Model

• Underlying Asset Price: Current market value of the asset the option is based on.
• Options Strike Price: Agreed-upon price at which the investor can exercise the option.
• Time to Expiration: Remaining time until the option expires.
• Implied Volatility: Market’s expectation of the asset’s future price volatility.
• Risk–Free Rate: Current risk-free interest rate, often based on government bonds.

Outputs from the Option Pricing Model

• Option Value: The final value of the option.
• Options Greeks: Sensitivity indicators (delta, gamma, theta, vega, rho) reflecting how the option value changes with various factors.