Let Yt be the sales during month t (in thousands of dollars) for a photography studio, and let Pt be the price charged for portraits during month t. The data are in the file Week 4 Assignment Chapter 12 Problem 64Preview the document. Use regression to fit the following model to these data:

Yt = a + b1Yt1 + b2Pt + et

This equation indicates that last month's sales and the current month's price are explanatory variables. The last term, et, is an error term.

If the price of a portrait during month 21 is $10, what would you predict for sales in month 21?

Does there appear to be a problem with auto correlation of the residual? Explain your answer.

NOTES

Make Two scatterplots to determine which variables have the best correlation.First use a scatterplot of the sales (Yt) versus the price and then use a scatterplot of the sales (Yt) versus the lag sales (Yt-1).Look at the correlation coefficient (R) to determine which case has the stronger relationship (higher correlation).Consider why the correlation between the two cases (sales and lag sales) is different.If you answer why this is, you will gain an insight on why you did this problem.You would want to go with the relationship that has the higher correlation forthe most effective decisions.

To complete the regression portion of the assignment using excel, use the regression option in the data analysis tool.Input the data just like you did for linear regression, except for multiple regression, input the range of the dependent data (Yt) in the input Y range box and the independent variables (Yt-1 and Price) in the input X range box.Excel automatically orders the independent data from left to right, so make sure you properly label each column to keep track of the variables as Excel allows you to enter up to 16 independent variables.Choose the residuals and standardized residuals so you can complete the autocorrelation portion of the problem.

Part a: Complete the multiple regression analysis between the price and lag sales.Consider the R squared value and the residuals.Discuss what you find here.Using these data points and the equation in the problem, calculate a forecasted value (Y hat) for sales in month 21 at a price of $10 per portrait.

Part b:In order to determine if there is a problem with the autocorrelation of the residuals, you need to look at the residuals from your multiple regression and determine if the standard deviation of the residuals for four lag periods is greater than two standard errors where the standard error is one over the square root of the sample size (n).