Illustrated Excel 2019 | Module 8: End of Module Project 2 Sutton Insurance Agency ANALYZE DATA WITH PIVOTTABLES GETTING STARTED Open the file IL_EX19_EOM8-2_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Illustrated Excel 2019 | Module 8: End of Module Project 2

Sutton Insurance Agency

ANALYZE DATA WITH PIVOTTABLES

• GETTING STARTED

• Open the file IL_EX19_EOM8-2_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_EOM8-2_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file IL_EX19_EOM8-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Rajeev Shah is a sales analyst for the Sutton Insurance Company in Nashville, Tennessee. He is using an Excel workbook to analyze sales of insurance policies and asks for your help in creating PivotTables and PivotCharts to provide an overview of the sales.
   Go to the Sales worksheet, which contains a table of data named Sales. Rajeev wants to create a separate table displaying the types of policies each agent sold and the total billed. Insert a PivotTable on a new worksheet based on the Sales table. Use Sales by Agent as the name of the worksheet. (Hint: Make sure the "Add this data to the Data Model" checkbox is unchecked.)
2. Add the Agent ID field to the Rows area of the new PivotTable. Add the Policy Type field to the Columns area of the PivotTable. Add the Total field to the Values area, where it appears as Sum of Total. Change the Value Field Settings for the Sum of Total values to apply the Accounting number format with 2 decimal places and the $ symbol.
3. Apply Ice Blue, Pivot Style Medium 11 (2nd row, 4th column in the Medium section of the PivotTable Styles gallery) to the PivotTable to echo the design of similar PivotTables. Hide the field headers in the PivotTable to make it more compact.
4. Go to the Policy Types worksheet, which contains a PivotTable named Policies. Remove the Agent ID field from the Rows area to focus on policy and sales types. Change the Report Layout to Compact Form so the PivotTable so it is easier to interpret. Turn off the Grand Totals for rows and columns, and show all Subtotals at the bottom of the group.
5. Rajeev wants to display the average handling rates for each sales type rather than the sum of handling rates in the Policies PivotTable. Change the Value Field Settings for the Sum of Handling values to determine the Average shipping rates. Use Average Handling as the custom field name, which appears as the column heading. Apply the Percentage number format with 1 decimal place.
6. Go to the Sales by Month worksheet, which contains a PivotTable named MonthlySales. Rajeev wants to provide another way to visualize this data. Create a Stacked Column PivotChart based on the MonthlySales PivotTable. Move and resize the PivotChart so that its upper-left corner is in cell A20 and its lower-right corner is in cell H35. Apply Layout 3 to the PivotChart to display the values on the columns. Add the chart title Sales by Month in its default location. [Mac hint: Add the chart title in the above position.]
7. Add a slicer to the PivotChart based on the Policy Type field. Position the slicer so that its upper-left corner is in cell I20 and its lower-right corner is in cell K32. Apply Ice Blue, Slicer Style Light 3 to coordinate with the other content on the worksheet.
8. Rajeev thinks the November total seems high in the MonthlySales PivotTable. Drill down into the November grand total amount in the PivotTable to display the details of the sales on a new worksheet. (Hint: Double-click the amount.) Use Nov Sales as the name of the new worksheet. Resize column D to a width of 18.00.
9. Go to the Sales by Type worksheet, which contains a PivotTable filtered to show sales data for June. Rajeev wants to compare all the sales, not only those for June. Change the PivotTable filter to compare data for all date values.
10. Go to the Sales Totals worksheet. Rajeev wants this PivotTable to focus on agents, not dates. Reorder the fields in the Rows area so that the PivotTable displays data first by Agent ID and then by Date.
11. Rajeev is interested in the revenue generated from homeowners policies, which the company wants to increase. Hide the PivotTable Field list so that you have more room to work, and then use the Policy Type slicer to filter the PivotTable and display sales for Homeowner policies only.
12. Return to the Sales worksheet. Rajeev wants to display the number of Group sales along with the number of other sales types.
    In cell M6, enter a formula that uses the GETPIVOTDATA function. Using Quantity as the data field, extract data from the MonthlySales PivotTable starting in cell A5 on the Sales by Month worksheet. Use an absolute reference to the cell. Select the grand total quantity amount for the Group sales type by using Sales Type as the field1 argument and Group as the item1 argument.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.