Illiana Custom Homes IN Weekly Payroll Report 3 Employee Rate Hours Dep. Gross Pay Fed. Tax State Tax Net Pay % Taxes Jedi, Hubert $ 24.90 40.00 S 996.00 5 176.12 5 81.87 5 788.00 20.883% Kaden, Hadef 33.50 38.75 1,298.13 221.17 41.54 1,035.42 20.237% 6 Pancer, Dion 12.90 66.00 851.40 108.74 27.24 715.41 15.972% Rifken, Felix 29.75 27.25 810.69 139.06 25.94 645.68 20.354% Sanchez, Maria 21.35 45.00 960.75 153.69 30.74 776.32 19.197% 9 Scarff, Heidi 17.85 39.75 1 709.54 134.22 22.71 552.62 22.1 16% 10 Totals 256.75 $ 5,626.50 5 933.00 5 180.05 | 5 4,513.45 1 18.759% 11 Average $ 23.38 42.79 937.75 5 155.50 5 30.01 5 752.24 19.793% 12 Highest $ 33.50 66.00 1,298.13 5 221.17 5 41.54 $ 1,035.42 22.1 16% 13 Lowest $ 12.90 27.25 1 709.54 5 108.74 5 22.71 5 552.62 15.972% 14 15 16 17 18 FormatPoints=10 19 20 Instructions 21 1. Use the following formulas to determine the gross pay, federal tax, state tax, and net pay for all employees: 22 1.1 Gross Pay = Rate * Hours 23 1.2 Fed. Tax = 20% * (Gross Pay - Dep. * 38.46) 24 1.3 State Tax = 3.2% * Gross Pay 25 1.4 Net Pay = Gross Pay - (Fed. Tax + State Tax) 26 1.5 % Taxes = (Fed. Tax + State Tax) / Gross Pay 27 2. Calculate totals for hours, gross pay, federal tax, state tax, net pay, and % taxes paid 28 3. Use the appropriate functions to determine the average, highest, and lowest values of each column 29 4. Do the following format: 30 4.1 Assign the Comma style format with two decimal places to the ranges B5:H9 and C4:C13. 31 4.2 Assign a Currency style format with a floating dollar sign to the ranges B11:B13 and E11:H13. 32 4.3 Assign the Number style format with 0 decimal to the range D4:D13 and center the range. 33 4.4 Assign a Percent style format with three decimal places to the range 14:113. 34 5. Use the Conditional Formatting command on the Format menu to display white, bold font on a blue background for any rate in the range B4:09 greater than $25.00