Multiple Regression
Excel does an excellent job of completing multiple regression calculations (Even though it is not noted, you can input multiple variable columns for the independent (x) variable). Make sure you group all of the independent variables together to use this option. As I note in the weekly guidance, you can use excel to complete these calculations (see the steps below).
A best practice is to set up a lag sales column (Yt-1) where the sales lag the price by one time period (month). This shows the relationship between the price and the time when the customer actually buys the product.
Complete two scatterplots to determine which variables have the best correlation. First complete a scatterplot of the sales (Yt) versus the price and then complete 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 complete this problem. You would want to go with the relationship that has the higher correlation for the 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).