Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Use IF Functions to calculate the regular pay and overtime pay based on a regular 40-hour work-week in cells E5 and F5. Pay overtime only
- Use IF Functions to calculate the regular pay and overtime pay based on a regular 40-hour work-week in cells E5 and F5. Pay overtime only for overtime hours. Calculate the gross pay in cell G5 based on the regular and overtime pay.
- Create a formula in cell H5 to calculate taxable pay. Multiply the number of dependents by the deduction per dependent and subtract that from the gross pay.
- Insert a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. Hint: With a taxable pay of $417.40, Abrams tax rate is 25% and the withholding tax is $104.35. The VLOOKUP function returns the applicable tax rate, which you must then multiply by the taxable pay.
- Calculate FICA in cell J5 based on gross pay and the FICA rate, and calculate the net pay in cell K5.
- Copy all formulas down their respective columns.
- Use Quick Analysis tools to calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay on row 17. (On a Mac, this step must be completed using the AutoSum feature on your ribbon--Home tab, Editing group)
- Apply Accounting Number Format to the range C5:C16.
- Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (the range I21:K23) of the worksheet. Format the # of hours calculations as Number Format with one decimal and the remaining calculations with Accounting Number Format.
- Save and close the file. Exit Excel. Submit ExcelQuiz2_LastFirst in Canvas.
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