Econ 3406 A Consumer Buying Cooperative Tested The Effective

Statistics for Business IIExcel Assignment 2Due: Friday, July 22, 2016 by 5: 00pm ESTNo Excel Assignment will be accepted after the due day and time. No Exceptions!Emailed assignments will NOT be accepted. INSTRUCTIONS: All questions should be answered using the statistical functions and applications in Microsoft Excel. You mustsubmit your completed assignment by 5: 00pm EST on July 22 via the DropBox tool in Course Den. Youshould work all questions on separate worksheets within the SAME Excel file. Each sheet in the Excel fileshould be renamed to reflect the question you are answering. For “Excel” assignments, only “Excel” files willbe accepted. No exceptions will be made. Final assignment submissions should be clearly labeled and organized. You will not receive credit for answersthat are not clearly labeled. Answers should be labeled using the question number and letter (for example, 1a)and should be in the same order as the questions. Submitted files must contain specific answers to the questionsbeing asked and clear evidence of using Excel statistical commands. If you only submit numbers or answersand there is no evidence of using Excel statistical commands, you will not receive credit, even if your answersare correct. For specific illustrations, read the textbook, consult the statistical commands in Appendix C, andreview the examples in the lecture notes and pdf files. You can also email me with any questions you mayhave. Files should NOT be emailed to me AT ANY POINT. Only specific questions regarding theproblems will be addressed. I will not review an assignment or problem as to check for correctness. Anyrequests to review answers via email will be regarded as your final submission and will be graded assuch. You are to work on this assignment individually. Students who turn in the same assignment will eachreceive a grade of zero. These students will also be reported to the Student Conduct Office for violationof the Academic Honor Code. You will only be allowed to submit ONE file to the Course Den dropbox. You can upload files up until the deadline. Course den will only keep the latest submission. DO NOT EMAIL THIS FILE TO ME. When saving your Microsoft Excel file, name it in the following way: your name_Excel_2 (Example: HeatherBono_Excel_2)Emailed assignments will not be accepted. If you have trouble uploading your completed assignment to theCourseDen dropbox folder, you must notify me in writing no later than 8: 00am on Friday, July 22, 2016failure to do so will result in a grade of zero if the assignment is not turned in to the dropbox folder by thedeadline. Requests to turn in the assignment after the deadline will be denied no matter the reason. You should ensure that your Excel file is saved properly and can be reopened for grading. If you are unsurethat your file has been saved properly, you should notify me no later than 8: 00am on the scheduled duedate. If I cannot open your submitted Excel file for grading, a grade of zero will be given and no resubmissionwill be allowed. Be sure that you have successfully uploaded the file in the dropboxyou want to be graded . Once the due date/time has passed, yourgrade will be based on the assignment submitted to the dropboxfolder. Re-submissions after the deadline will not be allowed for anyreason! NO EXCEPTIONS!Assignment made available on Course Den on June 30, 2016Be sure to read and follow all instructions on the previous page before beginning yourassignment. Failure to follow all instructions will result in point deductions. 1. (20 points) A consumer buying cooperative tested the effective heating area of 20 different electricspace heaters with different wattages. Here are the results. Heater Wattage Area115002052750703150019941250151512501816125021771000948200029891000135101500211111250116125007213500821415002061575024516150021917750631815002001912501512050044a) Compute the correlation between the wattage and heating area. Is there a direct or an indirectrelationship?b) Conduct a test of hypothesis to determine if it is reasonable that the coefficient is greater thanzero. Use the 0. 05 significance level. c) Develop the regression equation for effective heating based on wattage (this must be written outin equation form, not just the Excel output). . d) Which heater looks like the “best buy” based on the size of the residual?*You must use statistical Excel commands where applicable to receive credit and you must show all stepsin a hypothesis test. For examples, see the pdf files posted on course den. 2. (20 points) The owner of Maumee Ford-Mercury-Volvo wants to study the relationship betweenthe age of a car and its selling price. Listed below is a random sample of 12 used cars sold at thedealership during the last year. Car Age (years) Selling Price($000)198. 1276. 03113. 64124. 0585. 06710. 0787. 6Assignment made available on Course Den on June 30, 201689101112111012668. 08. 06. 08. 68. 0a) Draw a scatter diagram. b) Determine the correlation coefficient. c) Interpret the correlation coefficient. Does it surprise you that the correlation coefficient isnegative?d) Estimate the regression equation that best fits the data (this must be written out in equation form,not just the Excel output). . e) What is the estimated selling price of a car that is 7 years old?f) Use the 0. 05 significance level, can we conclude that the slope of the regression line is differentfrom zero?g) Determine the standard error of estimate and the coefficient of determination. Interpret thecoefficient of determination. *You must use statistical Excel commands where applicable to receive credit and you must show all stepsin a hypothesis test. For examples, see the pdf files posted on course den. 3. (10 points) The following sample observations were randomly selected. X: 5 3 6 3 4 4 6 8Y: 13 15 7 12 13 11 9 5a) Determine the regression equation (this must be written out in equation form, not just the Exceloutput). b) Determine the value of ? when x is 7. c) Determine the 95% confidence interval for the mean predicted when x =7. d) Determine the 95% prediction interval for an individual predicted when x =7. *You must use statistical Excel commands where applicable to receive credit and you must show all stepsin a hypothesis test. For examples, see the pdf files posted on course den. 4. (20 points) Mike Wilde is president of the teachers’ union for Otsego School District. In preparingfor upcoming negotiations, he would like to investigate the salary structure of classroom teachers inthe district. He believes there are three factors that affect a teacher’s salary: years of experience, arating of teaching effectiveness given by the principal, and whether the teacher has a master’s degree. A random sample of 20 teachers resulted in the following data. Salary Years Rating Masters31. 1835033. 6543029. 32511431560138. 611730451480142976036. 8754148. 62255131. 7390125. 7130030. 65440Assignment made available on Course Den on June 30, 201651. 846. 738. 433. 641. 830. 732. 842. 8238411776012681142508901462028018720*1= yes, 0=noa) Develop a correlation matrix. Which independent variable has the strongest correlation with thedependent variable? Does it appear there will be any problems with multicollinearity?b) Determine the regression equation (this must be written out in equation form, not just the Exceloutput). What salary would you estimate for a teacher with 5 years’ experience, a rating by theprincipal of 60, and no master’s degree?c) Conduct a global test of hypothesis to determine whether any of the regression coefficients differfrom zero. Use the 0. 05 significance level. d) Conduct a test of hypothesis for the individual regression coefficients. Would you considerdeleting any of the independent variables? Use the 0. 05 significance level. e) If your conclusion in part (d) was to delete one or more independent variables, run the analysisagain without those variables. f) Determine the residuals for the equation of part e. Use a normal probability plot to verify that thedistribution of the residuals is approximately normal. g) Plot the residuals computed in part f in a scatter diagram with the residuals on the Y-axis and the ?values on the X-axis. Does the plot reveal any violations of the assumptions of regression?*You must use statistical Excel commands where applicable to receive credit and you must show all stepsin a hypothesis test. For examples, see the pdf files posted on course den. 5. (10 points) The Conch Café, located in Gulf Shores, Alabama, features casual lunches with a greatview of the Gulf of Mexico. To accommodate the increase in business during the summer vacationseason, Fuzzy Conch, the owner, hires a large number of servers as seasonal help. When heinterviews a prospective server, he would like to provide data on the amount a server can earn in tips. He believes that the amount of the bill and the number of diners are both related to the amount of thetip. He gathered the following sample information. Customer Amount of Tip Amount of Bill Diners1$7. 00$48. 9752$4. 50$28. 2343$1. 00$10. 6514$2. 40$19. 8235$5. 00$28. 6236$4. 25$24. 8327$0. 50$6. 2418$6. 00$49. 2049$5. 00$43. 26310$4. 75$31. 36411$5. 25$32. 87412$6. 00$34. 99313$4. 00$33. 91414$3. 35$23. 062Assignment made available on Course Den on June 30, 201615$0. 75$4. 65116$3. 30$23. 59217$3. 50$22. 30218$3. 25$32. 00219$5. 40$50. 02420$2. 25$17. 60321$5. 50$44. 47422$3. 00$20. 27223$1. 25$19. 53224$3. 25$27. 03325$3. 00$21. 28226$6. 25$43. 38427$5. 60$28. 12428$2. 50$26. 25229$9. 25$56. 81530$8. 25$50. 655a) Develop a multiple regression equation with the amount of tips as the dependent variable and theamount of the bill and the number of diners as independent variables. Write out the regressionequation (this must be written out in equation form, not just the Excel output). b) Interpret the coefficients in the regression equation. c) Develop a regression equation including an interaction term (this must be written out in equationform, not just the Excel output). Is there a significant interaction between the amount of the billand the number of diners? You must show your creation of the interaction variable and explainthe meaning of the interaction. *You must use statistical Excel commands where applicable to receive credit and you must show all stepsin a hypothesis test. For examples, see the pdf files posted on course den. 6. (10 points) Four brands of lightbulbs are being considered for use in the final assembly area of theFord F-150 truck plant in Dearborn, Michigan. The director of purchasing asked for samples of 100from each manufacturer. The numbers of acceptable and unacceptable bulbs from each manufacturerare shown below. At the 0. 05 significance level, is there a difference in the quality of the bulbs?ManufacturerABCDUnacceptable128511Acceptable88929589Total100 100 100 100*You must use statistical Excel commands where applicable to receive credit and you must show all stepsin a hypothesis test. For examples, see the pdf files posted on course den. 7. (10 points) Research in the gaming industry showed that 10% of all slot machines in the UnitedStates stop working each year. Short’s Game Arcade has 60 slot machines and only 3 failed last year. At the 0. 05 significance level, test whether these data contradict the research report. a) Why can you employ z as the test statistic?b) State the null and alternate hypothesis?c) Evaluate the test statistic and make a decisiond) What is the p-value and what does that imply?*You must use statistical Excel commands where applicable to receive credit and you must show all stepsin a hypothesis test. For examples, see the pdf files posted on course den. Assignment made available on Course Den on June 30, 2016

