InstructionsProject 1, descriptionUnderstanding 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...

Project 1, description

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 responses to several questions regarding your project in IvyLearn. Excel documents ONLY should be uploaded (xls or xlsx file extensions only).

In this project you are required to do the following:

Create an Excel spreadsheetthat 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 of your own scores).




• A table to calculate the final course percentage with the following headings: Categories, Category Weights, Category Averages and Category Percentage Points.




• Category Weights given on the next page used to determine the final weighted percentage for a MATH 123 class.




• A final course percentage based on the percent weightings. The cell where the final course percentage should be located at the bottom of your grade book calculation table and should be labeled.That cell should contain aformulaused to calculate the weighted final course percentage.If a value is changed in your spreadsheet of individual scores, your final course percentage calculation should immediately recalculate.Please limit your use of Excel functions to those used in the course (for a list see the ‘Excel Reference sheet’ by going to the bottom of the Module page in our IvyLearn course with the heading ‘Resources’ and click on ‘Excel Help’.)Highlight the final course percentage cell.

Your typed responses for this project will consist of answering the short-answer questions given below. Each short-answer response should be aminimum of 3 sentences and 50 words. Use proper quantitative reasoning and wording to address the solution. This should clearly convey your message to a reader who is not taking Math 123. Make sure to use your spreadsheet to answer the “what if” questions with specific values and provide support for your statements. See the last page of this project description for samples of what poorly written responses and well written responses would look like before you answer the questions below. Questions are not cumulative – return to your original spreadsheet values before going on to answer the next question.Your answers to these questions will be typed in and submitted in IvyLearn using the link to Project 1.

1. Building the gradebook:

• Explain how you created the Excel worksheet you have used for this project (an overview as if writing to someone not in this course-explain what you did).




• Make sure to include an example of each of the different functions/formulas you used but avoid using cell references (B5, A3, G12, etc.).




• What final course percentage (expressed in the spreadsheet as a whole percentage) did you calculate?




• What letter grade in the course did this student earn based on the percentage calculated?

2. Increasing project scores:

• If this student could earn more points back on the project scores, what would be the best possible score for the projects to increase their overall class grade to the next letter?




• Explain how you came to your conclusion for this answer. If it is not possible, state this in your response.

3. Raising one letter grade with test 3 score (return to your original grade book):

• What is the lowest percentage (as a whole number) this student would need to earn on Test 3 to bring their final course percentage (the answer from number 1) up to the next higher letter grade for the overall course?




• If it is not possible, state this in your response. Include in your response how you concluded whether it was possible or not.

4. Lowest average for homework scores (return to your original grade book):

• How many zeros in the homework category would it take to lower the overall class grade down one letter grade? (Start from the lowest scores and change them to zero until the overall grade changes. You can count the current zero in the score as 1 value.)




• Do you think this student was at risk of dropping to the next lower letter grade?




• Explain how you came to your conclusion.

5. Recalculate gradebook (return to original grade book):

• Is it possible for the student to get an A in this class if the weights of each category were different? Change the weights of each category to show that the student could get an A in the course.




• Is your weighted system reasonable for this student?




• Explain how you came to your conclusion.

Use the following parameters and scores in your Excel spreadsheet

Methods of Evaluation:There will be a Test 1, Test 2, and Test 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 virtual Math 123 course)





90 - 100 A Test 1: 20% Online Test Review: 5%

80 - 89 B Test 2: 20% Project: 10%

70 - 79 C Test 3: 25% Quiz: 10%

60 - 69 D Homework: 10%

0 - 59 F

The following scores are to be used for the sample student in this project. Assume that these scores are percentages.:

Example of a poorly written response:

Yes, I think his score would go up and yes he would be able to reach the next letter grade.

This response is not good and will get a failing grade. This is vague and not quantitative. The response does not indicate the student used their Excel spreadsheet, made the indicated changes, or analyzed the results. The response does not meet minimum sentence or word count. No specific numbers have been used which indicates the student is guessing at the correct answer without numerical evidence to show they have done the problem.

Example of a well written response:

The student’s score increased from 52 to 65%. This is an increase in the highest weighted category of 13%. The Test category is weighted at 40% of the overall grade. The student’s original score in this category earned 20.8 percentage points, but with his improved test score, he now earns 26 percentage points in this category. The overall score went from a 69.9 to a 75.1 - a 5.2% increase in the overall grade. This student raised his overall grade from a D to a C.

Student changed the data in their Excel spreadsheet and reported several changes in category, overall score, and grade. Student analyzed the increase or decrease of the new score and have shown evidence of understanding the question and displayed a correct interpretation of the results.