Question

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:236 should be named "Rate" 3. Use the range named Rate in your vookup D16 and copy the vookup till cell D23 4. Use an IF calculations for over 40 hours, time and half pay in E15. 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 SOC SEC TAX WITH TAX WITH 20% 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

Answer 1

Hi

As per the instructions in the question the solution is presented below. Pls note that, for your understanding I'm attaching the 2 snips below:

• 1 is for a solution in numbers.
• 2nd snip is the one which shows the formulas that have been used as per the instructions

Snip 1 : Solution with numbers as per the question instructions

A4 : fic А B D E F G H 1 J K L M N HomeworkLib expert comment: The blue arrows represent this is D16 cell in excel. Similarly all the cell references as per the question has been considered in solution here. Hourly rate 4 5 6 7 8 9 10 11 12 13 Employee Hours 14 Name Job title worked 15 16 Barness Trainee 15 17 Chen Technician Level 2 35 18 Clifford Technician Level 1 35 19 Gold Floor Assistant 25 20 Mangano Trainee 15 21 Murphy Technician Level 2 50 22 Rashad Laboratory Specialist 40 23 Ruiz Technician Level 1 45 24 25 26 27 28 29 30 Job title Hourly rate 31 Floor Assistant 18 32 Laboratory Specialist 38.51 33 Technician Level 1 22 34 Technician Level 2 31 35 Technician Level 3 40 36 Trainee 11 37 20 Gross FED INC SOC ING pay TAX with TAX with 20% 7.65% 111 165 -33 -13 31 1085 -217 -83 22 770 -154 -59 18 450 -90 -34 11 165 -33 -13 31 1395 -279 -107 38.5 1540 -308 -118 22 935 -187 -72 Net Pay 119 785 557 326 119 1009 1114 676

Snip 2 : Shows what formulas have been used as per instructions:

Arial 10 - AA ale Wrap Text General L Paste Qo BI U - A Insert Delete Format Merge & Center - $ - %, 60 .00 .00 2,0 Conditional Format as Cell Formatting - Table Styles Styles Sort & Find & Filter - Select - Clipboard Font Alignment Number Cells Editing A28 fr B D E F G H Chegg expert comment: The blue arrows represent This is D16 cell in excel. Similarly all the cell references as per the question has been considered in solution here. Hours worked Hourly rate Job title Gross pay A 4 5 6 7 8 9 10 11 12 13 Employee 14 Name 15 16 Barness 17 Chen 18 Clifford 19 Gold 20 Mangano 21 Murphy 22 Rashad 23 Ruiz 24 25 26 27 28 29 30 31 32 33 34 35 36 37 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 =VLOOKUP(B16, $B$31:$C$36,2) =VLOOKUP(B17,$B$31:$C$36,2) =VLOOKUP(B18, $B$31: $C$36,2) =VLOOKUP(B19 $B$31:$C$36,2) =VLOOKUP(B20,$B$31:$C$36,2) =VLOOKUP(B21, $B$31:$C$36,2) =VLOOKUP(B22 $B$31:$C$36,2) =VLOOKUP(B23,$B$31:$C$36,2) =IF(C16>40,((D16*40)+(((C16-40)*D16))/2),D16*C16) =IF(C17>40,((D17*40)+(((C17-40)*D17)}2),D17*C17) =IF(C18>40,(( D18*40)+(((C18-40)*D18)72),D18*C18) =IF(C19>40,((D19*40)+(((C19-40)*D19)}/2),D19*C19) =IF (C20>40,((D20*40)+(((C20-40)*D20)}2),D20*С20) =IF(C21>40,((D21*40)+(((C21-40)*D21)72),D21*C21) =IF(C22>40,((D22*40)+(((C22-40)*D22))/2),D22*C22) =IF(C23>40,((D23*40)+((C23-40)*D23)}/2), D23*C23) FED INC TAX with 0.2 =-E16*$F$15 =-E17*$F$15 =-E18*$F$15 =-E19*$F$15 =-E20*$F$15 =-E21$F$15 =-E22$F$15 =-E23*$F$15 SOC INC TAX with 0.0765 =-E16*$G$15 =-E17*$G$15 =-E18*$G$15 =-E19 $G$15 =-E20*$G$15 =-E21*$G$15 =-E22*$G$15 =-E23*$G$15 Net Pay =SUM(E16:G16) =SUM(E17.G17) =SUM(E18:G18) =SUM(E19:G19) =SUM(E20 G20) =SUM(E21 G21) =SUM(E22:G22) =SUM(E23:G23) Job title Floor Assistant Laboratory Specialist Technician Level 1 Technician Level 2 Technician Level 3 Trainee Hourly rate 18 38.5 22 31 40 11

Pls do let us know through comment section if you have any futher questions.

Happy to help :-)