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 C3 on the Payroll Summary worksheet. The function should calculate the average Years of Service in the 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. 2 dical Group Payroll Detail simple MT 15 12222 PA OOTD SITET DESTINATION SEC101011 30 SIS 17BETON YOUT 2010 12. Enter a SUMIF function into cell E3 on the Payroll Summary worksheet. The function should sum the Federal Tax withholdings in the range 13:J22 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 E4:17. 13. Enter a SUMIF function into cell F3 on the Payroll Summary worksheet. The function should sum the Net Payments in the range 03: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 F4:57. 14. Enter a SUMIF function into cell G3 on the Payroll Summary worksheet. The function should sum the Total Retirement Benefits in the range N3:N22 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 G4:G7. Enter a SUM function in cell B8 on the Payroll Summary worksheet that sums the values in the range B3:37. Copy and paste this SUM function into the range D8:G8. Then format the range D8:G8 to the Accounting number format. 15. Save the workbook by adding your name in front of the current workbook name (i.e., "your name Chapter 3 CIP Exercise 2"). I 16. Close the workbook and Excel Figure 3.57Completed CIP Exercise 2 Payroll Details Worksheet (Columns G-0) HT 22323 ISBN Figure 3.58Completed CiP Exercise 2 Payroll Summary Worksheet Pretce Peutti Achat internat Med Obertries Orthopedics Group Tota! Lietle Hills Medical Group Payroll Summary Number of Former Average Year Federal Tax Net Salary Employees of Service Total FICA whold Payment 545352714 1.083,50 5.10.03 5 738.00 1.63271200 1.511.40 4 430136100 2,04513 1.00 7.75 511440 S 36206510 806790 5 2.815.00 725.00 S 20003 1053 402.00 102004 5 OD 70 20 $ 2,507.02 5 8,579.65 $ 23,237 02 3,260 86 luluw Exercise 2 Payroll Summary Worksheet A1 A little Hills Medical Group Payroll Summary A B D F Little Hills Medical Group Payroll Summary 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 S 261.03 $ 462.00 $ 1,628.94 $ 85.78 8 Total 20 $ 2,507.02 s 8,579.65 $ 23,237.82 $ 3,260.86 9 10 11 12 13 14 HK Payroll Summary Payroll Detalls CiP Exercise 2 Payroll Details Worksheet (Columns G-0) . M dical Group Payroll Detail 1401 K Total Employee Social Medicare Total Federal 401K Company Retirement Retirement Net 2 Last Name First Name Position Security Tax Tax FICA Tax Retirment Match Benefit Benefits Payment 3 Rollet, Heather PA $ 25.20 $ 8.70 $ 33.90 $120.00 $18.005 S $ $ 428.10 Lake. Connie $ 28.35 $ 9.79$ 38.14 $135.00$ 27.00 $14 $ $ $ 474.86 5 Antoine Gerry $ 30.66 $10.59 $ 41.25 $146.00 $ 36.505 5 $ S 50626 6 Kade. Michael PA S 36.33 $ 12.54 $ 48.8731 173.00 $61.905 $ $ $ 591.23 7 Long Shari Physicians 96 60 $ 33 35 $129.95 $1675 005207 00 $ 103,50$ $ 103.50 $ 1,388.05 James Don Physicians 8883 3067 $119.505 528.75 $190.36 $ 95.18$ $ 95,18 $ 1276.40 Patel, Sanley Physicians 113.40 39.15 S152 55 675,00 S 216.00 $ 108.00 $216.00 $324.00 5.1,656.45 10 Washington Jane! Physicians 76.65 $126.46 $103.11$ 456 25512775 $53.88$ 146.00 $ 209.88 $ 1.137 89 11 Shin Burt Physicians $ 44,599 44 59 1768.75 $317.005 158 50 5246.00 $ 404 50 51 94466 12 Kumar Denise Physicians . $ 49 30 S 49,30 R$ 850 00317.00 168 50$ 272.00 S430,505 2.183.70 11 Xu, Weimin Physicians . $ 50 39.5 50 39 368.75 5317 00 158.505 278,00 5 436 505 2,238,86 14 Dixey. Bernard Physicians 1515851 5 59.511151008,75 151317005 158.60S 322 805 481305.2.650.74 15 Blane John Physicians $62715 62 71 $1.081 255317.00 $ 158 80$ 346.00 5 504 50$ 2.86404 16 LI. Wel Staff 24.15 58.34 $ 32495111500123.00 5 $ $ $ 404.51 17 Blanch Maurice Staff $ 24.23 SHB37 S 32.60 $ 1154015 34 62 $117.31 $ $ 1731394 38 1 Wan Stanley 5 36,331512,545 48 87 5173.00 43.2531121835 $ 21.635.599.88 19 Harlon Lori Staff $ 28 30 319.79$ 38.14 3135.00 133 755 16 885 $ 16 88 468.11 20 Hotho Peggy Staff s 33.34 $11.175.43,51 154003130,80 SA 16.40 38.60S 53.90 5541.70 11 Michaels Angela Staff S 44 52 5116 37 359 893 21200 131805A 90 53.00 58.905 755,31 2 Daniels Max $ 48,515 116 755 65 265 288.75 51 69,301521 34 66 57.755 92.40 $ 731.69 - Starr . Staff 24 ya Payroll Details 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 C3 on the Payroll Summary worksheet. The function should calculate the average Years of Service in the 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. 2 dical Group Payroll Detail simple MT 15 12222 PA OOTD SITET DESTINATION SEC101011 30 SIS 17BETON YOUT 2010 12. Enter a SUMIF function into cell E3 on the Payroll Summary worksheet. The function should sum the Federal Tax withholdings in the range 13:J22 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 E4:17. 13. Enter a SUMIF function into cell F3 on the Payroll Summary worksheet. The function should sum the Net Payments in the range 03: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 F4:57. 14. Enter a SUMIF function into cell G3 on the Payroll Summary worksheet. The function should sum the Total Retirement Benefits in the range N3:N22 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 G4:G7. Enter a SUM function in cell B8 on the Payroll Summary worksheet that sums the values in the range B3:37. Copy and paste this SUM function into the range D8:G8. Then format the range D8:G8 to the Accounting number format. 15. Save the workbook by adding your name in front of the current workbook name (i.e., "your name Chapter 3 CIP Exercise 2"). I 16. Close the workbook and Excel Figure 3.57Completed CIP Exercise 2 Payroll Details Worksheet (Columns G-0) HT 22323 ISBN Figure 3.58Completed CiP Exercise 2 Payroll Summary Worksheet Pretce Peutti Achat internat Med Obertries Orthopedics Group Tota! Lietle Hills Medical Group Payroll Summary Number of Former Average Year Federal Tax Net Salary Employees of Service Total FICA whold Payment 545352714 1.083,50 5.10.03 5 738.00 1.63271200 1.511.40 4 430136100 2,04513 1.00 7.75 511440 S 36206510 806790 5 2.815.00 725.00 S 20003 1053 402.00 102004 5 OD 70 20 $ 2,507.02 5 8,579.65 $ 23,237 02 3,260 86 luluw Exercise 2 Payroll Summary Worksheet A1 A little Hills Medical Group Payroll Summary A B D F Little Hills Medical Group Payroll Summary 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 S 261.03 $ 462.00 $ 1,628.94 $ 85.78 8 Total 20 $ 2,507.02 s 8,579.65 $ 23,237.82 $ 3,260.86 9 10 11 12 13 14 HK Payroll Summary Payroll Detalls CiP Exercise 2 Payroll Details Worksheet (Columns G-0) . M dical Group Payroll Detail 1401 K Total Employee Social Medicare Total Federal 401K Company Retirement Retirement Net 2 Last Name First Name Position Security Tax Tax FICA Tax Retirment Match Benefit Benefits Payment 3 Rollet, Heather PA $ 25.20 $ 8.70 $ 33.90 $120.00 $18.005 S $ $ 428.10 Lake. Connie $ 28.35 $ 9.79$ 38.14 $135.00$ 27.00 $14 $ $ $ 474.86 5 Antoine Gerry $ 30.66 $10.59 $ 41.25 $146.00 $ 36.505 5 $ S 50626 6 Kade. Michael PA S 36.33 $ 12.54 $ 48.8731 173.00 $61.905 $ $ $ 591.23 7 Long Shari Physicians 96 60 $ 33 35 $129.95 $1675 005207 00 $ 103,50$ $ 103.50 $ 1,388.05 James Don Physicians 8883 3067 $119.505 528.75 $190.36 $ 95.18$ $ 95,18 $ 1276.40 Patel, Sanley Physicians 113.40 39.15 S152 55 675,00 S 216.00 $ 108.00 $216.00 $324.00 5.1,656.45 10 Washington Jane! Physicians 76.65 $126.46 $103.11$ 456 25512775 $53.88$ 146.00 $ 209.88 $ 1.137 89 11 Shin Burt Physicians $ 44,599 44 59 1768.75 $317.005 158 50 5246.00 $ 404 50 51 94466 12 Kumar Denise Physicians . $ 49 30 S 49,30 R$ 850 00317.00 168 50$ 272.00 S430,505 2.183.70 11 Xu, Weimin Physicians . $ 50 39.5 50 39 368.75 5317 00 158.505 278,00 5 436 505 2,238,86 14 Dixey. Bernard Physicians 1515851 5 59.511151008,75 151317005 158.60S 322 805 481305.2.650.74 15 Blane John Physicians $62715 62 71 $1.081 255317.00 $ 158 80$ 346.00 5 504 50$ 2.86404 16 LI. Wel Staff 24.15 58.34 $ 32495111500123.00 5 $ $ $ 404.51 17 Blanch Maurice Staff $ 24.23 SHB37 S 32.60 $ 1154015 34 62 $117.31 $ $ 1731394 38 1 Wan Stanley 5 36,331512,545 48 87 5173.00 43.2531121835 $ 21.635.599.88 19 Harlon Lori Staff $ 28 30 319.79$ 38.14 3135.00 133 755 16 885 $ 16 88 468.11 20 Hotho Peggy Staff s 33.34 $11.175.43,51 154003130,80 SA 16.40 38.60S 53.90 5541.70 11 Michaels Angela Staff S 44 52 5116 37 359 893 21200 131805A 90 53.00 58.905 755,31 2 Daniels Max $ 48,515 116 755 65 265 288.75 51 69,301521 34 66 57.755 92.40 $ 731.69 - Starr . Staff 24 ya Payroll Details