91484835

, , , , , , , , , , , , , , , , – , , , , , , , , , , , , , , , , – , , , , , , , , , , , , , , , , – New Views excel 2010 , , , , , , , , , , , , , , , , – Tutorial 10: Case Problem 1 High Desert University Skills Define a scenario Perspective scenarios Edit a scenario Create a circumstance summary record Find an optimum solution applying Solver Switch on Solver Build Solver to locate a solution Make a Solver response report Conserve and load Solver models Job overview

Mentor Karen Reynolds teaches calculus at Excessive Desert School in Tempe, Arizona. The class has 230 students who are allocated among many sections and discussion groupings. Professor Reynolds wants to use Excel to determine the appropriate cutoff points on her behalf grading curve.

Generally, she wants to set the cutoff points in order that the following division of degrees is observed in the student physique: F five per cent D 10% C 35% B 35% A 15% Professor Reynolds has five possible grading curves. For example , in Grading Curve one particular, she will designate As to test out scores by 80 to 100.

Your woman wants you to evaluate every single grading curve scenario and determine what kind results in a distribution of grades nearest to her suggested distribution. When you choose which usually of the five scenarios fits the data the best, she desires you to use Solver to determine if there is a grading curve that is even closer to the desired distribution of levels. STUDENT begin FILE NP_Excel2010_T10_CP1a_FirstLastName_1. xlsx (Note: Download your personalized start file coming from www. cengage. com/sam2010) Recommendations Open the file NP_Excel2010_T10_CP1a_FirstLastName_1. lsx and save the file because NP_Excel2010_T10_CP1a_FirstLastName_2. xlsx before you move to the next thing. Verify that your name looks in cellular B4 with the Documentation sheet. (Note: Usually do not edit the Documentation piece. If your brand does not appear in cell B4, please down load a new backup of the commence file from your SAM Site. ) installment payments on your The Test Rating worksheet contains a table of specific student ratings and a table intended for the grading curve. In the Test Credit score worksheet, kids F4: G8 will contain the lower and upper ranges for each notification grade.

Add the lacking upper selection values inside the range G4: G7 by simply inserting formulas in each of those cells that calculates the upper selection for each letter grade as being one level lower than the reduced range of the next letter quality. Any becomes the numeric values in F5: F8 should result in changes to the calculated principles in G4: G7. three or more. In cellular D4, your VLOOKUP function to return the letter quality for the first college student in the list. The lookup value is the present student’s final report, the desk array is a cell selection $F$4: $H$8, the steering column index amount is a few, and the look for should find the best match inside the first line of the search table.

Replicate the solution in cell D4 in to the range D5: D223 to calculate the grades for the rest of the students’ scores. some. In cell I4, make use of the COUNTIF function to count the total number of letter degrees in the selection $D$4: $D$223 equal to the worth in cell H4 (e. g. “F). Copy your formula in the range I5: I8 to count the entire number of the other page grades designated under the current grading level. In cellular I9, estimate the total number of all notification grades, verifying that the total equals 220. 5. In the range J4: J8, calculate the percent of each letter grade assigned to the scholar body.

In cell J9, calculate the whole percentage of most letter levels, verifying the fact that total percentage equals completely. 6. Inside the range L4: L8, use the ABS function to estimate the absolute value of the big difference between the noticed percentage of every letter class and Professor Reynolds’ ideal percentage. In cell L9, calculate the overall value of such absolute distinctions. 7. Designate the following selection names: a) LowF, LowD, LowC, LowB, and LowA for the values in the range F4: F8. b) HighF through HighA pertaining to the ideals in the selection G4: G8. c) PercentF through PercentA for the values in the range J4: J8. ) DifferenceFromCurve to the value in cell L9. 8. Enter the values with the five grading curve cases named Grading Curve one particular through Grading Curve a few shown inside the chart listed below into your scenarios. Use the selection F4: F8 as your changing cells. Scenario Name Low F Low D Low C Low B Low A Grading Curve one particular 0 20 40 sixty 80 Grading Curve two 0 30 50 75 90 Grading Curve 3 0 60 65 eighty 95 Grading Curve 5 0 forty five 60 75 85 Grading Curve five 0 70 70 70 90 being unfaithful. Create a scenario summary report evaluating the results from each one of the five situations, displaying the values in the range J4: J8, L9 as your result cells. Take note: The closeness of each grading curve to Professor Reynolds’ optimal grading curve is usually expressed inside the value of cell L9. If there is ideal correspondence, the significance of cell L9 would be zero. ) twelve. Create a Solver model to minimize the value in cell L9 by changing the principles in the range F5: F8, subject to the constraint that every one of the ideals in the selection F5: F8 must be integers. Save the Solver version, selecting cellular L13 while the top cell holding the solver unit data. Save your valuable changes, close the workbook and get out of Excel. Stick to the directions on the SAM Internet site to submit your completed project.