Using the attached template, you are to compute the gross pay,federal withholding, Social Security withholding, Medicarewithholding and the net payment for this company. Assume there isno state income tax. Overtime is paid at a rate of 1.5 times thenormal wage rate.
NOTE: PLEASE DONT FORGET THE PIVOTTABLE!
In addition, compute the total gross pay, federalwithholding, Social Security withholding, Medicare withholding andnet payment for all the employees. Further, createa pivot table indicating the total gross pay, Social Securitywithholding, Medicare withholding and net pay for wage employeesand for salary employees. Please note you should use autofill to complete this assignment in an efficient manner. Use thefollowing Excel template:
| | | | | | Tax Rates | | |
| | | | | | | 6,20% | 1,45% | | |
| Employee Numbers | Pay Rate | Regular Hours Worked | Overtime Hours Worked | Gross Pay | Federal W/H Rate | Federal W/H | SS W/H | Medicare W/H | Net Pay | Type |
| 1 | $44,00 | 40 | 6 | | 0,25 | | | | | Wage |
| 2 | $56,00 | 40 | | | 0,28 | | | | | Wage |
| 3 | $35,00 | 40 | 9 | | 0,15 | | | | | Wage |
| 4 | $3 200,00 | 1 | | | 0,25 | | | | | Salary |
| 5 | $23,00 | 40 | 2 | | 0,25 | | | | | Wage |
| 6 | $19,00 | 1 | | | 0,15 | | | | | Wage |
| 7 | $33,00 | 40 | 9 | | 0,28 | | | | | Wage |
| 8 | $3 100,00 | 1 | | | 0,28 | | | | | Salary |
| 9 | $2 400,00 | 1 | | | 0,28 | | | | | Salary |
| 10 | $32,00 | 40 | 6 | | 0,25 | | | | | Wage |
| 11 | $23,00 | 40 | | | 0,28 | | | | | Wage |
| 12 | $37,00 | 40 | 9 | | 0,15 | | | | | Wage |
| 13 | $2 450,00 | 1 | | | 0,25 | | | | | Salary |
| 14 | $41,00 | 40 | 2 | | 0,25 | | | | | Wage |
| 15 | $23,00 | 1 | | | 0,15 | | | | | Wage |
| 16 | $36,00 | 40 | 9 | | 0,28 | | | | | Wage |
| 17 | $4 100,00 | 1 | | | 0,28 | | | | | Salary |
| 18 | $2 100,00 | 1 | | | 0,28 | | | | | Salary |
| 19 | $40,00 | 40 | 6 | | 0,25 | | | | | Wage |
| 20 | $27,00 | 40 | | | 0,28 | | | | | Wage |
| 21 | $31,00 | 40 | 9 | | 0,15 | | | | | Wage |
| 22 | $2 200,00 | 1 | | | 0,25 | | | | | Salary |
| 23 | $26,00 | 40 | 2 | | 0,25 | | | | | Wage |
| 24 | $18,00 | 1 | | | 0,15 | | | | | Wage |
| 25 | $31,00 | 40 | 9 | | 0,28 | | | | | Wage |
| 26 | $2 900,00 | 1 | | | 0,28 | | | | | Salary |
| 27 | $2 850,00 | 1 | | | 0,28 | | | | | Salary |
| 28 | $30,00 | 40 | 6 | | 0,25 | | | | | Wage |
| 29 | $41,00 | 40 | | | 0,28 | | | | | Wage |
| 30 | $36,00 | 40 | 9 | | 0,15 | | | | | Wage |
| 31 | $2 560,00 | 1 | | | 0,25 | | | | | Salary |
| 32 | $22,00 | 40 | 2 | | 0,25 | | | | | Wage |
| 33 | $42,00 | 1 | | | 0,15 | | | | | Wage |
| 34 | $31,00 | 40 | 9 | | 0,28 | | | | | Wage |
| 35 | $1 800,00 | 1 | | | 0,28 | | | | | Salary |
| 36 | $1 900,00 | 1 | | | 0,28 | | | | | Salary |
| 37 | $35,00 | 40 | 6 | | 0,25 | | | | | Wage |
| 38 | $53,00 | 40 | | | 0,28 | | | | | Wage |
| 39 | $32,00 | 40 | 9 | | 0,15 | | | | | Wage |
| 40 | $3 600,00 | 1 | | | 0,25 | | | | | Salary |
| 41 | $25,00 | 40 | 2 | | 0,25 | | | | | Wage |
| 42 | $19,00 | 1 | | | 0,15 | | | | | Wage |
| 43 | $29,00 | 40 | 9 | | 0,28 | | | | | Wage |
| 44 | $5 000,00 | 1 | | | 0,28 | | | | | Salary |
| 45 | $1 900,00 | 1 | | | 0,28 | | | | | Salary |
| 46 | $22,00 | 40 | 6 | | 0,25 | | | | | Wage |
| 47 | $31,00 | 40 | | | 0,28 | | | | | Wage |
| 48 | $52,00 | 40 | 9 | | 0,15 | | | | | Wage |
| 49 | $1 900,00 | 1 | | | 0,25 | | | | | Salary |
| 50 | $22,00 | 40 | 2 | | 0,25 | | | | | Wage |
| 51 | $18,00 | 1 | | | 0,15 | | | | | Wage |
| 52 | $28,00 | 40 | 9 | | 0,28 | | | | | Wage |
| 53 | $3 300,00 | 1 | | | 0,28 | | | | | Salary |
| 54 | $2 100,00 | 1 | | | 0,28 | | | | | Salary |
| 55 | $37,00 | 40 | 6 | | 0,25 | | | | | Wage |
| 56 | $52,00 | 40 | | | 0,28 | | | | | Wage |
| 57 | $19,00 | 40 | 9 | | 0,15 | | | | | Wage |
| 58 | $4 200,00 | 1 | | | 0,25 | | | | | Salary |
| 59 | $15,00 | 40 | 2 | | 0,25 | | | | | Wage |
| 60 | $21,00 | 1 | | | 0,15 | | | | | Wage |
| 61 | $35,00 | 40 | 9 | | 0,28 | | | | | Wage |
| 62 | $3 500,00 | 1 | | | 0,28 | | | | | Salary |
| 63 | $2 200,00 | 1 | | | 0,28 | | | | | Salary |
| 64 | $38,00 | 40 | 6 | | 0,25 | | | | | Wage |
| 65 | $51,00 | 40 | | | 0,28 | | | | | Wage |
| 66 | $41,00 | 40 | 9 | | 0,15 | | | | | Wage |
| 67 | $1 875,00 | 1 | | | 0,25 | | | | | Salary |
| 68 | $27,00 | 40 | 2 | | 0,25 | | | | | Wage |
| 69 | $33,00 | 1 | | | 0,15 | | | | | Wage |
| 70 | $27,00 | 40 | 9 | | 0,28 | | | | | Wage |
| 71 | $3 700,00 | 1 | | | 0,28 | | | | | Salary |
| 72 | $2 600,00 | 1 | | | 0,28 | | | | | Sala |
