# Financial Modeling

## Case Study – ITC Limited

To find out a stock's intrinsic value, financial modeling is useful. So let us find a stock's intrinsic value with the help of a case study.

Let’s calculate the Present Value of ITC Limited using DCF Analysis.

Before diving into the calculation, let’s first understand the method of Discounted Cash flow Analysis.

Discount rate is the minimum rate of return, which the investor expects from the investment.

Discount Rate = opportunity cost = desired return on investment. In other words, “The value of any investment is the discounted present value of all its future cash flow

Discounting Factor is a weighing factor that is used to calculate present value of future cash flows. It is calculated in the following way –

**DF = (1+r) ^ (-n)**

Therefore, we can calculate the present value for each year’s expected FCF and add them up. Use the formula below to do this.

**Present Value = FCF/ (1+r) ^ n, where r = discount rate**

Let us calculate the present value, using a discount rate of 10%.

Thus, if the stock is available at INR 500, it is a bargain.The stock is undervalued (i.e., Present Value > Market Value) and before the market realises it, we must buy it.

The most important, yet difficult aspect of DCF analysis is the projection of future cash flows and future capital expenditure. To do a meaningful analysis, you need at least the previous 5 – 10 years of data and projections for a similar period in future. Also, the DCF method requires a large number of assumptions. Hence, the model is very sensitive to changes in assumptions.

Nevertheless, it is an important and famous method of equity valuation.

So now, let us calculate the present value (you may call it the intrinsic value) of ITC Limited using the DCF Analysis model.

The formula for calculating the Present Value would be:

The formula we use looks somewhat complicated on paper but is extremely easy to use once you have assumed all values

PV = Present Value

CFi = Cash Flow in year ‘’' (i.e. CF1…CF2….CF3….till CFn-1)

k = Discount rate

g = growth rate assumption in perpetuity beyond terminal year

n = the number of periods in the valuation model including the terminal year

Therefore, we follow the following steps -

### Step 1: Determining the Revenue Growth Rates

Growth Rate: Growth Rate is the rate at which a company is expected to grow in the future. You have to be very realistic and rational about the growth rate you chose for the company, else the intrinsic value arrived may be misleading.

You can estimate the growth rate of a company by looking at the past earnings growth record or by reading the reports by various analysts and make a consensus growth estimate.

Based on the past and expected future performance of both ITC and the economy, we can project the growth rate of revenues -

Therefore, we have assumed that ITC will grow at 4%, -3%, 20%, 15% and 13% over the next 5 years.

### Step 2: Forecasting the Financial Statements

Now, based on financial statements of the last 5 years, we computed different ratios for ITC Limited, across financial figures in the P&L Account, Balance Sheet and Cash Flow Statement.

### Step 3: Deriving the FCFF

Free Cash Flow: Free cash flow is the cash left with the company after paying for all the expenses related to daily operations of the business and any assets bought to expand the business(such as plant and machinery).

Free cash flow is the most important and reliable part of Discounted Cash Flow (DCF) analysis because unlike Net Profit and sales, which are prone to accounting manipulation and window dressing, cash flow statements are hard to manipulate.

Free cash flow of the company can be calculated using following formula:

**Free Cash Flow = cash flow from operating activities – capital expenditures**

**Note** – Cash flow from operating activities indicates the net cash flow a company earns from its regular business operations, such as manufacturing and selling of goods and services. Capital expenditure is the money used for purchasing or maintaining physical assets like property, plants, machines, etc.

This is the trickiest part as projection is based on pure assumptions you make about the future of the business. It is always intelligent to be slightly conservative so that you do not overpay for a business.

### Step 4: Calculating the Terminal Value

Terminal value (TV) is the value of a business or project beyond the forecast period when future cash flows can be estimated. It assumes that a business will grow at a set growth rate forever after the forecast period. Terminal value often comprises a large percentage of the total assessed value.

The formula to calculate TV is –

**TV = FCF *(1 + Growth rate)/(Cost of capital – Growth rate)**

Remember that Growth Rate represents an assumption that a company will continue to grow at a steady constant rate into perpetuity at 6% p.a.

Typically, the perpetual growth rate ranges from historical inflation rate to historical GDP growth rate.

### Step 5: Calculating the Discount Rate

DCF analysis helps assess the viability of a project or investment by calculating the present value of expected future cash flows using a discount rate. Here we use the Weighted average cost of capital (WACC) to discount the cash flow. WACC is the cost of capital in which each type of capital (equity, debt) is weighed proportionately. The below table from the excel model shows the calculation of WACC for ITC Valuation.

WACC = Cost of Equity*Weight of Equity + Cost of Debt*Weight of Debt

= 12.17*0.9987 + 3.06*0.0013 = 12.16%

Discount rate is usually calculated by using CAPM (Capital Asset Pricing Model) method, which calculates the weighted average of cost of capital of both debt and equity. Thus, we will discount the cash Flows at 12.16%

### Step 6: Discounting the Cash flows

The WACC and the Cost of Equity for the company calculated in the above step are then used to discount the FCFF and Terminal Value calculated in Step 3 and 4.

In our case, we’ll only consider the FCFF based Intrinsic price of the shares as it represents the cash flow to all the suppliers of capital and not only to the equity shareholders. Thus we arrive at the Present value of future FCFF for ITC Valuation. (Units are INR Millions)

We find the discounted market capitalization of the company, which is the sum of present value of all the future cash flows and perpetual present value.

Thus, by applying the growth rates, we come to a calculation that the Present Value of the Cash Flow available to the firm is INR 27, 70,259 million.

### Step 7: Arriving at the Intrinsic Value of the Shares

Dividing the PV of the FCFF and Terminal Value (the Value of the entire firm) by the number of outstanding shares we get the per share intrinsic value. We can compare this price with the current market price of the stock to understand whether the stock is overvalued or undervalued, whether the stock is trading at discount or premium.

- If the Intrinsic Value of Share > Market Value of the Share, then the stock is undervalued – one should buy the stock.
- If the Intrinsic Value of Share < Market Value of the Share, then the stock is overvalued – one should not buy the stock, rather should sell the stock if in possession.

Future is anything but predictable. Always remember, "your predictions will more often be wrong than right" so give yourself a margin of safety (i.e. margin to go wrong). In other words, buy stocks, which are available far below their fair or intrinsic value.

**Conclusion**

So we have finally at the end of this module where we got a detailed explanation about the uses of financial modeling and its importance in stock analysis. There are more such types of modules on ELM School that offer knowledge on stock analysis and other areas of finance.