Excel Worksheet

Excel Worksheet

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

1/16

Excel Worksheet

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:

2/16

Excel Worksheet

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

3/16

Excel Worksheet

disposable income(net  pay)

1

JAMES

20

20

400

40

24.8

6

70.8

329.2

4/16

Excel Worksheet

2

JOHN

24

15

360

36

22.32

5.4

63.72

296.28

3

5/16

Excel Worksheet

MICHAEL

22

15

330

33

20.46

4.95

58.41

271.59

4

ALEX

20

6/16

Excel Worksheet

15

300

30

18.6

4.5

53.1

246.9

5

LINCOLN

18

20

7/16

Excel Worksheet

360

36

22.32

5.4

63.72

296.28

6

ELIAS

15

24

360

36

8/16

Excel Worksheet

22.32

5.4

63.72

296.28

7

SOLOMON

16

30

480

101.5

29.76

9/16

Excel Worksheet

7.2

138.46

341.54

8

DAVID

17

26

442

44.2

27.404

6.63

78.234

10/16

Excel Worksheet

363.77

9

GRACE

14

30

420

42

26.04

6.3

74.34

345.66

11/16

Excel Worksheet

10

CAROL

25

20

500

104.5

31

7.5

143

357

12/16

Excel Worksheet

Advantages:

13/16

Excel Worksheet

– Easy to use

– Can be used to calculate tax levels for a large

– Self explanatory in that all calculations are well

sample of employees within minutes defined

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.

14/16

Excel Worksheet

15/16

Excel Worksheet

References:

The department of treasury (2007) employers tax guide,

16/16