FIN 321 - Financial Management II Question is analysis about STOCK market and calculation

1   

College of Business and Economics

GROUP ACTIVITY I – STOCK VALUATION

FIN321 Financial Management II

Jongha Lim

As a new junior analyst for a large brokerage firm, 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.

1. Go to Yahoo Finance (http://finance.yahoo.com/) and enter the symbol for Johnson & Johnson (JNJ) in the search box, then select Johnson and Johnson. From the main page for JNJ, click “Statistics” tab, gather the following information, and enter it into a spreadsheet:

a. The current stock price

b. The Diluted EPS (TTM, which means ‘trailing twelve months’)

c. The number of shares outstanding (*Note that the unit is billion shares.)

2. Click the “Analysis” tab. Scroll down to find the Growth Estimates for the Next 5 Years and enter it into your spreadsheet. This will be used as the sales growth rate projection for the upcoming 5 years.

3. Click the “Financials” tab. choose “Income Statement” and “Annual” data frequency. Copy and paste the most recent three years’ worth of the following data items into your Excel file. (*Note that all financial numbers are in thousand dollars.)

a. Total Revenue

b. EBIT

c. Income Before Tax (Pretax Income)

d. Income Tax Expense (Tax Provision)

4. Now choose “Cash Flow” statement and “Annual” data frequency, and repeat the same process for the three years’ worth of the following cash flow statement items:

a. Depreciation & amortization

2   

b. Investments in Property, Plant, and Equipment (= Capital Expenditures) (*Note that this item is listed as minus value to indicate it is cash outflows. Therefore, use the absolute value for the ‘size’ of capital expenditures.)

5. Now choose “Balance Sheet” and “Annual” data frequency, and repeat the same process for the three years’ worth of the following balance sheet items:

a. Total Current Assets

b. Total Current Liabilities

c. Total Debt

d. Cash And Cash Equivalents

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

a. Based on the historical data from the financial statements downloaded from Yahoo Finance, compute the following ratios for each of the three years and take the average:

i. EBIT-to-sales ratio (=Operating Income or Loss/Total Revenue)

ii. Tax rate (=Income Tax Expenses/Income Before Tax)

iii. Depreciation-to-sales ratio (=Depreciation/ Total Revenue)

iv. Capex-to-sales ratio (=Capita Expenditures/Total Revenue)

v. NWC-to-sales ratio (=[Total Current Assets–Total Current Liabilities]/ Total Revenue)

b. Forecast future (annual) sales for the next five years, starting from the most recent year’s Total Revenue (Sales0) growing at the 5-year growth rate obtained in Question 2 for each of the next five years.

c. Use the average ratios computed in part (a) to forecast the following items for each of the next five years:

i. EBIT (=Total Revenue each year × EBIT-to-sales ratio)

ii. EBIT× (1-t) (=EBIT each year× (1- Tax rate))

iii. Depreciation (=Total Revenue each year × Depreciation-to-sales ratio)

iv. Capital expenditures (=Total Revenue each year × Capex-to-sales ratio )

v. Net working capital (=Total Revenue each year × NWC-to-sales ratio)

vi. Increase in net working capital (=this year’s NWC − previous year’s NWC)

* Tip: You can find Year0 NWC using Total Current Asset and Total Current Liabilities in Year0, i.e., NWC0 = Total Current Asset0 - Total Current Liabilities0

3   

d. Compute the free cash flow for each of the next five years using the free cash flow equation.

e. Determine the terminal enterprise value at the end of year 5 (EV5) using following equation and a long-run constant FCF growth rate of 2% and a WACC of 5%.

FCFwacc

FCF

FCFwacc gr

gFCF

gr

FCF EV

 

 

 )1(56

5

f. Determine the current enterprise value (EV0) of JNJ as the sum of present values of the five year free cash flows plus terminal enterprise value, i.e.:

5 55

4 4

3 3

2 21

0 )1()1()1()1()1( waccwaccwaccwaccwacc r

EVFCF

r

FCF

r

FCF

r

FCF

r

FCF EV

 

 

 

 

 



g. Find debt amount outstanding (Long Term Debt) and cash amount (Cash And Cash Equivalents) based on the most recent year’s financials. Determine the stock price using the following equation:

0-

000

goutstandinsharesofNumber

CashDebtEV P

 0

Note: Make sure to match units. (Note that if you haven’t made adjustments, your numerator is in thousand dollars, and the denominator is in billion shares.)

7. To calculate an estimate of JNJ’s stock price based on a comparable P/E ratio, multiply the industry average P/E ratio (to be given in class) by JNJ’s EPS.

8. Compare the stock prices produced by the two methods to the actual stock price. Explain to your boss why the estimates from the two valuation methods may differ. Specifically address the assumptions implicit in the models themselves as well as the assumptions you made in preparing your analysis.