6 MOST RELEVANT FINANCIAL MODELS USED BY PROFESSIONALS TODAY

6 MOST RELEVANT FINANCIAL MODELS USED BY PROFESSIONALS TODAY

Financial Modeling-Meaning and Uses — Financial Modeling is the use of any spreadsheet software such as MS Excel to prepare a business’ future financial statements. These future financial statements are called Financial Models.

The major perspective of Financial Modeling is to predict the future of a business on the basis of past and present data. The predictions are based on the company’s past financial information, the information that is currently available about the company and the industry, and assumptions about how the future will unfold. Conclusions from a Financial Model are then used for analysis and decision making in the company.

Another aspect of Financial Modeling is to perform the valuation of a company/business. It helps investors determine whether the share price of potential investment options is undervalued, overvalued or valued accurately. This valuation is done on the basis of financial statements. For example, the results of a Financial Modeling done on the basis of financial statements gives a particular insight into the valuation of the company, which is then compared to its share price. Imagine that the share price of the company turns out to be less than what the model predicts, then it indicates that the company is undervalued and should be invested in.

Types of Financial Models

There are numerous types of financial models used by professionals but the most relevant ones in today’s corporate finance world include:

Three-Statement Model

As the name suggests, this model includes three financial statements- the Income Statement (also called the Profit & Loss Statement), the Balance Sheet and the Cash Flow Statement.

  1. The Income Statement gives the details about the revenue and expenses generated by the company over the required time period.
  2. The Balance Sheet tells about what assets (such as cash in the bank, inventory or real estate) and liabilities (like bank loans, debt to suppliers) a company has at a given point of time. Subtracting the liabilities from the assets gives the net worth of the company at that given point in time.
  3. The Cash Flow Statement shows how much cash or anything equivalent to cash entered and left the company over the required time period.

These three statements are dynamically linked with formulas in Excel and financial analysis is then performed on the model.

Financial Models
Fig 1. The layout and components of Three-Statement Model | Source: CFI
Financial Models
Fig 2. The Income Statement | Source: CFI
Fig 3. The Balance Sheet  | Source: CFI
Fig 4. The Cash Flow Statement | Source: CFI

Discounted Cash Flow (DCF) Model

Free cash flow is that part of cash flow that is not required for day to day business operations of the company. Anything done with the free cash flow doesn’t affect the existing business and it is available for distribution among the investors (both debt or equity) of the organization. Free cash flow is used because it shows actual economic value, while metrics like net income or profits may be misleading.

There are two steps in the DCF model- estimation of the company’s free cash flow in the future and then discounting (the opposite of ‘compounding’) it back to today using the appropriate rate to find the present value of the business (called Net Present Value or NPV) and ultimately, the value per share of the business.

The DCF model takes into consideration the initial free cash flow (the average free cash flow generated by the company in the last 3 years), the annual growth rate of the free cash flow of a company (predicted with the help of historic data), terminal growth rate (it is assumed that the cash flow generated at the end of forecast period grows at a constant rate forever, this is because estimates made far off in the future should not be aggressive) and finally the discount rate or the expected return from the business.

To arrive at the discount rate, a measure called Weighted Average Cost of Capital (WACC) is used. The weighted average cost of capital (WACC) is a calculation of a firm’s cost of capital in which each category of capital is proportionately weighted. All sources of capital, including common stock, preferred stock, bonds, and any other long-term debt, are included in a WACC calculation.

So, for example, if the calculated WACC is 9%, it is used to discount expected cash flow to see what it is worth today. So, if cash flow is expected to be ₹100,00,000(1 crore) in 2 years for a company being valued today, that ₹1 crore in 2 years is worth ₹91,74,312 today.

Formula to calculate present value

= future value/(1+WACC)time

= 10000000/(1+9%)2

= 9174311.9

If the company we’re considering has 1 lakh outstanding shares, dividing ₹91,74,312 by 1,00,000 gives a present value of ₹91.74 per share. If our predictions about future cash flows are accurate, this is what the stock is worth today.

