Tuesday, December 20, 2011

MS Excell - Payroll Timesheet Day


Please review this article for your Journal Entry:  Philadelphia Poverty Figures (Click Link)
Federal Averages For Annual Income (Poverty Level) (Click Here For Article)

Today you will be working on Microsoft Excel and creating a time-card for yourself.  Please read the corresponding story to the number you are given in class to fill out your time-card for the week ending on December 31st, 2006.

Example Employee:  Rate per hour is $10.55.  Monday was a vacation day for eight hours. On Tuesday you worked for eight regular hours and added two hours of overtime.  On Wednesday and Thursday you worked eight hours, and Friday you worked for seven hours until you felt sick at your stomach.  You logged an hour of sick time.  You went in Saturday to make up for the time lost and added one hour of regular time and three hours of overtime.

Here are your scenarios:

Employee #1:  You make $20.25 an hour.  You felt sick on Monday and called out sick for eight hours.  You were feeling so much better the next day that you worked eight hours and then an extra three hours overtime to make up for your absence.  On Wednesday you could not be stopped again and put in an eight hour day and one hour of overtime.  Thursday you only were able to work seven hours, and Friday you worked eight hours and an extra hour of "flex" time at the normal rate to make up for Thursday.

Employee #2:  You make $14.50 an hour.  You arrived for work on Monday ready to go.  You put in eight hours and two hours of overtime.  On Tuesday you took a vacation day for eight hours of pay.  Wednesday and Thursday were regular days of eight hours each, and Friday you arrived late after two hours of sick time.  You finished the day strong and added six hours of regular time and an additional four hours of overtime.  Saturday you added four more overtime hours as well.

Employee #3:  You make $13.25 an hour.  On Monday you worked eight regular hours and four hours of overtime.  You took Tuesday as a personal day and clocked eight hours of vacation time.  On Wednesday you worked eight hours of regular time and added an additional two hours of overtime.  Thursday you were not feeling well and used eight hours of sick time.  Friday and Saturday you worked full eight hour days and logged an additional two hours of overtime.

Overtime for everyone is time and a half so multiply your hourly rate by 1.5 and you will have the overtime rate.  Sick time and Vacation Time are the same rate as your regular rate.

Take a look at your TOTAL PAY in the bottom right hand corner.  In your email, for extra credit, figure out what that number would be if you took out the following deductions:

  • Local Tax:  4.33%
  • State Tax:  3.07%
  • Federal Tax:  15%
  • Social Security:  6.5%
  • Medical Coverage:  1.4%
  • Retirement / 401K:  2%

Once you complete your time-card, email it to me and mention in your email what the total check would be after deductions.  I will review it and print a final one for you to sign.  This final form is your ticket out of my room today.  Anyone who does not complete the project by the end of class will owe me an additional task for homework.  This is easy and I will review a time-card with everyone before letting you loose on this project.

Good Luck!!

- Mr. S


  1. Eliminate the tedious process of creating time sheets after the fact by tracking your time as you go with
    Everyone seems to be mindful of the truth that the implementation of timesheet software system available organization boosts the responsibility and production.
    Creation in Excel is really cost effective way.

  2. Thanks for this blog sharing with us. Yes the above article mentioned the right thing. Its the report which provide the progress of the employee and then according to the report status we will train the skills of the employees.