The President of an auto dealership believes there is a relationship between the
ID: 3060297 • 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. You must 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)
A.2)Compute a 95% interval for mean car sales for weeks when 9 sales people are on duty.
A.3)In checking the work schedules for next week, the President finds that there are 9 sales people scheduled to be on duty. Develop a 95% interval for the number of cars sold next week.
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
Here we have given that, number of car sold(Y) is dependent variable and number of on duty sales staff(X) is independent and explanatory variable. Here we regress number of card sold on number of sales staff using excel. We have a given data is as follows,
1) Now by using Excel Data analysis tool pack, we regress Y on X and we get output is as follows,
Here R-square is 0.80. Hence we can say that, proportion of variation not explained by number of sales staff about number of car sold is 0.20. And regression equation is,
Number of Car Sold = 40.74 + 1.70*Number of Sales Staff.
2) Now we predict number of car sold when number of sales staff is 9 i.e. compute Y when X=9.
therefore, Number of Car Sold(Y) = 40.74 + 1.70*9
= 56
Hence 56 cars will be sold per week if president of auto dealership take 9 on duty sales staff.
Week Sales Staff (x) Cars Sold (y) 1 5 53 2 5 47 3 7 48 4 4 50 5 10 58 6 12 62 7 3 45 8 11 60Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.