Are you looking to start a business or invest in one? Understanding financial modeling is crucial to make informed decisions. A Financial model is a tool that helps businesses forecast their future financial performance. There are four types of financial models: DCF (Discounted Cash Flow), 3-statement models, LBO (Leveraged Buyout), and M&A (Merger & Acquisition) models. Each has its own unique approach and purpose.
This is 2nd blog of the series. Prior to this we had updated 1 blog.
FM Blog 1 -What is financial modeling?
In this blog, we'll explore each type of financial models and understand how or when to use them.
Types of financial models
1) 3 statement cash flow model
The 3 statement cash flow model is used to forecast the future financial performance of a company. It involves the preparation of the three financial statements - income statement, balance sheet, and cash flow statement. The cash flow statement is the primary focus of this model, as it provides a detailed view of the company's cash inflows and outflows.
Why is it used?
The 3 statement cash flow model is used by investors, analysts, and management teams to evaluate the financial health of a company, forecast future cash flows, and make strategic decisions.
Step by step instructions with example
Let's say you are evaluating the financial performance of a company and want to use the 3 statement cash flow model. Here are the steps:
Step 1: Collect financial statements
Collect the historical financial statements of the company, including the income statement, balance sheet, and cash flow statement.
Step 2: Create the income statement
Prepare the income statement for the next 3-5 years. Let's assume that the revenue for the first year is $10 million, with a 5% annual growth rate. The cost of goods sold (COGS) is 60% of revenue, and the operating expenses are $2 million per year. The tax rate is 30%.
Year 1: Revenue = $10 million
COGS = $6 million
Operating expenses = $2 million
Taxes = $0.6 million
Net income = $1.8 million
Step 3: Create the balance sheet
Prepare the balance sheet for the next 3-5 years. Let's assume that the total assets for the first year are $50 million, with a 10% annual growth rate. The liabilities are 50% of the assets, and the equity is the remaining 50%.
Year 1: Total assets = $50 million
Liabilities = $25 million
Equity = $25 million
Step 4: Create the cash flow statement
Prepare the cash flow statement for the next 3-5 years. Let's assume that the operating cash flow is equal to the net income, and the capital expenditures (CAPEX) are $1 million per year. The company is not continue this complete the step 4 with example like above points and continue with rest of the points accordingly
Year 1:
Operating cash flow = $1.8 million
Investing cash flow (CAPEX) = -$1 million
Financing cash flow = $0
Net cash flow = $0.8 million
Cash balance = $2.8 million
Step 5: Calculate free cash flow
Free cash flow for year 1 is equal to operating cash flow minus CAPEX:
Free cash flow = $1.8 million - $1 million = $0.8 million
Step 6: Discount future cash flows
Assuming a discount rate of 10%, we can calculate the present value of the free cash flows for each year:
Year 1: $0.8 million / (1+10%)^1 = $0.73 million
Step 7: Calculate the net present value (NPV)
The NPV is the sum of the present value of the free cash flows minus the initial investment:
NPV = $0.73 million - $1 million = -$0.27 million
In this example, the NPV is negative, indicating that the investment is not profitable. This could prompt further analysis of the assumptions made in the model or reconsideration of the investment decision.
Overall, the 3 statement cash flow model is a valuable tool for forecasting future cash flows, evaluating financial performance, and making strategic decisions. It requires careful analysis of historical data and industry trends, as well as thoughtful assumptions about future growth and expenses.
2) Discounted cash flow model
Discounted cash flow (DCF) is a financial model used to estimate the value of an investment based on its future cash flows. The model takes into account the time value of money, which means that a dollar received today is worth more than a dollar received in the future. By discounting future cash flows back to their present value, the DCF model helps investors and analysts evaluate the potential profitability of an investment.
Why is it used?
The DCF model is used by investors, analysts, and management teams to evaluate the financial attractiveness of an investment opportunity, determine the value of a company or business unit, and make investment decisions.
Step by step instructions with example
Let's say you are evaluating an investment opportunity and want to use the DCF model. Here are the steps:
Step 1: Determine cash flows
Determine the cash flows that will be generated by the investment over a specific time period. This could be the expected cash flows of a business unit, a project, or a specific investment.
Assume that the investment generates cash flows of $1 million per year for the next 5 years.
Step 2: Determine the discount rate
Determine the discount rate, which represents the cost of capital or the minimum rate of return required by investors. The discount rate reflects the risk associated with the investment. Assume a discount rate of 10%.
Step 3: Calculate the present value of cash flows
Discount each year's cash flows back to their present value using the discount rate. The present value of each year's cash flow is calculated by dividing the future cash flow by (1+discount rate)^n, where n is the number of years in the future that the cash flow will be received.
Year 1: $1 million / (1+10%)^1 = $0.91 million
Year 2: $1 million / (1+10%)^2 = $0.83 million
Year 3: $1 million / (1+10%)^3 = $0.75 million
Year 4: $1 million / (1+10%)^4 = $0.68 million
Year 5: $1 million / (1+10%)^5 = $0.62 million
Step 4: Calculate the sum of present value of the cash flow
Add up the present values of each year's cash flows to calculate the total present value of the investment. Using the formula, calculate the present value of each year's cash flow:
Total present value = $0.91 million + $0.83 million + $0.75 million + $0.68 million + $0.62 million = $3.79 million
Step 5: Compare to initial investment
Compare the total present value of the investment to the initial investment or purchase price to determine whether the investment is profitable or not. Assuming an initial investment of $3 million, the total present value of the investment T $3.79 million is greater than the initial investment. Therefore, this investment opportunity is profitable.
Overall, the DCF model is a powerful tool for evaluating the financial attractiveness of an investment opportunity. It requires careful analysis of cash flows, discount rates, and assumptions about future growth and expenses. By incorporating the time value of money, the DCF model helps investors and analysts make more informed investment decisions.
3) Merger and acquisition financial model
A merger and acquisition (M&A) financial model is a tool used to evaluate the financial implications of a merger or acquisition. It is an essential part of the due diligence process and helps companies assess the feasibility of the transaction, understand the impact on their financial statements, and estimate the potential return on investment.
Here are the steps to create an M&A financial model with an example:
Define the transaction: First, identify the companies involved in the transaction, the type of transaction (merger or acquisition), and the purchase price. Let's assume that Company A is acquiring Company B for $100 million.
Determine the financing structure: Next, decide how the acquisition will be financed. This includes determining the proportion of debt and equity financing, the interest rate on debt, and the expected return on equity. Let's assume that Company A will finance the acquisition with 60% debt and 40% equity.
Create a projected income statement: Use historical financial statements of both companies to create a pro forma income statement for the combined entity. The income statement should include projections for revenue, cost of goods sold, gross profit, operating expenses, and net income. Let's assume that the combined company will have revenue of $500 million and operating expenses of $300 million.
Calculate the purchase price allocation: Allocate the purchase price to the assets and liabilities of Company B based on their fair market value. This will result in a new balance sheet for the combined company.
Determine the impact on cash flows: Estimate the impact of the acquisition on the cash flows of the combined entity. This includes changes in working capital, capital expenditures, and debt payments.
Calculate the return on investment: Finally, calculate the expected return on investment based on the projected cash flows of the combined entity. This will help determine if the acquisition is a good investment.
Example
Step 1: Identify the companies involved in the transaction, the type of transaction (merger or acquisition), and the purchase price.
In this example, Company A is acquiring Company B for $100 million.
Step 2: Determine the financing structure.
In this example, Company A will finance the acquisition with 60% debt and 40% equity.
Step 3: Create a projected income statement.
Using historical financial statements of both companies, a pro forma income statement for the combined entity is created, as shown in the example:
| Revenue | $500 million |
| Cost of goods sold | $300 million |
| Gross profit | $200 million |
| Operating expenses | $100 million |
| Net income | $100 million |
Step 4: Calculate the purchase price allocation.
The purchase price is allocated to the assets and liabilities of Company B based on their fair market value. The example shows the allocation as follows:
| Assets | $120 million |
| Liabilities | ($20 million) |
| Goodwill | $100 million |
Step 5: Determine the impact on cash flows.
The impact of the acquisition on the cash flows of the combined entity is estimated. In this example, the acquisition is expected to result in an increase in working capital of $10 million, capital expenditures of $50 million, and debt payments of $20 million.
Step 6: Calculate the return on investment.
Using a discounted cash flow analysis, the expected return on investment is calculated based on the projected cash flows of the combined entity. The example shows the expected cash flows over the next five years and the resulting expected return on investment of 12%.
| Year 1 | $70 million |
| Year 2 | $80 million |
| Year 3 | $90 million |
| Year 4 | $100 million |
| Year 5 | $110 million |
The calculation of the return on investment involves discounting these cash flows back to present value using a discount rate that reflects the time value of money and the risk associated with the investment. The resulting net present value is then compared to the purchase price to determine if the acquisition is a good investment.
In this example, a return on investment of 12% is calculated, indicating that the acquisition is a good investment for Company A.
Overall, this process involves analyzing financial data and projections to determine the potential benefits and risks associated with an acquisition. By carefully considering factors such as financing structure, projected income, purchase price allocation, and expected cash flows, investors can make informed decisions about whether or not to pursue a particular acquisition opportunity.
4) Leveraged Buyout (LBO) financial model
A leveraged buyout (LBO) is a financial model used by investors to acquire a company using a combination of equity and debt financing. In an LBO, the acquiring company uses a significant amount of debt to finance the purchase of the target company, with the goal of increasing the potential returns for the investors.
The steps involved in an LBO model are as follows:
- Identify the target company and perform due diligence to assess its financial health, growth potential, and other factors that could impact the value of the investment.
- Determine the purchase price of the target company, which includes both the equity and debt components. The equity component is the amount of capital that the investors are willing to contribute, while the debt component is the amount that will be borrowed from banks or other lenders.
- Structure the financing for the LBO. This involves determining the amount of debt that will be used to finance the purchase and negotiating the terms of the debt with lenders, including interest rates and repayment schedules.
- Estimate the cash flows that the target company is expected to generate over the investment period. This involves analyzing the historical financial statements of the company and projecting future revenues, expenses, and cash flows.
- Calculate the expected returns on the investment. This involves determining the expected cash flows from the target company, subtracting the debt payments and other expenses, and dividing the remaining cash flows by the equity investment. This will provide an estimate of the internal rate of return (IRR) on the investment.
- Conduct sensitivity analysis to assess the potential risks and rewards of the investment. This involves testing the model under different scenarios, such as changes in interest rates, revenue growth, and other factors that could impact the financial performance of the target company.
- Make a decision on whether to proceed with the LBO. This involves weighing the potential risks and rewards of the investment and determining whether it meets the investors' criteria for risk and return.
Here's an example of an LBO model:
ABC Capital is considering an LBO of XYZ Company, a manufacturing company with strong growth potential. The purchase price of XYZ Company is $100 million, with $70 million of debt and $30 million of equity. The debt has an interest rate of 8% and a repayment period of 10 years.
ABC Capital estimates that XYZ Company will generate cash flows of $20 million per year over the next 10 years. After deducting the debt payments and other expenses, ABC Capital expects to receive $8 million per year in cash flows from the equity investment.
ABC Capital calculates the expected IRR on the investment as 25%, which meets its target return criteria. However, ABC Capital conducts sensitivity analysis and determines that if XYZ Company's revenues grow at a slower rate than expected, the IRR could drop to 15%.
Based on its analysis, ABC Capital decides to proceed with the LBO, believing that the potential rewards outweigh the risks. Based on the analysis of the LBO model, the expected IRR on the investment is 25%, which meets ABC Capital's target return criteria. This suggests that the LBO investment could potentially generate strong returns for the investors.
However, the sensitivity analysis also reveals that there are risks associated with the investment, particularly if XYZ Company's revenues grow at a slower rate than expected. Therefore, while the potential rewards may be significant, investors need to carefully consider the risks before making a decision to proceed with an LBO.
Conclusion
In conclusion, financial modeling is an essential tool for businesses and investors to make informed decisions about their future. Understanding the different types of financial models can help you determine which one to use for your specific situation. DCF models are great for forecasting long-term cash flows, while Comps models help to determine a company's value compared to its peers. LBO models are useful for evaluating a company's leverage and potential returns on investment, while M&A models help to evaluate potential acquisitions. By utilizing these different types of financial models, you can make informed decisions and ensure the success of your business or investments.
To get upskilled in Financial modeling, you may want to check out the keySkillset platform.
.jpg)
.jpg)
.jpg)
.jpg)
.png)
