Question 1:
Tim Cooper plans to invest money in a mutual fund that is tied to one of the major market indices, either the S&P 500 or the Dow Jones Industrial Average. To determine if these two funds are related, Time decided to take 20 weeks of data and compare the two markets. The closing price for each index is shown in Data for Question 1 provided in the Excel file. Use Data for Question 1 to answer the following questions:
a. Use Excel to plot DJIA (Y-axis) against S&P index (X-axis) and examine the plot to determine whether a linear model is reasonable?
b. Use the Regression procedure in Excel to develop a simple linear regression model with Y being DJIA and X being S&P index; write down the estimated regression equation; interpret the meaning of each regression coefficient in this context.
c. Look at the Excel outputs to report two important statistics: the Correlation Coefficient and the Coefficient of Determination; explain the meaning of each statistic.
d. Report the p-value of this model. Is the model statistically significant? Explain.
e. Based on this model, what would you expect the DJIA to be when the S&P is 1100?
Question 2:
Sales of industrial vacuum cleaners at R. Lowenthal Supply Co. over the past 13 periods are in Data for Question 2 provided in the Excel file.
a. Using a moving average with three periods, determine the demand for vacuum cleaners for period 14.
b. Using a weighted moving average with three periods, determine the demand for vacuum cleaners for period 14. Use 3, 2, and 1 for the weights of the most recent, second most recent, and third most recent periods, respectively.
c. Use exponential smoothing with a smoothing constant α = 0.7 to develop forecasts for period 2 through period 14. Assume that the initial forecast for period 1 is 7.
d. Report the Mean Absolute Deviation (MAD) for each model in part a), part b) and part c). Based on MAD criteria, which model would be most accurate in predicting the demand for vacuum cleaners for period 14? Why?
Question 3:
Trevor Harty, an avid mountain biker, always wanted to start a business selling top-of-the-line mountain bikes and other outdoor supplies. A little over 6 years ago, he and a silent partner opened a store called Hale and Harty Trail Bikes and Supplies. Growth was rapid in the first two years, but since that time, growth in sales has slowed a bit, as expected. The quarterly sales (in thousands of dollars) for the past 4 years are shown in the table in Data for Question 3 in the Excel file.
a. Create a line graph to present this time series. Interpret the graph.
b. Use Regression in Excel to develop a simple linear regression model with Sales (Y) being the dependent variable and Time (X) being an independent variable. ( Please use Data for question 3 part b)). Is this a good model to forecast the Sales for each quarter in Year 5? Why?
c. Use Multiple Regression Model to incorporate both trend and seasonal components into the forecast by developing a multiple regression model with Y being dependent variable and XI, X2, X3, X4 being independent variables. (Please use Data for Question 3 part c)). Write down the estimated regression equation. Look at R-square and the Standard Error to compare the two models in part b) and c). Which model is better? Why?
d. Use Multiple Regression Model in part c) to forecast the Sales for each quarter in Year 5.
Note: for quarter 1 [X1 = 17, X2 = 0; X3 = 0; X4 = 0]
for quarter 2 [ X1 = 18, X2 = 1; X3 = 0; X4 = 0]
for quarter 3 [X1 = 19, X2 = 0; X3 = I; X4 = 0]
for quarter 4 [X1 = 20, X2 = 0; X3 = 0; X4 = 0]
Attachment:- Data.xlsx