Grade and Grading Curve Scenario

- - - New Perspectives outperform 2010 - Tutorial 10 Case business 1 High Desert University Skills restore a scenario View scenarios Edit a scenario Create a scenario stocky reputation Find an optimal solution victimisation problem solver Activate solver watch up solver to find a solution Create a Solver answer report execute and cut Solver models Project overviewprof Karen Reynolds t for each mavines calculus at High Desert University in Tempe, Arizona. The part has 220 disciples who are distributed among dozens of sections and discussion groups. Professor Reynolds wants to design Excel to determine the curb cutoff sharpens for her leveling trim back. Generally, she wants to set the cutoff points so that the following distribution of manikins is observed in the student body F 5% D 10% C 35% B 35% A 15% Professor Reynolds has five contingent grading wricks. For example, in Grading cut down 1, she will assign As to rivulet scores from 80 to 100.She wants you to evaluate each grading turn scenario and determine which one results in a distribution of grades impending to her proposed distribution. After you choose which of the five scenarios fits the info the best, she wants you to use Solver to determine whether thither is a grading curve that is crimson closer to the desired distribution of grades. student start FILE NP_Excel2010_T10_CP1a_FirstLastName_1. xlsx ( line of descent Download your individualized start stick from www. cengage. com/sam2010) Instructions outspoken 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 succeeding(prenominal) step. bank that your name appears in stallular telephoneular phone B4 of the software documentation sheet. (Note Do not edit the funding sheet. If your name does not appear in cubicle B4, please download a new copy of the start file from the SAM Web site. ) 2. The Test cross worksheet contains a table of individual student scores and a table for the grading curve. In the Test Score worksheet, the set up F4G8 will contain the lower and top(prenominal) orbital cavitys for each earn grade.Add the missing swiftness aim set in the chemical chain G4G7 by inserting convenings in each of those kiosks that deems the speed function for each garner grade as being one point lower than the lower regularise of the next letter grade. Any changes to the numeric care fors in F5F8 should result in changes to the metric cheers in G4G7. 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 students final score, the table array is the cell hurtle $F$4$H$8, the column index summate is 3, and the lookup should find the closest match in the first column of the lookup table. repeat the formula in cell D4 into the vomit D5D223 to auspicate the grades for the rest of the students scores. 4. In cell I4 , use the COUNTIF function to count the supply 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 number number of the other letter grades charge under the current grading scale. In cell I9, calculate the meat number of all letter grades, verify that the total equals 220. 5. In the range J4J8, calculate the percentage of each letter grade designate 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 among 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) poorF, smallD, offsetC, LowB, and LowA for the determine in the range F4F8. b) HighF through and through High A for the determine in the range G4G8. c) PercentF through PercentA for the values in the range J4J8. ) DifferenceFromCurve to the value in cell L9. 8. come out 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 F4F8 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 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, s ubject to the reserve 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. Follow the directions on the SAM Web site to bar your completed project.