ECON 3406 A consumer buying cooperative tested the effective

Question
Statistics for Business II

Excel Assignment 2

Due: Friday, July 22, 2016 by 5:00pm EST
No Excel Assignment will be accepted after the due day and time. No Exceptions!
Emailed assignments will NOT be accepted.
INSTRUCTIONS:
All questions should be answered using the statistical functions and applications in Microsoft Excel. You must
submit your completed assignment by 5:00pm EST on July 22 via the DropBox tool in Course Den. You
should work all questions on separate worksheets within the SAME Excel file. Each sheet in the Excel file
should be renamed to reflect the question you are answering. For “Excel” assignments, only “Excel” files will
be accepted. No exceptions will be made.
Final assignment submissions should be clearly labeled and organized. You will not receive credit for answers
that are not clearly labeled. Answers should be labeled using the question number and letter (for example, 1a)
and should be in the same order as the questions. Submitted files must contain specific answers to the questions
being asked and clear evidence of using Excel statistical commands. If you only submit numbers or answers
and there is no evidence of using Excel statistical commands, you will not receive credit, even if your answers
are correct. For specific illustrations, read the textbook, consult the statistical commands in Appendix C, and
review the examples in the lecture notes and pdf files. You can also email me with any questions you may
have. Files should NOT be emailed to me AT ANY POINT. Only specific questions regarding the
problems will be addressed. I will not review an assignment or problem as to check for correctness. Any
requests to review answers via email will be regarded as your final submission and will be graded as
such.
You are to work on this assignment individually. Students who turn in the same assignment will each
receive a grade of zero. These students will also be reported to the Student Conduct Office for violation
of the Academic Honor Code. You will only be allowed to submit ONE file to the Course Den dropbox.
You can upload files up until the deadline. Course den will only keep the latest submission.
DO NOT EMAIL THIS FILE TO ME.
When saving your Microsoft Excel file, name it in the following way:
your name_Excel_2 (Example: HeatherBono_Excel_2)
Emailed assignments will not be accepted. If you have trouble uploading your completed assignment to the
CourseDen dropbox folder, you must notify me in writing no later than 8:00am on Friday, July 22, 2016
failure to do so will result in a grade of zero if the assignment is not turned in to the dropbox folder by the
deadline. Requests to turn in the assignment after the deadline will be denied no matter the reason.
You should ensure that your Excel file is saved properly and can be reopened for grading. If you are unsure
that your file has been saved properly, you should notify me no later than 8:00am on the scheduled due
date. If I cannot open your submitted Excel file for grading, a grade of zero will be given and no resubmission
will be allowed.

