80.2K

Verified Solution

Link Copied!

Please help with these following questions $1.$Use IF functions to calculate the regular pay and overtime pay in columns E and F based on a regular $40-$hour workweek. Be sure to use the appropriate absolute or mixed cell references. Pay overtime only for overtime hours. Note employees receive $1\mathrm{.}5$ their hourly wage for overtime hours worked. Calculate the gross pay in cell G$5$ based on the regular and overtime pay. Spencer$$s regular pay is $$440.$ With five overtime hours, Spencer$$s overtime pay is $$82\mathrm{.}50.20$

$3$ Create a formula in cell H$5$ to calculate the taxable pay. Multiply the number of dependents by the deduction per dependent and subtract that from the gross pay. With two dependents, Spencer$$s taxable pay is $$422\mathrm{.}50.10$

$4$ Insert a VLOOKUP function in cell I$5$ to identify and calculate the federal withholding tax. With a taxable pay of $$422\mathrm{.}50,$ Spencer$$s tax rate is $25\%$ and the withholding tax is $$105\mathrm{.}63.$ The VLOOKUP function returns the applicable tax rate, which you must then multiply by the taxable pay. $20$

$5$ Calculate FICA in cell J$5$ based on gross pay and the FICA rate, and calculate the net pay in cell K$5.$ Copy all formulas down their respective columns. Be sure to preserve the existing formatting in the document. Based on the hours Spencer worked he paid $$39\mathrm{.}97$ to FICA and had a weekly net pay of $$376\mathrm{.}905$

$6$ 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 the ribbon.$)10$

$7$ Apply Accounting Number Format to the range C$5$:C$16.$ Apply Accounting Number Format to the first row of monetary data and to the total row. Apply the Comma style to the monetary values for the other employees. $10$

$8$ Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area $($the range I$24$:K$26)$ of the worksheet. Format the # of hours calculations as Number format with one decimal and the remaining calculations with Accounting Number Format. $20$

$9$ Use the XLOOKUP function to look up the employee name in cell A$20($Wagner$)$ in the payroll data and return the specified information in row $20.$ Ensure the return array includes overtime pay, gross pay, taxable pay, federal tax, FICA and net pay. $5$

$10$ Save and close the workbook. Submit the file as directed. $0$

Answer & Explanation Solved by verified expert

See Answer