Illustrated Excel 2019 | Modules 5-8: SAM Capstone Project 1a CTC Casualty Insurance MANAGE FORMULAS, DATA, AND TABLES GETTING STARTED ? Open the file IL_EX19_CS5-8a_ FirstLastName _1

Subject:MS ExcelPrice:20.01 Bought23

Share With

Illustrated Excel 2019 | Modules 5-8: SAM Capstone Project 1a

CTC Casualty Insurance

MANAGE FORMULAS, DATA, AND TABLES

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

?

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:

?

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.

?

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.

Final Figure 1: Auto Insurance Worksheet

Final Figure 2: Premium Worksheet

Final Figure 3: Premiums Pivot Worksheet

Final Figure 4: Monthly Payments Worksheet

Final Figure 5: Clients Worksheet

Final Figure 6: Subtotals Worksheet