Question
Using the attached template, you are to compute the gross pay, federal withholding, Social Security withholding, Medicare withholding and the net payment for this company.
Using the attached template, you are to compute the gross pay, federal withholding, Social Security withholding, Medicare withholding and the net payment for this company. Assume there is no state income tax. Overtime is paid at a rate of 1.5 times the normal wage rate.
NOTE: PLEASE DONT FORGET THE PIVOT TABLE!
In addition, compute the total gross pay, federal withholding, Social Security withholding, Medicare withholding and net payment for all the employees. Further, create a pivot table indicating the total gross pay, Social Security withholding, Medicare withholding and net pay for wage employees and for salary employees. Please note you should use auto fill to complete this assignment in an efficient manner. Use the following 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 | ||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started