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

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