Share
Explore BrainMass

Test Opinion that Big Company Customers Influence Stock Price

See attached file.

Suppose an entry level financial analyst wants to use regression models to analyze and predict the daily closing price of Microsoft stock traded in the market. To begin with the task, he was given a dataset (stockprices.MTW dataset, downloadable from GeorgiaVIEW, source: Yahoo) which contains ten years' historical closing prices of stocks of Microsoft, GE and FORD, based on one colleague's suggestion that big companies such as GE and FORD are important influencing customers of Microsoft, therefore, the analyst need to decide whether or not should she/he include either GE or FORD or both stocks' prices into the model. In addition, it is argued that the performance of Microsoft's business is situated under the macroeconomic conditions. As indicators of the whole economy, U.S. Treasury Bill rates and S&P500 index values (here assuming these are good indicators, although theoretically S&P500 should not be used as independent variable here, which we ignore in favor of modeling purposes) over the ten years are collected into the dataset.

For constructing an effective regression model for daily closing prices of Microsoft stock, please complete the following tasks:

(a). Obtain a scatterplot matrix of the five variables, based on the plot, discuss which variable is most linearly correlated with the dependent variable of MSFT.
(b). Using stepwise regression method, identify the best simple regression model for predicting the dependent variable. Write down your model in the format of , interpret the meaning of regression coefficient ²1. Also discuss the significance of the model. µ²²++=XY10
[Note: in Minitab, for carrying out stepwise regression analysis, you must specify the significance level to be 0.05. Besides, for significance discussion, you must use appropriate statistical terms and statistics, and you can use formal hypothesis test].
(c). Based on stepwise regression analysis output, identify the best regression model fitting the data given. In so doing, write down your best (multiple) regression model. And discuss what does the partial regression coefficient ²1 mean [Note: you must explicitly explain its meaning based on the case and model estimate, please do not simply copy the definition of partial regression coefficient here] in your model.
(d). Please discuss the overall significance of the model in (c) and the significance of each independent variable, show your reasoning.
[Note: you must use appropriate statistical terms and statistics to support your discussion, also you can use formal hypothesis tests for the discussion here.]
(e). What is the R2 of your best model, please interpret its meaning.
(f). Obtain predictions of the historical closing prices of MSFT stock, based on your best fitting model in (c), and plot them with the real observed values in an overlay plot figure to show a graphical presentation of your model performance.
(g). Using appropriate statistics, please discuss whether there exists severe multicollinearity problem in your best model in (c)
(h). Assuming the analyst is asked to provide an estimate (point estimate) for tomorrow's Microsoft stock closing price, and assume based on reports of the Fed and other researchers, tomorrow's T-bill rate will be 1.0, S&P500 will be 1000, GE's stock closing price will be at $28 per share, and closing price of FORD's stock will be at $8 per share, what would be the estimate based on the best fitting model?
(i). Test the opinion/suggestion from the colleague that the performance of big company customers will have a positive influence on Microsoft's business. In so doing, conduct a formal hypothesis test. You MUST list hypotheses, test statistic(s), p-value(s) and other important and essential steps in obtaining statistical inferences.

Attachments

Solution Preview

See attached file.

Suppose an entry level financial analyst wants to use regression models to analyze and predict the daily closing price of Microsoft stock traded in the market. To begin with the task, he was given a dataset (stockprices. MTW dataset, downloadable from GeorgiaVIEW, source: Yahoo) which contains ten years' historical closing prices of stocks of Microsoft, GE and FORD, based on one colleagues suggestion that big companies such as GE and FORD are important influencing customers of Microsoft, therefore, the analyst need to decide whether or not should she/he include either GE or FORD or both stocks prices into the model.
In addition, it is argued that the performance of Microsoft's business is situated under the macroeconomic conditions. As indicators of the whole economy, U.S. Treasury Bill rates and S&P500 index values (here assuming these are good indicators, although theoretically S&P500 should not be used as independent variable here, which we ignore in favor of modeling purposes) over the ten years are collected into the dataset.

For constructing an effective regression model for daily closing prices of Microsoft stock, please complete the following tasks:
(a). Obtain a scatterplot matrix of the five variables, based on the plot, discuss which variable is most linearly correlated with the dependent variable of MSFT.

As seen in both graphs, SP500 is the most linearly correlated with the dependent variabe of MSFT.
(b). Using stepwise regression method, identify the best simple regression model for predicting the dependent variable. Write down your model in the format of, interpret the meaning of regression coefficient β1. Also discuss the significance of the model. εββ++=XY10
[Note: in Minitab, for carrying out stepwise regression analysis, you must specify the significance level to be 0.05. Besides, for significance discussion, you must use appropriate statistical terms and statistics, and you can use formal hypothesis test].
From the output below, the best simple regression model is:

MSFT = -17.10 + 0.04095(SP500)

This is because the introduction of SP500 into the model contributes to 88.31% of the variation in MSFT. The others in the next three steps add only 2.87% more.

Meaning: the Microsoft stock price (MSFT) is expected to decrease by $17.10 when S&P500 index is set at zero. In addition, for every unit increase in S&P500 index, we expect the Microsoft stock price (MSFT) to increase by of $0.04

Stepwise Regression: MSFT versus Tbill_rate, SP500, GE, FORD

Alpha-to-Enter: 0.05 Alpha-to-Remove: 0.05

Response is MSFT on 4 predictors, with N = 2363

Step 1 2 3 4
Constant -17.10 -10.34 -12.75 -11.64

SP500 0.04095 0.04112 0.05106 0.04736
T-Value 133.58 150.10 53.69 37.68
P-Value 0.000 0.000 0.000 0.000

Tbill_rate -1.596 -1.816 ...

Solution Summary

The solution provides a test opinion that big company customers influence stock price.

$2.19