Can you finish this?

2

>

Six-Year

Weekly Salary

Six-Year Weekly Salary

$ 895.00

00.00

$ 475.00 $ 500.00

$ 475.00

$ 275.00

$ 500.00

$ 300.00

$ 250.00 $ 400.00 $ 400.00 $ 400.00 $ 750.00

$ 700.00 $ 750.00

Name	20		1	4	2015	2016	2017	2018	2019	Sparkline Chart
	Frieze, Victor	$ 600.00		$ 895.00	$ 540.00			$ 475.00	$ 1,045.00	$ 1,095.00
	Zhao, Feng	$ 450.00		$ 700.00	$ 795.00			$ 500.00	$ Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	945.00
	Banner, David		$			3	$ 650.00	$ 790.00	$ 1,200.00
	Jenkins Jr., Robert	$ 350.00		$ 275.00	$ 765.00	$	815.00
	Guttenberg, Monica	$ 975.00	$ 1,025.00	$ 1,075.00	$ 1,125.00	$ 1,175.00	$ 1,225.00
	Padilla, Jose			$	750.00		$ 250.00	$ 680.00	$ 800.00
	Watkins, Mary				$ 400.00
	Cavill, Henry	$ 820.00	$ 950.00	$ 1,000.00	$ 1,300.00

Full-Time

Weekly Salary

Frieze, Victor

3

Zhao, Feng

2

945.00

Banner, David Administration

1

750.00

INS2

Jenkins Jr., Robert

2 0.14 815.00

INS2

Guttenberg, Monica Administration Manager 3 0.11

INS3

Padilla, Jose Concessions Manager 4 0.11

INS3

Watkins, Mary Maintenance Manager 1 0.19

INS2

Cavill, Henry Concessions

3 0.11

INS3

Insurance Codes

INS2

INS3

Full Time Health Insurance Costs
Employee Name	Department	Job Title	# of family members	Insurance Calculator	Weekly Insurance Premium	Employee Contribution	Employer Contribution	Percentage of Employer Contribution		Insurance Codes
		Administration	Human Resources			Manager				0.11	1095.00	361.35	216.81	144.54	14.93%					INS3
		Concessions	Assistant Manager		0.14	264.60	158.76	105.84	10.94%					INS2
Executive Assistant		0.19	142.50	85.50	57.00	5.89%
	Maintenance	Foreman	228.20	136.92	91.28	9.43%
1225.00	404.25	242.55	161.70	16.71%
1150.00	506.00	303.60	202.40	20.91%
984.00	186.96	112.18	74.78	7.73%
Senior Attendant	987.00	325.71	195.43	130.28	13.46%
Total	7951	2419.57	1451.74	967.83
Average	$ 302.45	$ 181.47	$ 120.98
Maximum	$ 506.00	$ 303.60	$ 202.40
Minimum	$ 142.50	$ 85.50	$ 57.00
less than 3 family members
3 or more family members
* The Insurance Calculator may vary due to family size and health care plan option

Weekly Salary Chart

weekly salary sheet

Frieze, Victor Zhao, Feng Banner, David Jenkins Jr., Robert Guttenberg, Monica Padilla, Jose Watkins, Mary 0

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