Employee’s deductions and net pay
The excel worksheet comprises of an example of an excel worksheet that will aid in the calculation of the employees net pay after the deductions such as taxes, union fees and social security funds, the worksheet could help in reducing lengthy calculations and it acts as a program that makes this calculation easier.
For an employer with more than 1000 workers it will be very difficult to prepare holdings for federal taxes and the net pay to be given to workers, however the use of an excel worksheet helps in reducing this work burden and the calculation of net pay for more than 1000 workers can be done in minutes.
The columns of the spread sheet include:
o The employee’s number
o Employee’s name
o Hours per week
o Hourly pay rate or pay per hour
o Total gross pay
o Federal tax level
o Social security and Medicare withholdings
o Union fees
o Total deductions
o Disposable income or net pay
The excel worksheet involves the provision of formulas in each cell and then auto fill to the other required cells, however caution must be undertaken to avoid errors when multiplying a column with a single cell, however auto filling helps reduce the problem of having to multiply on each row, further the sheet can be used to undertake calculation of over 1000 employees.
The worksheet has a number of 10 employees namely James, John, Michael, Alex, Lincoln, Elias, Solomon, David, Grace and Carol, all of them are married and they work different total of hours per week, they are also paid at different rates and their taxation depends on the level of gross weekly pay, below is a summary of the reductions for all the employees:
For Federal holdings if gross wage is less than 449 dollars the rate is 10%, if it is more than 449 dollars then the rate is 29.5 + 15% of gross pay, however all our employees earn between 0 and 500 dollars gross weekly wage. The social security and Medicare is 6.20% for all the employees and finally the union fee is 1.5% for all the workers.
Results:
|
number |
name |
hours per week |
pay per hour |
gross pay |
federal tax |
social security and Medicare withholdings |
union dues |
total deductions |
disposable income(net pay) |
|
1 |
JAMES |
20 |
20 |
400 |
40 |
24.8 |
6 |
70.8 |
329.2 |
|
2 |
JOHN |
24 |
15 |
360 |
36 |
22.32 |
5.4 |
63.72 |
296.28 |
|
3 |
MICHAEL |
22 |
15 |
330 |
33 |
20.46 |
4.95 |
58.41 |
271.59 |
|
4 |
ALEX |
20 |
15 |
300 |
30 |
18.6 |
4.5 |
53.1 |
246.9 |
|
5 |
LINCOLN |
18 |
20 |
360 |
36 |
22.32 |
5.4 |
63.72 |
296.28 |
|
6 |
ELIAS |
15 |
24 |
360 |
36 |
22.32 |
5.4 |
63.72 |
296.28 |
|
7 |
SOLOMON |
16 |
30 |
480 |
101.5 |
29.76 |
7.2 |
138.46 |
341.54 |
|
8 |
DAVID |
17 |
26 |
442 |
44.2 |
27.404 |
6.63 |
78.234 |
363.77 |
|
9 |
GRACE |
14 |
30 |
420 |
42 |
26.04 |
6.3 |
74.34 |
345.66 |
|
10 |
CAROL |
25 |
20 |
500 |
104.5 |
31 |
7.5 |
143 |
357 |
Advantages:
- Easy to use
- Can be used to calculate tax levels for a large sample of employees within minutes
- Self explanatory in that all calculations are well defined
Disadvantages:
- It is not easy to multiply or copy the program to a new spread sheet
- It will be cumbersome to make such a programming on weekly bases
- A single error may lead to wrong impressions of tax levels
Recommendation:
The excel spread sheet should have an option that allows copying the formulas from one spread sheet to another, this will aid in quick duplication of weekly and dairy tax pay.
References:
The department of treasury (2007) employers tax guide,