Financial Modeling Tutorials
- Financial Modeling Basics
- What is Financial Modeling?
- Financial Modeling in Excel
- Types of Financial Models
- Financial Modeling Interview Questions
- Alibaba IPO Financial and Valuation Model
- Box IPO Modeling Details
- Box IPO Valuation Model
- Download Alibaba IPO Financial Model
- Financial Modeling Books
- Financial Modeling Templates
- Financial Modeling Course
- Excel Modeling
- Financial Functions in Excel
- Sensitivity Analysis in Excel
- Time Value of Money
- Future Value Formula
- Present Value Factor
- Perpetuity Formula
- Annuity vs Perpetuity
- Internal Rate of Return (IRR)
- NPV vs XNPV
- NPV vs IRR
- NPV Formula
- IRR vs ROI
- Break Even Point
- Payback Period & Discounted Payback Period
- Payback period Formula
- Discounted Payback Period Formula
- Profitability Index
- Cash Burn Rate
- Simple Interest
- Effective Interest Rate
- Loan Amortization Schedule
- Rule of 72
- Geometric Mean Return
- Real Rate of Return Formula
- Continuous compounding Formula
- Weighted average Formula
- Holding Period Return Formula
- Cost Benefit Analysis
- Mortgage APR vs Interest Rate
Types of Financial Models – A financial model is an activity which involves a preparation of a model representing a real world financial situation. It is a mathematical model designed to display the performance of a Portfolio or certain financial assets of a business, project or any other investment.
- Types of Financial Model
- #1 – Discounted Cash Flow Model
- #2 – Leveraged Buyout Model
- #3 – Comparable Company Analysis Model
- #4 – Mergers & Acquisitions Model
Recommended Courses
Types of Financial Models
There are various kinds of financial models which are created according to the purpose and requirement for doing the same. While the majority of the financial models focus on Valuation some are computed to predict and calculate risk, performance of individual portfolio or macroeconomic trends within an industry or region.
In this article, we focus only on Financial Models that are relevant for Investment Banking and Equity Research
#1 – Discounted Cash Flow Model
This is perhaps one of the most important valuation methodology of measuring cash flow of a project. It utilizes the projected free cash flows expected to be extracted and discount them to arrive at a Net Present Value (NPV) which aids in the potential value of an investment and how quickly can they break even from the same.
This can be expressed with the below formula:
DCF = CF1/(1+r)^{ 1 } + 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 the computation of 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:
Year | 0 | 1 | 2 | 3 | 4 |
Cash Flow | (100,000) | 30,000 | 30,000 | 40,000 | 45,000 |
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 computation, 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.
In Equity Research, DCF Analysis is used to find the fundamental value of the company (fair value of the firm)
#2 – Leveraged Buyout Model
A leveraged Buyout (LBO) is the acquisition of 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 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 realised by the investors is computed 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 largely used in leveraged finance with sponsors like Private Equity firms who want to acquire companies with an objective of selling 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 5 times forward EBITDA at the end of Year Zero (before the commencement of the operations)
- The Debt to Equity ratio = 60:40
- Assume the weighted average interest rate on debt is to be 10%
- ABC expects to reach $100 million in sales revenue with an EBITDA margin of 40% in Year 1.
- Revenue is expected to increase by 10% year on year.
- EBITDA margins are expected to remain flat during the term of the investment.
- Capital Expenditures are expected to be 15% of sales each year.
- Operating working capital is expected to increase by $5million every year.
- Depreciation 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 Terminal Value Multiples
Using the 5.0 entry model, the price paid for the purchase price of ABC Target Company is computed 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 computed 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:
($ in mm) | Years | |||||
1 | 2 | 3 | 4 | 5 | 6 | |
Sales Revenue | 100 | 110 | 121 | 133 | 146 | 161 |
EBITDA | 40 | 44 | 48 | 53 | 59 | 64 |
Less: Depr & Amortization | (20) | (20) | (20) | (20) | (20) | (20) |
EBIT | 20 | 24 | 28 | 33 | 39 | 44 |
Less: Interest | (12) | (12) | (12) | (12) | (12) | (12) |
EBT | 8 | 12 | 16 | 21 | 27 | 32 |
Less: Taxes | (3) | (5) | (7) | (8) | (11) | (13) |
PAT (Profits after Tax) | 5 | 7 | 9 | 13 | 16 | 19 |
Please note that since the exit value at the end of year 5 will be based on Forward EBITDA multiple, the sixth years’ worth of income statement and not the fifth year.
The Cumulative Leveraged Free cash flow can be computed as follows:
($ in mm) | Years | |||||
1 | 2 | 3 | 4 | 5 | 6 | |
EBT (Tax-effected) | 5 | 7 | 10 | 13 | 16 | |
Plus: D&A (Non-cash exp) | 20 | 20 | 20 | 20 | 20 | |
Less: Capital Expenditure | (15) | (17) | (18) | (20) | (22) | |
Less: Increase in Net Working Capital | (5) | (5) | (5) | (5) | (5) | |
Free Cash Flow (FCF) | 5 | 6 | 7 | 8 | 9 |
We do not need to consider the information for the 6^{th} year since the FCF from years 1 to 5 can be used to pay down the debt amount assuming entire FCF is utilised for debt payment. The exit returns can be computed as follows:
Total Enterprise Value at Exit = taking forward EBITDA at exit along with a 5.0 times exit multiple to compute Exit TEV. $64 mm X 5.0 multiple = $320 million
Net Debt at Exit (also known as Ending Debt) is calculated as follows:
Ending Debt = Beginning debt – Debt Pay down [$120mm – $34 mm in Cumulative FCF = $86mm]
Ending Equity Value = Exit TEV – Ending Debt [$320mm -$86mm] = $234mm
Multiple of Money (MoM) EV return is computed as [Ending EV / Beginning EV] = [$234mm/$80mm = 2.93 times MoM]
The following table is useful for estimating IRR based on 5 year MoM multiples:
2.0x MoM over 5 years ~ 15% IRR |
2.5x MoM over 5 years ~ 20% IRR |
3.0x MoM over 5 years ~ 25% IRR |
3.7x MoM over 5 years ~ 30% IRR |
Thus, we can assume that the implied IRR for the above case is approximately 25% or slightly below the same.
You may also find these articles useful –
#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 EV/EBITDA. Subsequently, investors are able to compare a particular company to its competitors on a relative basis.
Broadly the selection criteria for comparable companies can be bifurcated as follows:
Business Profile | Financial Profile |
Sector | Size |
Products & Services | Profitability |
Customer and End Markets | Growth Profile |
Distribution Channels | Return on Investment |
Geography | Credit Rating |
The most critical multiples which are considered for comparative analysis are:
PE Multiple
- PE Valuation Multiple also known as “Price Multiple” or “Earnings Multiple” is computed as :
- Price per share / Earnings Per Share OR Market Capitalization / Net Income
- This multiple indicates the price an investor is willing to pay for each $ of earning.
EV/EBITDA Multiple
- Another common multiple is EV/EBITDA which is computed as follows: Enterprise Value / EBITDA
- where, EV represents all claims on the business (Common Equity + Net Debt + Preferred Stock + Minority Interest).
- This aids in neutralizing the effect of capital structure. EBITDA accrues to both debt and equity holders since it is before the interest component.
Price to Book Value Ratio
- PBV Ratio is the Price/Book ratio is an equity multiple computed as Market Price of a share/Book Value per share or Market Capitalization/Total Shareholder’s Equity
The steps to keep in mind for executing a comparative valuation are:
- Pick a group of competitor/similar companies with comparable industries and fundamental characteristics.
- Compute the market capitalization = Share Price X No. of shares outstanding.
- Calculate the Enterprise Value
- Use historical formulas from the company filings and projections from the management, equity analysts etc.
- Calculate the various spread multiples which will give a ball park view of how the firm is performing reflecting the truth behind the financial information.
- 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 merger model is widely used by the Investment Banking fraternity. The entire objective of merger modeling is to display to the clients 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 is a construction of the balance sheet post the merger of the 2 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 Debt instruments as well as 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 target for a price 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” – 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 Intangible assets.
The formula used for computing the goodwill created in an M&A transaction:
New Goodwill = Purchase price of equity – (Tangible Total 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 clearly 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.
Sample M&A Model – Combined Balance Sheet
Sample Merger Model Scenarios
Recommended Articles
You can make use of the following resources to learn the basics of Financial Modeling –
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 🙂
Hitesh Maheshwari says
That’s quite informative 101 with financial models! Keep posting Dheeraj…
Dheeraj Vaidya says
thanks Hitesh!
Bibhuti says
Many many thanks for giving information and knowledge
Dheeraj Vaidya says
thanks Bibhuti for the appreciation!
Himank says
Great article Dheeraj, this pretty much made things clear. You are really helping a lot of people through your content.
Dheeraj Vaidya says
thanks Himank!