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 ads2.xls (see the side menu bar of this PDF).
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.
a. To specify a suitable multiple regression equation for predicting sales, we can use the following equation:
Sales = β0 + β1(Radio Advertising) + β2(Newspaper Advertising) + ε
Where:
- Sales represents the dependent variable, which is the amount of sales (in thousands of dollars).
- Radio Advertising and Newspaper Advertising represent the independent variables, which are the expenditures on radio advertising and newspaper advertising, respectively.
- β0 is the intercept, which represents the expected sales when both types of advertising expenditures are zero.
- β1 and β2 are the slopes, which represent the expected change in sales for a one-unit increase in radio advertising and newspaper advertising, respectively.
- ε represents the residual term, which accounts for the unexplained variability in sales.
b. To estimate the coefficients of the regression equation using Excel, you can follow these steps:
1. Open the Excel spreadsheet (ads2.xls) that contains the data.
2. Select an empty cell where you want the regression output to appear.
3. Go to the "Data" tab and click on "Data Analysis" in the "Analysis" group.
4. In the "Data Analysis" dialog box, select "Regression" and click "OK".
5. In the "Regression" dialog box, enter the range of the dependent variable (Sales) and the range of the independent variables (Radio Advertising and Newspaper Advertising).
6. Check the box for "Labels" if your data contains column headers.
7. Under "Output options," select where you want the regression output to be displayed.
8. Click on "OK" to run the regression analysis. Excel will generate the regression output with the estimated coefficients.
c. To determine if the estimated slope coefficients are statistically significant, we need to look at the p-values associated with each coefficient. In the regression output generated by Excel, the p-values can be found under the "P-value" column.
If the p-value is less than the chosen significance level (e.g., 0.05), we can conclude that the coefficient is statistically significant. This means that there is evidence to suggest that there is a relationship between that independent variable and the dependent variable.
d. The estimated slope coefficients represent the expected change in sales associated with a one-unit increase in the corresponding independent variable, while holding other variables constant.
For example, if the estimated slope coefficient for Radio Advertising (β1) is 0.7, it means that, on average, an increase of one unit in radio advertising expenditure is associated with an expected increase of 0.7 units in sales, assuming all other factors remain the same. Similarly, if the estimated slope coefficient for Newspaper Advertising (β2) is 0.4, it means that an increase of one unit in newspaper advertising expenditure is associated with an expected increase of 0.4 units in sales, while everything else is held constant.
e. To predict the sales for a city in which radio advertising is $60,000 and newspaper advertising is $60,000, we need to substitute these values into the regression equation. Using the equation from part a:
Sales = β0 + β1(60) + β2(60) + ε
To obtain the prediction, plug in the estimated coefficients (from part b) and calculate the result.