Question

A Roofing and Siding company sells its products to home repair retailers. The owner is
interested in studying the effects of several variables on the value of shingles sold ($000).
The marketing manageris arguing that the company shpuld spend more money on
advertising, while a market researcher suggests it should focus more on making its brand
and product more distinct from its competitors.
The company has divided the United States into 26 marketing districts. In each district, it collected
information on the following variables:
		Advertising		Number
		dollars	Number of	of	Market
Obs.	Sales	(000's)	Accounts	competitors	Potential
1	79.3	5.5	31	10	8
2	200.1	2.5	55	8	6
3	163.2	8	67	12	9
4	200.1	3	50	7	16
5	146	3	38	8	15
6	177.7	2.9	71	12	17
7	30.9	8	30	12	8
8	291.9	9	56	5	10
9	160	4	42	8	4
10	339.4	6.5	73	5	16
11	159.6	5.5	60	11	7
12	86.3	5	44	12	12
13	237.5	6	50	6	6
14	107.2	5	39	10	4
15	155	3.5	55	10	4
16	291.4	8	70	6	14
17	100.2	6	40	11	6
18	135.8	4	50	11	8
19	223.3	7.5	62	9	13
20	195	7	59	9	11
21	73.4	6.7	53	13	5
22	47.7	6.1	38	13	10
23	140.7	3.6	43	9	17
24	93.5	4.2	26	8	3
25	259	4.5	75	8	19
26	331.2	5.6	71	4	9
	Conduct a multiple regression analysis to find the best predictors of sales
a.	Draw a scatter diagram comparing sales volume with each of the independent variables.
	Comment on the results.
b.	Develop a correlation matrix. Do you see any problems? Does it appear there are any
	redundant independent variables?
c.	Develop a regression equation. Conduct the global test. Can we conclude that some of
	the independent variables are useful in explaining the variation in the dependent variable.
d.	Conduct a test of each of the independent variables. Are there any that should be dropped?
e.	Refine the regression equation so the remaining variablesare all significant.
f.	Determine the variance inflation factor for each of the independent variables. Are there any
	problems?

# Please I need the answer by Excel with showing steps.

Answer 1

1) Conduct a multiple regression analysis to find the best predictors of sales.

using the Data analysis Add-in

Then regression

Now entering the dependent and independent variables.

. Check Labels.

The output is

Except advertising dollars and  Market potential all variable have significant p-value.

Eliminating the advertising dollars and market potential and finding the regression model again.

Repeat all steps again by not selecting the those variable.

The output will be

Here all variable are significant.

The best line will be sale= 186.694 +3.408077 *Accounts -21.193 * Competitors.

b.	Develop a correlation matrix. Do you see any problems? Does it appear there are any
	redundant independent variables?

using the Data analysis Add-in ,selecet correlation

using the Data analysis Add-in

The output is

Since Number of accountants and Number of compititors are strongly correlated to sales they are redundant variables.

c. Draw a scatter diagram comparing sales volume with each of the independent variables.

Select range

Go to insert and select scatter

The scatter plot obtain is as follows

We add trendline for better visulalisation..

We can observe that there is no evidence of correlation between sales and advertising dollars.

Similarly for other variables.

There is significant positive correlation between Numbers of accountant and sales.

There is significantly negative correlation between number of competitors and sales.

There is no significant correlation between market potential and sales.

c.	Develop a regression equation. Conduct the global test. Can we conclude that some of
	the independent variables are useful in explaining the variation in the dependent variable.

From anova table the global test F have p-value < 0.05. Hence the model is significantly explaining the variation between independent variables

The best line will be sale= 186.694 +3.408077 *Accounts -21.193 * Competitors..

Only Number of accountants and Number of compititors are useful in explaining the variation in the dependent variable.

d.	Conduct a test of each of the independent variables. Are there any that should be dropped?

Test for coefficient from anova table shows that

only Number of accountants and Number of compititors have significant p-value.(<0.05).

Yes they should be drop out.

e.	Refine the regression equation so the remaining variablesare all significant.

Sale= 186.694 +3.408077 *Accounts -21.193 * Competitors..

f.	Determine the variance inflation factor for each of the independent variables. Are there any prooblems

The VIF is given as

Calculating descrptive statistics using Data Analysis

The output is

The vif is calculated as

Only advertising dollars have very vif more than 10.

Other variable have vif = 1 = not correlated.