Types of Financial Models

Types of Financial Models

Financial models are used to represent the forecast of company’s financials based on its historical performance as well as future expectations with the purpose of using them for financial analysis and the most common types of financial models include Discounted Cash Flow model (DCF), Leveraged Buyout model (LBO), Comparable Company Analysis model, and Mergers & Acquisition model.

Here is the list of the top 4 types of financial models

Let us discuss each one of them in detail –

For eg:
Source: Types of Financial Models (wallstreetmojo.com)

#1 – Discounted Cash Flow Model

This is perhaps one of the most important types of a financial model that is a part of valuation methodologies. It utilizes the projected free cash flows expected to be extracted and discount them to arrive at a (NPV), which aids in the potential value of an investment and how quickly they can break even from the same.

This can be expressed with the below formula:

DCF =   CF1/(1+r) + CF2/(1+r) 2      +……..  + CFn/(1+r) n

where CF1 = the cash flow at the end of the year

r = Discounted rate of Return

n = Life of the project

In the NPV calculation, we shall assume that the cost of capital is known for calculating the NPV. The formula for NPV:

[NPV = Present Value of Cash Inflow – Present Value of Cash Outflow],

If NPV is positive, then the project is worth to be considered else; it is a loss-making option.

Discounted Cash Flow Model – Example

Let us consider an example for understanding the implications of the DCF Valaumodel:

The initial cash flow is INR 100,000 for the initiation of the project post, which all are the cash inflows.

100,000 = 30,000/(1+r) 1   +   30,000/(1+r) 2     +    40,000/(1+r) 3      +    45,000/ (1+r) 4

On calculation , r = 15.37%. Thus, if the rate of return from the project is expected to be greater than 15.37%, then the project shall be accepted else to be rejected.

A leveraged buyout (LBO) is acquiring a public or private company with a significant amount of borrowed funds. After the purchase of the company, the Debt/Equity ratio is generally greater than 1 (debt constituting a majority of the portion). During the ownership, the firm’s cash flows are used for servicing of the debt amounts and the interest. The overall return realized by the investors is calculated by the exit flow of the company (EBIT or EBITDA) and the amount of the debt which has been paid over the time horizon. This kind of strategy is mostly used in with sponsors like who want to acquire companies to sell them at a profit in the future.

If you want to learn LBO Modeling professionally, then you may want to look at 12+ hours of LBO Modeling Course

LBO Model Example

An illustrative example is stated as below with the Parameters and Assumptions:

• XYZ Private Equity partners purchase ABC target company for five times forward EBITDA at the end of Year Zero (before the commencement of the operations)
• The = 60:40
• Assume the weighted average interest rate on debt is to be 10%
• ABC expects to reach \$100 million in with an EBITDA margin of 40% in Year 1.
• Revenue is expected to increase by 10% year on year.
• are expected to remain flat during the term of the investment.
• are expected to be 15% of sales each year.
• Operating working capital is expected to increase by \$5million every year.
• is expected to equal \$20 million each year.
• Assuming a constant tax rate of 40%.
• XYZ exits the target investment after Year 5 at the same EBITDA multiple used at entry (5 times forward 12 months EBITDA) – see

Using the 5.0 entry model, the price paid for the purchase price of ABC Target Company is calculated by multiplying Year 1 EBITDA (which represents a 40% EBITDA margin on \$100 million in revenue) multiplied by 5. Hence, the purchase price = 40*5 = \$200 million.

The debt and equity funding is calculated taking into account the Debt: Equity ratio =

Debt portion = 60% * \$200 million = \$120 million

Equity portion = 40% * 200 million = \$80 million

Based on the above assumptions we can construct the table as follows:

Please note that since the exit value at the end of year five will be based on Forwarding EBITDA multiple, the sixth years’ worth of income statement and not the fifth year.

The Cumulative Leveraged Free cash flow can be calculated as follows:

We do not need to consider the information for the 6th year since the FCF from years 1 to 5 can be used to pay down the debt amount, assuming the entire FCF is utilized for debt payment. The exit returns can be calculated as follows:

Total Enterprise Value at Exit = taking forward EBITDA at an exit along with a 5.0 times exit multiple to calculate Exit TEV. \$64 mm X 5.0 multiple = \$320 million

Ending Debt = Beginning debt – Debt Pay down [\$120mm – \$34 mm in Cumulative FCF = \$86mm]

Multiple of Money (MoM) EV return is calculated as [Ending EV / Beginning EV] = [\$234mm/\$80mm = 2.93 times MoM]

The following table is useful for estimating IRR based on five year MoM multiples:

Thus, we can assume that the implied IRR for the above case is approximately 25% or slightly below the same.

