Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
- 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
- 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
- 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:
-
Summary Statistics # of Hours Gross Pay Net Pay Average Highest Lowest
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 |
Assumptions | |
---|---|
Base Work Hours | 40 |
Overtime Rate | 1.5 |
FICA Rate | 7.65% |
Deduction per Dependent | $80.80 |
Taxable Pay | Tax Rate |
---|---|
$0 | 10% |
$250 | 12% |
$600 | 22% |
$700 | 24% |
$925 | 32% Add the information below to cells H17:K20: |
Summary Statistics | # of Hours | Gross Pay | Net Pay |
---|---|---|---|
Average | |||
Highest | |||
Lowest |
Questions:
- Select the ranges C6:C13, E6:K6, and E14:K14. Apply the Accounting Number Format (be sure it includes a $) to these ranges.
- Select the range E7:K13 and apply the Comma Style.
- Select the range E14:K14 again, and apply the Total style from the Styles group.
- 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.
- 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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started