Payroll_calculator

Document Sample
Payroll_calculator Powered By Docstoc
					 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