Question

As a new junior analyst for a brokerage firm, you are excited to demonstrate the skills you learned 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 P/E ratio method. You are really hoping the two methods will reach similar prices. Good luck!

1. Go to Reuters (https://www.reuters.com) and enter the symbol for Johnson& Johnson (JNJ) in the “Search” Box, then select Johnson and Johnson. From the main page for JNJ, gather the following information, and enter it into a spreadsheet:

1. Current stock price
2. The EPS
3. The number of shares of stock outstanding
4. The industry P/E 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 (www.morningstar.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 data) the most recent three years’ worth of income statements into a new worksheet in your existing Excel file. Repeat this process for both the balance 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.

5. To determine the stock value based on the discounted free cash flow method:

1. Forecast the free cash flows. Start by using the historical data from the financial statements downloaded from Morningstar to compute the three-year average of the following ratios:

1. EBIT/sales
2. Tax rate = income tax expenses/income before tax
3. Property plant and equipment /sales
4. Depreciation/ property plant and equipment
5. Net working capital/sales

2. Create an empty timeline for the next five years
3. Forecast future sales based on the most recent year’s total revenue growing at the LT growth rate from Reuters for the first five years
4. Use the average ratios computed in part (a) to forecast EBIT, property, plant and equipment, depreciation, and net working capital for the next five years.
5. Forecast the free cash flow for the next five years.
6. Determine the terminal enterprise value for year 5. Long-run growth rate is 4% and a cost of capital for JNJ is 11%.
7. Determine the enterprise value of the firm as the present value of the free cash flows.
8. Estimate “Debt” (take the average of the past five years of debt)
1. Estimate “Cash” (take the average of the past five years of cash)
10. Determine the stock price = (Enterprise value – Debt + Cash)/number of shares outstanding.

6. To calculate an estimate of JNJ price based on a comparable P/E ratio, multiply the industry average P/E 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?

Answer 1

Assumptions:

FCF Calculation follows:

Terminal Value at the end of 5th year = (FCF at 5th year-1)/(11%-4%)

Stock price using P/E = EPS x Number of outstanding shares

Given stock price by P/E method is higher than enterprise stock price. This stock is overvalued. Therefore, it should be sold.