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