80.2K

Verified Solution

Link Copied!

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. Select the ranges C6:C13, E6:K6, and E14:K14. Apply the Accounting Number Format (be sure it includes a $) to these ranges.
2. Select the range E7:K13 and apply the Comma Style.
3. Select the range E14:K14 again, and apply the Total style from the Styles group.
4. At this point, the total Net Pay should be $4054.35. If not, review your formulas to double check that all values are calculated successfully.
5. Use appropriate functions and cell ranges in the range I18:K20 to find the average, highest, and lowest values for Hours Worked, Gross Pay, and Net Pay. Format the # of Hours values in this range using the Number format with one decimal place, and the Gross Pay and Net Pay values in this range with the Accounting Number Format with a $ and two decimal places.

Answer & Explanation Solved by verified expert

See Answer