1. A5:K14 range. Please be sure to use your actual names for the last employee.

   Employee Name	No. of Dependents	Hourly Wage	Hours Worked	Regular Pay	Overtime Pay	Gross Pay	Taxable Pay	Federal Withholding Tax	FICA	Net Pay
   Bobby, Ricky	1	19.5	45
   Burgundy, Ron	2	16.50	32.5
   Butabi, Steve	2	16.65	28
   Crick, Harold	0	18	41
   Huff, Brennan	2	14.25	30
   Moon, Jackie	3	19	52
   Reinhold, Chazz	2	13	27.25
   YourLastName, YourFirstName	0	18	48
   Totals

2. Add the information below to cells A17:B21:

   Assumptions
   Base Work Hours	40
   Overtime Rate	1.5
   FICA Rate	7.65%
   Deduction per Dependent	$80.80

3. Add the information below to cells D17:E22:

   Taxable Pay	Tax Rate
   $0	10%
   $250	12%
   $600	22%
   $700	24%
   $925	32% Add the information below to cells H17:K20:

4. Summary Statistics	# of Hours	Gross Pay	Net Pay
   Average
   Highest
   Lowest

Questions:

1. Use an appropriate function (VLOOKUP) and formula with relative and absolute cell references to calculate the Federal Withholding Tax in cell I6. (Hints: use the VLOOKUP first, with the Lookup_value being the cell that contains Taxable Pay. Use absolute cell references for the range that contains the table used to return the Tax Rate. The second column of the table should be used for the Col_index_num, and leave the Range_lookup blank so it does not require an exact match. Once a tax rate is returned and shown in cell I6, press F2 to edit the cell contents and add *H6 after the closing ) of the VLOOKUP function.)
2. Use an appropriate formula with relative and absolute cell references to calculate the FICA in cell J6. The FICA is the value in the cell that contains Gross Pay times the value of the FICA Rate.
3. Use an appropriate formula with relative cell references to calculate the Net Pay in cell K6. The Net Pay is the Gross Payminus the Federal Withholding Tax minus FICA. The Net Pay for the first employee should be $652.48.
4. Copy all formulas down their respective columns, stopping at row 13.
5. Use AutoSum or Quick Analysis Tools to display the totals of Regular Pay, Overtime Pay, Gross Pay, Taxable Pay, Federal Withholding Tax, FICA, and Net Pay in row 14. (Please ensure that totals for No. of Dependents, Hourly Wage, and Hours Worked do not display. If they are added automatically, please delete them.)