Math 123 Quantitative Reasoning Project Fall 2017 Excel Grade Book Project Due Date: Understanding how weighted averages work will be a very important topic for you in this course, since your grades are determined in this way. In this project, you will create an Excel spreadsheet that can calculate a student's final grade in a course. You will then write a reflection paper answering several questions regarding your project. In this project you are required to do the following: 1. Create an Excel spreadsheet that will be capable of calculating a student's final grade in a given course. Keep in mind that weighting is involved in determining the final grade. Your Excel spreadsheet should contain the following: Your name, course name and project name. The scores provided on the next page Category averages The weights used to determine the final weighted grade for the course A labeled cell with the Final course grade that uses a formula to calculate the weighted average grade for the course. If a value is changed in your spreadsheet, your final calculation should recalculate. Please limit your use of Excel functions to those used in the course-packet (for a list see the 'Excel Reference sheet' in Ivy Learn's 'Excel Help' Module. Include a pie chart to represent the weights of each category. In addition to correctly calculating the final grade, your spreadsheet should be well-organized, with clear labels 2. Write a typed reflection paper (minimum one page). Remember that we are looking for elegant and sophisticated reasoning. It must include the following information: Your name, course name and project name. An explanation of your process for using Excel to calculate the weighted average final grade. If you encountered any difficulties, how did you overcome them? Analyze and reflect on the results for the student data provided. What is the final course score you determined based on the initial data provided? What letter grade is this? The student wishes to know what changes she could have made to increase her grade to the next higher letter grade. Is it possible for the student to have improved in any individual category in order to receive the next higher letter grade? If it is possible, what was the minimum increase and category(s) that would achieve this increase of a letter grade? If not possible, explain why. The student also has questions about how dropping the lowest homework scores would affect the grade. If the lowest 2 homework scores were dropped from the total, how would this change the grade? This student feels that the homework is undervalued in the course grading. Create a new weighting system and explain why/how you chose your new weight values. What final course grade would the student earn under your new category weighting system? Compare this to your current MATH 123 course. Based on the percentage of each category in your class, what can you conclude about your MATH 123 grade? What category is more important? If your teacher were to give you 5 extra points, where would you want them to be applied? Reflect on the results of your project. What have you learned from doing this project? In addition to addressing the given statements, your project should: Contain a paper that is well organized and in paragraph form (not just bulleted answers!). Math 123 Quantitative Reasoning Project Fall 2017 Contain a paper that is clear to someone who is not familiar with the project. (i.e. don't assume that you are writing this to your instructor, make sure anyone could understand your statements.) Give specific values (i. e. quantify statements). Provide support for your statements (e.g. explain how values were found and justify statements). Contain a spreadsheet using the original grade values given below. Include a pie chart that represents how much each weighted category includes. Use appropriate representations (tables, color, formatting) that will help the reader understand the project. Have sophisticated and elegant solutions. Project should be your original work. Copying someone else's work is cheating. Sharing your work with someone else is cheating. These instructions cover the MINIMUM expectations of this project. Students are encouraged to make additions beyond these items. 3. Submit both an Excel and Word file following your instructor's directions for submission. Use the following parameters and scores in your Excel spreadsheet Create an Excel spreadsheet that will compute the following sample student's data set for the course described below. Assume all scores are out of 100 and that all grades have been recorded for this student. Grading and Evaluation Rules for EDUC 250 Educational Psychology: Methods of Evaluation: There will be a Midterm and Final Exam in this class. Three Labs will also be assigned. Homework and ten quizzes will also be part of the course grade. This course requires a C or better for graduation requirements. Final Grade Calculation: Homework Projects30% Quizzes Midterm Exam Final Exam Grading Scale: 5% 80 - 89 15% 20% 30% 90 - 100 B 70 - 79 60 - 69 0 - 59 A C D F A student has the following grades in each category: Homework: 80, 84, 82, 82, 80, 86, 88, 87, 86, 94, 88, 83, 92, 89, 92 Quiz: 96, 97, 98, 95, 92, 95, 96, 98, 92, 94 Projects: 50, 80, 98 Midterm Exam: 60 Final Exam: 90 I formatted the Excel spreadsheet by organizing each category into a column so that the entire Gradebook could be viewed without the need to scroll. Next, I entered the individual grades for each category into the appropriate column and used the Excel function "Average" to compute the average for each category. Using the "Average function as opposed to manually entering the formula to compute the average will help when answering the fourth question. I obtained the weighted average (score) by using Excel to multiply the average by the weight. The final grade is the sum of each weighted average (score). A pie chart that was created based on the information in the accompanying table shows visually the breakdown of the final grade by category. Based on the information provided, this student earned a final grade of 80.405, which corresponds to a letter grade of B. If the student wishes to increase her grade to the next higher letter grade, then she would need to increase her final grade to at least 90 an increase of at least 9.595 points. Improvement in a single category would not achieve this result because in each category, the difference between the maximum weighted average (i.e. the weighted average obtained when average is 100) and the weighted average is less than 9.595. In particular, her lowest score was obtained on the midterm exam. If she had obtained a score of 100 on the midterm exam, then after modifying the information in the Excel spreadsheet, one can note that her modified final grade would be 88.405, which still corresponds to a letter grade of B. In fact, in order to achieve the desired result, she would need to improve her grade in multiple categories. One specific scenario could involve increasing her "Projects" average to 90 while increasing her "Midterm" average (or score) to 90. In that case, after modifying the information in the Excel spreadsheet, one can note that her final grade would be 90.605, which corresponds to a letter grade of A. Given that Homework accounts for a mere 5% of the final grade, dropping the two lowest homework scores would lead to only a marginal or insignificant increase in the final grade. Specifically, after deleting the two lowest Homework scores of 80 from the total, one can note that the modified final grade would be 80.45269 an increase of 0.04769. I agree with the student that homework is undervalued in the course grading because oftentimes students can devote a significant amount of time completing coursework throughout the term outside of class and still, possibly due to anxiety, perform poorly under pressure on quizzes and exams. I argue that a fair grading system would involve weighting Homework and Projects at 50% and weighting Quizzes, Midterm, and Final Exam at a combined 50%. Under my system, the individual weights would be: Homework 20%; Quizzes 5%; Projects 30%; Midterm 15%; Final Exam 30%, and the student would achieve a final course grade of 80.805. Although I personally believe that this particular grading system is more appropriate, one can note that in this particular case, the increase to the student's final grade is minor. EDUC 250: Educational Psychology Student Gradebook Category 1: Homework Category 2: Quizzes Grade: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Average: Weight: Weighted Average: FINAL GRADE: Category 3: Projects Grade: 80 84 82 82 80 86 88 87 86 94 88 83 92 89 92 86.2 0.05 4.31 80.405 1 2 3 4 5 6 7 8 9 10 Average: Weight: Weighted Average: 96 97 98 95 92 95 96 98 92 94 95.3 0.15 14.295 1 2 3 Average: Weight: Weighted Average: Category 4: Midterm Grade: Category 5: Final Exam Grade: 50 80 98 76 0.3 22.8 Grade: 1 60 1 90 Average: Weight: Weighted Average: 60 0.2 12 Average: Weight: Weighted Average: 90 0.3 27 Category Homework Quizzes Projects Midterm Final Exam Final Grade Breakdown 0.05 0.15 0.3 0.2 0.3 Homework Quizzes Projects Midterm Final Exam Homework Quizzes Projects Midterm Final Exam