Imagine that this stock is trading at just ₹70/share in the market. This indicates that it is undervalued and should be invested in. But, if this stock is trading at ₹100/share, then it means that it is overvalued.

Fig 5. DCF Model |Source: CFI

Mergers & Acquisitions (M&A) Model

As the name implies, the M&A Model is an analysis of the combination of two companies that come together through an M&A process. A merger is the “combination” of two companies, under a mutual agreement, to form a single entity. An acquisition occurs when one company completely takes over the other company. In both cases, a single company emerges. This is usually done to reduce competition, increase operational efficiency and for growth.

This model builds on both the Three Statement Model as well as the DCF Model and hence has all the standard components of a Three-Statement and DCF Model. The steps followed in a Merger Model include:

  1. Forecasting the valuation of the first company (acquirer)
  2. Forecasting the valuation of the second company (target)
  3. Combining the two frameworks and making adjustments for various types of synergies
Fig 6. Overview of The Acquirer Model (in an M&A Model) | Source: CFI
Fig 7. The Target Model (all components exactly similar to The Acquirer Model) | Source: CFI
Fig 8. Deal Assumptions & Analysis | Source: CFI
Fig 9. Pro Forma Model | Source: CFI

Sum of the Parts Valuation (SOTP Model)

The sum-of-the-parts valuation is typically used for companies that have diversified businesses, i.e., when a company is a conglomerate and derives its revenue through business units in different industries that cannot be valued using a single relative valuation technique. In such cases, the different parts of the business are valued differently and then they are all summed up so as to obtain the final number or the Total Enterprise Value (TEV). For example, Reliance Industries owns businesses engaged in energy, petrochemicals, textiles, natural resources, retail, and telecommunications.

The steps followed in an SOTP Valuation include:

  1. Determining the different business segments
  2. Valuing each segment
  3. Combining up the total to get the final result

In the following example, the multinational company Amazon has been divided into different separately valued segments and then added together at the end.

Fig 10. SOTP Model for Amazon | Source: CFI

Leveraged Buyout (LBO) Model

Whenever a private equity firm purchases a business using a significant amount of borrowed money and then uses the profits generated by this business to eventually pay down the debt, ultimately reducing the debt and increasing the level of equity, it is called a leveraged buyout.

The private equity firm may borrow as much as up to 70 or 80 percent of the purchase price from a variety of lenders(Banks, NBFCs, Financial Institutions) and fund the balance with its own equity.

Over time, as the debt is paid off, the equity portion increases significantly and over a long time period, the equity investors can achieve an Internal Rate of Return (IRR) of up to 20-30% or even higher.

Financial Models
Fig 11. All available cash flow goes towards repaying debt | Source: CFI
Fig 12. LBO Model for a Retail Company | Source: CFI
Financial Models
Fig 13. Other Components in LBO Model | Source: CFI

Comparable Company Analysis (CCA) Model

In this method, a company is valued using the metrics of other businesses of similar size in the same industry. For example, the valuation of an e-commerce startup like Flipkart must be done by comparing it with an e-commerce company like Amazon India.

Using valuation measures like P/E Ratio, P/B Ratio, P/S Ratio, EV/Sales, EV/EBITDA etc, it is determined if a company is overvalued or undervalued.

The basic steps followed while performing CCA are:

  1. Finding comparable companies
  2. Determining relevant valuation measure
  3. Validating key fundamental metrics (this refers to excluding non-recurring expenses and/or income which might reflect in the numbers)
Financial Models
Fig 14. Example of a CCA Model for Food & Beverage Corporations | Source: CFI

References:

  1. Ahern, D. (2020). Explaining the DCF Valuation Model with a Simple Example. https://einvestingforbeginners.com/dcf-valuation/
  2. Chen, J. (2020). Comparable Company Analysis (CCA). https://www.investopedia.com/terms/c/comparable-company-analysis-cca.asp
  3. Hargrave, M. (2021). Weighted Average Cost of Capital (WACC). https://www.investopedia.com/terms/w/wacc.asp

Arati Jose

Leave a Reply

Your email address will not be published. Required fields are marked *