Question

please use excel and provide the formulas

The Times-Observer is a daily newspaper in Metro City. Like many city newspapers, the TimesObserver is suffering through difficult financial times. The circulation manager is studying other papers in similar cities in the United States and Canada. She is particularly interested in what variables relate to the number of subscriptions to the paper. She is able to obtain the following sample information on 25 newspapers in similar cities. Ignore the data in the textbook problem. Instead, use the data in the Exam 2 template. Using Excel, run a multiple regression analysis (ensure level of significance = 0.05). Ignore the textbook questions in the problem. Instead, respond to the following: a. What is the dependent variable? b. What are the independent variables? c. Create a scatter diagram for each independent variable acting on the dependent variable. Comment. d. What is the Null Hypothesis for the overall model? e. What is the Alternative Hypothesis for the overall model? f. What is the multiple regression equation? g. What is the standard error? Describe what this means as related to the problem. h. For your initial model equation, discuss whether R2 or Adjusted R2 should be used in interpreting how good the linear equation is. Then, interpret the appropriate R2 value. i. What does the overall statistical significance explain with respect to the model? j. Evaluate the p-value for each independent variable. Should any of the independent variables be dropped? Why or why not? k. Develop a correlation matrix for the independent variables. Should any of the independent variables be dropped? Why or why not? l. Is this a good model?

Subscriptions	Population	Adv	Income
38.95	588.90	13.06	33.51
37.66	585.30	13.24	33.63
38.55	566.30	13.43	33.68
38.78	642.90	13.61	33.75
38.67	624.20	13.99	33.86
38.23	603.90	14.18	33.90
37.90	571.90	14.86	34.05
38.28	584.30	17.64	34.11
38.95	605.00	17.72	34.17
40.27	676.30	17.73	34.21
39.30	587.40	18.20	34.25
38.84	576.40	18.53	34.27
38.14	570.80	19.82	34.45
39.39	586.50	20.24	34.66
38.29	544.00	20.94	34.72
39.15	611.10	21.38	34.84
38.29	643.30	21.61	34.88
38.09	635.60	21.68	35.20
37.83	598.90	21.70	35.26
39.37	657.00	22.35	35.44
37.81	595.20	23.41	35.72
38.42	520.00	23.69	35.76
38.83	629.60	23.81	35.81
39.33	680.00	25.39	35.85
41.24	651.20	25.92	35.87

Answer 1

using Excel

data -> data analysis -> regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.6142
R Square	0.3773
Adjusted R Square	0.2883
Standard Error	0.6827
Observations	25
ANOVA
	df	SS	MS	F	Significance F
Regression	3	5.9290	1.9763	4.2409	0.0172
Residual	21	9.7864	0.4660
Total	24	15.7155
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	66.6594	21.1794	3.1474	0.0049	22.6144	110.7044
Population	0.0102	0.0036	2.8084	0.0105	0.0026	0.0177
Adv	0.2432	0.1312	1.8532	0.0780	-0.0297	0.5162
Income	-1.1182	0.6837	-1.6356	0.1168	-2.5399	0.3035

a)

a)
dependent variable is subsriptions
b)
independent variables are Population, Advertisement and Income
c)

plot scatterplot yourself
d)

e)

Ha: at least one slope is different from 0

f)
y^ = 66.6594 + 0.0102 Population + 0.2432 Adv -1.1182* Income

g)
standard error = 0.6827

h)
adjusted R^2 should be use as this is multiple linear regression
adjusted R^2 = 0.2883