5%. Copy and paste the function into the range M4M22. 10. Enter a formula into cell N3 on the Payroll Details worksheet that calculates the Total Retirement Benefits. Your formula should add the 401K Company Match value to the Retirement Benefit alue. Copy and paste the formula into the range N4:N22. 1. Enter a formula into cell 03 on the Payroll Details worksheet that calculates the Net Payment for each employee. Your formula should subtract the values in the range 13K3 from the Gross Pay This Week in cell F3. The range 13 K3 includes the following: Total FICA (cell 13). Federal Tax (cell J3). and 401K Retirement (cell K3). Copy and paste this formula into the range 04:022 12. In the Lookup Employee worksheet, use the appropriate lookup function in cells B3, B4 and B5 to lookup the position, practice, and years of service for the employee that can be entered in cell B2. In the lookups, use a cell range named Employee Table to refer to the appropriate employee information Hint: create the cell range 13.Enter a COUNTIF function into cell B3 on the Payroll Summary worksheet. The function hould count the number of employees in the range C3 C22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range B4 B7 4. Enter an AVERAGEIF function into cell C3 on the Payroll Summary worksheet. Th function should calculate the average Years of Service in the range D3 022 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range C4:C7 15. Enter a SUMIF function into cell D3 on the Payroll Summary worksheet. The functi should sum the Total FICA tax in the range 13122 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. However, the gove requires that employers match the FICA tax that is withheld from emplo pay checks erefore. multiply the result of this SUMIF function by 2 Copy and paste the function the range D4:D7 Summary worksheet. The function Details Pavroll Little Hills Medical Group Payroll Detail Years of Pay Year to Gross Pay Social Service Date Medicare Position Practice Total FICA 25.20 $ 8.70 S 33.90 28.35 5 979 38 14 513 24820 730 S30.66 S 1059 S 4125 08 S 29410 S865 36 33 12543 4887 9660 3335 S 12995 2 71910 S 2115 8883 S 3067 S 119.50 This Week Security Tax Tax PA PA s PA 01 5 20.400 $ 02$ 22,950 800 S 675 S Obstetrics Adult Intermal Med Pediatrics 6 PA Physician Adut Internal Med 78,200 S 2300 S rthopedics 9 Physician Adut Intenal Med o Physician Pediatrics 43 91800 S 2700113.40 S 39 15 152 55 7665 $ 26.46 S 103.11 44.59 S 44.59 S 49.3049.30 S 50.39 S 50 39 $ 58.51 $ 58.51 62.71 S 6271 4 S 62,050 S 1 Physician 6 104,550 3,075 S 18115600 | $ 3.400 : $ 8 $ 118,150 $ 3475 S Obstetrio 13 Physician Orthopedics 14 Ptysician Orthopedics s Physician 6 Staff 17 Staff 8 Staff 19 Staff 10S 137 190 4,035 16 147050 S 4325 $ 195505575 24.15 834 S 324 Adult Intemal Med Pediatics 1 19618 S 1 S 29410 S 2S 22 950 S 4 26.180 S 577 $ 2423 S 837$ 3260 3633 S 12.54 $ 48 87 885 675 S 770 5 28.35 $9.79 5 38.1 20 Staff 21 Staff ObstetnCS 32.34 1117 4351 22 Staff 36,040 1060 44 52 S 15.37 $ 59 edics 9S 39270 S 1.155 S 4851 S 1675 $ 85 26 File Home InsertPage Leyout Formdas Ds Revie Vie MESIRA Lookup Employee Infeo Employee Name Position 4 Practice s Years of Service i0 IA i5 is, 18 5%. Copy and paste the function into the range M4M22. 10. Enter a formula into cell N3 on the Payroll Details worksheet that calculates the Total Retirement Benefits. Your formula should add the 401K Company Match value to the Retirement Benefit alue. Copy and paste the formula into the range N4:N22. 1. Enter a formula into cell 03 on the Payroll Details worksheet that calculates the Net Payment for each employee. Your formula should subtract the values in the range 13K3 from the Gross Pay This Week in cell F3. The range 13 K3 includes the following: Total FICA (cell 13). Federal Tax (cell J3). and 401K Retirement (cell K3). Copy and paste this formula into the range 04:022 12. In the Lookup Employee worksheet, use the appropriate lookup function in cells B3, B4 and B5 to lookup the position, practice, and years of service for the employee that can be entered in cell B2. In the lookups, use a cell range named Employee Table to refer to the appropriate employee information Hint: create the cell range 13.Enter a COUNTIF function into cell B3 on the Payroll Summary worksheet. The function hould count the number of employees in the range C3 C22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range B4 B7 4. Enter an AVERAGEIF function into cell C3 on the Payroll Summary worksheet. Th function should calculate the average Years of Service in the range D3 022 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range C4:C7 15. Enter a SUMIF function into cell D3 on the Payroll Summary worksheet. The functi should sum the Total FICA tax in the range 13122 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. However, the gove requires that employers match the FICA tax that is withheld from emplo pay checks erefore. multiply the result of this SUMIF function by 2 Copy and paste the function the range D4:D7 Summary worksheet. The function Details Pavroll Little Hills Medical Group Payroll Detail Years of Pay Year to Gross Pay Social Service Date Medicare Position Practice Total FICA 25.20 $ 8.70 S 33.90 28.35 5 979 38 14 513 24820 730 S30.66 S 1059 S 4125 08 S 29410 S865 36 33 12543 4887 9660 3335 S 12995 2 71910 S 2115 8883 S 3067 S 119.50 This Week Security Tax Tax PA PA s PA 01 5 20.400 $ 02$ 22,950 800 S 675 S Obstetrics Adult Intermal Med Pediatrics 6 PA Physician Adut Internal Med 78,200 S 2300 S rthopedics 9 Physician Adut Intenal Med o Physician Pediatrics 43 91800 S 2700113.40 S 39 15 152 55 7665 $ 26.46 S 103.11 44.59 S 44.59 S 49.3049.30 S 50.39 S 50 39 $ 58.51 $ 58.51 62.71 S 6271 4 S 62,050 S 1 Physician 6 104,550 3,075 S 18115600 | $ 3.400 : $ 8 $ 118,150 $ 3475 S Obstetrio 13 Physician Orthopedics 14 Ptysician Orthopedics s Physician 6 Staff 17 Staff 8 Staff 19 Staff 10S 137 190 4,035 16 147050 S 4325 $ 195505575 24.15 834 S 324 Adult Intemal Med Pediatics 1 19618 S 1 S 29410 S 2S 22 950 S 4 26.180 S 577 $ 2423 S 837$ 3260 3633 S 12.54 $ 48 87 885 675 S 770 5 28.35 $9.79 5 38.1 20 Staff 21 Staff ObstetnCS 32.34 1117 4351 22 Staff 36,040 1060 44 52 S 15.37 $ 59 edics 9S 39270 S 1.155 S 4851 S 1675 $ 85 26 File Home InsertPage Leyout Formdas Ds Revie Vie MESIRA Lookup Employee Infeo Employee Name Position 4 Practice s Years of Service i0 IA i5 is, 18