GETTING STARTED

·





Open the file

IL_EX19_CS5-8a_FirstLastName_1.xlsx, available for download from the SAM website.

·





Save the file as

IL_EX19_CS5-8a_FirstLastName_2.xlsx


by changing the “1” to a “2”.

o





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.

·





To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

o





Support_EX19_CS5-8a_Deductible.csv

·





With the file

IL_EX19_CS5-8a_FirstLastName_2.xlsx


still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

o





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

PROJECT STEPS

1.





Alex Fernandez recently became an insurance agent for CTC Casualty Insurance, a major insurance company. Although Alex has the support of CTC Casualty Insurance, he is working independently to track and analyze data about his insurance products and clients. He asks for your help in managing and analyzing data about his auto insurance policies.
Go to the

Auto Insurance


worksheet, which shows the coverage options clients can add to their insurance policies. Prepare for updating this worksheet as follows:

a.





Unprotect the worksheet so that you can edit the data.

b.





Unfreeze the first column, since the worksheet is not wide enough to scroll horizontally.

2.





Alex wants to include the name of the insurance company on each worksheet, including the

Clients


worksheet, which he has hidden to keep the data private.

a.





Unhide the

Clients


worksheet.

b.





Group the

Auto Insurance,

Premiums, and

Clients


worksheets so that you can edit and format them at the same time.

c.





In cell A1 of the

Auto Insurance


worksheet, type

CTC Casualty Insurance


to display the name of the company on each worksheet.

d.





Fill cell A1 with

Tan, Accent 2, Darker 50%


to coordinate with the company logo, and then ungroup the worksheets.

3.





Alex says he is likely to sort and filter the auto insurance data. Convert the range of data to a table as follows:

a.





On the

Auto Insurance


worksheet, format the auto insurance data in the range A3:E13 as a table with headers using

Orange, Table Style Medium 7.

b.





Fill the header row of the new table with

Tan, Accent 2, Darker 50%.

4.





The coverage options are offered in two types. Alex wants to make sure that anyone entering insurance data enters only "Standard" or "Add-on" in the Type column.
Create a data validation rule for the Type column as follows:

a.





For all cells in the Type column, create an in-cell dropdown data validation rule that accepts only entries from a list.

b.





Use

Standard, Add-on


as the Source values.

c.





Provide an input message that uses

Coverage Type


as the title and the following sentence as the input message:



Enter the type of insurance coverage.

d.





Set a Stop style error alert that uses

Entry Error


as the title and the following sentence as the error message:



Enter Standard or Add-on.

5.





The table is currently sorted by Coverage ID, but Alex prefers to have it sorted by Type so that he can list the standard coverages apart from the add-on coverages.
Sort the table in descending order by the values in the Type column.

6.





Alex has a text file containing data that compares deductible amounts and their typical monthly payments.


Import the text file as follows:

a.





Get data from the Text/CSV file

Support_EX19_CS5-8a_Deductible.csv.

b.





Edit the text file before loading it, making sure the first row appears as headers.

c.





In the Power Query Editor window, choose to close and load to a location in the worksheet.

d.





View the imported data as a table and insert the data in cell

A15


of the existing worksheet.

e.





Format the dollar amounts in the range A16:B20 using the

Currency


number format with

0


decimal places and the

$


symbol.

7.





Add hyperlinks to the workbook as follows to make it easier to navigate:

a.





In the

Auto Insurance


worksheet, make the text in cell F15 a hyperlink to cell

A1


of the

Premiums


worksheet.

b.





Include

Display annual premiums by age and state


as the ScreenTip text.

c.





Copy the hyperlink from cell F15 in the

Auto Insurance


worksheet to cell I1 of the

Clients


worksheet.

8.





Alex wants to examine the typical premiums charged by age for the four states he covers: Illinois, Michigan, Minnesota, and Wisconsin.
Go to the

Premiums


worksheet, which contains a table named Premiums, and then remove the filter from the Premiums table to display data for all four states.

9.





Alex wants to print a list of premium information for people in Illinois, Michigan, Minnesota, and Wisconsin who are younger than 35.
Use an advanced filter to provide this information for Alex as follows:

