Purpose
Complete the following two Excel problems and submit completed Excel files for grading.
Instructions
Question 1: Download the “Calculating Course Grades” Excel file. Professor Rao would like to accurately calculate the grades for the 58 students in his Operations Planning and Scheduling class (OM 455). He has thus far constructed a spreadsheet, part of which follows:
Task description:
The course average is calculated by weighting the midterm score and final score 50% each. Use the VLOOKUP function with the table shown to generate the course grade for each student in cells E14 through E24.
Use the COUNTIF function to determine the number of students receiving each letter grade.
Question 2: Download the “Williamson” Excel file. Consider the transportation model in the Williamson file, which is very similar to the Foster Generators model discussed in this chapter. Williamson produces a single product and has plants in Atlanta, Lexington, Chicago, and Salt Lake City and warehouses in Portland, St. Paul, Las Vegas, Tucson, and Cleveland. Each plant has a capacity, and each warehouse has a demand. Williamson would like to find a low-cost shipping plan. Mr. Williamson has reviewed the results and notices right away that the total cost is way out of line. Use the “Formula Auditing” tool under the “Formulas” tab in Excel to find any errors in this model. Correct the errors. (Hint: The model contains two errors. Be sure to check every formula.)
Grading
For grading criteria, refer to the Written Assignment Rubric in the Getting Started section.
