No. of Hourly Hours Dependents Wage Worked Regular Pay Pay Overtime Assignment Instructions FIC Taxable Pay 5417.40 Gross Pay $517.40 $9.95 48 59.55 48 S11.7550 $11.7535 10.00 40 $9.55 44 $15.75 40 $12 25 20 $13.35 38 $10.00 45 9.95 15 $11.75 41 5398.00 119.4 5 Abram 6 Acosta 7 Bordeaux 8 Higinbotham 9 lanziti 10 Jauss 11 Ling 12 Munoz Use a VLOOKUP function in cell 15 to identify and calculate the federal withholding tax. Use the tax ra from the range D21 E25. The VLOOKUP function eturns the applicable tax rate, which you must then multiply by the taxable Calculate FiCA in cell J5 based on gross pay and the FICA rate (ell B23), and calculate the net pay in cell K5. Copy all formulas down their respective columns t row 16 16 Weston 17 18 19 Totals 6 With the range E5:K16 selected, use Quick Analysis Summary Statistics Average tools to calculate the total regular pay, overtime pay gross pay. taxable pay, withholding tax, FICA, and net pay on row 17 axable 20 #Of Hours Gross Tax Rate 15% 22% 25% 28% 31% Pay Assumptions 21 Base Work Hours 22 Overtime rate 23 FICA rate 24 Deduct per Depend S 50.00 50 $250 $300 $450 $525 40 Note, Mac users, with the range selected, on the Home tab, in the Editing group, click AutoSum 7 65% Lowest 7 Apply Accounting Number Format to the range C5 C16 Apply Accounting Number Format to the first row of monetary data and to the total row. Apply the Comma 26 Notes 28 1. The base workweek is 40 hours. Regular pay is based on hourly wage and hours worked up to 40 29 2. Overtime pay is based on overtime hours, the hourly wage, and the overtime rate 30 3. The gross pay is the total of the regular pay and overtime pay Close 4 The taxable pay is the difference between the gross pay 31 and the product of the number of dependents and deduction per dependernt 32 5. Federal withholding tax is calculated on the taxable pay based on the tax table. 33 6. FICA is calculated on the employee's gross pay 34 7. The net pay is based on the gross pay, federal withholding tax, and FICA 35 36 37 38 Payroll Data Preview Steps Edit Scroll Lock