4) In the range F13:F45, insert a formula to calculate the 401(k) contributions for each employee....

  

Transcribed Image Text:

4) In the range F13:F45, insert a formula to calculate the 401(k) contributions for each employee. The formula should determine that if the number of years employed is greater than or equal to 2, then the contribution is equal to the contribution percentage in cell F4 multiplied by the employee's salary; otherwise, the contribution is zero. 5) In the range G13:G45, insert a formula to calculate the health insurance cost for each employee by testing whether the name of the employee's plan is equal to the name of the plan in cell B4. If it is, then the cost of the health plan is equal to the value of cell C4, otherwise; the cost is equal to the value of cell C5. 6) In the range B7:B9, enter the formulas to calculate the total salaries, 401(k) contributions, and health insurance costs. A 1 Payroll Information 2 Today's Date 3 4 Health Plans 5 6 7 Total Salaries 8 9 Total Health Insurance 10 11 Total 401(k) Contributions 12 Employee 13 Abbot 14 Anderson 15 Baker 16 Clausen 17 Czechiski 18 Dawes 19 Dearborn 20 Derks 21 Eagleton 22 Evans 23 Fawkes 24 Freeborn Ready Documentation Accessibility: Good to go B 5/1/2022 Premier Standard Date Hired 2/28/2018 12/31/2009 12/31/2020 6/1/2015 4/30/2016 1/31/2018 4/30/2005 3/31/2022 3/31/2022 5/1/2019 11/30/2016 7/31/2010 Payroll C $6,500 $5,500 (+) Years 4 12 1 7 6 0 D Salary Health Plan $50,000 Premier $65,000 Premier $22,500 Standard $41,000 Standard $43,000 Standard 4 $21,500 Standard 17 $81,000 Premier $39,000 Standard $39,000 Standard $41,500 Premier $49,500 Premier ܘ ܚ ܗ ܘ E 401(k) Costs 12 $61 500 Premier F 3% G 401(k) Health Insurance H 4) In the range F13:F45, insert a formula to calculate the 401(k) contributions for each employee. The formula should determine that if the number of years employed is greater than or equal to 2, then the contribution is equal to the contribution percentage in cell F4 multiplied by the employee's salary; otherwise, the contribution is zero. 5) In the range G13:G45, insert a formula to calculate the health insurance cost for each employee by testing whether the name of the employee's plan is equal to the name of the plan in cell B4. If it is, then the cost of the health plan is equal to the value of cell C4, otherwise; the cost is equal to the value of cell C5. 6) In the range B7:B9, enter the formulas to calculate the total salaries, 401(k) contributions, and health insurance costs. A 1 Payroll Information 2 Today's Date 3 4 Health Plans 5 6 7 Total Salaries 8 9 Total Health Insurance 10 11 Total 401(k) Contributions 12 Employee 13 Abbot 14 Anderson 15 Baker 16 Clausen 17 Czechiski 18 Dawes 19 Dearborn 20 Derks 21 Eagleton 22 Evans 23 Fawkes 24 Freeborn Ready Documentation Accessibility: Good to go B 5/1/2022 Premier Standard Date Hired 2/28/2018 12/31/2009 12/31/2020 6/1/2015 4/30/2016 1/31/2018 4/30/2005 3/31/2022 3/31/2022 5/1/2019 11/30/2016 7/31/2010 Payroll C $6,500 $5,500 (+) Years 4 12 1 7 6 0 D Salary Health Plan $50,000 Premier $65,000 Premier $22,500 Standard $41,000 Standard $43,000 Standard 4 $21,500 Standard 17 $81,000 Premier $39,000 Standard $39,000 Standard $41,500 Premier $49,500 Premier ܘ ܚ ܗ ܘ E 401(k) Costs 12 $61 500 Premier F 3% G 401(k) Health Insurance H

Answer rating: 100% (QA)

the formulas to calculate the 401k contributions and health insurance costs for each employee and also calculate the total salaries 401k contributions and health insurance costs Calculating 401k contr... View the full answer