Question

2.         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.

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

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.) (10 points)

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. (10 points)

Answer 1

2b) Here we use excel to find the multiple linear regression. to do so we follow the following steps-

1. Write the data in excel sheet in 5 different columns.

2. Select Data from the bar then Data Analysis.

3. From the dropdown menu select regression.

4. In input y range select the sales cells and in input x range select the month,price,advertising and interest rate celss.

5. Click OK.

After running the following steps we get the output given below.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.824173
R Square	0.679261
Adjusted R Square	0.62348
Standard Error	42.64793
Observations	28
ANOVA
	df	SS	MS	F	Significance F
Regression	4	88594.85	22148.71	12.17735	1.84E-05
Residual	23	41833.45	1818.846
Total	27	130428.3
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	1836.453	108.1657	16.97816	1.66E-14	1612.696	2060.211	1612.696	2060.211
X Variable 1	-0.83998	1.043933	-0.80463	0.42927	-2.99952	1.319557	-2.99952	1.319557
X Variable 2	-33.3873	7.125536	-4.68559	0.000102	-48.1276	-18.647	-48.1276	-18.647
X Variable 3	12.96641	5.300818	2.446114	0.022506	2.00083	23.93198	2.00083	23.93198
X Variable 4	-14.1274	2.941995	-4.80198	7.63E-05	-20.2134	-8.04142	-20.2134	-8.04142

2c) Here, from the regression result that we obtained in part 2b we now determine if each of the explanatory variable used in the regression is statistically significant at a 5% level. We now calculate the degrees of freedom = N-k-1 were, N is the number of observations and k is the number of independent variables. So here df=28-4-1=23.

We find the value of the critical t from the t-distribution table , t0.025,23 = 2.069

Now if the critical value is the value of t, such that if the t statistic for your independent variable is greater than the critical t or less than (-1) times the critical t,that is |T|>t, then you reject the null hypothesis,were T is the observed value of the statistic.

Here, t statistic for month is -0.83998 so |T|< critical t hence we accept the null hypothesis, that is including month in regression equation is not significant.

Here, t statistic for price is -33.3783 so |T|> critical t hence we reject the null hypothesis, that is including price in regression equation is significant.

Here, t statistic for advertising is 12.96641 so |T|> critical t hence we reject the null hypothesis, that is including advertising in regression equation is significant.

Here, t statistic for interest rate is -14.1274 so |T|> critical t hence we reject the null hypothesis, that is including interest rate in regression equation is significant.