# Payroll_calculator

Document Sample

You are responsible for entering formulae that have gray backgrounds, like this -->

1 Employee Information Worksheet
Cell:
Total Taxes Withheld                    The value should be a sum of the following fields
v State Tax
v Federal Income Tax
v Social Security Tax
v Medicare Tax
Total Regular Deductions                The value should be a sum of the following fields
v Insurance Deduction
v Other Regular Deductions

2 Payroll calculator Worksheet
Cell:
Period Ending                           Enter the appropriate formula to automatically reflect Today's Date
Employee Name                           Enter the appropriate formula to lookup the Employee Name from the Empl
v Use VLOOKUP
Overtime Hours                          Determine ovetime hours using IF.
v Anything over 40 hours is considered overtime.
Overtime Rate                           Enter the appropriate formula to calculate Overtime Rate
1.) Use VLOOKUP to determine the Employee's Hourly Wage
2.) Multiply that by 1.5
Gross Pay                               Enter the appropriate formula for the Gross Pay column
1.) Use VLOOKUP to determine the Employee's Hourly Wage
2.) Multiply that value by the sum of:
v
v
v
3.) Add to that value: The Overtime Rate multiplied by the Ove

Hint: Remember operator sequence

Taxes & Deductions                      Enter the appropriate formula for the Taxes & Deductions column
1.) Use VLOOKUP to determine the Total Taxes Witheld
2.) Multiply that value by the employee's Gross Pay
3.) Add to that value the employee's Total Regular Deduction
Net Pay                                 Enter the appropriate formula for the Net Pay column
Net Pay = Gross Pay - Taxes and Deductions - Other Ded

3 Individual Pay Stubs
There are four paystubs.
NOTE: Do these in order.
HINT: You only really need to do this for the first paystub. Provided that you
you can then just copy and paste the formulae into the other 3 stubs
For each paystub, the appropriate formulae are as follows:
Cell:
Period                                 Should equal the Period Ending date on the Payroll Calculator Worksheet
Employee Name                          Use VLOOKUP from the Employee Information worksheet
Gross Pay                              Use VLOOKUP from the Payroll Calculator worksheet
Tax Status                             Use VLOOKUP from the Employee Information worksheet
Hourly Rate                            Use VLOOKUP from the Employee Information worksheet
Social Security Tax                 Use VLOOKUP from the Employee Information worksheet to find the rate
Medicare Tax                        Use VLOOKUP from the Employee Information worksheet to find the rate
Insurance Deduction                 Use VLOOKUP from the Employee Information worksheet
Federal Allowance (From W-4)        Use VLOOKUP from the Employee Information worksheet
Overtime Rate                       Use VLOOKUP from the Payroll Calculator worksheet
Federal Income Tax                  Use VLOOKUP from the Employee Information worksheet to find the rate
State Tax                           Use VLOOKUP from the Employee Information worksheet to find the rate
Other Regular Deduction             Use VLOOKUP from the Employee Information worksheet
Other Deduction                     Use VLOOKUP from the Payroll Calculator worksheet
Hours Worked                        Use VLOOKUP from the Payroll Calculator worksheet
Sick Hours                          Use VLOOKUP from the Payroll Calculator worksheet
Vacation Hours                      Use VLOOKUP from the Payroll Calculator worksheet
Overtime Hours                      Use VLOOKUP from the Payroll Calculator worksheet
Total Taxes and Deductions          Use VLOOKUP to find the Total Taxes and add it to the Other Deduction f
Net Pay                             Use VLOOKUP from the Payroll Calculator worksheet
Total Taxes and Regular       SUM of :
Deductions                     v Social Security Tax
v Medicare Tax
v Insurance Deduction
plus the SUM of:
v Federal Income Tax
v State Tax
v Other Regular Deduction

EXTRA CREDIT

For the [Company Name] cell:
Use RIGHT() and LEN() functions to extract the company name from the Employee Information worksheet.
ally reflect Today's Date
Employee Name from the Employee Information worksheet based on their Empoyee ID

considered overtime.
Overtime Rate
ne the Employee's Hourly Wage

s Pay column
ne the Employee's Hourly Wage

Regular Hours Worked
Vacation Hours
Sick Hours
rtime Rate multiplied by the Overtime Hours

s & Deductions column
ne the Total Taxes Witheld
mployee's Gross Pay
oyee's Total Regular Deduction

xes and Deductions - Other Deductions

he first paystub. Provided that you use cell referncing correctly,
ulae into the other 3 stubs

he Payroll Calculator Worksheet
mation worksheet
or worksheet
mation worksheet
mation worksheet
mation worksheet to find the rate and multiply it by the Gross Pay for that paystub
mation worksheet to find the rate and multiply it by the Gross Pay for that paystub
mation worksheet
mation worksheet
or worksheet
mation worksheet to find the rate and multiply it by the Gross Pay for that paystub
mation worksheet to find the rate and multiply it by the Gross Pay for that paystub
mation worksheet
or worksheet
or worksheet
or worksheet
or worksheet
or worksheet
d add it to the Other Deduction for that paystub
or worksheet

e Information worksheet.
Employee information for Airtronics Corp.
Federal Income Tax                                       Total Taxes   Insurance    Other Regular   Total Regular Deductions
Hourly                Federal Allowance     State Tax                            Social Security   Medicare Tax
Employee ID            Name                   Tax Status                                      (Percentage based on                                       Withheld     Deduction     Deduction        (Excluding taxes, in
Wage                     (From W-4)       (Percentage)                          Tax (Percentage)   (Percentage)
Federal Allowance)                                     (Percentage)    (Dollars)     (Dollars)             dollars)