Be sure that you have successfully uploaded the file in the dropbox
you want to be graded . Once the due date/time has passed, your
grade will be based on the assignment submitted to the dropbox
folder. Re-submissions after the deadline will not be allowed for any
reason! NO EXCEPTIONS!
Assignment made available on Course Den on June 30, 2016

Be sure to read and follow all instructions on the previous page before beginning your
assignment. Failure to follow all instructions will result in point deductions.
1. (20 points) A consumer buying cooperative tested the effective heating area of 20 different electric
space heaters with different wattages. Here are the results.
Heater Wattage Area
1
1500
205
2
750
70
3
1500
199
4
1250
151
5
1250
181
6
1250
217
7
1000
94
8
2000
298
9
1000
135
10
1500
211
11
1250
116
12
500
72
13
500
82
14
1500
206
15
750
245
16
1500
219
17
750
63
18
1500
200
19
1250
151
20
500
44
a) Compute the correlation between the wattage and heating area. Is there a direct or an indirect
relationship?
b) Conduct a test of hypothesis to determine if it is reasonable that the coefficient is greater than
zero. Use the 0.05 significance level.
c) Develop the regression equation for effective heating based on wattage (this must be written out
in equation form, not just the Excel output). .
d) Which heater looks like the “best buy” based on the size of the residual?
*You must use statistical Excel commands where applicable to receive credit and you must show all steps
in a hypothesis test. For examples, see the pdf files posted on course den.
2. (20 points) The owner of Maumee Ford-Mercury-Volvo wants to study the relationship between
the age of a car and its selling price. Listed below is a random sample of 12 used cars sold at the
dealership during the last year.
Car Age (years) Selling Price($000)
1
9
8.1
2
7
6.0
3
11
3.6
4
12
4.0
5
8
5.0
6
7
10.0
7
8
7.6
Assignment made available on Course Den on June 30, 2016

