Grade and Grading Curve Scenario

- - - New Perspectives excel 2010 - Tutorial 10 Case line of work 1 High Desert University Skills Define a scenario View scenarios thin out a scenario gain a scenario summary report Find an best solution using convergent thinker Activate solver Set up Solver to find a solution relieve oneself a Solver answer report economize and load Solver models Project overviewprof Karen Reynolds t for individu each(prenominal)y onees calculus at High Desert University in Tempe, Arizona. The class has 220 students who are distributed among scads of sections and discussion groups. Professor Reynolds wants to use Excel to determine the appropriate cutoff points for her grading booze-up. Generally, she wants to set the cutoff points so that the following diffusion 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 bow 1, she pass on assign As to test scores from 80 to 100.She wants you t o 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 encompassing(prenominal) to the desired distribution of grades. STUDENT start FILE NP_Excel2010_T10_CP1a_FirstLastName_1. xlsx (Note Download your personalize start burden 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 next step. Verify that your make water appears in cell B4 of the Documentation 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 surface-to-air missile Web site. ) 2. The canvass Score worksheet contains a table of individual student scores and a table for the grading curve. In the Test Score worksheet, the swan F4G8 allow contain the lower and hurrying sicks for each earn grade.Add the missing upper range note places in the range G4G7 by inserting formulas in each of those cells that judges the upper range for each earn grade as be one point lower than the lower range of the next garner grade. Any changes to the numeric set in F5F8 should result in changes to the metrical jimmys in G4G7. 3. In cell D4, enter the VLOOKUP function to requite the letter grade for the first student in the list. The hunt 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 regard the keep down 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 flowing 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 pct of each letter grade assigned to the student body.In cell J9, calculate the total per centum of all letter grades, verifying that the total percentage equals 100 percent. 6. In the range L4L8, use the ABS function to calculate the supreme 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) number oneF, LowD, LowC, LowB, and LowA for the values in the range F4F8. b) HighF by HighA for the values in the range G4G8. c) PercentF through PercentA for the values in the range J4J8. ) DifferenceFro mCurve to the value in cell L9. 8. levy 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, subject to the reserve that all of the values in the range F5F8 must be integers. Save the Solver model, selecting cell L 13 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.