Grade and Grading Curve Scenario

Problem 1

1. High Desert University Skills Define a scenario View scenarios Edit a scenario Create a scenario summary report Find an optimal solution using Solver Activate Solver Set up Solver to find a solution Create a Solver answer report Save and load Solver models Project overview Professor Karen Reynolds teaches calculus at High Desert University in Tempe, Arizona. The class has 220 students who are distributed among dozens of sections and discussion groups. Professor Reynolds wants to use Excel to determine the appropriate cutoff points for her grading curve. Generally, she wants to set the cutoff points so that the following distribution of grades is observed in the student body: F 5% D 10% C 35% B 35% A 15%. Professor Reynolds has five possible grading curves. For example, in Grading Curve 1, she will assign As to test scores from 80 to 100. She wants you to evaluate each grading curve scenario and determine which one results in a distribution of grades closest to her proposed distribution. After you choose which of the five scenarios fits the data the best, she wants you to use Solver to determine whether there is a grading curve that is even closer to the desired distribution of grades.
2. The Test Score worksheet contains a table of individual student scores and a table for the grading curve. In the Test Score worksheet, the range F4: G8 will contain the lower and upper ranges for each letter grade. Add the missing upper range values in the range G4: G7 by inserting formulas in each of those cells that calculate the upper range for each letter grade as being one point lower than the lower range of the next letter grade. Any changes to the numeric values in F5: F8 should result in changes to the calculated values in G4: G7.
3. In cell D4, enter the VLOOKUP function to return the letter grade for the first student in the list. The lookup value is the student’s final score, the table array is the cell range $F$4:$H$8, the column index number is 3, and the lookup should find the closest match in the first column of the lookup table. Copy the formula in cell D4 into the range D5: D223 to calculate the grades for the rest of the students’ scores
4. In cell I4, use the COUNTIF function to count the total number of letter grades in the range $D$4:$D$223 equal to the value in cell H4 (e. g. “F”). Copy your formula into the range I5: I8 to count the total number of the other letter grades assigned under the current grading scale. In cell I9, calculate the total number of all letter grades, verifying that the total equals 220.
5. In the range J4: J8, calculate the percent of each letter grade assigned to the student body. In cell J9, calculate the total percentage of all letter grades, verifying that the total percentage equals 100 percent.
6. In the range L4: L8, use the ABS function to calculate the absolute value of the difference between the observed percentage of each letter grade and Professor Reynolds’ optimal percentage. In cell L9, calculate the total value of these absolute differences.
7. Assign the following range names: a) LowF, Lowd, LowC, LowB, and Lowa for the values in the range F4: F8. b) high through HighA for the values in the range G4: G8. c) percent through PercentA for the values in the range J4: J8. DifferenceFromCurve to the value in cell L9.
8. Enter the values of the five grading curve scenarios named Grading Curve 1 through Grading Curve 5 shown in the chart below into your scenarios. Use the range F4:F8 as your changing cells. Scenario Name Low F Low D Low C Low B Low A Grading Curve 1 0 20 40 60 80 Grading Curve 2 0 30 50 70 90 Grading Curve 3 0 50 65 80 95 Grading Curve 4 0 40 60 75 85 Grading Curve 5 0 60 70 80 90.
9. Create a scenario summary report evaluating the results from each of the five scenarios, displaying the values from the range J4: J8, L9 as your result cells. Note: The closeness of each grading curve to Professor Reynolds’ optimal grading curve is expressed in the value of cell L9. If there is perfect correspondence, the value of cell L9 would be zero.
10. Create a Solver model to minimize the value in cell L9 by changing the values in the range F5: F8, subject to the constraint that all of the values in the range F5: F8 must be integers. Save the Solver model, selecting cell L13 as the top cell holding the solver model data. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM Web site to submit your completed project.