8
9
10
11
12

11
10
12
6
6

8.0
8.0
6.0
8.6
8.0

a) Draw a scatter diagram.
b) Determine the correlation coefficient.
c) Interpret the correlation coefficient. Does it surprise you that the correlation coefficient is
negative?
d) Estimate the regression equation that best fits the data (this must be written out in equation form,
not just the Excel output). .
e) What is the estimated selling price of a car that is 7 years old?
f) Use the 0.05 significance level, can we conclude that the slope of the regression line is different
from zero?
g) Determine the standard error of estimate and the coefficient of determination. Interpret the
coefficient of determination.
*You must use statistical Excel commands where applicable to receive credit and you must show all steps
in a hypothesis test. For examples, see the pdf files posted on course den.
3. (10 points) The following sample observations were randomly selected.
X:
5 3 6 3 4 4 6 8
Y: 13 15 7 12 13 11 9 5
a) Determine the regression equation (this must be written out in equation form, not just the Excel
output).
b) Determine the value of ? when x is 7.
c) Determine the 95% confidence interval for the mean predicted when x =7.
d) Determine the 95% prediction interval for an individual predicted when x =7.
*You must use statistical Excel commands where applicable to receive credit and you must show all steps
in a hypothesis test. For examples, see the pdf files posted on course den.
4. (20 points) Mike Wilde is president of the teachers’ union for Otsego School District. In preparing
for upcoming negotiations, he would like to investigate the salary structure of classroom teachers in
the district. He believes there are three factors that affect a teacher’s salary: years of experience, a
rating of teaching effectiveness given by the principal, and whether the teacher has a master’s degree.
A random sample of 20 teachers resulted in the following data.
Salary Years Rating Masters
31.1
8
35
0
33.6
5
43
0
29.3
2
51
1
43
15
60
1
38.6
11
73
0
45
14
80
1
42
9
76
0
36.8
7
54
1
48.6
22
55
1
31.7
3
90
1
25.7
1
30
0
30.6
5
44
0
Assignment made available on Course Den on June 30, 2016

51.8
46.7
38.4
33.6
41.8
30.7
32.8
42.8

