Comp25: Excel - Payroll Worksheet

Learning Objectives

1. To use Excel to manage your employees' payroll and taxes
2. To learn how to use Excel formulas
3. To reinforce formatting concepts

Background

Every business must manage its most expensive resource: its employees.  Businesses need to track the time their employees have worked, overtime, taxes, commission earned, vacation and sick time, and more.  The automation of this process can save a business owner valuable time.

Further, that information must be easy to understand by anyone who happens to read it.

Steps to Take

1. Using Excel, create a new worksheet
2. Referring to the example at the end of this assignment, create a header row with the following columns:
   1. Employee Name
   2. Hourly Rate
   3. Regular Hours
   4. Overtime Hours
   5. Gross Pay
   6. Tax Withholdings
   7. Net Pay
3. Enter five (or more) of your employee names in Column A.  As with your website, do not use real names since this might be found through an internet search.
4. Enter realistic information for the Hourly Rate, Regular Hours, and OT Hours for your employees based on one 40-hour week.
5. At the bottom of your spreadsheet, enter a label and value for "Tax Withholding Percent" (33%) and "OT Premium" (1.5).
6. Use formulas to calculate your first employee's Gross Pay, Tax Withholdings, and Net Pay. 
   1. Gross Pay should equal the hourly rate of that employee multiplied by the hours worked.  But remember that any overtime hours are paid a premium of 1½ their hourly rate.  This calculation should reference the "OT Premium" cell you created below the table in step 5.
   2. Tax Withholdings should equal 33% of the calculated Gross Pay.  Be sure to reference the "Tax Withholding Percent" you created in step 5.
   3. Net pay should equal the Gross Pay minus the Tax Witholdings.
7. Use Excel's fill feature to the formulas you created in step 6 to the remaining employees.  Be sure you use absolute cell references in your formulas for this shortcut to work properly.  You should not have to manually edit each row.
8. Confirm your formulas worked properly by reviewing the results.
9. Add a row for the total of each column, as shown in the example below.
10. Now use formatting (fonts, number formats, color, borders, alignment, and column width) to make your spreadsheet more presentable and easier to read. Your completed file should look similar to the example below.  The formatting should compliment your website.
11. In the Page Setup window, adjust the page orientation and scaling as necessary so that it looks good when printed.  Add a logo to the Page Header.
    
    
12. Save your file and upload it using Blackboard.

Grading Guidelines

This assignment will be graded using the following guidelines: