Furman University



CSC101 Payroll Problem Excel Lab

Goal: to practice with the more involved concepts of Excel

Payroll Problem

You are an employer with six employees who have worked varying hours at varying pay rates. You want to calculate their Net Pay

Start by opening Excel and creating a spreadsheet to match the following:

[pic]

• Compute the gross pay for John Doe.The formula for gross pay is: If the number of hours worked is 40 or less

gross pay = (hours*pay_rate)

If the hours worked is greater than 40

gross pay = (40*pay_rate) + ( (hours-40) * pay_rate * (l+overtime_rate))

Use the overtime_rate in cell B3. Check your answer with a calculator.

• Replicate the formula to the rest of column D.

• Compute the deductions for John Doe.

The formula for deductions is:

deductions = gross_pay * deductions_tax

Again, use the constant at the top of the spreadsheet and check your answers before replicating the formula to the remaining cells in the column.

• Now compute the net pay as gross pay minus the deductions.

• Replicate the formula.

• If you haven't done so already, SAVE YOUR WORK.

• Now, sort the employees by highlighting their names AND their information (A6:F11). Select Sort from the Data tab. When you are sure you did this correctly, save again.

• Use the built-in function SUM to total the gross pay, deductions and net pay in row 13.

• Format the cells to make them aesthetically pleasing (two digits after decimal points as appropriate, bold face header cells, ...).

• Finally, you are feeling munificent, and want to give anyone working overtime a bonus.

If the employee works:

1 - 5 hours overtime bonus is $50

More than 5 but no more than 10 hours overtime bonus is $75

Over 10 hours overtime bonus is $100

Add a bonus amount column between columns C and D (highlight column D including the header, right click and select insert.

Include this amount in the gross pay. The employee will have to pay taxes on the bonus.

• Save and print

• Turn in one copy of this with your name at the top

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download