23
84
1
17
76
0
12
68
1
14
25
0
8
90
1
4
62
0
2
80
1
8
72
0
*1= yes, 0=no
a) Develop a correlation matrix. Which independent variable has the strongest correlation with the
dependent variable? Does it appear there will be any problems with multicollinearity?
b) Determine the regression equation (this must be written out in equation form, not just the Excel
output). What salary would you estimate for a teacher with 5 years’ experience, a rating by the
principal of 60, and no master’s degree?
c) Conduct a global test of hypothesis to determine whether any of the regression coefficients differ
from zero. Use the 0.05 significance level.
d) Conduct a test of hypothesis for the individual regression coefficients. Would you consider
deleting any of the independent variables? Use the 0.05 significance level.
e) If your conclusion in part (d) was to delete one or more independent variables, run the analysis
again without those variables.
f) Determine the residuals for the equation of part e. Use a normal probability plot to verify that the
distribution of the residuals is approximately normal.
g) Plot the residuals computed in part f in a scatter diagram with the residuals on the Y-axis and the ?
values on the X-axis. Does the plot reveal any violations of the assumptions of regression?
*You must use statistical Excel commands where applicable to receive credit and you must show all steps
in a hypothesis test. For examples, see the pdf files posted on course den.
5. (10 points) The Conch Café, located in Gulf Shores, Alabama, features casual lunches with a great
view of the Gulf of Mexico. To accommodate the increase in business during the summer vacation
season, Fuzzy Conch, the owner, hires a large number of servers as seasonal help. When he
interviews a prospective server, he would like to provide data on the amount a server can earn in tips.
He believes that the amount of the bill and the number of diners are both related to the amount of the
tip. He gathered the following sample information.
Customer Amount of Tip Amount of Bill Diners
1
$
7.00
$
48.97
5
2
$
4.50
$
28.23
4
3
$
1.00
$
10.65
1
4
$
2.40
$
19.82
3
5
$
5.00
$
28.62
3
6
$
4.25
$
24.83
2
7
$
0.50
$
6.24
1
8
$
6.00
$
49.20
4
9
$
5.00
$
43.26
3
10
$
4.75
$
31.36
4
11
$
5.25
$
32.87
4
12
$
6.00
$
34.99
3
13
$
4.00
$
33.91
4
14
$
3.35
$
23.06
2
Assignment made available on Course Den on June 30, 2016

15
$
0.75
$
4.65
1
16
$
3.30
$
23.59
2
17
$
3.50
$
22.30
2
18
$
3.25
$
32.00
2
19
$
5.40
$
50.02
4
20
$
2.25
$
17.60
3
21
$
5.50
$
44.47
4
22
$
3.00
$
20.27
2
23
$
1.25
$
19.53
2
24
$
3.25
$
27.03
3
25
$
3.00
$
21.28
2
26
$
6.25
$
43.38
4
27
$
5.60
$
28.12
4
28
$
2.50
$
26.25
2
29
$
9.25
$
56.81
5
30
$
8.25
$
50.65
5
a) Develop a multiple regression equation with the amount of tips as the dependent variable and the
amount of the bill and the number of diners as independent variables. Write out the regression
equation (this must be written out in equation form, not just the Excel output).
b) Interpret the coefficients in the regression equation.
c) Develop a regression equation including an interaction term (this must be written out in equation
form, not just the Excel output). Is there a significant interaction between the amount of the bill
and the number of diners? You must show your creation of the interaction variable and explain
the meaning of the interaction.
*You must use statistical Excel commands where applicable to receive credit and you must show all steps
in a hypothesis test. For examples, see the pdf files posted on course den.
6. (10 points) Four brands of lightbulbs are being considered for use in the final assembly area of the
Ford F-150 truck plant in Dearborn, Michigan. The director of purchasing asked for samples of 100
from each manufacturer. The numbers of acceptable and unacceptable bulbs from each manufacturer
are shown below. At the 0.05 significance level, is there a difference in the quality of the bulbs?
Manufacturer
A
B
C
D
Unacceptable
12
8
5
11
Acceptable
88
92
95
89
Total
100 100 100 100
*You must use statistical Excel commands where applicable to receive credit and you must show all steps
in a hypothesis test. For examples, see the pdf files posted on course den.
7. (10 points) Research in the gaming industry showed that 10% of all slot machines in the United
States stop working each year. Short’s Game Arcade has 60 slot machines and only 3 failed last year.
At the 0.05 significance level, test whether these data contradict the research report.
a) Why can you employ z as the test statistic?
b) State the null and alternate hypothesis?
c) Evaluate the test statistic and make a decision
d) What is the p-value and what does that imply?
*You must use statistical Excel commands where applicable to receive credit and you must show all steps
in a hypothesis test. For examples, see the pdf files posted on course den.
Assignment made available on Course Den on June 30, 2016

Order a Unique Copy of this Paper