Your Office Excel 2016 Business Unit 2 Prepared Exam ________________________________________ Computer Applications Gradebook As a student worker, you are an assistant to Dr. McGregor, an Information Technology professor. You have been asked to complete the gradebook to ensure all students get the grades they have earned. a. Open e02_exam_bu_data and save it as e02_exam_bu_LastFirst Enable the content if necessary. b. Rename the Sheet1 worksheet as Gradebook and rename Sheet2 as Grades Set the orientation of all sheets to Landscape c. On the Gradebook worksheet, insert a row above A1. Type Computer Applications 1001, Section 54 Apply bold to the title, and change the font to Arial 36 pt Merge and center A1:X1. d. In cells A2:X2, change the cell orientation to 90 degrees, and bold the font. Increase the font size to 12 pt. Select the entire worksheet and Autofit the column widths. e. In cell W3, sum the grades for row 3. Copy the formula down to W16. f. Insert a column before column X and type Grade for the heading. g. Format the range C3:X16 with 2 decimal places. Name the range X3:X16 as FinalGrade h. In cell X3, insert a formula that divides W3 by an absolute reference to the value in AA3 and multiplies by 100. Copy the formula down to X16. Format the result with 2 decimal points. i. In cell Y3, enter a VLOOKUP function that looks up the letter grade on the Grades worksheet. Make sure the cell range is an absolute reference. Copy the formula down to Y16 j. Delete rows 1820. In cells B27:B30, insert formulas that provide the average class grade, the minimum grade, the maximum grade, and the median grade using the named range FinalGrade. k. Apply the Retrospect theme to the workbook. Resize columns as needed. l. Apply the Title cell style to cell A1 and apply cell background color Tan, Accent 5, Lighter 80% m. In cell AA1, enter a date function that will insert todays date and update each time you open the workbook. Format the date as bold and 12 pt n. Create a 3-D Pie chart that visually depicts the percentages of the letter grade distribution. Use Class Grade Distribution as the chart title. Move the chart to a new chart sheet named Chart o. Add the Data Callout data labels in Outside End location, and change the font to bold and 16 point. Set Label Options to Category Name and Value. Delete the legend. Explode the slice for A grades. Move the chart sheet to after the Gradebook worksheet. p. Add the file name to the left page footer on all worksheets. q. Save the file and close Excel. Submit as directed by your instructor.