Case Study #1 Due Wednesday, December 13^th at 11:59p.m. Part A Instructions You are an instructor teaching a computer science course. You have decided to use Excel to create a gradebook to keep track of your students assignments.

1. Open the data file EL2-U1-SA1-Gradebook-DataFile that was provided with this project. Save the file with the name PartA_FirstName_LastName.
2. Rename Sheet1 to CompSci.
3. In column K, calculate the number of missing chapter assignments for each person. An assignment that is missing should have a score of 0.
4. In column L, calculate the overall average grade for each students chapter assignments and exam. The overall average grade is the average of the contents in columns D through J. Add the ROUND function to calculate the average to one digit after the decimal point. Format the range to display one digit after the decimal point.
5. In Row 14 for each chapter and exam, create an AVERAGEIF function to calculate the average grade for those students who actually received a grade other than 0. Format both Row 14 to show one digit after the decimal point.
6. In column M, calculate the final grade using a nested IF statement using the guidelines below:

A	93-100
B	85-92
C	77-84
D	70-77
F	69 and below

1. Use conditional formatting in the final grade cell using light red fill with dark red text for any student earning an F.
2. Create a named range called Final for the range M4:M12. In the Distribution of Grades section, use the named range and a function to calculate the number of students who have earned each grade.
3. Add a custom number format to the student number to insert SN- before the number in the cell.
4. Add a header to the worksheet; it should include your name at the left and the sheet name at the right.
5. Add additional formatting to the worksheet as necessary. Do not use a predefined table style to format the worksheet.
6. Save and upload to Blackboard as PartA_FirstName_LastName.

Part B Instructions

1. Open PreBulkSales.
2. Save the workbook with the name PartB_FirstName_LastName.
3. Select the range A4:I22 and create a PivotTable in a new worksheet as follows:
   1. Add the Category field as the report filter field.
   2. Add the distributor field as the rows.
   3. Sum the North, South, East, and West sales values.
   4. Name the worksheet PivotTable.
4. Apply formatting to the PivotTable to make the data easier to read and interpret.
5. Insert a Slicer for the Model and show the data for PD-1140, PD-1150, and PD-1155.
6. Move the Slicer pane under the PivotTable.
7. Create a PivotChart and move it to a separate sheet named PivotChart that graphs the data from the PivotTable in a 3-D Clustered Column Chart.
8. Move the legend to the bottom of the chart.
9. Apply the Style 3 format to the chart.
10. Make Sales the active sheet and then create Sparklines in the range J5:J22 that show the North, South, East, and West sales in a line chart.
11. Set the width of column J to 18 characters.
12. Customize the Sparklines by changing the Sparkline colour and adding data points (you determine which data points to show and what colour to make the points).
13. Type and appropriate label in cell J4 and add other formatting that will improve the appearance of the worksheet format at least two parts.
14. Save and upload to Blackboard as PartB_FirstName_LastName.

Part C Instructions

1. Open ACPremiumReview.
2. Save the workbook with the name PartC_FirstName_LastName.
3. Create a formula in cell G4 to display Yes if the number of at-fault claims is greater than one and the current rating is greater than two. Both conditions must test true to display Yes; otherwise, display No in the cell.
4. Create a formula in cell H4 to display Yes in the cell if either the number of claims is greater than two or the current deductible is less than $1,000.00; otherwise, display No in the cell.
5. Copy the formulas in cells G4 and H4 to the ranges G5:G23 and H5:H23, respectively. Deselect each range after copying.
6. Save and upload to Blackboard as PartC_FirstName_LastName.

Attachments:

case-study--1....docx