Prescott College General Biology Grades EXAM 1 EXAM 2 73 93 89 96 73 53 69 47 87 79 69 70 93 79 70 93 78 81 88 78 82 86 78 76 96 EXAM 3 80 88 91 98 66 46 74 56 79 70 70 65 95 80 73 89 75 90 92 83 86 82 83 83 93 75 93 90 100 70 55 77 60 90 88 73 74 91 73 78 96 68 93 86 77 90 89 85 71 95 FINAL 152 185 180 196 142 101 149 115 175 164 141 141 184 152 148 192 147 183 177 159 177 175 175 149 192 ` Qnt. 5040 - Mini Report Regressions: The Prescott College General Biology Regression Case Study, 2015 Maximum Points: 10 Excel File Needed: Prescott College General Biology Regression Data 2015 Introduction to the Case Study Nan McPherson, an assistant professor of biology at Prescott College (PC), has been wondering if she could predict the scores on the final exam for her students in a General Biology Class (Bio. 457) and then also predict the student's final course grade. She has recorded the exam and final grades for 25 students who took her General Biology class last semester and wants your help in predicting the final exam scores and the final grades for 5 \"typical\" PC students. The \"typical\" PC students are as follows: Table 1. Typical General Biology Students at PC Student's Name Mary D. Mike A. Ginny V. Isaac G. Stan L. Exam 1 Exam 2 Exam 3 (Max. 100 pts.) (Max. 100 pts.) (Max. 100 pts.) 82 89 76 60 90 86 84 79 70 91 Predicted Final Exam Score (Max. 200 pts.) Predicted Total Course Points (Max. 500 pts.) Predicted Course Final Grade (A to F) 90 87 83 76 99 The professor knows that there are a maximum of 100 points for each of the three unit exams and a maximum of 200 points for the final exam. Based on the college's grading scale, the final grade in this course will be determined by the following total point value ranges. Copyright R&A 2015 Page 1 Table 2. Prescott College Uniform Grading Scheme for General Biology Classes (Max. of 500 points) Total Max. Points: 500 Final Grade A AB+ B BC+ C CD+ D DF Percentiles 94 to 100 89 to 93 85 to 88 82 to 84 78 to 81 74 to 77 70 to 73 66 to 69 61 to 65 57 to 60 52 to 56 51 and below Minimum points 470 445 425 410 390 370 350 330 305 285 260 0 Maximum points 500 469 444 424 409 389 369 349 329 304 284 259 As an example, if student A earns 75, 87, and 90 points on the three unit exams and an additional 168 points on the final exam, their total points would be 420 points and they would earn a B in the class (their total fell into the range of 410 to 424 or the B range from this table above). Your Tasks You will help professor McPherson by doing the following tasks and analyzing the results for each task1. 1. Complete a one variable summary for all 25 students over the three unit exams and the final exam. a. What do these statistics tell you about the group of students? Be specific and look at the range, standard deviation, the IQR, the mean versus the median and other statistics from this output. 2. Complete a single histogram on all four of these test scored and analyze the results. a. What do these histograms tell you about the overall grades for each of these exams? 3. Complete 4 Q-Q plots for these exams. 1 NOTE: Only work done using StatTools will be accepted for this report. Copyright R&A 2015 Page 2 a. What do these plots tell you about the data? Is it relatively normal or not and why? 4. Complete a single box and whisker plot with all four of the exams on it. a. First explain what a box and whisker plot is, and how to understand it. b. What do these box and whisker plots tell you about the individual exams and how do they compare to each other? 5. Complete a multiple regression using the final exam as the dependent variable, and the unit exams as the independent variables. a. Do any of the variables exceed the alpha for this test? If so, remove that/those variables and rerun the regression. b. What does the r-squared value tell you about this data? c. What does the standard error of the estimate tell you about the data? d. According to the f-ratio, is this regression significant or not and why? e. Using the coefficients generate predict the final exam points, the final course total points and the final grade for the five \"typical\" students found in Table 1 above. (You are to complete the three columns found on the right in yellow and include this table in your mini report.) 6. What general things can you tell professor McPherson about the types of students who will either earn a good grades (B to A) or a poor grades (F to D) based on the first three unit exams? Be specific. (Yes, you should be able to figure this out, look at the results for both the original data and for the 5 students, that will tell you how to determine who might make good or poor grades.) Remember to develop your mini report as shown in the chats for this assignment. All work is to be 1 & spaces, inch margins, with all tables, graphs and figures having a number and a title and explain in the report. Include an embedded Excel file showing your work, but note this file will not be graded and is only used to demonstrate that you did the actual work for this case study. ### Copyright R&A 2015 Page 3 EXECUTIVE SUMMARY (200 WORDS) BACKGROUND (250) Table 1. Prescott College Uniform Grading Scheme for General Biology Classes (Max. of 500 points) Total Max. Points: 500 Final Grade Percentiles A AB+ B BC+ C CD+ D DF 94 to 100 89 to 93 85 to 88 82 to 84 78 to 81 74 to 77 70 to 73 66 to 69 61 to 65 57 to 60 52 to 56 51 and below Minimum points 470 445 425 410 390 370 350 330 305 285 260 0 Maximum points 500 469 444 424 409 389 369 349 329 304 284 259 Problem Here the main problem is to test the assumptions carefully and analyze the data accordingly by modifying the data accordingly or as is required. After carefully analyzing and studying the data we have to conduct the final regression analysis and then interpret t according to the required problem. Analysis One variable summary is : EXAM 1 One Variable Summary Mean Variance Std. Dev. Skewness Kurtosis Median Mean Abs. Dev. Mode Minimum Maximum Range Count Sum 1st Quartile 3rd Quartile Interquartile Range 1.00% 2.50% 5.00% 10.00% 20.00% 80.00% 90.00% 95.00% 97.50% 99.00% EXAM 2 EXAM 3 FINAL exam grades exam grades exam grades exam grades 79.04 150.79 12.28 -0.8321 3.9451 79.00 9.16 78.00 47.00 96.00 49.00 25 1976.00 73.00 88.00 15.00 47.00 47.00 53.00 69.00 70.00 89.00 93.00 96.00 96.00 96.00 79.48 156.34 12.50 -0.9051 3.8241 82.00 9.66 83.00 46.00 98.00 52.00 25 1987.00 73.00 89.00 16.00 46.00 46.00 56.00 65.00 70.00 90.00 93.00 95.00 98.00 98.00 81.48 138.43 11.77 -0.4790 2.4998 85.00 10.14 90.00 55.00 100.00 45.00 25 2037.00 73.00 90.00 17.00 55.00 55.00 60.00 68.00 71.00 91.00 95.00 96.00 100.00 100.00 162.04 578.12 24.04 -0.7395 3.3378 164.00 19.88 175.00 101.00 196.00 95.00 25 4051.00 148.00 180.00 32.00 101.00 101.00 115.00 141.00 142.00 183.00 192.00 192.00 196.00 196.00 The single histogram of all the four scores is Histogram of EXAM 1 / exam grades 9 8 7 6 5 4 Number of Students 3 2 1 0 Grade Percentiles Histogram of EXAM 2 / exam grades 8 7 6 5 4 3 Frequency 2 1 0 Histogram of EXAM 3 / exam grades 8 7 6 5 Frequency 4 3 2 1 0 58.75 66.25 73.75 81.25 Histogram of FINAL / exam grades 7 6 5 4 3 Frequency 2 1 0 88.75 96.25 The Q-Q plots are: Q_Q plot of EXAM 1 120 100 80 60 EXAM 1 scores 40 20 -2.5 -2 -1.5 -1 -0.5 0 0 0.5 1 1.5 2 2.5 1 1.5 2 2.5 Z-score Q_Q plot of EXAM 2 120 100 80 60 EXAM 2 scores 40 20 -2.5 -2 -1.5 -1 -0.5 0 0 z-score 0.5 Q-Q plot of Exam 3 120 100 80 60 Exam 3 score 40 20 -2.5 -2 -1.5 -1 0 -0.5 0 0.5 1 1.5 2 2.5 1.5 2 2.5 Z-score Q-Q plot of final score 250 200 150 Final score 100 50 -2.5 -2 -1.5 -1 -0.5 0 0 z-score 0.5 1 The box plot of the four scores in the single graph is Box Plot 200 180 160 140 120 100 80 60 40 20 0 EXAM 1 EXAM 2 EXAM 3 FINAL The multiple regression output is : Regression Statistics Multiple R 0.99481736 0.98966157 R Square 9 Adjusted R 0.98818466 Square 2 2.61356514 Standard Error 9 Observations 25 ANOVA df Regression Residual Total SS 3 13731.51482 21 24 143.4451786 13874.96 Standard Error Coefficients Intercept EXAM 1 EXAM 2 EXAM 3 4.33610240 1 0.35593821 9 0.54251875 8 1.16744421 6 3.764226068 0.121388888 0.100849459 0.103014055 MS 4577.17160 7 6.83072279 1 F 670.085984 7 t Stat P-value 1.15192401 4 2.93221418 5 5.37949100 1 11.3328633 8 0.26229818 5 0.00796121 4 2.46036E-05 2.07538E-10 Significance F 5.34312E-21 Lower 95% Upper 95% 12.1642390 2 0.10349620 8 0.33279082 7 0.95321476 1 3.49203421 6 0.60838022 9 0.75224668 8 1.38167367 1 The predicted final scores, total scores and the grades are as follows: Predicted Final Exam Score Student's Name Predicte d Total Course Points Predicted Course Final Grade (Max. 200 pts.) (Max. 500 pts.) (A to F) Exam 1 Exam 2 Exam 3 (Max. 100 pts.) 86 (Max. 100 pts.) Mary D. (Max. 100 pts.) 82 90 176.577424 435 B+ Mike A. Ginny V. Isaac G. Stan L. 89 76 60 90 84 79 70 91 87 83 76 99 174.481622 162.472054 143.722264 192.644522 434 400 350 473 B+ BC A Coefficients Conclusions and Recommendations (75 words) Intercept EXAM 1 EXAM 2 EXAM 3 4.336102401 0.355938219 0.542518758 1.167444216 This is an example of what a perfect paper in this class should look like. Look it over, and follow the directions as provided by your professor. While this is for an individual report, the way the report was developed is applicable to all reports in this class. You will find my comments in this paper showing you what the student did correctly. Look for those comments. Grade: 37.5 points Comments: A perfect paper at last. This is the first one this term. Thanks, I enjoyed reading this paper. I've added a few points for such a great paper. Management Report for Prescott College Bookstore 1 Executive Summary The Prescott College Bookstore is in jeopardy of having its operation outsourced to a third party company. The college president plans to use the proceeds from the new $1 million contract to fund a new bell tower for the campus. Dr. Mary Ann Lane, vice president of business services, fears that the new operating company will cause a considerable increase in the cost of textbooks for her students. A series of tests were conducted on the bookstore's past 4 year's sales data, including descriptive statistics, histograms, box-and-whisker plots, and various forecasting methods to determine whether the bookstore's sales are growing or declining. The study revealed that the bookstore's sales are growing and quite substantially. It was determined that Winter's method was the best forecasting model for the bookstore, presenting the lowest error and the most expected forecast graphically. A comparison of projected sales for 2013 to prior years revealed a growth of approximately $200,000. It was recommended that the Prescott College Bookstore remain in operation. The college should not enter into a contract with the third party company, and the college president will not be getting his bell tower. Management Report for Prescott College Bookstore 2 Background Dr. Mary Ann Lane, vice president of business services at Prescott College, is facing uncertainty about the future of her institution's on-campus bookstore. The college has ran its own bookstore for the entirety of its 25 year history, but now the college president wants to explore the possibility of off-loading the bookstore to a third party company. The third party company will make a $1 million donation to the college if awarded the contract, and the president plans on using these proceeds to finance a new bell tower for the campus. Dr. Lane is aware that the third party company is known for \"increasing the cost of textbooks and e-books by over 20%\Predicting Total Attendance at the Prescott Maple's Home Games Game # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total Attendance Dependent Variable 872 893 4623 1452 645 3421 3871 2431 2976 1398 2740 870 2156 1489 2103 2503 1913 1567 1445 2272 Regular Ticket Sales 345 369 2489 564 357 1589 1782 1003 1256 783 2401 1555 625 1677 1271 1360 741 517 1194 934 Advanced ticket Sales 247 317 1247 784 247 1106 1749 875 963 415 318 613 521 1148 418 234 1163 653 407 808 Hot dog Sales 503 691 2638 1347 602 2493 3502 2100 1947 927 1725 2822 560 2487 1105 773 265 1894 2513 2383 Game a b c d e Attendance Estimated Regular Ticket Advanced Hot Dogs to Sales Ticket Sales be Sold 645 743 1,745 1,854 1,745 231 320 134 871 765 539 852 396 783 1,239 Predicting Total Attendance at the Prescott Maple's Home Games An Extra Credit Opportunity Maximum points: 10 QNT. 5040, Summer 2015 Your professor makes available to you one extra credit opportunity to possibly raise your total points in this class. This extra credit is a multiple regression that is similar to the one you completed for your mini report. Follow the directions below and determine what variables contribute to projecting the attendance at the Maples home games. Prescott Maples Prescott The Back Story Bud Ford, the general manager and owner of the double A farm team, the Prescott Maples, has been talking to his staff about a better way of predicting attendance at the Maple's home games. Playing at ACE Stadium in west Prescott, the team averages about 2,000 people in attendance for each home game. The team sells season tickets, day of game tickets and gives away some tickets to sponsors, nonprofit organizations serving children, and for other good causes. So while the team may sell over the average number of seats, this does not translate into people actually attending a game. Bud said, \"I just don't understand it! They pay for a seat then don't come. It makes no sense to me.\" So his stadium manager and concession manager have been trying to figure out how they could better predict the attendance at five (5) typical Maple games. The concession manager, Ralph Peanut, believes that part of this prediction could be the estimated number of hot dogs that would be sold at the game. Bud and the stadium manager aren't sure, but are willing to put this theory to the test. That's where you come into the picture. Your job is to investigate the historical data prepared by Bud, and then using that data create a multiple regression that helps to predict the attendance at these five typical games. Table 1. Estimated number of Attendance at Five Typical Maple's Games Game Attendance Regular Advance Estimate Ticket d Ticket d Hot Sales Sales Dogs to be Sold a 645 231 539 b 743 320 852 c 1,745 134 396 d 1,854 871 783 e 1,745 765 1,239 Copyright R&A 2015 Page 1 Your Tasks 1. Complete a one variable summary for all 20 games over the four variables: 1) attendance; 2) regular ticket sales; 3) advanced ticket sales; and 4) Hot dogs sold at the game. a. What do these statistics tell you about the group of games? Be specific and look at the range, standard deviation, the IQR, the mean versus the median and other statistics from this output. 2. Complete a correlation on the data (StatTools: Summary Statistics/Correlation and Covariance/Correlation/Entries below the diagonal only). What does this correlation show you about the data, which variables are correlated with attendance the most and the least? What do you think the regression will show about these variables? 3. Complete a single histogram on all four of these variables and analyze the results. a. What do these histograms tell you about the relationship for each of these variables? 4. Complete 4 Q-Q plots for these variables. a. What do these plots tell you about the data? Is it relatively normal or not and why? 5. Complete a single box and whisker plot with all four of the variables on it. a. First explain in the paper what a box and whisker plot is, and how to understand it. b. What do these box and whisker plots tell you about the individual variables and how do they compare to each other? 6. Complete a multiple regression using the attendance as the dependent variable, and the rest as the independent variables. a. Do any of the variables t-score (p-value) exceed the alpha (.05) for this test? If so, remove that/those variables and rerun the regression. b. What does the r-squared value tell you about this data? c. What does the standard error of the estimate tell you about the data? d. According to the f-ratio, is this regression significant or not and why? e. Using the coefficients generate predict the attendance for the five \"typical\" games found in Table 1 above. (You are to complete the column found on the right in yellow and include this table in your mini report.) Remember to only use the variables that are significant to calculate this table. 7. What general things can you tell Bud Ford about projected attendance at these games? Does knowing the number of hot dogs that could be sold help or hurt the estimated attendance theory? Be specific in your answer. Remember to develop your mini report as shown in the chats for this assignment. All work is to be 1 & spaces, inch margins, with all tables, graphs and figures having a number and a title and explain them in the report. Include an embedded Excel file showing your work, but note this file will not be graded and is only used to demonstrate that you did the actual work for this case study. ### Copyright R&A 2015 Page 2 This is an example of what a perfect paper in this class should look like. Look it over, and follow the directions as provided by your professor. While this is for an individual report, the way the report was developed is applicable to all reports in this class. You will find my comments in this paper showing you what the student did correctly. Look for those comments. Grade: 37.5 points Comments: A perfect paper at last. This is the first one this term. Thanks, I enjoyed reading this paper. I've added a few points for such a great paper. Management Report for Prescott College Bookstore 1 Executive Summary The Prescott College Bookstore is in jeopardy of having its operation outsourced to a third party company. The college president plans to use the proceeds from the new $1 million contract to fund a new bell tower for the campus. Dr. Mary Ann Lane, vice president of business services, fears that the new operating company will cause a considerable increase in the cost of textbooks for her students. A series of tests were conducted on the bookstore's past 4 year's sales data, including descriptive statistics, histograms, box-and-whisker plots, and various forecasting methods to determine whether the bookstore's sales are growing or declining. The study revealed that the bookstore's sales are growing and quite substantially. It was determined that Winter's method was the best forecasting model for the bookstore, presenting the lowest error and the most expected forecast graphically. A comparison of projected sales for 2013 to prior years revealed a growth of approximately $200,000. It was recommended that the Prescott College Bookstore remain in operation. The college should not enter into a contract with the third party company, and the college president will not be getting his bell tower. Management Report for Prescott College Bookstore 2 Background Dr. Mary Ann Lane, vice president of business services at Prescott College, is facing uncertainty about the future of her institution's on-campus bookstore. The college has ran its own bookstore for the entirety of its 25 year history, but now the college president wants to explore the possibility of off-loading the bookstore to a third party company. The third party company will make a $1 million donation to the college if awarded the contract, and the president plans on using these proceeds to finance a new bell tower for the campus. Dr. Lane is aware that the third party company is known for \"increasing the cost of textbooks and e-books by over 20%\Predicting Total Attendance at the Prescott Maple's Home Games Game # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Total Attendance Dependent Variable 872 893 4623 1452 645 3421 3871 2431 2976 1398 2740 870 2156 1489 2103 2503 1913 1567 1445 2272 Regular Ticket Sales 345 369 2489 564 357 1589 1782 1003 1256 783 2401 1555 625 1677 1271 1360 741 517 1194 934 Advanced ticket Sales 247 317 1247 784 247 1106 1749 875 963 415 318 613 521 1148 418 234 1163 653 407 808 Hot dog Sales 503 691 2638 1347 602 2493 3502 2100 1947 927 1725 2822 560 2487 1105 773 265 1894 2513 2383 Game a b c d e Attendance Estimated Regular Ticket Advanced Hot Dogs to Sales Ticket Sales be Sold 645 743 1,745 1,854 1,745 231 320 134 871 765 539 852 396 783 1,239 Predicting Total Attendance at the Prescott Maple's Home Games An Extra Credit Opportunity Maximum points: 10 QNT. 5040, Summer 2015 Your professor makes available to you one extra credit opportunity to possibly raise your total points in this class. This extra credit is a multiple regression that is similar to the one you completed for your mini report. Follow the directions below and determine what variables contribute to projecting the attendance at the Maples home games. Prescott Maples Prescott The Back Story Bud Ford, the general manager and owner of the double A farm team, the Prescott Maples, has been talking to his staff about a better way of predicting attendance at the Maple's home games. Playing at ACE Stadium in west Prescott, the team averages about 2,000 people in attendance for each home game. The team sells season tickets, day of game tickets and gives away some tickets to sponsors, nonprofit organizations serving children, and for other good causes. So while the team may sell over the average number of seats, this does not translate into people actually attending a game. Bud said, \"I just don't understand it! They pay for a seat then don't come. It makes no sense to me.\" So his stadium manager and concession manager have been trying to figure out how they could better predict the attendance at five (5) typical Maple games. The concession manager, Ralph Peanut, believes that part of this prediction could be the estimated number of hot dogs that would be sold at the game. Bud and the stadium manager aren't sure, but are willing to put this theory to the test. That's where you come into the picture. Your job is to investigate the historical data prepared by Bud, and then using that data create a multiple regression that helps to predict the attendance at these five typical games. Table 1. Estimated number of Attendance at Five Typical Maple's Games Game Attendance Regular Advance Estimate Ticket d Ticket d Hot Sales Sales Dogs to be Sold a 645 231 539 b 743 320 852 c 1,745 134 396 d 1,854 871 783 e 1,745 765 1,239 Copyright R&A 2015 Page 1 Your Tasks 1. Complete a one variable summary for all 20 games over the four variables: 1) attendance; 2) regular ticket sales; 3) advanced ticket sales; and 4) Hot dogs sold at the game. a. What do these statistics tell you about the group of games? Be specific and look at the range, standard deviation, the IQR, the mean versus the median and other statistics from this output. 2. Complete a correlation on the data (StatTools: Summary Statistics/Correlation and Covariance/Correlation/Entries below the diagonal only). What does this correlation show you about the data, which variables are correlated with attendance the most and the least? What do you think the regression will show about these variables? 3. Complete a single histogram on all four of these variables and analyze the results. a. What do these histograms tell you about the relationship for each of these variables? 4. Complete 4 Q-Q plots for these variables. a. What do these plots tell you about the data? Is it relatively normal or not and why? 5. Complete a single box and whisker plot with all four of the variables on it. a. First explain in the paper what a box and whisker plot is, and how to understand it. b. What do these box and whisker plots tell you about the individual variables and how do they compare to each other? 6. Complete a multiple regression using the attendance as the dependent variable, and the rest as the independent variables. a. Do any of the variables t-score (p-value) exceed the alpha (.05) for this test? If so, remove that/those variables and rerun the regression. b. What does the r-squared value tell you about this data? c. What does the standard error of the estimate tell you about the data? d. According to the f-ratio, is this regression significant or not and why? e. Using the coefficients generate predict the attendance for the five \"typical\" games found in Table 1 above. (You are to complete the column found on the right in yellow and include this table in your mini report.) Remember to only use the variables that are significant to calculate this table. 7. What general things can you tell Bud Ford about projected attendance at these games? Does knowing the number of hot dogs that could be sold help or hurt the estimated attendance theory? Be specific in your answer. Remember to develop your mini report as shown in the chats for this assignment. All work is to be 1 & spaces, inch margins, with all tables, graphs and figures having a number and a title and explain them in the report. Include an embedded Excel file showing your work, but note this file will not be graded and is only used to demonstrate that you did the actual work for this case study. ### Copyright R&A 2015 Page 2