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
Questions:
- 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.)
- 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.
- 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.
- Copy all formulas down their respective columns, stopping at row 13.
- 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.)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored 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