St ep Instructions

$1)$ Start Excel. Download and open the file named Exp$22\_$Excel$\_$Ch$02\_$ML$1\_$Payroll.xlsx$.$ Grader has automatically added your last name to the beginning of the filename.

$2)$ 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.$

$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.$

$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.

$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{.}90$

$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.$)$

$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.

$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.

$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.

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