Alex Fernandez recently became an insurance agent for CTC Casualty Insurance, a major insurance company

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

  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.

 

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme