CIS 5.2 Fall 2008

Prof. Sokol

PAYROLL WORKSHEET

Britney’s Music Emporium has hired you as an intern in its software applications department. The manager has asked you to prepare a weekly payroll report for the six employees listed below.

The data presented to you includes:

Name: The first and last name of each employee.

Hire Date: The date the employee was hired.

Hours Worked: The total number of hours the employee has worked this week.

Rate per Hour: The amount of money the employee earns for each hour of work.

Dependents: The number of dependents of the employee. (this is used for calculating the amount of federal taxes paid.)

Employee / Hire Date / Dependents / Rate
per Hour / Hours
Worked
Aquire, Raul / 1/3/2007 / 2 / 7.25 / 27.25
Kwasny, Casimir / 11/5/2004 / 1 / 16.25 / 23.50
Mohammed, Aadil / 2/6/2002 / 1 / 11.50 / 18.75
Ruiz, Tepin / 11/10/2003 / 3 / 14.25 / 29.00
James, Delmar / 8/9/2000 / 3 / 8.70 / 32.00
Holkavich, Fred / 4/15/1999 / 2 / 13.40 / 26.80

The following columns should be calculated:

·  Gross Pay: Gross Pay = Hours * Rate

·  Retirement: Part of an employee's gross pay is taken out and put into a retirement plan. In this case 8% of the gross pay is put into retirement.

·  Taxes, State and Local: State and local taxes are based upon a percentage of the gross pay. In this case 3.2% of the gross pay goes to state and local taxes.

·  Taxes, Federal: Federal taxes are computed using the following formula:
Federal Tax = 20% * (Gross Pay - Dependents * 38.46)

·  Net Pay: The net pay represents the amount of money the employee actually receives. It is computed by subtracting from the gross pay those monies which go to federal and state taxes, and retirement.

·  In addition, Ray's Ready Mix keeps track of the following statistics. Calculate the total, average, highest, and lowest for each of the columns: hours, gross pay, federal tax, state tax, retirement, net pay.

·  Use conditional formatting to display bold white font on an orange background for any net pay less that $550.00.

YOU ARE TO:

A. Create a spreadsheet to help the company keep track this information; submit two copies of this spreadsheet, the values version and the formulas version.

B. Create a bar graph to compare employee net salaries.

Neatness counts! Format your spreadsheet so that the data looks good. (a title, dollar signs and decimal places are a must). Your name and class section must appear somewhere on the spreadsheet.