Your work will be graded both on mathematical accuracy and presentation.
The homework submission should be in a single pdf file and include a cover page
according to the requirements of HW2 and HW3 (see also below). Make sure you
show all work needed. You should also upload one Excel file with your work.
Create an Excel file containing the prices for Google stock for 07/01/2019 – 11/25/2019
Use Yahoo Finance or similar websites to obtain historical data.
a) Assuming a linear relationship between the closing price and the business day
(07/01/2019 – business day 1, 07/02/2019 – business day 2, etc), use the least-squares
method to compute the regression coefficients. You can use Data Analysis Toolpack for
this. State the obtained model with the found estimated parameters.
b) State the extrapolated price for the Google stock for 11/26/2019 (since the date is out
of the range, this predicted value for the stock for 11/26/2019 will be an extrapolation).
c) Perform the residual analysis. Based on the results, evaluate whether the
assumptions of the linear regression are seriously violated.
d) Find the Durbin-Watson statistic. Do this with Excel but don’t use Data Analysis or
any additional Excel Toolpacks for this. At level of significance Î± = 0.01, is there
evidence of positive autocorrelation among the errors?
Be careful with the formulation of your hypothesis here.
e) Based on your observations in (c)-(d), is there a reason to question the validity of the
Explain your solutions in complete sentences.
The pdf file should contain 4-5 pages corresponding to this problem and include the
1 page with data (remove all columns that you don’t use),
1 page with formulae for the same worksheet (use Ctrl + ~ to see the formulae),
1 page with the output from Data Analysis ToolPack
1-2 pages with handwritten or typed responses for parts (a)-(e). The summary should
be written in complete sentences stating all obtained results.
The printout of each worksheet should fit into 1 page. Make sure you include all
headings (A, B, ….; 1, 2, ) in the printout so that it’s possible to trace the formulae.
Format your work so that it’s readable.
Purchase answer to see full