Question

Hi Experts Working on a assignment and stuck at it. Can someone help me with the solution. Also find attached question and Excel data. Please do provide excel sheet data attached with the solution. Find the question in bold font.

"A company wants to estimate how effective different types of advertising are for promoting its products. Specifically, the company is interested in estimating the effectiveness of radio advertising and newspaper advertising. A sample of 50 cities is selected for the study during a test period of one month. The populations of the selected cities are approximately the same. Each city is allocated a specific expenditure level for radio advertising and for newspaper advertising. Sales of the product (in thousands of dollars) and the levels of expenditure (in thousands of dollars) on both types of advertising are recorded during the test month. The collected data are given in the attached Excel spreadsheet ads1.xls."

Sales	Radio	Newspaper
2433	0	60
2798	0	60
2188	37.5	37.5
1563	37.5	37.5
2275	45	45
2428	45	45
2328	52.5	52.5
2943	52.5	52.5
2205	60	37.5
2455	60	37.5
4070	67.5	67.5
3943	67.5	67.5
2610	75	0
2285	75	0
3323	82.5	37.5
3325	82.5	37.5
3513	90	45
3590	90	45
3803	97.5	52.5
4353	97.5	52.5
4665	105	60
4293	105	60
1563	37.5	37.5
2275	45	45
2428	45	45
2328	52.5	52.5
2943	52.5	52.5
2205	60	37.5
2455	60	37.5
4070	67.5	67.5
3943	67.5	67.5
2610	75	0
2285	75	0
3323	82.5	37.5
3325	82.5	37.5
3513	90	45
3590	90	45
2610	75	0
2285	75	0
3323	82.5	37.5
3325	82.5	37.5
3513	90	45
3590	90	45
3803	97.5	52.5
4353	97.5	52.5
4665	105	60
4293	105	60
1563	37.5	37.5
2275	45	45
2428	45	45

a.            Specify a suitable multiple regression equation for predicting sales.

b.            Use Excel to estimate the coefficients of the regression equation.

c.             Are the estimated slope coefficients statistically significant? Explain.

d.            Interpret the estimated slope coefficients.

e.            Predict the sales for a city in which radio advertising is $60,000 and newspaper advertising is $60,000.

Answer 1

a) A suitable multiple regression for predicting the sales will be-

• Our Population regression model will be-

Sales = β₁ + β₂ Radio+ β₃ Newspaper + u

• We will estimate the regression line

Sales = β₁ + β₂ Radio+ β₃ Newspaper

b)

For this, we have used the data analysis in Microsoft excel-

The results of this regression have been attached to the image

Sales = 251.09 + 25.38 Radio+ 24.83 Newspaper

c) Are the estimated slope coefficients statistically significant? Explain.

Yes, The estimated slope coefficients will be statistically significant.

• We can see that the t-statistic value is much greater than 1.96 which is the critical value of the t-test at a 95% confidence interval. t- statistic value of the coefficient of Radio is 12.66 and the t- statistic value of the coefficient of the newspaper is 9.04, both of which are much above the critical value.

Hence, we reject our null hypothesis that our estimated slope coefficients are statistically insignificant.

• The p-value is also very close to 0, hence we can say that both the coefficients of radio as well as newspapers are statistically significant.
• We also checked for the overall significance of the regression model and via the f-test, we got a similar result that all the regressors are individually as well as jointly significant.

d)  Interpretation of the estimated slope coefficients-

• Holding the newspaper advertisement expenditure fixed, a thousand dollar increase in the radio advertising expenditure would lead to a $25,385 increase in the sales of the product.
• Holding the radio advertisement expenditure fixed, a thousand dollar increase in the newspaper advertising expenditure would lead to a $24,830 increase in the sales of the product.

e) Predict the sales for a city in which radio advertising is $60,000 and newspaper advertising is $60,000-

Our estimated regression equation is-

Sales = 251.09 + 25.38 Radio+ 24.83 Newspaper

Now as given in the question we will put the value of both radio advertisement expenditure as well as newspaper advertisement expenditure as 60 (in thousand dollars) in our estimated regression equation-

Sales = 251.09 + 25.38 * (60) + 24.83 * (60) = $ 3263.69 (in thousand dollars)