The President of an auto dealership believes there is a relationship between the
ID: 3061184 • Letter: T
Question
The President of an auto dealership believes there is a relationship between the number of new cars sold and the number of sales people on duty. To investigate, he selects a sample of 8 weeks and observes the number of cars sold and the number of sales people on duty for that week as follows:
A.1) Run a regression analysis using the Data Analysis tool in Excel. Compute the proportion of the variation in number of new cars sold that cannot be explained by the variation in number of sales people on duty. Find this proportion using the sample statistics given in the first table of the regression output AND also by using the information given in the ANOVA table of the output. show your calculation in the space below to illustrate that the two values are equivalent. Also, copy/paste both tables from the regression output in the space below.( SHOW PROCESS TO DO IT AS WELL)
Week
Sales Staff
Cars Sold
1
5
53
2
5
47
3
7
48
4
4
50
5
10
58
6
12
62
7
3
45
8
11
60
Week
Sales Staff
Cars Sold
1
5
53
2
5
47
3
7
48
4
4
50
5
10
58
6
12
62
7
3
45
8
11
60
Explanation / Answer
We have to plug given data in Excel
Then click on DATA -- > Data Analysis -- > Select regression
In input Select y data values as Y input , X data values as X input.
Selct label option .
Select output and give any empty cell address.
Then click on OK , this gives you output as follows.
SUMMARY OUTPUT Regression Statistics Multiple R 0.912089 R Square 0.831906 Adjusted R Square 0.803891 Standard Error 2.844822 Observations 8 ANOVA df SS MS F Significance F Regression 1 240.3169 240.3169 29.69438 0.001588 Residual 6 48.55807 8.093012 Total 7 288.875 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 40.74208 2.443165 16.67595 2.97E-06 34.76387 46.72029 34.76387 46.72029 sales staff 1.702866 0.312495 5.449255 0.001588 0.938218 2.467514 0.938218 2.467514Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.