Weighted Grade Book Project
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 submit a WORD document answering several questions regarding your project. WORD and Excel documents ONLY.
In this project you are required to do the following:
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. You should NOT be using a template for this project. You will be required to CREATE the gradebook from a blank worksheet. Your Excel spreadsheet should contain the following:
• Your name, course name and project name.
• A data table labeled by Category using the category headings and scores on the next page for a MATH 123 class (DO NOT use any other scores).
• A table to calculate the overall grade with headings Categories, Category Weights, Category Average and Category Percentage Points.
• Category Weights used to determine the final weighted grade for a face-to-face MATH 123
• A Final course grade, Labeled cell, at the bottom of your grade book calculation table that uses a formula to calculate the weighted OVERALL COURSE grade. 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.) Highlight the final course percentage cell.
Your written portion of the project will consist of answering the short answer questions given below. Each short answer response should be a minimum of 3 sentences and 50 words. Use proper quantitative reasoning and wording to address the solution. This should include sophisticated and elegant solutions to clearly convey your message to the reader. Make sure to use you spreadsheet to answer the "what if" questions with specific values and provide support for your statements. Questions are not cumulative - return to your original spreadsheet to answer the next question.
1. Explain how you setup your excel worksheet. Make sure to address any formula used by showing an example but avoid using cell references (B5, A3, G12, etc.) What final course percentage (to 1 decimal) did you calculate? What letter grade in the course did this student earn?
2. At the end of the semester, the instructor decides to drop any zero in the course for the student. How does this affect the final grade? Will they reach the next letter grade?
3. The student already missed 2 homework assignments in the course. How many total homework assignments could the student have missed and keep their current overall class grade?
4. Given the student's final grade, what is the lowest possible score for Exam 3 that the student could have had to keep the same letter grade? Justify how you came to this conclusion.
5. If the student failed both projects, would they still have the same grade they have currently? Explain how you came to your conclusion for this answer. What if the student did not turn in either project?
6. Which scenario is best for the student: A. If the student failed every Exam, but had a perfect score in all other assignments? OR B. If the student failed all assignments, but had a perfect score on every Exam? Justify which is best using a quantitative argument in your response. Make sure to address the number of failed assignments for each scenario.
Submit both an Excel and Word file following your instructor's directions for submission.
Use the following parameters and scores in your Excel spreadsheet
Methods of Evaluation: There will be an Exam 1, Exam 2, and Exam 3 in this class. A total of 20 homework papers are given and 10 total quizzes are assigned. The course has 2 projects and 3 online test reviews.
Grading Scale: Category Weights: (NOTE: These are grade weights for a current face-to-face math 123 course) 90 - 100 A Exam 1: 20% Test Review: 5%
80 - 89 B Exam 2: 20% Project: 10%
70 - 79 C Exam 3: 25% Quiz: 10%
60 - 69 D Homework: 10%
0 - 59 F
A student has the following grades in each category:
Exam 1 92
Exam 2 89
Exam 3 84
Online Test Review 85 65 0
Project 72 88
Quiz 0 60 79 77 80 88 100 90 90 100
Homework 100 100 65 70 95 100 75 76 95 83
80 100 85 90 92 100 0 55 100 0
The Excel file contains all formulas, calculations and solutions for the gradebook. The text provided answers the questions asked.