New Perspectives Excel 365 | Module 7: SAM Project A Panoramic Insurance

GETTING STARTED

1. Save the file NP_EX365_7A_FirstLastName_1.xlsx as NP_EX365_7A_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_EX365_7A_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Shira Hayak is the assistant director of the claims center for Panoramic Insurance in Nashville, Tennessee. She has been tracking monthly insurance claim submissions in an Excel workbook and asks for your help in analyzing the data. Go the Claims Log worksheet. This worksheet contains a table named Claims that lists data about each claim submission. Shira wants you to list the policy type for each claim, which corresponds to the data in the Prefix column. Provide the policy types as follows:

a. In cell F6, enter a formula using the HLOOKUP function to look up the policy type. Use a structured reference to look up the value in the Prefix column.

b. Retrieve the value in row 2 of the Policy_Types named range on the Lookups worksheet.

c. Each policy type covers a range of prefix values, so find an approximate match (TRUE).

d. Fill the formula into the rest of the Policy Type column, if necessary.

2. Shira asks you to also list each claim type, which corresponds to the code in the Claim Code column. Provide the claim types as follows:

a. In cell I6, enter a formula using the XLOOKUP function to look up the claim type. Use a structured reference to look up the value in the Claim Code column.

b. Use the Codes named range on the Lookups worksheet as the lookup array. Return the corresponding value from the Claim_Types named range on the Lookups worksheet.

c. Specify an exact match or next larger item (1) as the match mode.

d. Fill the formula into the rest of the Claim Type column, if necessary.

3. When submitting a claim, customers rate their experience with Panoramic Insurance according to three criteria: policy coverage, value, and service. Shira entered the ratings in the Claims table and wants you to calculate the average rating for each customer claim as follows:

a. In cell M6, enter a formula using the AVERAGE function and structured references to average the values in the Coverage to Service columns of the Claims table.

b. Fill the formula into the rest of the Average column, if necessary.

4. The company policy is to make a follow-up call within one week to customers who submit claims on a renters insurance policy and to call customers within three days if they provide an average rating of 3.0 or less. Include the follow-up information as follows:

a. In cell N6, insert a formula using the IFS function.

b. Use a structured reference to test whether the Policy Type is "Renters" and display the text "1 week" if it is.

c. Use a structured reference to test whether the Average rating is less than or equal to 3 and display the text "3 days" if it is.

d. Add a final default argument to test whether any other condition is TRUE and display a blank cell ("") for that condition.

e. Fill the formula into the rest of the Follow Up column, if necessary.

5. Shira asks you to complete the Summary information in the range P4:R12. Calculate the number of claims made for each insurance agent as follows:

a. In cell Q6, enter a formula using the COUNTIF function.

b. Count the entries in the Agent column of the Claims table (Claims[Agent]) to display the number of claims for Fernandez (cell P6).

c. Fill the formula into the range Q7:Q12, filling without formatting.

6. Calculate the average rating associated with each insurance agent as follows:

a. In cell R6, enter a formula using the AVERAGEIF function.

b. Use the entries in the Agent (Claims[Agent]) and Average (Claims[Average]) columns in the Claims table to average the ratings for Fernandez (cell P6).

c. Fill the formula into the range R7:R12, filling without formatting.

7. For the Record Locator information in the range P14:Q17, Shira asks you to list the position of the records in the Claims table with the highest and lowest average customer rating. Identify the record with the highest average rating as follows:

a. In cell Q16, enter a formula using the XMATCH function.

b. Look up the highest rating listed in the Customer Satisfaction Ratings area (cell P21).

c. Use the entries in the Average column in the Claims table (Claims[Average]) as the lookup array.

d. Specify an exact match or next largest item (1) as the match mode.

8. Identify the record with the lowest average rating as follows:

a. In cell Q17, enter a formula using the XMATCH function.

b. Look up the lowest rating listed in the Customer Satisfaction Ratings area (cell P27).

c. Use the entries in the Average column in the Claims table (Claims[Average]) as the lookup array.

d. Specify an exact match or next largest item (1) as the match mode.

9. Go to the Claim Type by Agent worksheet. Shira asks you to compare the claims data by type for each agent. Insert a PivotTable to make this comparison as follows:

a. In cell B3, insert a PivotTable based on the Claims table.

b. Use AgentClaims as the name of the PivotTable.

c. Display the Agent names as column headings.

d. Display first the Claim Type and then the Policy Type data as row headings.

e. Use a count of the Claim Type data as values. If necessary, show all subtotals at the top of the group.

10. Format the AgentClaims PivotTable as follows:

a. Apply Lime, Pivot Style Light 17 to the PivotTable to coordinate with the table on the Claims Log worksheet.

b. Center the data in the range C5:J29 to make it easier to read.

c. In cell B4, use Claim Types to replace the "Row Labels" text.

d. In cell C3, use Agents to replace the "Column Labels" text.

11. Shira asks you to insert a chart that compares the agents with the top three claims. Add and filter a PivotChart as follows:

a. Insert a Stacked Column PivotChart based on the AgentClaims PivotTable.

b. Filter the PivotChart to show data only for agents Fernandez, Hawkins, and Khouri.

c. Resize and reposition the PivotChart so it covers the range H3:O23.

d. Change the PivotChart colors to Monochromatic Palette 2 to coordinate with the AgentClaims PivotTable.

e. Hide the field buttons to remove clutter from the PivotChart.

12. Go to the Ratings by Claim Type worksheet. Shira asks you to identify the average ratings by the types of claims made each month. Insert a PivotTable to provide this information as follows:

a. In cell B3, insert another PivotTable based on the Claims table.

b. Use MonthlyRatings as the name of the PivotTable.

c. Display the Date values as column headings, which automatically groups the data by month.

d. Display the Claim Type data as row headings.

e. Use the Average data as values.

13. Change the summary function to Average for the data in the Values area and apply the Number format with 1 decimal place to those values to make them easier to interpret. Apply Lime, Pivot Style Light 17 to the PivotTable to coordinate with the table on the Claims Log worksheet.

14. Shira asks you to focus on ratings for claims on Homeowner policies in April. Display only Homeowner data as follows:

a. Create a slicer for the MonthlyRatings PivotTable based on the Claim Type field.

b. Resize and reposition the slicer so it covers the range G3:H11.

c. Format the slicer using Lime, Slicer Style Light 2 to coordinate with the MonthlyRatings PivotTable.

d. Use the slicer to filter the MonthlyRatings PivotTable by the Homeowner claim type.

15. Use a timeline slicer to focus on the April claims as follows:

a. Create a timeline slicer based on the Date field in the MonthlyRatings PivotTable.

b. Apply the Lime, Timeline Style Light 2 style to the timeline slicer to coordinate with the MonthlyRatings PivotTable.

c. Resize the timeline slicer to a width of 6.9".

d. Move the slicer so it covers the range B13:H19.

e. Use the timeline slicer to show data for April only.

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 website to submit your completed project.

Final Figure 1: Claims Log Worksheet

Final Figure 2: Claim Type by Agent Worksheet

Final Figure 3: Ratings by Claim Type Worksheet

Final Figure 4: Lookups Worksheet