Exercise 2 Payroll Details Worksheet (Columns G-0) M dical Group Payroll Detail Tax - . 401K Employee Total Social Medicare Total Federal 401K 2 Last Name. First Name Position Security Tax Tax Company Retirement Retirement Net FICA Retirment Match Benefit 3 Rollet Heather Benefits PA $ Payment 25.20 $ 8.70 $ 33.90 $ 120.00 $ 18.00 $ $ $ 4 Lake Connie PA $ $ 428.10 28.35 $ 9.79 S 38.14 $135.00 $ 27.00 $ - s 5 Antoine Gerry $ PA $ $ 474.86 30.66 S 10.59 $ 41.25 $ 146.00 $ 36 50$ $ - $ 6 Kade. Michael $ 506,26 PA $ 36.33 $ 12.54 $ 48.87 $ 173.00 $ 51.905 $ 7 Long Shani $ $ 591 23 Physicians 96.60 $ 33,35 $129.95 $575.00 $ 207.00 $ 103.505 . $ 103.50 $ 1.388.05 8 James Don Physicians 88.83% 30.67 $119.50 $ 528.75 $ 190,35 $95.18 5 $ 9 Patel Sanjev 95.18 5.1.276 40 Physicians 113.40$ 39.15 S 152.555 675.00 $ 216 00 $ 100.00 $ 216.00 $ 324.00 $1,656.45 10 Washington, Janet Physicians 76.65 $26.46 $103.11 $ 456 25 $ 127.75 $ 63.88 $ 146.00 $ 209.88 $ 1.137.89 11 Shin Burt Physicians $ 44,59 $ 44.59 $ 768.75 $317.005 158.50 246.00 $ 404,50 51.944.66 12 Kumar Denise Physicians $ 49,30 $ 49,30 $ 850,00 $ 317.00 $ 158.505 272.00 $ 430.50 $ 2.183.70 13 Xu, Weimin Physicians $ 50.39 $ 50.39 868 76 $ 317.00 $ 158,50 S 278.00 S 436 505 2.238.86 14 Dixey Bernard Physicians $ 58.51 $ 58.51 $1.008.75 $317.00 $ 158.50 $ 322.80 $ 48130 $ 2.650.74 15 Biano, John Physicians $ 62 71 $ 62.71 $1.081.25 $ 317.00 $ 158.50 $ 345.00$ 5045052 864.04 16 LI Wei Staff $ 24.15 $8.34 $ 32.49 $115.00 23.00 $ $ . $ - $ 404.51 17 Blanch Maurice Staff $ 24.2315 8.37 S 32.60 116 405 34.625 1731 $ 17315 394,38 10 Wan Stanley Staff $ 36.335 12.545 48 875 173.00 $ 43 25 $ 21.635 . $ 21.63 $599.88 19 Harlen Lor Staff $ 28.35 $ 9.79 S 38.14 $135.00 $ 33.75 $ 16.885 $ 16.88S 468.11 20 Hothe Peggy Staff $ 32 34 S 11,175.43.51 $154.00 5 30.805 15 40$ 38.50 $ 53.90541.70 21 Michaels Angela Staff $ 44.52 $ 15.37 5 59.89 $ 212.00 $ 31.805 15.90$ 5300 $ 68,90 5756.31 22 Daniels. Max Staff $ 48.51 $ 16.75 $ 65 26 288.755 69 30$ 34.65 $ 67275$ 92 405 731.69 - . 24 Payroll Summary Payroll Details Sheet AI Exercise 2 Payroll Summary Worksheet Little Hills Medical Group Payroll Summary B D E Little Hills Medical Group Payroll Summary 1 Retirement Number of Average Years Federal Tax Net Salary Benefit 2 Practice Employees of Service Total FICA Withheld Payments Payments 3 Pediatrics 4 5.45 $ 527.14 $ 1,883.50 $ 5,193.03 $ 736.00 4 Adult Internal Med 4 1.63 $ 712.69 $ 1,511.40 $ 3,945.13 $ 444.81 5 Obstetrics 4 4.30 $ 351.06 $ 1,907.75 $ 5.144.92 $ 888.90 6 Orthopedics 5 5.82 $ 655.10$ 2.815.00 $ 7.325.80 $ 1,105.38 7 Group 3 2.90 $ 261.03 $ 462.00 $ 1,628.94 $ 85.78 8 Total 20 $ 2,507.02 $ 8,579.65 $ 23,237.82 $ 3,260.86 9 10 11 12 13 * Payroll Summary Payroll Details Sheet3 9. Enter a COUNTIF function into cell B3 on the Payroll Summary worksheet. The function should count the number of employees in the range A3:A22 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:37. 10. Enter an AVERAGEIF function into cell 03 on the Payroll Summary worksheet. The function should calculate the average Years of Service irrthe range D3:D22 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:07. 11. Enter a SUMIF function into cell D3 on the Payroll Summary worksheet. The function should sum the Total FICA tax in the range 13:122 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. However, the government requires that employers match the FICA tax that is withheld from employees' paychecks. Therefore, multiply the result of this SUMIF function by 2. Copy and paste the function into the range D4:07
