Please complete the following on the Employee Data table $\backslash$table$[[,$A$,$B$,$C$,$D$,$E$,$F$,$G$,$H$,,$I,J$,$K$],[1,\backslash$table$[[$Emp$],[$ID$]],\backslash$table$[[$Last$],[$Name$]],$Hire Date,Birth Date,Gender,Location,$\backslash$table$[[$Job$],[$Status$]],$Pay Grade,Curren,nt Salary,Medical Plan,Vision Plan$],[2,1002,,8/28/2014,9/6/1966$ F$,,$NY$,$FT$,$A$,$$$,85,000,$SPOUSE$2500,$EMP$+$Spouse$],[3,1006,$orbes,$5/24/2010,12/15/1985$ F$,,$SF$,$FT$,$B$,$$$,40,000,$None,None$],[4,1010,$peulda,$4/24/2015,9/24/1968$ M$,,$SF$,$FT$,$B$,$$$,37,244,$FAMILY$1000,$EMP$+$Family$],[5,1014,,7/18/2014,8/9/1959$ M$,,$NY$,$FT$,$C$,$$$,80,000,$SPOUSE$1000,$None$],[6,1018,$anson,$8/21/2015,7/15/1950$ F$,,$NY$,$FT$,$A$,$$$,65,000,$FAMILY$1000,$EMP$+$Family$],[7,1022,,3/5/2015,5/2/1958$ F$,,$SF$,$FT$,$B$,$$$,125,000,$FAMILY$1000,$EMP$+$Family$],[8,1026,,3/1/2013,12/7/1977$ M$,,$SF$,$FT$,$C$,$$$,95,000,$FAMILY$2500,$None$],[9,1030,$khalaghi,$12/8/2015,12/4/1961$ F$,,$NY$,$FT$,$B$,$$$,36,000,$SPOUSE$1000,$EMP$+$Spouse$],[10,1034,$ankeure

$1.$ The $401$K Max Contribution column is $10\%$ of the Current Salary for each person.

$2.$ The Company Match column will be $7\%$ of the Current Salary for each person.

$3.$ The Bonus Amount is based on the Years of Service. Use the vlookup.

$4.$ The Salary Increase is $4\%$ for those who have worked $10$ or less years and $5\%$ for those

who have worked more than $10$ years. And yes, you get to use an IF function here! $$

$5.$ Calculate the Medical Plan and Vision Plan Cost using a vlookup. Your input will be

column J and K respectively.

$6.$ Make sure you calculate the Total Compensation which is the Current Salary plus all of

those things you just got done computing in columns N thru S$.$

Add two new sheets. Copy the entire Employee Data sheet and paste it onto each of the two new

sheets you just created, naming the first sheet Sort $1$ and the second sheet Sort $2.$ Complete the

following.

$1.$ Sort the Sort $1$ sheet so that the reader can use the information better using one column of

your choice.

$2.$ Sort the Sort $2$ sheet so that the reader can use the information better using two columns

of your choice.

$3.$ Open up a Word document, saving it as Project $2.$

$4.$ In the first paragraph, tell me which column you used to sort the Employee Data sheet

and why?

$5.$ In the second paragraph, tell which $2$ columns you used to sort the Employee Data sheet

and why?

Go back to the Employee Data sheet and using the information from this sheet, create a Pivot

Table and Chart on a new sheet using the following.

$1.$ Location, pay grade, the average current salary and the average total compensation

should be represented in the pivot table.

$2.$ Create the pivot chart from the pay grade information from each location.

$3.$ Copy both the pivot table and pivot chart to the Project $2$ Word document.

$4.$ What is your initial reaction to the difference in average current salary and the average

total compensation, to the different locations and to the different pay grades?Please complete the following on the Employee Data table

The $401$ K Max Contribution column is $10\%$ of the Current Salary for each person.

The Company Match column will be $7\%$ of the Current Salary for each person.

The Bonus Amount is based on the Years of Service. Use the vlookup.

The Salary Increase is $4\%$ for those who have worked $10$ or less years and $5\%$ for those

who have worked more than $10$ years. And yes, you get to use an IF function here!

Calculate the Medical Plan and Vision Plan Cost using a vlookup. Your input will be

column $J$ and $K$ respectively.

Make sure you calculate the Total Compensation which is the Current Salary plus all of

those things you just got done computing in columns N thru S $.$

Add two new sheets. Copy the entire Employee Data sheet and paste it onto each of the two new

sheets you just created, naming the first sheet Sort $1$ and the second sheet Sort $2.$ Complete the

following.

Sort the Sort $1$ sheet so that the reader can use the information better using one column of

your choice.

Sort the Sort $2$ sheet so that the reader can use the information better using two columns

of your choice.

Open up a Word document, saving it as Project $2.$

In the first paragraph, tell me which column you used to sort the Employee Data sheet

and why?

In the second paragraph, tell which $2$ columns you used to sort the Employee Data sheet

and why?