# Equity Valuation Model by xmf29996

VIEWS: 89 PAGES: 7

• pg 1
```									                              How to Value Stocks

Discounted Cash Flow (DCF) Stock Valuation Model
DCF Valuation approach is the widely accepted as the appropriate way of determining
the value of a company. The DCF Valuation Model follows the same techniques used
by Warren Buffet and other great investors. The model calculates the present value of a
company’s future free cash flows to determine its intrinsic value.

The process of calculating the value of a stock can take hours. With the aid of Importing
Financial Data directly into an excel based Stock Valuation Model and focusing on a few
key value drivers, reduces the valuation time down to a few minutes.

Ten years of financial data is imported into the Stock Valuation Model as well as a few
other parameters. Once the financial data has been imported into the Stock Valuation
Model it’s a snap to calculate the intrinsic value. You simply input 6 value drivers on the
valuation sheet.

   Discount Rate
   Tax Rate Net
   Operating Profit Margin
   Net Investment Margin
   Change in Working Capital
   Growth Rate

Do not worry about calculating the above values; the tool does it for you. The Stock
Valuation Model will give you the historical average value for the past 3 years and as
well as analyst expected growth rates.

I feel it is important that you understand the mechanics behind the model so that you can
gain confidence and conviction in your valuations. The following is a detailed description
of the calculation behind the model:

The value of a stock is equal to the following:

Present Value of the Next 10 Years of FCF
+ Discounted Residual Value
+ Total Current Assets
- Long Term Debt
- Total Current Liabilities
Total Value of Common Equity / Number of Outstanding Shares
Step 1 - Free Cash Flow
Free Cash Flow (FCF) represents the cash that a company is able to generate after laying
out the money required to maintain or expand its asset base. Free cash flow is important
because it allows a company to pursue opportunities that enhance shareholder value.

Free Cash Flow = Net Operating Profit – Net Investment – Change in Working Capital

Net Operating Profit (NOP)

The following provides a description of how to calculate NOP which is found on the
income statement:

Revenue
- CGS (Cost of Goods Sold)
- SGA (Sales and General Administration Cost)
- R&D (Research and Development)
- Other Cost
NOP (Net Operating Profit)

NOP should be based upon the operations so other costs should not be included;
however, many companies utilize other costs so frequently that it is essentially becomes
part of common operations. Further investigation should be done to determine if the other
cost could be removed from the NOP. I generally include other cost which results in a
more conservative valuation and exclude by exception. If the other cost is truly a one
time event you can exclude it from the calculation.

Net Investment

It is the total spending on new capital expenditures minus replacement investment, which
simply replaces depreciated capital goods.
Net Investment = Capital Expenditures less the Depreciation.

Capital Expenditures and Depreciation values can be found on the cash flow statement.

Change in Working Capital

Working capital, also known as net working capital, is a financial metric which
represents operating liquidity to a business. Along with fixed assets such as plant and
equipment, working capital is considered a part of operating capital.

Working Capital = Accounts Receivables + Inventory – Accounts Payable.

Change in working capital is the difference between current and previous year.

The working capital information is found on the balance sheet
Step 2 –Free Cash Flow for the next 10 years (Excess Return Period)
Use the Calculations from step 1 to calculate the Free Cash Flow for the next 10 years.

The following is an excerpt from the DCF Model Sheet

Step 3 –Present Value of the Next 10 Years of FCF
The Discount Factor is calculated based upon the discount rate which is the minimum
rate of return required for the stock. I generally use between 9% (large companies with
consistent cash flow) and 11% for small companies.

The Discount Factor = 1/(1+Discount Rate) ^ (Number of Years in the Future)

The Discounted FCF also known as the Present Value = FCF for a given year multiplied
by the Discount Factor

Add the next 10 years Discounted FCF values together to get the Discounted Excess
Return value.

Step 4 –Residual Value
Residual Value is the value of all FCF year 11 and beyond.

Residual Value = Year 11 FCF / (Discount Rate – Perpetuity Growth Rate)

Perpetuity Growth Rate (Beyond Year 10): The growth rate beyond 10 years
should typically be about the growth rate of the economy at whole which
historically has been around 3%.

Discounted Residual Value = Residual Value * Year 10 Discount Factor
Step 5 –Total Value of Common Equity

Present Value of the Next 10 Years of FCF
+ Discounted Residual Value
+ Total Current Assets
- Long Term Debt
- Total Current Liabilities
Total Value of Common Equity

Note: Total Current Assets, Long Term Debt and Total Current Liabilities values come
from the Balance Sheet.

Step 6 – Intrinsic Stock Value:
Calculated by taking the Total Value of Common Equity divided by the Total Number of
Outstanding Shares
How to Calculate PE Valuation
Utilizing the PE to calculate the value of a stock has its limitations since it is based upon
earnings and the PE that the market is willing to bear; however, it is the most commonly
used method of calculating the value of stock. I’ve taken it one step further and based
the intrinsic value on a minimum rate of return (Discount Rate) which you can manually
vary.

The PE Stock Valuation is a good reality check against the DCF Stock Valuation. Large
capitalized stocks that have consistent operations often sell for a premium to their DCF
intrinsic valuation which might warrant a blended valuation between the two valuation
approaches. The Stock Valuation Tool will automatically generate a blended stock
value.

Ten years of historical PE values are imported into the Stock Valuation Model as well as
the other parameters required to calculate the PE Valuation. Once the financial data has
been imported into the Stock Valuation Model you input the PE you think the stock will
be trading at 10 years from now

I feel it is important that you understand the mechanics behind the model so that you can
gain confidence and conviction in your valuations. The following is a detailed description
of the calculation behind the model

Step 1 –Dividend Payout Ratio
The Dividend Payout Ratio is calculated by taking the Dividend Payout / Earnings
Dividend Payout value can be found on the Cash Flow Statement and Earnings are found
on the Income Statement.

Step 2 –EPS (Earnings per Share) over the next 10 Years
EPS is calculated for each of the next 10 years. This is calculated by taking the current
EPS and multiplying it by the assumed growth rate.

Step 3 – Sum of Dividend Payouts over the next 10 Years
Multiply Year 1 EPS by the Dividend Payout Ratio. Repeat this for the next 10 years
then sum Year 1 to Year 10. In the above example it is 17.5

Step 4 – Forecasted Share Price Year 10 (PE)
Total Forecasted Share Price Year 10 (PE) = Year 10 EPS * Average P/E Value (PE
Value expected in Year 10)

Step 5 – Forecasted Share Price Year 10 (Dividends)
This value is equal to the Sum of Dividend Payouts over the next 10 years calculated in
Step 3.

Step 6 – Forecasted Share Price Year 10
Forecasted Share Price Year 10 = Forecasted Share Price Year 10 (PE) + Forecasted
Share Price Year 10 (Dividends)

Step 7 – Average Forecasted Return over 10 Years
Calculate the expected rate of return at the current price using the following formula:

Forecasted Share Price Year 10 / Current Price * Exp (1/10)-1

Step 8 - Price Required for 15% Return
Calculate the present value of the Forecasted Share Price Year 10 using a discount rate of
15%. This will provide you the price you should pay to make 15% return on your
investment.

Step 9 – Intrinsic Value

Calculate the present value of the Forecasted Share Price Year 10 using the appropriate
discount rate. I generally use between 9% (large companies with consistent cash flow)
and 11% for small companies
When you purchase the Stock Valuation model, I will provide you three recommended
books to read that will provide additional insight into the how to pick winning stocks and
the valuation process. Below is a brief description of each book:

Book 1

This book will walk you through the process of identifying a quality company and will
provide some high level valuation concepts. The PE valuation model is based upon the
concepts in this book.

Book 2

This is an easy read book, walking you through the process of how to identify a quality
company and just as importantly identifying the characteristics of a bad company and
investment.

Book 3

Prior to reading this book my DCF valuation model was unduly complicated. This book
does a great job of describing the DCF valuation process in an easy to understand
manner. This book should fill most of the theory holes you might have on how to value
a stock. I should point out that I disagree with their approach on determining the
appropriate discount rate. My model subscribes to the Warren Buffets approach that the
discount rate should equal the minimum required return hurdle.

```
To top