Weighted Grade Book Project 1
Understanding how weighted averages work is very important 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 Math 123 student’s final grade. 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 this 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 (calculated using formulas)
□ The weights used to determine the final weighted grade for the course – Hint: where might you find this?
Additionally, include a pie chart of these weights with data labels.
□ 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). Do not use the SUMPRODUCT function.
□ In addition to correctly calculating the final grade, your spreadsheet should be well organized, with clear labels, and helpful formatting.
2. Write a typed, double-spaced reflection paper (minimum one page). Remember that we are looking for responses that are backed by values that support your statements. The explanations should be clear to a broad audience. Do NOT use cell references in your paper. The paper must include the following information:
□ Your name, course name and project name.
□ An introductory paragraph explaining the purpose of this project.
□ An explanation of how the final grade is calculated and how Excel is used in this process.
□ 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. Could the student receive the next higher letter grade by improving any individual score from any category? If it is possible, what was the minimum new score that would achieve an increase of a letter grade for her course grade? Consider at least TWO scenarios to answer this question. If not possible, explain why. Add a spreadsheet for each scenario to your workbook to prove your answer. If a score was changed, highlight the one score that is different from the original. See directions at the end of this assignment for how to copy your first spreadsheet to make changes and how to name each spreadsheet.
□ Reflect and explain the impact of the student’s final course score for each of the following scenarios.
Copy your first spreadsheet (2 more times) to show each scenario on a separate sheet in your workbook with your changes highlighted.
i. If the student didn’t turn in any projects?
ii. If the student received perfect scores on all of the homework assignments?
□ The school’s math committee is considering changes to the weights. One professor has suggested not giving credit for quizzes and the Final Outcomes assignments but weighing tests more heavily. Copy your spreadsheet again to create a new weighting system to adjust for this change. The removed quiz and Final Outcomes weights should be evenly distributed between the three exams. How would the student’s grade be impacted by this change? Quantify your answer. Would this be a good change to make?
□ Reflect on the results of your project. What have you learned from doing this project? How will Excel help to determine YOUR grade? How could you use Excel in the future?
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!), with an introduction, main body, and conclusion.
• 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).
• Provide a workbook with at least six spreadsheets using the original grade values given below.
• Use appropriate representations (tables, color, formatting) that will help the reader understand the project.
• Project should make use of Excel functions.
• 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 be creative.
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
Please refer to your math 123 syllabus for specific grading information and use this in your calculations.
Create an Excel spreadsheet that will compute the following sample student’s data set for a math 123 class. Assume all scores are out of 100 and that all grades have been recorded.
A student has the following grades in each category:
Homework: 95, 96, 100, 88, 0, 80, 100, 100, 0, 42, 0, 73, 81, 89, 0, 80, 90
Quizzes: 74, 82, 90, 84, 83, 75, 66, 80, 95
Final Outcomes Assessment: 55
Projects: 75, 90
Exam 1: 92
Exam 2: 78
Exam 3(final): 70
Directions for how to copy or rename a spreadsheet in a workbook
At the bottom of the workbook, there is a tab labeled “Sheet1”.
If you right click. You will be able to rename or copy a spreadsheet.
Renaming
Click on “Rename” and start typing the new name.
Copying
Click on “Move or Copy…” then select “(move to end)” and “Create a copy” and click “Ok”.
This will copy the sheet with all the data and formulas into another sheet.
Rubric for Project 1: Excel Gradebook
Points 0 1 2 3 4
Specifications
-Heading (Excel & Word)
-At least 1 page
-Double spaced
-Spelling/grammar None of the items are present. 1 item present: heading, min 1 pg., double spaced,
spelling & grammar 2 items present: heading, min 1 pg., double spaced,
spelling & grammar 3 items present: heading, min 1 pg, dbl spaced,
spelling & grammar 4 items present: heading, min 1 pg., double spaced,
spelling & grammar
Spreadsheet
–HW, quiz, project, & exam scores with averages
–Include category weights and pie chart with labels
–Used formulas to calculate averages Spreadsheet did not include any of the required items. Spreadsheet missing three of the following: categories, scores, averages, weights, pie chart, formulas Spreadsheet missing two of the following: categories, scores, averages, weights, pie chart, formulas Spreadsheet missing one of the following: categories, scores, averages, weights, pie chart, formulas Spreadsheet included:
Categories, scores, averages, weights, pie chart, formulas
Final Grade
–Final grade with weighted averages
-Used formula to calculate No final grade calculated. Final grade calculated with formulas, but was not weighted Final grade was calculated with weighted averages, but no formulas. Final grade calculated with formulas, but incorrect weights Final grade was calculated with weighted average and used formulas
Reflection Part I
–Explained process of calculating weight avg
–Stated letter grade
–Found min grade to raise grade (2 scenarios)
–Included & renamed 2nd & 3rd spreadsheet to show work None of the questions were answered. 1 of the questions was answered.
2 of the questions were answered. 3 questions answered with weak or no supportive statements. All 4 questions answered with supportive statements
Reflection Part II
– -Found grade with missing work
–Found grade with 100% homework scores
–Removed Quiz/Final Outcome Assmt & created new weights
–Included & renamed 4th -6th spreadsheet to show work None of the questions were answered. 1 of the questions was answered. 2 of the questions were answered. 3 questions answered with weak or no supportive statements. All 4 questions answered with supportive statements
Reflection Part III
-What have you learned?
–How will Excel help to determine YOUR grade?
–How will you use excel in the future? None of the questions were answered. 1 of the questions was answered. 2 of the questions were answered. All 3 questions answered with weak or no supportive statements. All 3 questions answered with supportive statements
Organization
-Logical
–Has intro, main body, conclusion
-Easily understood Poor Below Average Average Above Average Excellent
