Analyzing the Learning Centers Data The Learning Center at your school has been tracking the students, including you, who attend tutoring sessions. Because you have been doing well in your Excel sessions, they have asked you to analyze the data that has been collected over the past two semesters. a. Open the Excel file, e03ch06Tutor. Save your file as e03ch06Tutor_LastFirst, using your last and first name. b. Create a copy of the StudentData worksheet, and then place it at the end of the workbook. Rename the StudentData(2) worksheet as StudentDataBackup. c. On the StudentData worksheet, insert a table with headers that uses the range $A$11:$G$211. With the data table selected, create named ranges using the top row as the names. d. Copy range A11:G11, and then paste the range in cell A1. In cell C2, type Sophomore. In cell D2, type Microsoft Office. e. Create an advanced filter using the data in range A1:G2. Filter the list in-place to display the filtered data on the StudentData worksheet. Copy the filtered data, and then paste it in cell A1 on the Filter worksheet. Resize the columns so all the data is visible. Press Ctrl+Home. f. On the StudentData worksheet, in cell H11, type TotalDue. In cell H12, enter a structured reference that multiplies TotalSessions and Rate. Format the TotalDue column as Currency with 0 decimal places. Create a named range for the TotalDue column that uses the column heading as the name. g. Insert a slicer for the Subject field. Click Science and Math in the Subject slicer. Drag the Subject slicer so the top left corner is in the top left corner of J1. Drag the bottom edge of the Subject slicer to adjust the height so that the extra white space is no longer visible. Apply Slicer Style Dark 5 to the slicer. h. Use the SUBTOTAL function to complete the following:. In cell F6, insert a formula that counts the number of cells in the LastName field that are not empty. In cell F7, insert a formula that averages the cells in the Rate field. In cell F8, insert a formula that sums the cells in the TotalDue field. i. Using the range $A$11:$H$211, insert a PivotTable in cell A10 on the Analysis worksheet. j. Configure the PivotTable using the following: Add Subject, LastName, and TotalSessions to the PivotTable. Move Subject to the Columns area. Sort the data in column H in descending order. Apply Pivot Style Dark 6 to the PivotTable. k. Remove LastName and TotalSessions from the PivotTable, and then modify the PivotTable so you can answer the second question. Add the TotalDue, Date, and ClassStanding fields to the PivotTable. Move Subject to the Rows area. Move ClassStanding to the Filters area. Format column B in the PivotTable as Currency with 0 decimal places. Modify the dates to be displayed by month. Modify the subtotals so they are displayed at the bottom of the group. In cell A10, use the Filter arrow to display data for Aug, Sep, Oct, Nov, and Dec. l. Modify the PivotTable so you can answer the third pivot analysis question. Add the FirstName field to the PivotTable. Move FirstName to the Values area. In cell B8, use the Filter arrow to display data for Sophomore. m. Modify the PivotTable to prepare for creating a PivotChart. Remove the FirstName field from the PivotTable. Remove the ClassStanding filter, and then move ClassStanding to the Columns area. Move the Date field as the filter. In cell A10, type Amount Due. In cell A11, type Subject by Month. n. Using the data in the PivotTable, insert a Clustered Column PivotChart. Format the PivotChart as follows. Move the PivotChart to a new sheet named RevenueChart. Add an Above Chart title. Replace Title with Revenue Generated by Class and Subject. Apply Style 8 to the PivotChart, and then change the color to Color 16. o. Complete the Documentation worksheet according to your instructors direction. p. Save the workbook, exit Excel, and then submit your file as directed by your instructor.