The medical group offers its employees an additional retirement benefit based on the employee's position and years of service. Employees who are with the practice 3 or more years will receive an additional contribution to their retirement account Calculate this benefit by entering an IF function in cell M3 on the Payroll Details worksheet. If an employee is a physician and has been working in the medical group for 3 or more years, the function should multiply the Gross Pay This Week by 896. For all other employees who have been working at the medical group for 3 or more years, the function should multiply the Gross Pay This Week by 5%. Copy and paste the function into the range M4:M22 Enter a formula into cell N3 on the Payroll Detalls worksheet that calculates the Total Retirement Benefits Your formula should add the 401K Company Match value to the Retirement Benefit value. Copy and paste the formula into the range N4:N22 Enter a formula into cell 03 on the Payroll Detalls worksheet that calculates the Net Payment for each employee. Your formula should subtract the values in the range 13:K3 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 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 Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range B4:37. 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 Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range 04.07. 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 Enter a SUMIF function into cell E3 on the Payroll Summary worksheet. The function should sum the Federal Tax withholdings in the range J3:J22 on the Payroll Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range E4:E7 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 Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range 14:47. 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 Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range G4:47 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:38. Then format the range 08:G8 to the Accounting number format. Total Retirement Retirement Benefit Benefits Net Payment Employee Last Name, First Name Position Rollet, Heather PA Lake, Connie PA Antoine, Gerry Kade, Michael PA Long, Shari Physician James, Don Physician Patel, Sanjev Physician Washington, Janet Physician Shin, Burt Physician Kumar, Denise Physician Xu, Weimin Physician Dixey, Bernard Physician Blane, John Physician Li, Wei Staff Blanch, Maurice Staff Wan, Stanley Staff Harlen, Lori Staff Hothe, Peggy Staff Michaels, Angela Staff Daniels, Max Staff Practice Orthopedics Obstetrics Adult Internal Med Pediatrics Adult Internal Med Orthopedics Adult Internal Med Pediatrics Obstetrics Obstetrics Orthopedics Orthopedics Pediatrics Group Adult Internal Med Pediatrics Group Obstetrics Group Orthopedics Little Hills Medical Group Payroll Detail 401K Years of Pay Year to Gross Pay Social Medicare Federal 401K Company Service Date This Week Security Tax Tax Total FICA Tax Retirment Match 0.11 $ 20,400 $ 600 $ 25.20 $ 8.70 $ 33.90 $ 120.00 $ 18.00 0.21 $ 22,950 $ 675 / $ 28.35 $ 9.79 | $ 38.14 $ 135.00 $ 27.00 0.51 $ 24,820 $ 730 $ 30.66 $ 10.59 $ 41.25 $ 146.00 $ 36.50 0.8 $ 29,410 $ 865 $ 36.33 $ 12.54 $ 48.87 $ 173.00 $ 51.90 1 $ 78,200 $ 2,300 $ 96.60 $ 33.35 $ 129.95 $ 575.00 $ 207.00 2 $ 71,910 $ 2,115 $ 88.83 $ 30.67 $ 119.50 $ 528.75 $ 190.35 4 $ 91,800 $ 2,700 $ 113.40 $ 39.15 $ 152.55 $ 675.00 $ 216.00 41 $ 62,050 $ 1,825 $ 76.65 $ 26.46 $ 103.11 $ 456.25 / $ 127.75 6 $ 104,550 $ 3,075 $ - $ 44.59 $ 44.59 $ 768.75 $ 317.00 7 $ 115,600 $ 3,400 $ - $ 49.30 $ 49.30 $ 850.00 $ 317.00 8 $ 118,150 $ 3,475 $ - $ 50.39 $ 50.39 $ 868.75 $ 317.00 10 $ 137,190 $ 4,035 $ - $ 58.51 $ 58.51 $ 1,008.75 $ 317.00 16 $ 147,050 $ 4.325 $ - $ 62.71 $ 62.71 $ 1,081.25 $ 317.00 0.71 $ 19,550 $ 575 $ 24.15 $ 8.34 $ 32.49 $ 115.00 $ 23.00 1 $ 19,618 $ 577 $ 24.23 $ 8.37 $ 32.60 $ 115.40 $ 34.62 1 $ 29,410 $ 865 $ 36.33 $ 12.54 $ 48.87 $ 173.00 $ 43.25 21 $ 22,950 $ 675 $ 28.35 $ 9.79 $ 38.14 $ 135.00 $ 33.75 4 $ 26,180 $ 770 $ 32.34 $ 11.17 $ 43.51 $ 154.00 $ 30.80 6 $ 36,040 $ 1,060 $ 44.52 $ 15.37 $59.89 $ 212.00 $ 31.80 9 $ 39,270 $ 1,155$ 48.51 $ 16.75 $ 65.26 $ 288.75 $ _69.30 | The medical group offers its employees an additional retirement benefit based on the employee's position and years of service. Employees who are with the practice 3 or more years will receive an additional contribution to their retirement account Calculate this benefit by entering an IF function in cell M3 on the Payroll Details worksheet. If an employee is a physician and has been working in the medical group for 3 or more years, the function should multiply the Gross Pay This Week by 896. For all other employees who have been working at the medical group for 3 or more years, the function should multiply the Gross Pay This Week by 5%. Copy and paste the function into the range M4:M22 Enter a formula into cell N3 on the Payroll Detalls worksheet that calculates the Total Retirement Benefits Your formula should add the 401K Company Match value to the Retirement Benefit value. Copy and paste the formula into the range N4:N22 Enter a formula into cell 03 on the Payroll Detalls worksheet that calculates the Net Payment for each employee. Your formula should subtract the values in the range 13:K3 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 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 Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range B4:37. 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 Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range 04.07. 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 Enter a SUMIF function into cell E3 on the Payroll Summary worksheet. The function should sum the Federal Tax withholdings in the range J3:J22 on the Payroll Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range E4:E7 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 Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range 14:47. 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 Detalls worksheet for each Practice listed in Column A on the Payroll Summary worksheet Copy and paste the function into the range G4:47 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:38. Then format the range 08:G8 to the Accounting number format. Total Retirement Retirement Benefit Benefits Net Payment Employee Last Name, First Name Position Rollet, Heather PA Lake, Connie PA Antoine, Gerry Kade, Michael PA Long, Shari Physician James, Don Physician Patel, Sanjev Physician Washington, Janet Physician Shin, Burt Physician Kumar, Denise Physician Xu, Weimin Physician Dixey, Bernard Physician Blane, John Physician Li, Wei Staff Blanch, Maurice Staff Wan, Stanley Staff Harlen, Lori Staff Hothe, Peggy Staff Michaels, Angela Staff Daniels, Max Staff Practice Orthopedics Obstetrics Adult Internal Med Pediatrics Adult Internal Med Orthopedics Adult Internal Med Pediatrics Obstetrics Obstetrics Orthopedics Orthopedics Pediatrics Group Adult Internal Med Pediatrics Group Obstetrics Group Orthopedics Little Hills Medical Group Payroll Detail 401K Years of Pay Year to Gross Pay Social Medicare Federal 401K Company Service Date This Week Security Tax Tax Total FICA Tax Retirment Match 0.11 $ 20,400 $ 600 $ 25.20 $ 8.70 $ 33.90 $ 120.00 $ 18.00 0.21 $ 22,950 $ 675 / $ 28.35 $ 9.79 | $ 38.14 $ 135.00 $ 27.00 0.51 $ 24,820 $ 730 $ 30.66 $ 10.59 $ 41.25 $ 146.00 $ 36.50 0.8 $ 29,410 $ 865 $ 36.33 $ 12.54 $ 48.87 $ 173.00 $ 51.90 1 $ 78,200 $ 2,300 $ 96.60 $ 33.35 $ 129.95 $ 575.00 $ 207.00 2 $ 71,910 $ 2,115 $ 88.83 $ 30.67 $ 119.50 $ 528.75 $ 190.35 4 $ 91,800 $ 2,700 $ 113.40 $ 39.15 $ 152.55 $ 675.00 $ 216.00 41 $ 62,050 $ 1,825 $ 76.65 $ 26.46 $ 103.11 $ 456.25 / $ 127.75 6 $ 104,550 $ 3,075 $ - $ 44.59 $ 44.59 $ 768.75 $ 317.00 7 $ 115,600 $ 3,400 $ - $ 49.30 $ 49.30 $ 850.00 $ 317.00 8 $ 118,150 $ 3,475 $ - $ 50.39 $ 50.39 $ 868.75 $ 317.00 10 $ 137,190 $ 4,035 $ - $ 58.51 $ 58.51 $ 1,008.75 $ 317.00 16 $ 147,050 $ 4.325 $ - $ 62.71 $ 62.71 $ 1,081.25 $ 317.00 0.71 $ 19,550 $ 575 $ 24.15 $ 8.34 $ 32.49 $ 115.00 $ 23.00 1 $ 19,618 $ 577 $ 24.23 $ 8.37 $ 32.60 $ 115.40 $ 34.62 1 $ 29,410 $ 865 $ 36.33 $ 12.54 $ 48.87 $ 173.00 $ 43.25 21 $ 22,950 $ 675 $ 28.35 $ 9.79 $ 38.14 $ 135.00 $ 33.75 4 $ 26,180 $ 770 $ 32.34 $ 11.17 $ 43.51 $ 154.00 $ 30.80 6 $ 36,040 $ 1,060 $ 44.52 $ 15.37 $59.89 $ 212.00 $ 31.80 9 $ 39,270 $ 1,155$ 48.51 $ 16.75 $ 65.26 $ 288.75 $ _69.30 |
