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 Must Read Books 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.
Pages to are hidden for
"Equity Valuation Model"Please download to view full document