Stock Valuation Model Instructions / Investment Guide The following provides step by step instructions on how to utilize the Valuation Tool to calculate the intrinsic value of a stock. You will be able to get a rough valuation in a matter of a few minutes allowing you to spend more time evaluating your input assumptions, sustainability of the company’s competitive moat and the company’s management. Note: This at this time this model does not support financial companies due to their financial statements being different from other companies. Step 1 (Open Stock Valuation Model) Open the excel Stock Valuation Model In order to take full advantage of the Stock Valuation Model you will need to enable the use of Macros. These Macros will reduce the time it takes to do a valuation by cleaning up the imported data and clearing old valuation inputs. Follow the direction below for the excel version that you are using. Excel 2003 – Enable Macros http://www.meisteel.com/Security/Excel2003Security.htm To use the "Fix Imported Data" macro Excel's macro security setting must be medium or less. To change the security setting, select Excel's “Tools” pull down menu, select Security and change the setting to Medium. Save the file and reopen it. You will only have to do this the initial time that you open the file. The following warning will pop up as the file loads. There are several Macros within in the spreadsheet that are used to automatically populate various fields. Select “Enable Macros” for the three pop-ups Excel 2007 – Enable Macros http://www.meisteel.com/Security/Excel2007Security.htm Both versions of Excel The following warning will pop up as the file loads. Queries are used to automatically load financial data. Select “Enable automatic refresh” Step 2 – Model Inputs The Yellow Shaded fields are the input fields Connect to the Internet Ticker Symbol Once a new stock symbol is entered, the spreadsheet will automatically populate data from Morningstar, Moneyline and Quicken. Note: Entering an invalid stock symbol will corrupt the excel file requiring you to close the file and open an uncorrupt version of the model. Click on the button. The “Fix Import Data” button cleans up the imported data on the Import Income, Import Balance and Import Cash Flow sheets. The source data often uses “---“ and “(NaN)” instead of 0. The Macro will replace the “---“ and (NaN) with 0. It also clears the scenario table. The cells highlighted in Turquoise on the Import Income, Import Balance and Import Cash Flow sheets are utilized by the model. Caution: Most of the time replacing the “---“ with 0 or blank is appropriate; however, prior to making an investment, I would recommend that you validate all the data utilizing SEC filings. If you wish to adjust financial historical data make the corrections on the Import Income, Import Balance and Import Cash Flow sheets. Discount Rate: The Discount Rate is used to determine the intrinsic value of the company. It is calculated by adding the 10 Year bond rate plus the Risk Premium. I require a 6% premium above the 10 year bond rate. You should look at the discount rate as a minimum return hurdle unlike many in the financial community who calculate the discount rate as Weighted Average Cost of Capital. My approach is the same approach that Warren Buffet and other legendary investor use. Margin of Safety: The margin of safety is the percentage amount below the intrinsic value. A large margin of safety reduces your probability of permanent loss of capital as well as increasing your likely return. I generally require a margin of safety of 30% or greater below the intrinsic value. Tax Rate %: Enter the forecasted Tax Rate going forward. Note: Companies that have not been publicly traded for 10 years will have no data imported in some of the years artificially lowering the average tax rate. Make certain that the values are not artificially low due to no data being available. These values are calculated on the Income sheet which is a few excel sheets to the right of the Valuation sheet. If you are uncertain, I would recommend inputting a tax rate of 39%. PE Ratio: The average historical PE value for 1, 3, 5 and 10 years are shown to the right of the yellow PE entry field. . Note: Unless you have a strong reason to justify a PE ratio hiring than 20 in year 10, I would recommend capping the PE ratio at 20. Net Operating Profit Margin (NOPM): The average historical NOPM value for 1, 3, 5 and 10 years is shown to the right of the NOPM entry field. You should look for companies that have very consistent or improving NOPM so that you can forecast the future NOPM with a high degree of certainty. The following provides a description of how to calculate NOPM. 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. You can exclude other costs on the income statement by zeroing out the other cost. Net Operating Profit Margin (NOPM) is the NOP / Revenue. The model calculates the NOP based upon a % of sales utilizing the NOPM entry. Net Investment Margin The average historical Net Investment Margin for 1, 3, 5 and 10 years is shown to the right of the Net Investment Margin field. The lower the Net Investment Margin the better. You should look for companies that have very consistent or improving Net Investment Margin so that you can forecast the future Net Investment Margin with a high degree of certainty. I recommend being conservative unless there is a fundamental change in efficiencies with the companies cost structure. The following provides a description of how to calculate Net Investment Margin. The Net Investment value is the Capital Expenditures less the Depreciation. The model calculates the Net Investment based upon a % of sales utilizing the Net Investment Margin entry. Change in Working Capital: The average historical Net Change in Working Capital for 1, 3, 5 and 10 years is shown to the right of the Net Change in Working Capital field. You should look for companies that have very consistent or improving Change in working Capital so that you can forecast the future Net Change in Working Capital with a high degree of certainty. The following provides a description of how to calculate Change in Working Capital Working Capital is Accounts Receivables + Inventory – Accounts Payable. Working capital is the difference between current and previous year. The value you are entering is this value as a percentage of sales Growth Rate: To the right of the growth rate yellow field are the analysis low, middle and high estimated growth rates. I would recommend using either the medium or low growth rate estimate. Generally, the high estimated growth rate is not very likely and in many cases the middle growth rate is rather optimistic. I generally either use the middle growth rate for years 1-5 and the low growth rate for years 6-10. If you get a White Strike Through with a Red Filled cell, the spreadsheet detected a value that did not import correctly. Go to the appropriate import sheet and manually input the correct value. The Analyst Forecast Links provide quick links to Quicken and MSN Money to get additional growth forecast information. I generally look at the Quicken intrinsic value web page to get analyst grow rate forecasts. Step 3 – Valuation Two valuation outputs are calculated FCF Valuation and PE Valuation. Margin of Safety Price – Intrinsic Value Less (Intrinsic Value * Margin of Safety %) Price Required for 15% Return – This is the price required to make a 15% return. It is equivalent to using a discount rate of 15% Intrinsic Value - The Value of the Stock The Blended Valuation is the average of each of the independent values. Step 4 - Scenario Analyzer Assign a probability to each of the scenario from 0 to 100% such that the total is equal to 100%. Enter the probability in the Yellow Shaded fields. Using Steps 1 and 2 calculate a valuation for each scenario. Once you have calculated a valuation for a given scenario select the scenario macro button it represent (Pessimistic, Mid 1, Mid 2 or Optimistic). This will automatically populate the Scenario Analyzer with the valuation output for this scenario. The Scenario Analyzer will calculate a weighted average value. 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 10 years, 5 years, 3 years and trailing 12 months 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 and a description of each value driver. The value of a stock is equal to the present value of all future free cash flows. Free Cash Flow is the amount of cash that a company has left over after it has paid all of its expenses, including investments. Free Cash Flow = Net Operating Profit - Taxes – Net Investment – Net Change in Working Capital Income Statement - Data Revenue - CGS (Cost of Goods Sold) - SGA (Sales and General Administration Cost) - R&D (Research and Development) - Other Cost NOP (Net Operating Profit) Note: Other Cost should not be included if it is truly a one time event; however, I’ve found that companies use Other Cost rather liberally and it seems to be rather consistent cost. I generally, include it in my initial valuation and exclude it if I feel it truly is a one time event. Cash Flow Statement - Data The Net Investment value is the Capital Expenditures less the Depreciation. The model calculates the Net Investment based upon a % of sales utilizing the Net Investment Margin entry. Balance Statement - Data Working Capital is Accounts Receivables + Inventory – Accounts Payable. Working capital is the difference between current and previous year. The value you are entering is this value as a percentage of sales The Free Cash Flow values are all derived as a percentage of the expected Revenue. On the DCF Model sheet the following historical value drivers are calculated Net Operating Profit Margin (Line 9) Net Investment Margin (Line 24) Change In Invested Capital Margin (Line 36) To limit risk and increase the probability of an accurate valuation, you should screen for stocks that have a very consistent historical value drivers. The following is and excerpt from the DCF Model Sheet The DCF Valuation Model utilizes the following User Inputted Value Drivers which are located on the Valuation sheet to calculate the Free Cash Flow of a Stock: Growth Rate Revenue Net Operating Profit Margin Tax Rate Net Investment Margin Change In Working Capital It also utilizes the following imported data Line 59 – Revenue: Calculated by taking previous 12 months of revenue * the growth rate entered on the Valuation sheet. Line 60 – Net Operating Profit: Calculated by multiplying the Net Operating Profit Margin which was entered on the Valuation sheet by the corresponding years revenue forecast on Line 59 Line 61 – Less Taxes: Calculated by multiplying the Tax Rate which was entered on the Valuation sheet by the Net Operating Profit (Line 60) Line 62 – Net Operating Profit After Taxes (NOPAT): Calculated by taking Net Operating Profit (Line 60) minus Taxes (Line 62) Line 64 – Net Investment: Calculated by multiplying the Net Investment Margin which was entered on the Valuation sheet by the corresponding year’s revenue forecast Line 59. Line 65 – Change in Working Capital: Calculated by multiplying the Change in Working Capital Margin which was entered on the Valuation sheet by the corresponding year’s revenue forecast Line 59. Line 67 – Free Cash Flow: Calculated by taking NOPAT (Line 62) – Net Investment (Line 64) – Change in Working Capital (Line 65). The Discount Factor is calculated based upon the discount rate which is the minimum rate of return required for the stock. You enter the discount rate on the Valuation sheet. I generally use between 9% (large companies with consistent cash flow) and 11% for small companies. Line 71 - The Discount Factor = 1/(1+Discount Rate) ^ (Number of Years in the Future) Line 72 - The Discounted FCF also known as the Present Value = FCF for a given year multiplied by the Discount Factor Now Comes the Easy Part of the Valuation Line 41 - The Discounted Excess Return Period FCF per Share is the Sum of the next 10 years of Discounted FCF values Residual Value is the value of all FCF year 11 and beyond. Residual Value = Year 10 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%. Line 42 -Discounted Residual Value = Residual Value * Year 10 Discount Factor Line 43 – Total Current Assets: Comes from the balance sheet. Line 44 – Long Term Debt: Comes from the balance sheet Line 45 – Total Current Liabilities: Comes directly from the balance sheet. Line 46 - Total Value of Common Equity Discounted Excess Return Period FCF + Discounted Residual Value + Total Current Assets - Long Term Debt - Total Current Liabilities Total Value of Common Equity Line 47 – Intrinsic Stock Value: Calculated by taking the Total Value of Common Equity (Line 46) Divided by the Total Number of Outstanding Shares Price to Earnings (PE) Stock Valuation Model 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 and a description of each value driver. The following imported values are used by the PE Model: The Dividend Payout Ratio is calculated by taking the Total Dividend / Total # Shares. The PE Valuation Model utilizes the following User Inputted Value Drivers which are located on the Valuation sheet: - Discount Rate - PE Ratio The PE Valuation Model calculates the expected EPS in year 10 plus the sum of the next 10 years of dividends payouts per year. It then discounts the “Total Forecasted Share price Year 10” by the 15% to determine the price required for 15% Return. It then discounts the “Total Forecasted Share price Year 10” by the Discount Rate which was entered on the Valuation sheet to determine the Intrinsic Value of the Stock.