Instructions: 1. For cell D16 you need to use Vlookup it is based on the job title of each employee. (3 columns in the table below) 2. Naming a range: the yellow cells in the table below-cells B31:D36 should be named "Rate" 3. Use the range named Rate in your vlookup D16 and copy the vlookup till cell D23 4. Use an IF calculations for over 40 hours, time and half pay in E16. Copy your if down till cell E23 5. For column F and G. You should refer to the cell F14 in the formula and G14 as well when you calculate the taxes. Use the fixed refrence-cell address ($F$1 for example) to refer to the 20% and the 7.65% 6. Finish all the calculation (hint: Gross-taxes=Net pay). Auto sum for cell e24 till H24 Lexington Hospital for Special Surgery Payroll Summary For the Week Ending November 16, 2020 EMPLOYEE NAME HOURS WORKED HOURLY RATE GROSS PAY FED INC TAX WITH 20% SOC SEC TAX WITH 7,65% NET PAY JOB TITLE Barnes Chen Clifford Gold Mangano Murphy Rashad Ruiz Totals Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Laboratory Specialist Technician Level 1 15 35 35 25 15 50 40 45 Employee Rate Schedule Hourly Rate Job Title Floor Assistant Laboratory Specialist Technician Level 1 Technician Level 2 Technician Level 3 Trainee 18.00 38.50 22.00 31.00 40.00 11.00 Instructions: 1. For cell D16 you need to use Vlookup it is based on the job title of each employee. (3 columns in the table below) 2. Naming a range: the yellow cells in the table below-cells B31:D36 should be named "Rate" 3. Use the range named Rate in your vlookup D16 and copy the vlookup till cell D23 4. Use an IF calculations for over 40 hours, time and half pay in E16. Copy your if down till cell E23 5. For column F and G. You should refer to the cell F14 in the formula and G14 as well when you calculate the taxes. Use the fixed refrence-cell address ($F$1 for example) to refer to the 20% and the 7.65% 6. Finish all the calculation (hint: Gross-taxes=Net pay). Auto sum for cell e24 till H24 Lexington Hospital for Special Surgery Payroll Summary For the Week Ending November 16, 2020 EMPLOYEE NAME HOURS WORKED HOURLY RATE GROSS PAY FED INC TAX WITH 20% SOC SEC TAX WITH 7,65% NET PAY JOB TITLE Barnes Chen Clifford Gold Mangano Murphy Rashad Ruiz Totals Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Laboratory Specialist Technician Level 1 15 35 35 25 15 50 40 45 Employee Rate Schedule Hourly Rate Job Title Floor Assistant Laboratory Specialist Technician Level 1 Technician Level 2 Technician Level 3 Trainee 18.00 38.50 22.00 31.00 40.00 11.00