Exception Analytics Directions

The purpose of this assignment is to revisit the federal payroll tax calculations from the Payroll Taxes for a Medical Group exercise. In that project it was 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 payroll distributions.

The Excel workbook for this assignment contains two worksheets. The Payroll Details worksheet is a subset of the data that was used in the Payroll Taxes for a Medical Group 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 x .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) x .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. Note that when constructing the VLOOKUP function, you will not be able to search for an exact match.

AutoSave Off Pv= Excel Lab M5 - Exception Analytics (2) - Protecte... - Saved to this PC File Home Insert Page Layout Formulas Data Review View Automate Help PROTECTED VIEW This file has been verified by Microsoft Defender Advanced Threat Protection and it hasn't detected any threats. If you A3 fx Rollet, Heather Payroll Details Withholding Table Ready i? 67F Rain Q S 1 AutoSave Off Pv= Excel Lab M5 - Exception Analytics (2) - Protecte... - Saved to this PC File Home Insert Page Layout Formulas Data Review View Automate Help PROTECTED VIEW This file has been verified by Microsoft Defender Advanced Threat Protection and it hasn't detected any threats. If you A3 fx Rollet, Heather Payroll Details Withholding Table Ready i? 67F Rain Q S 1