Excel Worksheet Example

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:

1. The employee’s number

2. Employee’s name

3. Hours per week

4. Hourly pay rate or pay per hour

5. Total gross pay

6. Federal tax level

7. Social security and Medicare withholdings

8. Union fees

9. Total deductions

10. Disposable income or net pay

1/14

Excel Worksheet

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

2/14

Excel Worksheet

gross pay

federal tax

social security and  Medicare withholdings

union dues

total deductions

disposable income(net  pay)

1

JAMES

20

20

400

40

3/14

Excel Worksheet

24.8

6

70.8

329.2

2

JOHN

24

15

360

36

22.32

4/14

Excel Worksheet

5.4

63.72

296.28

3

MICHAEL

22

15

330

33

20.46

4.95

58.41

5/14

Excel Worksheet

271.59

4

ALEX

20

15

300

30

18.6

4.5

53.1

246.9

6/14

Excel Worksheet

5

LINCOLN

18

20

360

36

22.32

5.4

63.72

296.28

6

7/14

Excel Worksheet

ELIAS

15

24

360

36

22.32

5.4

63.72

296.28

7

SOLOMON

16

8/14

Excel Worksheet

30

480

101.5

29.76

7.2

138.46

341.54

8

DAVID

17

26

9/14

Excel Worksheet

442

44.2

27.404

6.63

78.234

363.77

9

GRACE

14

30

420

42

10/14

Excel Worksheet

26.04

6.3

74.34

345.66

10

CAROL

25

20

500

104.5

31

11/14

Excel Worksheet

7.5

143

357

12/14

Excel Worksheet

Advantages:

– 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.

13/14

Excel Worksheet

References:

The department of treasury (2007) employers tax guide,

14/14