$15.$ Alex also needs to find the policy type for the Client ID entered in cell K$3.$ Look up the policy type as follows:

a$.$ In cell J$6,$ below the "Policy Type" text, begin to enter a formula using the VLOOKUP function to determine the policy type.

b$.$ The formula should look up the Client ID and return the value in the Policy Type column of the Clients table, using a structured reference to the table.

c$.$ Look up an exact match to the Client ID in the Clients table.

$16.$ The third calculation Alex wants to make is to determine the total payments for the policy type of the client he is looking up$,$ which now appears in cell J$6.$

Calculate the total payments for a policy type as follows:

a$.$ In cell K$7,$ begin to enter a formula using the DSUM function.

b$.$ Use structured references to the $[$#Headers$]$ and $[$#Data$]$ in the Clients table to specify the formula database.

c$.$ Use a structured reference to the Payment field header to specify the field to summarize.

d$.$ Use the values in the range J$5$:J$6$ as the criteria.

$17.$ Alex also wants to identify the number of a policies he has sold of a specified type. Calculate this information as follows:

a$.$ In cell K$8,$ begin to enter a formula using the DCOUNTA function.

b$.$ Based on the headers and data in the Clients table, and using structured references, count the number of values in the Policy Type column that match the criteria in the range J$5$:J$6.$

$18.$ Alex wants to create a separate table displaying the policies sold by date.

To provide this information, create and format a PivotTable as follows:

a$.$ Insert a PivotTable on a new worksheet based on the Clients table.

b$.$ Use Monthly Payments as the name of the worksheet.

c$.$ Use Payments as the name of the PivotTable.

d$.$ Display the start dates in rows.

e$.$ Display the policy types in columns.

f$.$ Display the sum of the payment amounts as values.

$19.$ Format the new PivotTable as follows to make it easier to interpret:

a$.$ Format the payment amounts as Currency using $0$ decimal places and the $ symbol.

b$.$ Group the dates by month.

c$.$ Hide the Field Headers to remove some clutter from the PivotTable.

$20.$ Add a slicer to the PivotTable as follows to make it easy for Alex to filter the data:

a$.$ Add a slicer to the PivotTable based on the Start Date field.

b$.$ Position the slicer so that its upper$-$left corner is in cell G$3$ and its lower$-$right corner is in cell H$17.$

$21.$ Return to the Clients worksheet. In the range K$10$:K$13,$ Alex wants to display the total payments received for each type of policy.

Display this information for Alex as follows:

a$.$ In cell K$10,$ enter a formula that uses the GETPIVOTDATA function.

b$.$ Using "Payment" as the data field, extract data from the PivotTable starting in cell A$3$ on the Monthly Payments worksheet. Use an absolute reference to the cell.

c$.$ Select the grand total quantity amount for Auto policies by using "Policy Type" as the field$1$ argument and the name of the policy type $($cell J$10$ of the Clients worksheet$)$ as the item$1$ argument.

d$.$ Fill the range K$11$:K$13$ with the formula in cell K$10,$ filling the range without formatting.

$22.$ Alex wants to calculate subtotals for payments received for policies that are bundled and those that are not. Because Alex also wants to be able to access other client data along with the subtotals, he asks you to perform this task on a copy of the Clients worksheet.

a$.$ Create a copy of the Clients worksheet and place it at the end of the workbook.

b$.$ Use Subtotals as the name of the copied worksheet.

c$.$ On the Subtotals worksheet, remove the Total Row from the table.

d$.$ Clear the contents and formatting from the range J$3$:K$13.$

e$.$ Sort the table in ascending order by the Bundled? column.

f$.$ Convert the table to a range.

$23.$ Include subtotals on the Subtotals worksheet as follows:

a$.$ Add subtotals to the range A$3$:H$48$ so that for each change in the Bundled? value, the formula uses the Sum function to add subtotals to the Payment field.

b$.$ Collapse the outline to display only the subtotals and the grand total.

$24.$ Alex wants to illustrate bundled insurance policies on the Subtotals worksheet. Add SmartArt to the worksheet as follows:

a$.$ Insert a Radial Venn SmartArt from the Relationships section of the SmartArt gallery.

b$.$ Type the following text in the SmartArt shapes:

Middle shape: Bundles

Top shape: Auto

Right shape: Renters

Bottom shape: Life

Left shape: Home

$25.$ Format and position the SmartArt as follows:

a$.$ Change the colors of the SmartArt to Colorful $$ Accent Colors.

b$.$ Move the SmartArt so that its upper$-$left corner is in cell A$54$ and its lower$-$right corner is in cell D$67.$

$26.$ Add Policy Analysis in the Categories document property so that Alex can find the workbook easily when he is searching for files.