Case Problem 1Data File needed for this Case Problem: NP_EX_7-3.xlsxSTEM Mentors Robert Harshaw is an Events Coordinator for STEM Mentors, a company special-izing in education software for High School STEM teachers. Every July, the company sponsors a conference to showcase its wares and provide informative speakers and workshops on technology in science and math education. After the conference, Robert compiles results from a survey to acts a guide for the next conference. Youll help Robert generate a report on the conference response. In the Survey Results worksheet, the answers to seven survey questions have been entered in an Excel table named Survey. The responses for the first four questions are the letters a through d, which rep-resent responses from very satisfied to very dissatisfied. The text of the survey questions is on the Survey Questions worksheet. Complete the following. 1. Open the NP_EX_7-3.xlsx workbook located in the Excel7 > Case1 folder included with your Data Files, and then save the workbook as NP_EX_7_STEM in the location specified by your instructor. 2. In the Documentation sheet enter your name and the date. 3. In the Survey Results worksheet, in the Workshops column, display text associated with answers to Q1 by clicking cell I6 and inserting the VLOOKUP function to do an exact match lookup with the Q1 field as the lookup value, the survey_lookup table as the lookup table, the second col-umn of that table as the return value, and the range_lookup value set to FALSE. 4. Repeat Step 3 for the Speakers through Meals field, using values of the Q2 through Q4 fields. (Hint: You can use AutoFill to quickly enter the formulas for the Speakers through Meals fields.) 5. In the School column, display the type of school of each attendee (Public, Private, Online, or Tutor) by clicking cell M6 and inserting the VLOOKUP function to do an exact match lookup of values in the Q5 field from the school_lookup table, returning values from the second column of that table, and setting the range_lookup value to FALSE. 6. In the Prior Conferences column, indicate the number of conferences previously attended (0, 1, 2, and 3+) by clicking cell N6 and inserting an approximate match look up of the values in the Q6 field using the conference_lookup table as the lookup table and returning the value from the second column of that table. 7. On the Report worksheet, do the following:a. In cell B14, use the COUNTIF function to count the number of records in the Return field from the Survey table that equal will return. b. In cell B15, calculate the difference between cell B11 and B14.c. In the range B18:B21, use the COUNTIF function to count the number of records of the School field in the Survey table that equal Public, Private, Online, and Tutor.d. In the range B24:B27, use the COUNTIF function to count the number of records in the Prior Conferences field of the Survey table that equal 0, 1, 2, and 3+. e. In cells C14, C15, C18:C21, and C24:C27, divide the counts you calculated for each response group by the total number of responses shown in cell B11 to express the values as percentages. 8. In the Survey Results worksheet, create a PivotChart, placing it in cell A4 of the PivotTables worksheet, and then do the following to analyze what factors might have contributed to a person decided against returning to next years conference:a. Name the PivotTable as workshop pivot.b. Place the Workshops field in the Columns area, the Return field in the Rows area, and the ID field in the Values area. 9. Make the following changes to the PivotChart:a. Move the chart to the Report worksheet to cover the range E7:I17.b. Change the chart type to the 100% Stacked Column chart.c. Remove the chart legend and field buttons from the chart.d. Add the chart title Workshop Satisfaction to the chart.e. Display the table associated with this chart by clicking the Data Tables check box in the Chart Elements menu. Verify that data table rows are arranged from top to bottom in the order Very Satisfied, Satisfied, Dissatisfied, and Very Dissatisfied. 10. Repeat Steps 8 and 9 to create a 100% Stacked column chart plotting the Speakers field against the Return field. Place the PivotTable in cell A10 on the PivotTables worksheet. Enter speaker pivot as the PivotTable name. Place the PivotChart in the range K7:O17 on the Report worksheet and add Speaker Satisfaction as the chart title. 11. Repeat Steps 8 and 9 to create a 100% Stacked column chart plotting the Facilities field against the Return field. Place the PivotTable in cell A16 on the PivotTables worksheet. Enter facility pivot as the PivotTable name. Place the PivotChart in the range E19:I29 on the Report worksheet and add Facility Satisfaction as the chart title. 12. Repeat Steps 8 and 9 to create a 100% Stacked column chart plotting the Meal field against the Return field. Place the PivotTable in cell A22 on the PivotTables worksheet. Enter meal pivot as the PivotTable name. Place the PivotChart in the range K19:O29 on the Report worksheet and add Meal Satisfaction as the chart title. 13. Click the first PivotChart to select it and then create a slicer for the School field. Move and resize the slicer to cover the range E2:O5 and then arrange the buttons in 4 columns. Connect the slicer to all four PivotTables in the workbook. 14. Use the School slicer to filter the PivotCharts to show only the summaries of the public school attendees. 15. Save the workbook, and then close it.