Question

A patient of Florida Hospital recently made a substantial donation in gratitude for the excellent care she received during a recent hospital stay. The money will be added to a board-designated fund that can be used only for cancer research. James Brown is the chair of the investment committee of Florida Hospital’s board, which has been asked to invest the funds. The committee is considering investment in two stocks: Johnson & Johnson (JNJ) and Mednax (MD). The historical values and returns of the two stocks and the S&P 500 (used as a market proxy) over the past ten years are as follows:

	JNJ Stock	JNJ Stock	MD Stock	MD Stock	S&P 500	S&P 500
Year	Price	Return	Price	Return	Value	Return
2006	$   30.00		$31.00		$1,438.24
2007	$   45.29	50.97%	$33.92	9.42%	$1,578.55	9.76%
2008	$   40.00	-11.68%	$23.30	-31.31%	$925.88	-41.35%
2009	$   45.10	12.75%	$27.20	16.74%	$1,073.87	15.98%
2010	$   46.28	2.62%	$33.08	21.62%	$1,486.12	38.39%
2011	$   30.00	-35.18%	$35.61	7.65%	$1,212.41	-18.42%
2012	$   70.00	133.33%	$42.78	20.13%	$1,498.11	23.56%
2013	$   60.12	-14.11%	$40.00	-6.50%	$1,782.59	18.99%
2014	$   70.00	16.43%	$60.28	50.70%	$1,994.99	11.92%
2015	$   40.00	-42.86%	$69.46	15.23%	$1,940.24	-2.74%
2016	$ 150.00	275.00%	$60.00	-13.62%	$2,278.87	17.45%

The risk-free rate (RF) is 4 percent, the required rate of return on the market, R[RM], is 6.74%. Use the average of the provided historical returns to estimate expected returns.

Part a

Calculate the market risk (beta) for JNJ’s stock and describe how risky the stock is relative to the market. (Hint: Beta is not $ or %, format the beta cell as “number”. Also, please round it to two decimal places).

Part b

Calculate the market risk (beta) for MD’s stock and describe how risk the stock is relative to the market.

Part c

Calculate the required rate of return for JNJ’s stock

Part d

Calculate the required rate of return for MD’s stock

Part e

Compare the expected rate of return with the required rate of return of each investment and based on that, provide an investment recommendation to the committee.

Answer 1

Part a&b

formula used

for

Covariance of JNJ with S&P500

=(((C4-B16)*($G$4-$B$15))+((C5-B16)*($G$5-$B$15))+((C6-B16)*($G$6-$B$15))+((C7-B16)*($G$7-$B$15))+((C8-B16)*($G$8-$B$15))+((C9-B16)*($G$9-$B$15))+((C10-B16)*($G$10-$B$15))+((C11-B16)*($G$11-$B$15))+((C12-B16)*($G$12-$B$15))+((C13-B16)*($G$13-$B$15)))/($J$4-1)

for

covariance of MD with S&P500

=(((E4-B17)*($G$4-$B$15))+((E5-B17)*($G$5-$B$15))+((E6-B17)*($G$6-$B$15))+((E7-B17)*($G$7-$B$15))+((E8-B17)*($G$8-$B$15))+((E9-B17)*($G$9-$B$15))+((E10-B17)*($G$10-$B$15))+((E11-B17)*($G$11-$B$15))+((E12-B17)*($G$12-$B$15))+((E13-B17)*($G$13-$B$15)))/($J$4-1)

for

Variance of S&P500 RETURNS

=(((G4-B15)^2)+((G5-B15)^2)+((G6-B15)^2)+((G7-B15)^2)+((G8-B15)^2)+((G9-B15)^2)+((G10-B15)^2)+((G11-B15)^2)+((G12-B15)^2)+((G13-B15)^2))/(J4-1)

,

now since beta for JNJ = 1.51

and beta for S&P 500/ market = 1 ( beta for market is always considered 1)

since beta of JNJ > beta for market, it is riskier than market

and since beta for MD < beta for market , it is less riskier than market

part c&d

Part e)

The expected rate of return is the average return calculated for each stock

we can see that

average return for MD ( expected return for MD) = 0.0818 or 8.18%

required return for MD = 5.37%

average return > required margin for MD

average return for JNJ ( expected return for JNJ) = 0.3520 or 35.20%

required return for JNJ = 8.13%

average return > required margin for JNJ

both stocks are undervalued since their expected return are > their required return

since JNJ provides a much better expected rate of return relative to its required return, the committee should choose stock JNJ