Cs.furman.edu
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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- hampton inn near furman university
- cs ny employee benefits nyship
- 7 cs of communication ppt
- cs ny gov employee benefits
- 7 cs of effective communication
- the 7 cs of communication
- cs phd salary
- seven cs of communication
- project ideas for cs students
- oxford cs philosophy
- the 7 cs of communication definitions
- freud and cs lewis