Question

Bonus Homework for Week 5
Preparing a Weekly Payroll Report
Employee Name	Gross Weekly Pay		State Withholding Tax	Federal Withholding Tax	Net Pay
Sue Smith	$800			________________	________________
Tom Brown	$300			________________	________________
Mary Wilson	$250			________________	________________
James Jackson	$100			________________	________________
John Sinatra	$600			________________	________________
Betty Rockford	$1,255			________________	________________
Similar assignment as our in-class IF function work - you must calculate the amount of State Withholding Tax and Federal
Withholding Tax. Also calculate Net Pay. Watch-out - I've changed the numbers and percentages.
The difference with this assignment are the minimum tax amounts that apply at the $500 income level.
Given that difference and to help your formulas be less complex, I recommend using VLOOKUP rather than IF.
If the gross pay is less than $500, the state withholding tax is 8% and the federal withholding tax is 15%
If the gross pay is greater than or equal to $500 but less than $800, the state withholding tax is $40 plus 12% on income
over $500 and the federal withholding tax is $75 plus 20% on the income over $500
If the gross pay is greater than or equal to $800, the state withholding tax is $76 plus 20% on income
over $800 and the federal withholding tax is $135 plus 35% on the income over $800
Calculate the withholding taxes and the net pay for the above six employees. You may use the table above as a template
To solve this, I recommend first developing an inputs/assumptions table that will act as your VLOOKUP table.
You can then use VLOOKUP to extract key info you need to calculate tax liabilities for the employees.
THIS IS NOT ANY EASY ASSIGNMENT - YOUR FORMULAS AND VLOOKUP TABLE WILL BE A BIT COMPLEX!!
If you are successful at solving the problem, I will give you 10 bonus points. You must turn in
your completed spreadsheet with correct formulas to get credit.
YOU MAY USE INDEX/MATCH FUNCTIONS IF YOU THINK THAT WILL BE EASIER - MY
SOLUTION USES VLOOKUP

Answer 1