You may not have enough time during the organized lab time to finish this exercise. You will need to go to the U.C. user room at another time to finish it.
Work Hands-On Exercise 3, pages 111-122 with the following changes:
According to the text, the instructor's grading policy is to calculate the students weighted test average and then add 3 points if the student did "OK" on the homework. The result is then compared to the absolute 60, 70, 80, 90 scale in the Grading Criteria table. The alternate "Curve" grading system is similar but tends to give higher grades. However, suppose the instructor considers grading using a normal (bell shaped) distribution. Modify the sheet as follows to allow the instructor compare the results of grading assuming a bell shaped curve as compared with "no curve" and easy (curve) systems.
1. Move the homework bonus value from H19 to H20. Move the "HW Bonus" label from G19 to H19.
2. Copy the calculations for Average, High, Low, Range from the Test 4 column to the Test Avg. column.
3. Move the exam weights and labels from row 24 to 25.
4. In row 23, add the label "St. Dev." and formulas to calculate the standard deviation for each of the tests and the average. In this exercise, assume that the students in the class are a sample from a larger population of students who might take the class at some other time.
5. Add the "Criteria for Grading on a Curve" table to the spreadsheet in L38:N46. Prepare the table as instructed in parts 5a through 5c.
Criteria for Grading on a
Bell-Shaped Curve
Proportion Rank Cutoff
6% 0% 0.0
10% 6% 63.8
33% 16% 69.1
32% 49% 78.3
19% 81% 86.9
100% Total of proportions
6. Complete the labels in L2:N3 and L19:N25. Use simple formulas to set the cutoffs in column L to those int he "Criteria for Grading on a Curve" table.
7. In the column with the new "Grade" labels, calculate grades for each student by looking them up in the new "Criteria for grading on a bell-shaped curve" table. Lookup the "Semester Average" value in order to include the student's homework bonus. Hint: VLOOKUP(...) always uses the left most column of the lookup table to determine which row should be used for the result. Thus the lookup table should include the two columns below the Cutoff and Grade headers.
8. Format you spreadsheet to make it look nice. Use color, boxes, and so on as you believe is appropriate.
9. (By hand) how many grades will be changed if the instructor changes from the fixed (no curve) grading scale to the curve scale suggested in the bell-shaped curve criteria? How were they changed (up or down)?
10. (By hand) count the number of each "bell-shaped" grade earned by the students. Compare the percent specified by the proportions specified to the percentages actually earned.
11. Give several possible reasons why the actual grade distribution is quite different the one specified in part 5.
12. Print the current spreadsheet showing the "no curve" grades and those based on the bell shaped curve.
13. The instructor decides to change the proportions so that 5% fail, 15% get D's, 30% get C's, 30% get B's and 20% get A's. How does this change effect the grades of the students?
14. Print a copy of the spreadsheet. Also print the formulas as directed in step 13 of part 1. When printing formulas, make sure that all the formulas show completely but it is OK if the labels are only shown in part.
Hint: Make sure that you save the spreadsheet before you change column widths and margins to print the formulas. When you are finished printing the formulas, very carefully close the workbook without saving it and reload the workbook in its original look instead of trying "unadjust" the column widths and margins. Alternatively, to play it safe you may want to save the sheet showing formulas under a different name just before closing it.
Part 3. As owner of Tacoma Shirt Co., you must you are planning to make a batch of mens' shirts. SUPPOSE that men shirts sizes are determined by the following table.
Size Size XXX Small 14 XX Small 14-1/2 X Small 15 Small 15-1/2 Medium 16 Large 16-1/2 Extra Large 17 XX Large 17-1/2 XXX Large 18
SUPPOSE that men will buy size 14-1/2 if their actual "size" is between 14.25 and 14.75. They will buy size 15 if their actual size is 14.75 and 15.25. Likewise for other sizes. Further SUPPOSE that the custormers have shirt sizes that are normally distributed with a mean of 16.0 and standard deviation of .62. Before the lab, create a paper and pencil design for a spreadsheet to solve this problem. Based on your paper and pencil design, set up a spreadsheet to determine the number of each size of shirt you should produce. Format and label the spreadsheet appropriately. Be sure to shade the cells with input values (number of slippers to be produced, mean, standard deviation, the minimum numbers in the above table and the last maximum) to make it easy to replace the above values by more realistic ones. Unshaded cells should have formulas.
Create a chart showing the number of each size that should produced. Print the values and formulas for this spreadsheet.
Hint: Start your planning by drawing a normal curve and use the given data to label the curve. Design this spreadsheet on paper and determine the formulas before using the computer.
Your can use the Excel function NormDist(value, mean, stdev, TRUE) to calculate the "% less than ... " values..
(1 pt.)Also calculate the average GPA given the Bell shaped curve grading scale.
(2 pt.)Repeat the above for the originals grades in column J.
In part 3 (2 pts.), produce a chart showing the percentage of each size of shirts that should be produced.
If you did the extra credit for part 2, just make sure the extra cells show in the printouts. Also add a comment to the printout telling the paper grader that your did the options. Print out the extra chart if you did the option for part 3. Carefully label each item turned in so we know its purpose.
Lab09.html 4/7/2000