Can you finish this?

Health Insurance Costs – Excel Project

Directions:

Open the file “Health Insurance Data_startfile” from Blackboard and save as “yourname_Health Insurance Data” and follow the directions below

For the Full-Time Worksheet

· Format titles and Headings

· Merge and Center the title from A1:K1 and format using cell style, fill colors, or text formatting

· Wrap text and adjust the width to see all column titles in row 2

· Apply Heading 3 cell style to column headers in row 2

· Enter a formula for Weekly Insurance Premium that multiplies the # of family members, Insurance Calculator, and Weekly Salary

· The Employee Contribution is 60% of the Weekly Insurance Premium. Create a formula to calculate the Employee Contribution. Hint: use multiplication

· Calculate the difference of the Weekly Insurance Premium and the Employee Contribution to determine the Employer’s Contribution

· Calculate the total (in row 11) for the Weekly Salary, Weekly Insurance Premium, Employee Contribution and Employer Contribution

· Calculate the Percentage of each Individual Employer Contribution compared to the total of all the Employer Contributions for the company. Hint: Divide Individual Employer Contribution by the total for all Employer Contribution

· Using an absolute cell reference in the formula

· Format percentages with percent sign and 2 decimal places

· Find the Average, Max and Min for the Weekly Insurance Premium, Employee Contribution, and Employer Contribution

· Format cells G12:I14 by applying the accounting number style

· Format the data F3:I10 (Weekly Salary, Weekly Insurance Premium, Employee and Employer Contributions) with 2 decimal places

· Format A11:I11 with the total cell style

Create a column chart by using the data on the Full time worksheet, that includes the Employee Name and Employee Weekly Salary

· Select non-adjacent cells to highlight the Employee Name A3:A10 and the Weekly Salary F3:F10 cells to create the column chart

· Move the Chart to its own New sheet

· Name the new sheet Weekly Salary Chart – Hint: the chart should take up the whole sheet

· Add a meaningful title to the chart and add a chart style

If Statement

Create an If Function to show the insurance code for the number of family members. Employees who have less than 3 family members will have the code INS2. Employees that have 3 or more family members will have the code INS3.

· In the Insurance Code Column, assign a code for each employee by using an If Function to show employees with 3 or more family members (“INS3”) OR families with less than 3 members (“INS2”)

Conditional Formatting

· Apply a Conditional Format to the Weekly Salary Column that will highlight any employee salary that is greater than $1000. Hint: Apply conditional formatting with a fill color for any cell greater than 1000.

Sparkline Charts

· In the Six-Year Weekly Salary worksheet, Create a Sparkline Column Chart for each employee over the course of the six years in the Sparkline Chart column

· Merge and Center the title from A1:H1 and format using cell style, fill colors, or text formatting

· Format the column titles in A3:H3 using any cell style, fill colors, or text formatting

Additional

· Add tab colors to all sheet tabs

· Add a header to the Full Time sheet with the date (left aligned)

· Re-order tabs in the following order – Six-Year Weekly Salary, Full Time, Weekly Salary Chart

Excel Project – Rubric
Format Headings in Full Time sheet · Merge and Center the title and format using cell style, fill colors, or text formatting (5pt) · Wrap text and adjust column width in Row 2 (3pt) · Applied Heading 3 cell style in Row 2 (2pt)	10
Calculated the Weekly Insurance Premium	5
Calculated the Employee Contribution	5
Calculated the Employer Contribution	5
Calculated the Percentage of the Employer Contribution (5pts) using an absolute cell reference (1pt) with % sign (2pts) and 2 decimal places (2pts)	10
Applied the Average, Maximum and Minimum Functions	15
Formatted cells in Full-Time sheet · Format cells G12:I14 by applying the accounting number style · Format the data F3:I10 with 2 decimal places · Format A11:I11 with the total cell style	10
Created a Column Chart (3pt), Moved chart to its own sheet (3pt), titled chart (2pt), and applied a chart style (2pt)	10
Applied an IF Function using Insurance Codes	5
Applied a Conditional Formatting	5
Inserted Column Sparkline Charts	5
Applied Worksheet tab colors	5
Add a header to the Full Time sheet with the date (left aligned)	5
Re-order tabs in the following order – Six-Year Weekly Salary, Full Time, Weekly Salary Chart	5
Project TOTAL	100

Excel Project – Health Insurance Costs 2020 Page 3 of 3