#3 – Comparable Company Analysis Model

A comparable company analysis (CCA) is a process used to evaluate the value of a firm using the metrics of other businesses of similar size in the same industry. It operates under the assumption that similar companies will have similar valuation multiples, such as the . Subsequently, investors can compare a particular company to its competitors on a relative basis.

Broadly the selection criteria for comparable companies can be bifurcated as follows:

The most integral multiples which are considered for comparative analysis are:

EV/EBITDA Multiple

• Another common multiple is EV/EBITDA, which is calculated as follows: Enterprise Value / EBITDA
• where EV represents all business (Common Equity + Net Debt + Preferred Stock + ).
• This aids in neutralizing the effect of capital structure. accrues to both debt and equity holders since it is before the interest component.

Price to Book Value Ratio

• PBV Ratio is the is an equity multiple calculated as Market Price of a share/ or Market Capitalization/Total Shareholder’s Equity

The steps to keep in mind for executing a comparative valuation are:

1. Pick a group of competitors/similar companies with comparable industries and fundamental characteristics.
2. = Share Price X No. of .
3. Calculate the Enterprise Value
4. Use historical formulas from the company filings and projections from the management, equity analysts, etc.
5. Calculate the various spread multiples, which will give a ballpark view of how the firm is performing, reflecting the truth behind the .
6. Value the target company by picking the appropriate benchmark valuation multiple for the peer group, and value the target company based on that multiple. Generally, an average or median is used.

Comparable Company Analysis Model – Example

• The table above is the comparable comp for Box Inc. As you can see that there is a list of companies on the left-hand side along with its respective valuation multiples on the right-hand side.
• Valuable multiples include EV/sales, EV/EBITDA, Price to FCF, etc.
• You can take an average of these industry multiples for finding the fair valuation of Box Inc.
• For more details, please refer to Box Valuation

#4 – Mergers and Acquisitions Model

The investment Banking fraternity widely uses the type of financial model. The fundamental objective of merger modeling is to display the impact of the acquisition on the acquirer’s EPS and how this EPS is comparable in the industry.

The basic steps for building an M&A model are as follows:

The focus of this model involves a construction of the balance sheet post the merger of the two entities.

The sources and users model section of this model contains information regarding the flow of funds in an M&A transaction, specifically where the money is coming from and where the money is getting utilized towards. An investment banker determines the amount of money raised through various Equity and and cash in hand to fund the purchase of the target company, which represents sources of the funds. The uses of the funds will show the cash that is going out to purchase the target as well as various fees required to complete the transaction. The most important factor is that the Sources have to be Equal to the Uses of the Funds.

Cash on Hand = Total Uses of Funds – Total sources of funds excluding cash on hand =

(Purchase of Equity + Transaction Fees + Financing Fees) – (Equity + Debt)

Goodwill: It is an asset that arises on an acquiring company’s Balance Sheet whenever it acquires a that exceeds the Book Value of Net Tangible assets (i.e., Total Tangible Assets – Total Liabilities) on the target’s Balance sheet. As a part of the transaction, some portion of the acquired assets of the target company will often be “written up” – the value of the assets will be increased upon transaction closure. This increase in asset valuation will appear as an increase in Other Intangible assets on the Buyer’s balance sheet. This will trigger a Deferred Tax liability, equal to the assumed tax rate times the write up to Other .

New Goodwill = Purchase price of equity – (Total Tangible Assets – Total Liabilities) – Write up of assets * (1-Tax rate)

Goodwill is a long term asset but is never depreciated or amortized unless Impairment is found – if it is determined that the value of the acquired entity becomes lower than what the original buyer paid for it. In that case, a portion of the goodwill will be “written off” as a one-time expense i.e., the goodwill will be decreased by an equal amount of the impairment charge.

Recommended Articles

In this article, we discussed the top 4 types of financial models, including the DCF Model, Comparable Comp Model, M&A, and LBO Model with examples. You can make use of the following resources to learn the basics of Financial Modeling –

Thank you so much, Dheeraj! Very, very helpful. .)

• Dheeraj Vaidya says

2. RAJAN KUMAR says

hello sir In LBO model eg. under the table in 3 yr column there is some mistake. EBT-tax(16-7)= 9 but u hav written 10. pls rectify it

• Dheeraj Vaidya says

thanks Ranjan. You have an eye for details :-)

3. Hitesh Maheshwari says

That’s quite informative 101 with financial models! Keep posting Dheeraj…

• Dheeraj Vaidya says

thanks Hitesh!

4. Bibhuti says

Many many thanks for giving information and knowledge

5. Himank says

Great article Dheeraj, this pretty much made things clear. You are really helping a lot of people through your content.