Multiple Regression Model
Suppose a large consumer product company wants to measure the effectiveness of different types of advertising media in the promotion of its products. Specifically, two types of advertising media are to be considered: (1) radio and TV advertising, and (2) newspaper advertising including the cost of discount coupons. A sample of 22 cities with approximately equal populations is selected for study during a test period of 1 month. Each city is allocated a specific expenditure level for both types of advertising. The sales of the product (in thousands of dollars) and also the level of media expenditure during the test month are recorded as follows: (Please view the attachment to see the chart in a clearer format)
City Radio & TV Advertising ($000) Newspaper Advertising ($000) Sales ($000)
1 0 40 973
2 0 40 1,119
3 25 25 875
4 25 25 625
5 30 30 910
6 30 30 971
7 35 35 931
8 35 35 1,177
9 40 25 882
10 40 25 982
11 45 45 1,628
12 45 45 1,577
13 50 0 1,044
14 50 0 914
15 55 25 1,329
16 55 25 1,330
17 60 30 1,405
18 60 30 1,436
19 65 35 1,521
20 65 35 1,741
21 70 40 1,866
22 70 40 1,717
Using Megastat correlation/regression or MS EXCEL regression function under TOOLS menu, Data Analysis:
1. Find the coefficient of multiple correlation (R) between sales and advertising costs. Interpret the result. [hint: First, enter advertisement and sales data in Excel. Highlight the y-cell range. Highlight all x-cell ranges at once].
2. Find the coefficient of multiple determination (R2). Interpret the result.
3. State the multiple regression equation.
4. Interpret the meaning of the slopes in the equation.
5. Predict the average sales for a city in which radio and TV advertising is $20,000 and newspaper advertising is $20,000 [use 20 instead of 20,000 in the equation].
6. If you were Director or Marketing, which method of advertising would you use most - TV & radio advertising or newspaper advertising? Why?
7. Is the data free from auto correlation? [see the value for DW statistic in Excel output]
8. Is the data free from multi-collinearity? [see the values for VIF in Excel output]
Please view the attached Word document for question 3 and the attached Excel document shows all of the calculations pertaining to the regression analysis.
1. Multiple correlation coefficient R =0.899.
2. R^2 =0.809
80.9% variability in sales can be explained by the regression model with radio and TV advertising, and ...
This solution provides the step by step method required for multiple regression analysis for the effectiveness of different types of advertising media in the promotion of its products. Formulas for the calculations and Interpretations of the results are also included. An attached Excel file and attached Word document also accompanies this solution.
Computing Multiple Regression Analysis
Must see attachment for number 2.
1) What are the components of a time series? What external factors might affect each of the different components?
In number 2, I need assistance step by step details in understanding how to work statistics problems in words of interpretation (that helps explain the results and what it represents) and in EXCEL so that I can understand these types of problems in future problems.
**If charts are use please provide the information if possible on Excel Spreadsheet so I can see how it was done but it canâ??t provide the chart anyway and I will try and figure it out.**
2) A sports enthusiast created an equation to predict Victories (the teamâ??s number of victories in the National Basketball Association regular season play) using predictors FGP (team field goal percentage),
FTP (team free throw percentage), Points = (team average points per game), Fouls (team average number of fouls per game), TrnOvr (team average number of turnovers per game), and Rbnds (team average number of rebounds per game).
The fitted regression was Victories=â?'281 + 523 FGP + 3.12 FTP + 0.781 Points â?' 2.90 Fouls + 1.60 TrnOvr + 0.649 Rbnds (R2 = .802, F = 10.80, SE = 6.87). The strongest predictors were FGP (t = 4.35) and Fouls (t=â?'2.146). The other predictors were only marginally significant and FTP and Rbnds were not significant.
The matrix of correlations is shown below. At the time of this analysis, there were 23 NBA teams.
(a) Do the regression coefficients make sense?
(b) Is the intercept meaningful? Explain.
(c) Is the sample size a problem (using Evansâ??s Rule or Doaneâ??s Rule)?
(c) Why might collinearity account for the lack of significance of some predictors?