Introduction: A small insurance company located in the Southeastern corner of Nebraska provides home and auto insurance to local customers. They intend to grow their business over the coming years to include more branches, agents, and markets. You have been given a snapshot of their data in an Excel file $($Links to an external site.$).$ save the file as your own copy to edit $--$ always append your name to the file$)$ You will need to perform some calculations from them.

Background Information: Premiums are calculated with sophisticated formulas to ensure the insurance company can make a return on the policy. In general, they want to make sure that over the life of the policy, they will mostly cover the value of the property through the premiums the customer pays. Different policies typically last different amounts of time, and of course insurance companies will invest premiums to come out ahead. You will perform calculations to determine how much the insurance company needs to charge to cover their investment. $($These formulas have been simplified, but real insurance companies will factor in many pieces of data to determine premiums.$)$

On the main $$data$$ sheet:

Divide the customer $$name$$ column into first name and last name columns

Setup a formula to calculate $($Annual$)$ Premium using the following information $($the formula should be the same in every cell of this column$)$:

If it is an Auto policy, the premium is: the coverage minus the deductible, then take this value and divide it by the amount $($number$)$ of monthly payments in $3$ years.

If it is a Home policy, the premium is: the coverage minus two times the deductible then take this value and divide it by the amount $($number$)$ of monthly payments in ten years.

If it is a Life policy, the premium is: the three$-$fifths the coverage amount then take this value and divide by the amount $($number$)$ of monthly payments in $40$ years.

Calculate $$Monthly Premium$$

Calculate $$Loyalty$$ which is a calculation of how long the customer has had this policy in days. Have this formula update using today$$s data if it is an active policy.

Calculate $$LoyaltyYears$$ which determines how many years the customer has had this policy, rounded to two decimal places.

Calculate $$Current$$ using an If statement to display the word $$Current$$ if the policy is active, and nothing if it is no longer active.

Calculate totals for all of the currency variables using the sum function.

Format the data:

Add a double underline for the totals calculations.

Format all currency variables appropriately.

Format LoyaltyYears to two decimal places.

Resize columns to be only as large as they need to be to display all of the data.

Create a second sheet, title it Analysis

Insert a pivot table to determine how the total amount $($not count$)$ of premiums and deductibles collected by each agent.

Insert a second pivot table to determine the average deductibles, coverage, and monthly payment by each policy type. Include a filter and only select the current policies.

Give the tables appropriate headings $($change the font, size, and color$)$

Create a third sheet that is a copy of the Data Sheet and name it $$Sorted Data$$

Sort by smallest to largest policy number $($making sure to keep all of the data with each row$)$

Add a fourth sheet titled $$Lookup$($do NOT copy the data to this tab as well$).$

At the top, use $$Data Validation$$ to create a dropdown of the policy numbers

Use this value in your VLOOKUP so that only one policy is displayed at a time.

Using the VLOOKUP function create a table of summary information by policy $($First name, last name, current, years, type, agent, deductible, monthly premium, and coverage$).$

Give it a title, format the cells appropriately

Create a pie chart of your pivot table that shows what percentage of premiums come from each agent $($reference data, do not copy it to a new location$).$

Place it on it's own sheet $($not as an object in a worksheet$)$

Give it an appropriate title

Display the agent names and what percentage they make in the pie chart, not as a key

Add a header so that when it prints $($and not directly on the sheet$)$ it displays $$Prepared by YOUR NAME$$