The purpose of this assignment is to revisit the federal payroll tax calculations that were demonstrated in the Careers in Practice: Payroll for a Medical Group exercise. In that exercise, we assumed that if the employees weekly income was less than or equal to $1,150, the federal tax was estimated at 20%. For any income over $1,150, the federal tax was estimated at 25%. However, the IRS publishes tables that provide more details as to how federal taxes should be calculated and withheld for weekly payrolls.

The Excel workbook for this assignment contains two worksheets. The Payroll Details worksheet is a subset of the data that was used for the Careers in Practice exercise. The Withholding Table worksheet contains part of an Excel worksheet that was published by the IRS. There are six levels of weekly income in Columns A and B of the worksheet. Columns C and D provide information for calculating the amount of federal tax that should be withheld. For example, if an employees weekly salary is $200, you would use Row 3 to calculate the amount of federal tax that should be withheld. This means you would first subtract the value in cell C3 from the weekly salary, which is: 200 111.15 = 88.85. Then the result of 88.85 is multiplied by the percentage in cell D3, which is: 88.85 .10 = 8.885. Therefore, the federal tax that should be withheld for an employee who is paid $200 a week is $8.885. Expressed as a formula, the calculation would be as follows: (200 111.15) .10.

Your assignment is to complete the Federal Tax calculations in Column F of the Payroll Details worksheet. You are required to reference the data in the Withholding Table worksheet such that if the tax rates change in the future (a very common occurrence), your federal tax calculations will automatically be updated. Hint: You will need to use the VLOOKUP function twice in your formula. Follow the example calculation carefully and manually check the output of your formula to determine whether you are producing an accurate result.

Please show the fomula and use excel!

Little Hills Medical Group Payroll Detail Employee Pay Year to Gross Pay Last Name, First Name Practice This Week Orthopedics Rollet, Heather Obstetrics Lake, Connie Antoine, Gerry PA Adult Internal Med S 24,820 730 PA Pediatrics S 29,410 865 Kade, Michael Physician Adult Internal Med S 78200 S Long, Shari 300 S 71,910 2,115 Physician Orthopedics Physician Adult Internal Med James, Don Patel, Sanjev S 91.800 S 2.700 S 62,050 1,825 Washington, Janet Physician Pediatrics Shin, Burt physician Obstetrics S 104,550 3,075 Physician Obstetric S 115,600 S 3,400 Kumar. Denise Xu, Weimin Physician orthopedics S 118,150 3,475 Orthopedics S 137.190 S 4.035 Physician Dixey, Bernard S 147.050 S 4.325 Blane, John Physician Pediatrics Li, We Staff Group S 19,550 575 Maurice Staff Adult Internal Med S 19618 577 Blanch Staff Pediatrics S 29,410 865 Wan, Stanley Group S 22,950 Staff Harlen, Lori 675 Hothe, Peggy Staf Obstetrics S 25.180 S 770 Staff S 36,040 Michaels, Angela 060 Staff Orthopedics S 39270 1,155 Daniels, Max