Question

You collected data from 7 Travel Agencies in order to determine if estimates of sales improved by incorporating advertising in the regression model.

a. Copy the data below and paste it in a Data Table in excel.

Travel Agency	Advertising	Sales
1	20000	150000
2	25000	180000
3	45000	220000
4	40000	210000
5	55000	300000
6	60000	350000
7	80000	400000

b. Write the simple regression equation that you are testing (hypothesis) for each travel agency (that incorporates a variable for its advertising).

c. Run an excel regression that is consistent with your hypothesized sales equation above and show the regression output in your excel spreadsheet.

d. Using the regression output, write the estimated equation for travel agency sales corresponding to the data that you used.

e. Do you believe that, overall, Advertising helps explain Sales for the 7 travel agencies? (Why?)

f. Copy and paste the table below on your excel spreadsheet. Using your estimated equation in part d (above), show the regression estimates of $Sales for each travel agency (1, 2, 3 … 7). (Put those sales estimates in the Table below (column 4).)

Travel Agency (1)	Advertising ($)    (2)	Actual Sales (3)	Regression Predicted Sales (with Advertising) (4)	Predicted Sales (under Null) (5)
1	20000	150000
2	25000	180000
3	45000	220000
4	40000	210000
5	55000	300000
6	60000	350000
7	80000	400000

g. Recall that under the null hypothesis, the estimate of $Sales for each travel agency (1, 2, 3,….7) is the simple average of past sales for all travel agencies. Put that estimate “under the null” in column (5) of your excel Table.

h. Using the last 3 columns of the above table, which model appears to provide better estimates of the past: the null (without advertising); or the regression with advertising? WHY?

i. Which of the two estimating equations (null or the alternative) do you believe should be used to make future predictions? WHY?

Answer 1

A, B).

The following table shows the data set.

Here we want to predict “Sales” depending “Advertising”, => “Sales” is the dependent variable and “Advertising” is the explanatory variable, => the required regression equation is given by.

=> Sales = b0 + b1*Advertising + e.

C).

The following table shows the “regression result”.

D).

Here the estimated regression equation is given by.

=> Sales = 56,312.1547 + 4.3564*Advertising. So, here the intercept term is “56,312.15”, => if “travel Agency” decide” not to spend any money on advertising then the “mean sales” is given by “56,312.15”. Now, if

“travel Agency” decide” to spend “$1” more on advertising then the “mean sales” will increase by “$4.36” .

E).

Here the “p-value” for “Advertising” is “0.00024”, => “Advertising” is significant at “5%” as well as “1%” level of significance. Now, the “coefficient of determination” is given by “R^2=0.9458”, => “Advertising” is able to explain “94.58%” variation in “Sales” which is quite high, => given the data “Advertising” really able to explain “Sales”.