I just need help calculating the Federal Tax column. Please use the IF statement on Excel and show the inputs. Thank you.
Federal Tax Net Pay Employees Job Position Jack Grey Chef Susan Grey Chef Mary Wright Chef John Berry Cashier Collyn Bear Cashier Chris Hill Wait Staff Laura Godwin Wait Staff Samantha Collins Wait Staff Ed Loyd Wait Staff Steve Little Driver Hours Worked 35 $ 40 $ 35 $ 33 $ 32 $ 23 $ 30 $ 34 $ 28 $ 25 $ Corner Bakery Payroll Report September 24, 2021 Hourly Wage Regular Pay Retirement 15.00 $ 525.00 $ 21.00 $ 15.00 $ 600.00 $ 24.00 $ 10.00 $ 350.00 $ 10.50 $ 10.00 $ 330.00 $ 9.90 $ 10.00 $ 320.00 $ 9.60 $ 7.00 $ 161.00 $ 4.83 $ 7.00 $ 210.00 $ 6.30 $ 7.00 $ 238.00 $ 7.14 $ 7.00 $ 196.00 $ 5.88 $ 8.00 $ 200.00 $ 6.00 $ Pay After Pre-Tax Deductions 504.00 $ 576.00 $ 339.50 $ 320.10 $ 310.40 $ 156.17 $ 203.70 $ 230.86 $ 190.12 $ 194.00 $ Social Security 32.26 36.86 21.73 20.49 19.87 Assumption #1 Tax Information Social Security Tax Federal Tax Annual Income Baseline $ Income Tax Bracket #1 Income Tax Bracket #2 6.40% 36,000.00 27% 22% 9.99 13.04 14.78 12.17 12.42 Assumption Box #2 Retirement Contribution Scale Retirement Regular Pay Percentage 20.86 Mike Nitsch Driver Gwen Manchester Manager Setphanie Baker Manager 42 $ 36 $ 45 $ 8.00 $ 15.00 $ 15.00 $ 336.00 $ 540.00 $ 675.00 $ 10.08 $ 21.60 $ 27.00 $ 325.92 $ 518.40 $ 648.00 $ 33.18 41.47 $ $ $ 400.00 800.00 3.0% 4.0% 5.0% Total Average Maximum 289.10 22.24 438 $ 33.69 $ 45 $ 23 $ 134.00 $ 4,681.00 $ 10.31 $ 360.08 $ 15.00 $ 675.00 $ 7.00 $ 161.00 $ 163.83 $ 12.60 $ 27.00 $ 4.83 $ 4,517.17 $ 347.47 $ 648.00 $ 156.17 $ 41.47 Minimum 9.99 Comments TABLE 10.1 Payroll Component Regular pay Retirement Contribution Formula (Hours worked *Hourly wage) (Vlookup function)*Regular Pay Vlookup is based on the Retirement Scale assumption box 2. Use absolute reference for the Retirement Contribution scale. Pay after pretax deductions Regular Pay-Retirement Contribution Social Security Pay after pretax deductions Social Security Tax Federal Tax If Statement=Logical test: Pay after pretax deduction*52>Annual Federal income tax baseline (assumption box 1) True: Pay after pretax deduction Assumption box 1, Tax bracket 1 False: Pay after pretax deduction * Assumption box 1, Tax Bracket 2 Net Pay (Pay after deductions)-(Social Security + Federal tax) Use absolute reference for the Social Security Tax 52 weeks in the year determines the employee's approximate annual salary Federal Tax Net Pay Employees Job Position Jack Grey Chef Susan Grey Chef Mary Wright Chef John Berry Cashier Collyn Bear Cashier Chris Hill Wait Staff Laura Godwin Wait Staff Samantha Collins Wait Staff Ed Loyd Wait Staff Steve Little Driver Hours Worked 35 $ 40 $ 35 $ 33 $ 32 $ 23 $ 30 $ 34 $ 28 $ 25 $ Corner Bakery Payroll Report September 24, 2021 Hourly Wage Regular Pay Retirement 15.00 $ 525.00 $ 21.00 $ 15.00 $ 600.00 $ 24.00 $ 10.00 $ 350.00 $ 10.50 $ 10.00 $ 330.00 $ 9.90 $ 10.00 $ 320.00 $ 9.60 $ 7.00 $ 161.00 $ 4.83 $ 7.00 $ 210.00 $ 6.30 $ 7.00 $ 238.00 $ 7.14 $ 7.00 $ 196.00 $ 5.88 $ 8.00 $ 200.00 $ 6.00 $ Pay After Pre-Tax Deductions 504.00 $ 576.00 $ 339.50 $ 320.10 $ 310.40 $ 156.17 $ 203.70 $ 230.86 $ 190.12 $ 194.00 $ Social Security 32.26 36.86 21.73 20.49 19.87 Assumption #1 Tax Information Social Security Tax Federal Tax Annual Income Baseline $ Income Tax Bracket #1 Income Tax Bracket #2 6.40% 36,000.00 27% 22% 9.99 13.04 14.78 12.17 12.42 Assumption Box #2 Retirement Contribution Scale Retirement Regular Pay Percentage 20.86 Mike Nitsch Driver Gwen Manchester Manager Setphanie Baker Manager 42 $ 36 $ 45 $ 8.00 $ 15.00 $ 15.00 $ 336.00 $ 540.00 $ 675.00 $ 10.08 $ 21.60 $ 27.00 $ 325.92 $ 518.40 $ 648.00 $ 33.18 41.47 $ $ $ 400.00 800.00 3.0% 4.0% 5.0% Total Average Maximum 289.10 22.24 438 $ 33.69 $ 45 $ 23 $ 134.00 $ 4,681.00 $ 10.31 $ 360.08 $ 15.00 $ 675.00 $ 7.00 $ 161.00 $ 163.83 $ 12.60 $ 27.00 $ 4.83 $ 4,517.17 $ 347.47 $ 648.00 $ 156.17 $ 41.47 Minimum 9.99 Comments TABLE 10.1 Payroll Component Regular pay Retirement Contribution Formula (Hours worked *Hourly wage) (Vlookup function)*Regular Pay Vlookup is based on the Retirement Scale assumption box 2. Use absolute reference for the Retirement Contribution scale. Pay after pretax deductions Regular Pay-Retirement Contribution Social Security Pay after pretax deductions Social Security Tax Federal Tax If Statement=Logical test: Pay after pretax deduction*52>Annual Federal income tax baseline (assumption box 1) True: Pay after pretax deduction Assumption box 1, Tax bracket 1 False: Pay after pretax deduction * Assumption box 1, Tax Bracket 2 Net Pay (Pay after deductions)-(Social Security + Federal tax) Use absolute reference for the Social Security Tax 52 weeks in the year determines the employee's approximate annual salary
