How to Build a Payroll Spreadsheet

Written by grace ferguson
  • Share
  • Tweet
  • Share
  • Email

If you require your employees to clock in and out of a timekeeping system using time cards, you might want to transfer the time card data to a spreadsheet that shows each employee's time and payment received for the pay period. The Fair Labor Standards Act requires you to keep certain timekeeping records for nonexempt employees; a payroll spreadsheet helps you to meet the act's record-keeping criteria. You can create one using a spreadsheet program.

Skill level:
Moderately Challenging

Other People Are Reading

Things you need

  • Spreadsheet software

Show MoreHide

Instructions

  1. 1

    Open your spreadsheet and create the following lines at the top of the page: Pay Period Start Date, Pay Period End Date and Pay Date.

  2. 2

    Create the necessary columns: Employee, Employee ID (if applicable), Regular Hours, Overtime Hours, Pay Rate, Salary (if salaried), Gross Pay, Federal Income Tax, Social Security Tax, Medicare Tax and Net Pay. If other mandatory deductions apply, such as wage garnishment or state income tax, make headings for them as well. Create headings for voluntary deductions, such as health and retirement benefits, if the employee has them. The Net Pay heading should be last column.

  3. 3

    Enter formulas in applicable columns to simplify calculations. For example, if the employee has no overtime hours, the calculation you input into your Gross Pay column should equal the employee's hourly pay rate multiplied by his hours worked.

  4. 4

    Fill in the respective information. For example, enter the pay period start and end dates and the payday you are recording on their respective lines at the top of the workbook. Consult each employee's time card and paycheck for the wage and deduction information for the pay period. Enter the data under the respective columns.

  5. 5

    Save the template to your hard drive and use it whenever you need to input new payroll information.

Tips and warnings

  • Always choose "Save as" instead of "Save" to save the current information and to keep the original template blank.
  • You can enter formulas into the tax deduction columns; this comes in handy if you have only the employee's time card and want to know what her net pay will be for the upcoming payroll. Consult IRS Circular E for federal tax rates. For example, the formula for Social Security tax should show as F4*0.042, since the FICA rate for 2011 is 4.2 per cent.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.