1             Dan D. Lyons      \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

2              Adam Baum        \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

3              Barry Cade       \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

4             Earl Lee Riser    \$12.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

5              Doug Graves      \$10.00       0               4               2.30%             32.00%                6.30%            1.45%                       \$20.00         \$40.00

6               Matt Tress      \$10.00       0               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

7               Holly Day       \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

8               Ella Vader      \$12.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

9             Dinah Soares      \$10.00       2               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

10              Royal Payne      \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

11             Warren Peace      \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

12             May Ann Naze      \$10.00       1               4               2.30%             32.00%                6.30%            1.45%                       \$20.00         \$40.00

13              Laura Norder     \$10.00       0               4               2.30%             32.00%                6.30%            1.45%                       \$20.00         \$40.00

14             Chris P. Bacon    \$10.00       0               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

15               Beau Tye        \$15.00       2               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

16              Andy Friese      \$10.00       2               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

17               Lou Pole        \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

18               Mel Loewe       \$15.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

19              Molly Kuehl      \$15.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

20                Jo King        \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

21              Gene Poole       \$10.00       1               4               2.30%             32.00%                6.30%            1.45%                       \$20.00         \$40.00

22              Owen Cash        \$10.00       1               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

23              X. Benedict      \$12.00       0               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00

24             Seymour Butz      \$10.00       1               4               2.30%             32.00%                6.30%            1.45%                       \$20.00         \$40.00

25        Robin Andis Merryman   \$10.00       2               4               2.30%             28.00%                6.30%            1.45%                       \$20.00         \$40.00
Payroll calculator                                                                                                                          Period Ending:

Regular Hours                                                                                                      Other
Employee ID   Employee Name                 Vacation Hours   Sick Hours   Overtime Hours Overtime Rate   Gross Pay   Taxes and Deductions                    Net Pay
Worked                                                                                                         Deduction

1                             50             5              1                                                                              \$20.00

2                             40             5              5                                                                               \$0.00

3                             42             5              5                                                                               \$0.00

4                             40             5              5                                                                               \$0.00

5                             40             5              5                                                                               \$0.00

6                             40             5              10                                                                              \$0.00

7                             47             5              10                                                                             \$12.98

8                             51             0              10                                                                              \$0.00

9                             49             0              10                                                                              \$0.00

10                             20             0              4                                                                               \$0.00

11                             20             0              4                                                                               \$0.00

12                             25             0              5                                                                               \$9.44

13                             40             0              3                                                                               \$0.00

14                             40             3              6                                                                               \$0.00

15                             45             5              10                                                                             \$50.00

16                             32             4              10                                                                              \$0.00

17                            34.5            5              6                                                                               \$0.00

18                             56             5              7                                                                               \$0.00

19                             40             5              8                                                                               \$0.00

20                             40             5              10                                                                              \$0.00

21                             40             5              10                                                                              \$0.00

22                             39             2              10                                                                              \$0.00

23                             40             2              10                                                                              \$2.45

24                             40             1              10                                                                              \$0.00

25                             12             5              10                                                                              \$0.00
[Company Name]
Period:                   Employee Name                  Employee ID       1

Tax Status                Federal Allowance (From W-4)   Hours Worked
Hourly Rate               Overtime Rate                  Sick Hours
Social Security Tax       Federal Income Tax             Vacation Hours
Medicare Tax              State Tax                      Overtime Hours
Insurance Deduction       Other Regular Deduction        Gross Pay
Total Taxes and Regular                                  Total Taxes and
Other Deduction
Deductions                                               Deductions
Net Pay

[Company Name]
Period:                   Employee Name                  Employee ID       2

Tax Status                Federal Allowance (From W-4)   Hours Worked
Hourly Rate               Overtime Rate                  Sick Hours
Social Security Tax       Federal Income Tax             Vacation Hours
Medicare Tax              State Tax                      Overtime Hours
Insurance Deduction       Other Regular Deduction        Gross Pay
Total Taxes and Regular                                  Total Taxes and
Other Deduction
Deductions                                               Deductions
Net Pay

[Company Name]
Period:                   Employee Name                  Employee ID       3

Tax Status                Federal Allowance (From W-4)   Hours Worked
Hourly Rate               Overtime Rate                  Sick Hours
Social Security Tax       Federal Income Tax             Vacation Hours
Medicare Tax              State Tax                      Overtime Hours
Insurance Deduction       Other Regular Deduction        Gross Pay
Total Taxes and Regular                                  Total Taxes and
Other Deduction
Deductions                                               Deductions
Net Pay

[Company Name]
Period:                   Employee Name                  Employee ID       4

Tax Status                Federal Allowance (From W-4)   Hours Worked
Hourly Rate               Overtime Rate                  Sick Hours
Social Security Tax       Federal Income Tax             Vacation Hours
Medicare Tax              State Tax                      Overtime Hours
Insurance Deduction       Other Regular Deduction        Gross Pay
Total Taxes and Regular                                  Total Taxes and
Other Deduction
Deductions                                               Deductions
Net Pay

DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 10 posted: 2/23/2012 language: English pages: 8