The closing stock price for each of two stocks was recorded over a 12-month period. the...

Transcribed Image Text:

The closing stock price for each of two stocks was recorded over a 12-month period. the closing price for the Dow Jones Industrial Average (DIIA) was also recorded over this same time period. These values are shown in the following table. a) Develop a regression model by running regression to predict the price of stock 1 based on the Dow Jones Industrial Average. Place the regression on a separate Sheet named "Stock 1Reg". What price would Stock 1 be if DJIA was 12,365? (DO NOT USE THE FORECAST FORMULA) b) Which of the two stocks is most highly correlated to the Dow Jones Industrial Average over this time period? c) Do a Trend Analysis Scatterplot with the DJIA? Forecast the DJIA for Month 14? (DO NOT USE THE FORECAST FORMULA) Month DJIA Stock 1 Stock 2 1 11,168 48.5 32.4 2 11,150 48.2 31.7 3 11,186 44.5 31.9 4 11,381 44.7 36.6 5 11,679 49.3 36.7 6 12,081 49.3 38.7 7 12,222 46.1 39.5 8 12,463 46.2 41.2 9 12,622 47.7 43.3 10 12,269 48.3 39.4 11 12,354 47.0 40.1 12 13,063 47.9 42.1 13 13,326 47.8 45.2 The closing stock price for each of two stocks was recorded over a 12-month period. the closing price for the Dow Jones Industrial Average (DIIA) was also recorded over this same time period. These values are shown in the following table. a) Develop a regression model by running regression to predict the price of stock 1 based on the Dow Jones Industrial Average. Place the regression on a separate Sheet named "Stock 1Reg". What price would Stock 1 be if DJIA was 12,365? (DO NOT USE THE FORECAST FORMULA) b) Which of the two stocks is most highly correlated to the Dow Jones Industrial Average over this time period? c) Do a Trend Analysis Scatterplot with the DJIA? Forecast the DJIA for Month 14? (DO NOT USE THE FORECAST FORMULA) Month DJIA Stock 1 Stock 2 1 11,168 48.5 32.4 2 11,150 48.2 31.7 3 11,186 44.5 31.9 4 11,381 44.7 36.6 5 11,679 49.3 36.7 6 12,081 49.3 38.7 7 12,222 46.1 39.5 8 12,463 46.2 41.2 9 12,622 47.7 43.3 10 12,269 48.3 39.4 11 12,354 47.0 40.1 12 13,063 47.9 42.1 13 13,326 47.8 45.2