Question

Create one Excel file to submit named "Exam 3 Team X.xlsx" where X is your team letter.

Show work for credit. USE EXCEL

1. Use the data below.

	Team	C
Date	DOW	10YTREAS
8-Apr-20	23,719.37	0.729
1-Apr-20	22,653.86	0.736
25-Mar-20	21,917.16	0.698
18-Mar-20	20,704.91	0.816
11-Mar-20	21,237.38	0.997
4-Mar-20	25,018.16	0.748
26-Feb-20	25,917.41	1.01
19-Feb-20	27,081.36	1.33
12-Feb-20	29,232.19	1.556
5-Feb-20	29,276.34	1.59
29-Jan-20	28,807.63	1.603
22-Jan-20	28,722.85	1.641
15-Jan-20	29,196.04	1.769
8-Jan-20	28,939.67	1.818
1-Jan-20	28,583.68	1.827

a. Create a scatter plot. Say what you see.

b.  Use the data to develop an estimated regression equation showing how your team data is related to DOW, the Dow Jones industrial average. What is the estimated regression model (y-mx+b, slope & intercept)?
Let x represent the DOW indexes.

c.  How much of the variation in the sample values of your team data does the model estimated in part (b) explain?
Round your answer to two decimal places.

d. Suppose that the closing price for the DOW is 29,000. Estimate the closing price for your data

e. Preform a hypothesis test for the model (F test) with an significance of 0.05. State your conclusion.

f. Preform a hypothesis test for each of the estimated coefficients at the 0.05 level of significance. State your conclusions.

2. From the data above create 3 variables where Month01=1, 0 otherwise for Janurary, Month02=1, 0 otherwise for February, Month03=1, 0 otherwise for March.

a..  Use the data to develop an estimated regression equation showing how your team data is related to DOW, the Dow Jones industrial average. What is the estimated regression model?
Let x represent the DOW indexes.

b.  How much of the variation in the sample values of your team data does the model estimated in part (b) explain?
Round your answer to two decimal places.

c. Suppose that the closing price for the DOW is 29,000. Estimate the closing price for your data for April.

d. Preform a hypothesis test for the model (F test) with an significance of 0.05. State your conclusion.

e. Preform a hypothesis test for each of the estimated coefficients at the 0.05 level of significance. State your conclusions.

3. Use the data below

Month	Team A	Team B	Team C	Team D	Team E	Team F
1	17	18	27	30	37	43
2	21	29	35	42	44	45
3	16	25	31	33	35	36
4	23	32	38	40	50	54
5	17	26	27	29	34	38
6	18	20	21	26	30	34
7	22	32	36	40	50	57
8	20	21	22	25	29	36
9	21	27	32	34	36	43
10	19	24	31	34	35	43
11	17	23	26	31	41	42
12	24	34	35	41	45	48

a.  Show the naive forecast, an exponential smoothing forecasts using α = 0.2, and a 3-month moving average forecast.

b. Compare the MFE, MSE, and MAPE on the models

c.  Make a conclusion on which model to use.

d. Find the alpha (smoothing constant) that minimizes the MSE.

Answer 1