a.





Create an advanced filter that copies the results to another location.

b.





Use the Premiums table (range

A3:E35) as the List range.

c.





Use the data in the range

G3:G4


as the Criteria range.

d.





Copy the results to the range starting in cell

G6.

e.





Set the new range (range

G6:K18) as the print area.

10.





Alex wants to create a summary showing the average minimum premium for each state. Provide this summary for Alex as follows:

a.





Insert the

Sum of State Minimum by State


recommended PivotTable based on the data in the Premiums table.

b.





Use

Premiums Pivot


as the name of the new worksheet.

c.





Apply

Light Orange, Pivot Style Medium 13


to the PivotTable.

d.





Change the calculation for the State Minimum field to

Average.

e.





Change the number format of the Average of State Minimum field to

Currency


with

0


decimal places and the

$


symbol.

f.





Move the

Premiums Pivot


worksheet after the

Premiums


worksheet so that they appear in logical order.

11.





Alex wants to compare the average state minimum premiums with the average premium for a full-coverage policy.


On the

Premiums Pivot


worksheet, modify the PivotTable and add a PivotChart as follows to display the comparison information for Alex:

a.





Add the Full Coverage field to the Values area of the PivotTable.

b.





Change the calculation for the Full Coverage field to

Average, and change the number format to match the Average of State Minimum field.

c.





Insert a

Clustered Column


PivotChart that displays the data from the PivotTable on the same worksheet.

d.





Move and resize the PivotChart so that its upper-left corner is within cell A10 and its lower-right corner is within cell C24.

e.





Apply

Layout 2


to the PivotChart to display the values at the top of each column.

f.





Change the PivotChart colors to

Monochromatic Palette 5.

g.





Add the title

Average Premiums by State


to the PivotChart.

12.





Filter the PivotChart to display data for Illinois and Wisconsin only.

13.





Go to the

Clients


worksheet, which includes a table named Clients. CTC Casualty Insurance provides a discount of $10.00 per month for clients who bundle their insurance by buying more than one type of policy, such as auto and homeowners insurance. Alex wants to display the monthly payment amount, including the discount as appropriate, in the Payment column.
Provide this information for Alex as follows using an IF function and structured references:

a.





In cell H4, create a formula using the

IF


function that tests whether the value in the Bundled column ([@[Bundled?]]) is

"Yes".

b.





If the value in the Bundled column is Yes, subtract

10


from the amount in the Per Month column ([@[Per Month]]).

c.





Otherwise, the payment is the same as the value in the Per Month column.

d.





If necessary, fill the formula to the range H5:H48.

e.





Add a

Total Row


to the Clients table, which automatically totals the amounts in the Payment column.

f.





In cell B49, use the total row to display the count of the clients.

14.





Alex has created an area in the range J3:K8 for looking up data in the Clients table. First, he wants to find the name of the client by looking up the client ID. He has already entered the client ID in cell K3.
Look up the client name by using a

MATCH


formula to modify the INDEX formula as follows:

a.





In place of the 165 after the structured reference to the Clients table, insert the MATCH function.

b.





Match the value in cell

K3, and look it up using a structured reference to the

Client ID


column of the

Clients


table.

c.





Return an exact match.

15.





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

a.





In cell J6, 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 J6.


Calculate the total payments for a policy type as follows:

a.





In cell K7, 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

J5:J6


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 K8, 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

J5:J6.

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 G3 and its lower-right corner is in cell H17.

21.





Return to the

Clients

worksheet. In the range K10:K13, Alex wants to display the total payments received for each type of policy.
Display this information for Alex as follows:

a.





In cell K10, enter a formula that uses the

GETPIVOTDATA


function.

b.





Using

"Payment"


as the data field, extract data from the PivotTable starting in cell

A3


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 field1 argument and the name of the policy type (cell

J10

of the

Clients


worksheet) as the item1 argument.

d.





Fill the range K11:K13 with the formula in cell K10, 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 J3:K13.

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 A3:H48 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 A54 and its lower-right corner is in cell D67.

26.





Add

Policy Analysis


in the Categories document property so that Alex can find the workbook easily when he is searching for files.

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.