As a new junior analyst for a arge brokerage firm, you are excited to demonstrate the skills you leaned in college and prove that you are worth your attractive salary. Your first assignment is to analyze Johnson&Johnson stock, Your boss recommends determining prices based on both the discounted free cash flow valuation method and the comparable PE ratio method. You are a little concerned about your boss's recommendation because your finance professor explained that these two valuation methods can result in widely differing estimates when applied to real data. You are really hoping the two methods will reach similar prices. Good luck with that 1. Go to Reuters (httpeww.reuters.com) and enter the symbol for Johnson &Johnson UNJ) in the "Search" bos, then select Johnson and Johnson. From the main page for JNJ, gather the following information, and enter it into a spreadsheet: a. The current stock price (on the left side of the page) b. The EPS (TTM) c. The number of shares of stock outstanding d. The industry P/E (TTM) ratio 2. Click the "Analysts" tab. On the Analyst page, scroll down to find the LT (Long-Term) Growth Rate and enter the Mean value into your spreadsheet. 3. Go to Morningstar http/www.momingstar.com) and enter "JNJ" into the "Quote" box. 4. Under "Financials," Click Income Statement. Copy and paste (or use the Export to Excel button to create a new file with the datal the most recent three years' worth of income statements into a new worksheet in your existing Excel file. Repeat this pro- cess for both the halance sheet and the cash flow statement for Johnson and Johnson. Keep all of the different statements in the same Excel worksheet. Note: Make sure you are collecting annual data, rather than quarterly or interim data Part 3 Valsaton and the Firm 5. To determine the stock value based on the discounted free cash flow method: a. Forecast the free cash flows. Start by using the historical data from the financial statements doenloaded from Morningstar to compute the three-year average of the following ratios: L EBIT/sales i. Tax rate (income tax expense/income before tax) ili. Property plant and equipmentisales v. Depreciation/property plant and equipment . Net working capitalsales b. Create an empty timeline for the next five years c. Forecast future sales based on the most recent year's total revenue growing at the d. Use the average ratios computed in part (a) to forecast EBIT, property, plant and f. Determine the horizon enterprise value for year 5 using Eq10.6 and a long-run g. Determine the enterprise value of the firm as the present value of the free cash h. Determine the stock price using Eq. 10.4. Note that your enterprise value is in s. To calculate an estimate of JNJ price based on a comparable P/E, ratio, multiply the LT growth rate from Reuters for the first five years equipment, depreciation, and net working capital for the next five years. e. Forecast the free cash floe for the next five years using Eq. 10.2 growth rate of 4% and a cost of capital for JNJ of 11%. floves $ thousands and the number of shares outstanding is in billions industry average PE ratio by JNJ EPS 7. Compare the stock prices produced by the two methods to the actual stock price. What recommendations can you make as to whether clients should buy or sell JNJ stock based on your price estimates? 8. Explain to your boss why the estimates from the two valuation methods differ. Specifically address the assumptions implicit in the models themselves as well as the assumptions you made in preparing your analysis. Why do these estimates differ from the actual stock price of JNJ As a new junior analyst for a arge brokerage firm, you are excited to demonstrate the skills you leaned in college and prove that you are worth your attractive salary. Your first assignment is to analyze Johnson&Johnson stock, Your boss recommends determining prices based on both the discounted free cash flow valuation method and the comparable PE ratio method. You are a little concerned about your boss's recommendation because your finance professor explained that these two valuation methods can result in widely differing estimates when applied to real data. You are really hoping the two methods will reach similar prices. Good luck with that 1. Go to Reuters (httpeww.reuters.com) and enter the symbol for Johnson &Johnson UNJ) in the "Search" bos, then select Johnson and Johnson. From the main page for JNJ, gather the following information, and enter it into a spreadsheet: a. The current stock price (on the left side of the page) b. The EPS (TTM) c. The number of shares of stock outstanding d. The industry P/E (TTM) ratio 2. Click the "Analysts" tab. On the Analyst page, scroll down to find the LT (Long-Term) Growth Rate and enter the Mean value into your spreadsheet. 3. Go to Morningstar http/www.momingstar.com) and enter "JNJ" into the "Quote" box. 4. Under "Financials," Click Income Statement. Copy and paste (or use the Export to Excel button to create a new file with the datal the most recent three years' worth of income statements into a new worksheet in your existing Excel file. Repeat this pro- cess for both the halance sheet and the cash flow statement for Johnson and Johnson. Keep all of the different statements in the same Excel worksheet. Note: Make sure you are collecting annual data, rather than quarterly or interim data Part 3 Valsaton and the Firm 5. To determine the stock value based on the discounted free cash flow method: a. Forecast the free cash flows. Start by using the historical data from the financial statements doenloaded from Morningstar to compute the three-year average of the following ratios: L EBIT/sales i. Tax rate (income tax expense/income before tax) ili. Property plant and equipmentisales v. Depreciation/property plant and equipment . Net working capitalsales b. Create an empty timeline for the next five years c. Forecast future sales based on the most recent year's total revenue growing at the d. Use the average ratios computed in part (a) to forecast EBIT, property, plant and f. Determine the horizon enterprise value for year 5 using Eq10.6 and a long-run g. Determine the enterprise value of the firm as the present value of the free cash h. Determine the stock price using Eq. 10.4. Note that your enterprise value is in s. To calculate an estimate of JNJ price based on a comparable P/E, ratio, multiply the LT growth rate from Reuters for the first five years equipment, depreciation, and net working capital for the next five years. e. Forecast the free cash floe for the next five years using Eq. 10.2 growth rate of 4% and a cost of capital for JNJ of 11%. floves $ thousands and the number of shares outstanding is in billions industry average PE ratio by JNJ EPS 7. Compare the stock prices produced by the two methods to the actual stock price. What recommendations can you make as to whether clients should buy or sell JNJ stock based on your price estimates? 8. Explain to your boss why the estimates from the two valuation methods differ. Specifically address the assumptions implicit in the models themselves as well as the assumptions you made in preparing your analysis. Why do these estimates differ from the actual stock price of JNJ