New Perspectives Excel 365 | Module 7: SAM Project A

Panoramic Insurance
SUMMARIZING DATA WITH PIVOTTABLES

GETTING STARTED
 Save the file NP_EX365_7A_FirstLastName_1.xlsx as
NP_EX365_7A_FirstLastName_2.xlsx
o Edit the file name by changing “1” to “2”.

o If you do not see the .xlsx file extension, do not type it. The file extension
will be added for you automatically.
 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.
o 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.
New Perspectives Excel 365 | Module 7: SAM Project A

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.
New Perspectives Excel 365 | Module 7: SAM Project A

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.
New Perspectives Excel 365 | Module 7: SAM Project A

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.
New Perspectives Excel 365 | Module 7: SAM Project A

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