Question

Multiple Linear Regression

A Brightwater car dealership, which serves the city of Brightwater and its surrounding communities, was taken over about four years ago by a group of investors led by Jake Rogers. Jake had previously studied marketing and economics at Brightwater University. After taking over the dealership, Jake decided to apply some of the knowledge he had gained from his studies to selling cars. After a few months of operation, he began experimenting with the price of cars and the monthly expenditure on radio advertising. He varied the price and advertising expenditure each month and kept track of the average rate of interest on automobile loans for the month. The data on per car price in thousands (Pr), advertising in thousands (Ad), interest rate (IR), month in which the values applied (Mth), and sales in the thousands (Sales) appear in the data table. Jake would like to know the functional relationship between sales, the price, advertising expenditure, and interest rate on car loans. He is also interested in determining whether there is a trend to the firm’s sales.

2a.       See data is sheet 2 of Excel file with data for this assignment.

2b.       Run a multiple linear regression on the data file for part (2a). (EXCEL Data, Data, Analysis, Regression). Since “Sales” is the dependent (or regressor) variable, the sales data is the Input Y Range. The other 4 variables (Month, Price, Advertising, and Interest Rate) are independent (or explanatory) variables; the explanatory variable data is the Input X Range. (Hint: Excel will permit you to include multiple columns and rows in your X range.)

2c.       From the regression results you obtain in part (2b), determine if each of the explanatory variables used in the regression is statistically significant at a 5 percent level (This means 2.5 percent in each tail of the distribution). You will need to use the t distribution table for this purpose. In your answer, make sure you state what the critical value of t is for each independent variable.   The critical value is the value of t, such that if the t statistic for your independent variable (from your Excel output) is greater than the critical t or less than (-1) times the critical t, then you reject the null hypothesis. Hint: To determine the critical t, you will need both the level of significance (2.5% in this case) and the degrees of freedom.   You will need to calculate the degrees of freedom, which is the number of observations minus the number of independent variables minus 1. Degrees of freedom = N-k-1.

2d.       Rerun the regression using only those explanatory variables which were found to be significant in part (2c). Note that some of the explanatory variable coefficient estimates are positive and some are negative.   Do the signs (positive or negative) on the explanatory variable coefficients make sense? Discuss.

2e.       What purpose does the adjusted R² serve? Discuss. (Note that we generally use adjusted R² rather than R² because it adjusts for the reduction of degrees of freedom as more explanatory variables are added into a regression model.)

2f.        If, at current prices and interest rates, each additional sales dollar contributes $0.18 to profit before advertising and taxes, should the firm continue to advertise? Why or why not? Discuss.   Hint: You will also need the relation between advertising and sales. You estimated this relation in the regression in part (2d).

Month	Price	Advertising	Interest Rate	Sales
1.00	11.70	7.40	12.10	1395.10
2.00	11.40	6.40	11.00	1306.30
3.00	13.50	8.20	10.60	1396.90
4.00	12.40	5.80	13.90	1289.40
5.00	10.00	7.40	15.70	1353.20
6.00	13.30	7.80	11.90	1299.70
7.00	12.60	5.30	13.10	1356.00
8.00	13.50	2.60	6.20	1318.60
9.00	12.50	7.30	12.90	1344.40
10.00	12.80	5.90	14.70	1273.90
11.00	12.20	6.20	14.70	1310.10
12.00	11.40	2.90	13.70	1335.60
13.00	14.00	6.30	6.40	1386.40
14.00	14.10	5.70	11.60	1189.30
15.00	13.60	5.90	15.60	1196.20
16.00	13.90	6.70	11.90	1252.90
17.00	11.30	3.80	12.00	1292.10
18.00	13.30	8.40	14.80	1308.80
19.00	11.50	8.30	18.50	1258.80
20.00	10.60	6.90	13.60	1328.20
21.00	12.10	6.60	9.30	1357.70
22.00	13.10	8.80	9.20	1379.50
23.00	11.60	7.20	9.80	1357.10
24.00	15.50	5.30	15.50	1117.80
25.00	13.00	5.70	15.80	1270.70
26.00	11.60	5.90	12.80	1405.80
27.00	12.30	5.40	13.30	1237.20
28.00	12.10	4.10	17.90	1230.40

Answer 1