Question

Fundamental Toys Inc. is looking to expand its stock portfolio. You receive an urgent text message from Bob: I need you to analyze a Stock X as a possible investment candidate for the company's stock portfolio. The CFO has asked me to make a decision ASAP. I left the data you need on your desk. Fortunately, Excel has the capability of performing regression analysis with built-in algorithms. This frees the analyst from complicated algebraic formulations and data management. The following data has been collected to help you run a linear regression between Stock X and the New York Stock Exchange (NYSE) on which Stock X is traded. Use CAPM as the investment decision model. Data from Bob Historical Rates of Return Year NYSE Stock X 1 (30%) (19.0%) 2 32.5 20.5 3 21.6 16.5 4 (9.8) 0.5 5 7.8 9.7 6 22 20.5 7 33.5 19.8 (Source: Intermediate Financial Management, Brigham and Davies, 10th edition) The NYSE is the independent variable, X-axis, which is the proxy market portfolio. Stock X is the dependent variable, Y-axis. Also, assume a market return (NYSE) of 12%, the risk-free rate of 4% and the market return of Stock X of 7.85%. Submit the following items in to the online drop box. 1.The Excel regression function (the regression equation). 2.The SML constructed from the risk-free rate, beta (slope of the regression function), and the market return provided above. 3.Your conclusion on the stock purchase given your CAPM analysis and the market return of Stock X. Provide support for your decision.

Answer 1

(a) the regression equation y=1.6572+0.6037x

(b) slope of the regression is 0.6037

for x=12, y=1.6572+0.6037*12=8.9016

for x=4, y=1.6572+0.6037*4=4.072

for x=7.85, y=1.6572+0.6037*7.85=6.3962

(c) since p-value=0.0184 of the regression is less than typical value of level of significance alpha=0.05, so model is significant and we can use this SML

following regression analysis information has been generated using ms-excel

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.838571793
R Square	0.703202652
Adjusted R Square	0.643843182
Standard Error	4.491197678
Observations	7
ANOVA
	df	SS	MS	F	Significance F
Regression	1	238.9542885	238.9543	11.84651	0.018398092
Residual	5	100.8542829	20.17086
Total	6	339.8085714
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	1.65718276	4.289084754	0.386372	0.715116	-9.368260596	12.68262612
X	0.603687791	0.175394976	3.441876	0.018398	0.152820652	1.054554929

following data has been used as , x is independent and y is dependent variable in the regression analysis

NYSE(x)	Stock_X(y)
30	19
32.5	20.5
21.6	16.5
9.8	0.5
7.8	9.7
22	20.5
33.5	19.8