Case II: Microsoft stock closing prices

Suppose now we are interested in analyzing the behavior of stock price of Microsoft using linear regression analysis. Below at the end there is an Excel dataset that contains ten years historical data of daily closing prices of Microsoft (MSFT), S&P500 and GE.

(a). Obtain scatterplots of three variables included, and discuss whether there exists an obvious graphical linear relationship between each pair of the variables. [Note: Using Excel, you need to create three scatter plots separately and list them together.]

(b). Using the data given, develop three regression models (two simple linear regression models and one multiple regression model). Write down all models. [Note: you must copy and paste the outputs from Excel regression analysis].

(c). Based on the outputs of three models, which one performs the best in fitting the data given, and why?

(d). What is the R2 of the best regression model you identified in (c)? Please interpret its meaning.

(e). What does the partial regression coefficients in your model above mean? Please provide correct interpretations for them.

(f). Use =0.05 significance level, discuss the overall (Global) significance of your regression model, also discuss the significances of the partial regression coefficients. In so doing, list hypotheses and test statistics and show your steps in making the conclusion.

(g). As majority of financial analysts would argue that the stock price of MSFT is positively influenced by the macroeconomic condition, which is (although not precisely here) represented by the S&P500 index. Please use a formal hypothesis test to check the validity of the claim.

(h). Based on your best model selected, provide an overlay plot of MSFT stock prices vs. predicted prices.

(i). Suppose we know that tomorrow the closing price for GE will be $28 and S&P500 will be 900, based on your best model in (c), provide the point estimate for the stock closing price of MSFT for tomorrow.

MSFT	SP500	GE
2.52	469.1	6.72
2.5	468.44	6.69
2.45	463.02	6.64
2.38	457.49	6.46
2.45	459.57	6.49
2.45	460.21	6.46
2.44	460.33	6.51
2.53	463.72	6.52
2.55	465.39	6.47
2.51	463.75	6.5