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

If you directly want to get the project from us then contact us on our Whatsapp. Link is given below,

https://wa.me/message/PPXGSJS2TLWUA1

Whatsapp Contact Number:
+91 8114420233

Email ID:
excelprojectshelp@gmail.com

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

PROJECT STEPS

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:

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.

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

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

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

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:

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.

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.

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

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

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:

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.

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

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:

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

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

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.

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

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

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:

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

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

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

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

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

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).

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

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:

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

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

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

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

Identify the record with the lowest average rating as follows:

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

Look up the

#samproject1a #samprojects #cengageassignments #NP_EX365_7a #NP_EXCEL #excel_assignments #excel_sam_projects #Module7SamProjecta #panoramic_insurance #new_perspectives_excel #NP_EX365_7a_FirstLastName_1.xlsx #NP_EX365_7a_FirstLastName_2.xlsx