Weighted Grade Book Analysis

Math 123 Quantitative Reasoning Project 1 Fall, 2022
Online Weighted Grade Book Project
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? 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 the last two homework assignments and the last project?
ii. If the student received perfect scores on all of the quizzes?
Math 123 Quantitative Reasoning Project 1 Fall, 2022
□ The school’s math committee is considering changes to the weights. One professor has suggested not giving
credit for homework and quiz assignments but weighing tests more heavily. Copy your spreadsheet again to
create a new weighting system to adjust for this change. The removed homework and quiz 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 five 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: 100, 90, 88, 84, 100, 0, 90, 54, 100, 85, 92, 80, 63, 56, 85, 98, 100, 89
Module Quizzes: 65, 75, 89, 85, 0, 74, 75, 83, 95
Discussion Boards: 100, 52, 0, 100, 77
Final Outcomes Assessment: 69
Projects: 54, 90
Exam 1: 84
Exam 2: 71
Math 123 Quantitative Reasoning Project 1 Fall, 2022
Exam 3(final): 64
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.
Math 123 Quantitative Reasoning Project 1 Fall, 2022
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, DB &
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
--Included & renamed 2nd
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
Math 123 Quantitative Reasoning Project 1 Fall, 2022
Reflection Part II
- -Found grade with
missing work
--Found grade with 100%
quiz scores
--Removed HW/Quiz &
created new weights
--Included & renamed 3rd -
5th 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