Grade and Grading Curve Scenario

- - - New Perspectives excel 2010 - Tutorial 10 Case Problem 1 High abjure 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 baring a solution Create a Solver answer report Save and load Solver models Project overviewProfessor 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 fin possible grading curves. For example, in grade reduce 1, she will assign As to test scores from 80 to 100.She wants you to label 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. STUDENT straggle FILE NP_Excel2010_T10_CP1a_FirstLastName_1. xlsx (Note Download your personalized flummox file from www. cengage. com/sam2010) Instructions Open the file NP_Excel2010_T10_CP1a_FirstLastName_1. lsx and save the file as NP_Excel2010_T10_CP1a_FirstLastName_2. xlsx before you move to the beside step. Verify that your name appears in mobile phone B4 of the accompaniment sheet. (Note Do not edit the Documentation sheet. If your name does not appear in cell B4, please download a new copy of the start file from the SAM Web site. ) 2. The sort Score worksheet contains a table of individual student scores and a table for the grading curve. In the Test Score worksheet, the move F4G8 will contain the lower and upper sicks for each earn grade.Add the missing upper range values in the range G4G7 by inserting formulas in each of those cells that calculates the upper range for each earn grade as being one point lower than the lower range of the next letter grade. Any changes to the numeric values in F5F8 should result in changes to the calculated values in G4G7. 3. In cell D4, enter the VLOOKUP function to ease up the letter grade for the first student in the list. The lookup value is the students 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 D5D223 to calculate the grades for the rest of the students scores. 4. In cell I4, use the COUNTIF function to count the follow 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 I5I8 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 J4J8, 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 L4L8, 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 F4F8. b) HighF through HighA for the values in the range G4G8. c) PercentF through PercentA for the values in the range J4J8. ) DifferenceFromCurve to the value in cell L9. 8. Enter the values of the five grading curve scenarios named Grading Curve 1 throu gh Grading Curve 5 shown in the chart below into your scenarios. Use the range F4F8 as your ever-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 J4J8,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 F5F8, subject to the constraint that all of the values in the range F5F8 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. Follo w the directions on the SAM Web site to submit your blameless project.