The LaborPro is a Microsoft® hosted Excel™ spreadsheet that will assist anyone interested in tracking worker attendance and hours worked during their calendar year. Future year tracking is possible through a few user changes to this program. The spreadsheet allows up to 50 workers to be tracked by user input items (like hours worked) per week and has user input absences.

Why Track People? Two Management Reasons
1. By utilizing the Goal Chart, we were able to see which of the 50 workers had put in the most hours worked per week. This could reflect how much dedication an employee had put into the project, or conversely, how much "chronic" overtime was being recorded (over 100%) on a project. This information along with the Attendance Chart (depicting types of absences) would assist us in allocating raises and planning promotions.

2. By seeing the pattern of the number and types of absences (by day over a period of 1 year) on each Worker/Technician Page (T1, T2 etc.), coupled with when these absences took place (Monday, Friday and before/after holidays), would give you an indication of how dedicated an employee was to your project(s).

As on overview, LaborPro is broken down into 4 areas:
Area 1: the Parameter and Input Sheets feeds every sheet
Area 2: the Input Sheet feeds the Detail Sheet showing actual to plan
Area 3: Detail Sheet feeds Labor, Effort and all 50 Worker Sheets
Area 4: The 50 Daily Worker/Technician Sheets feed the Attendance Chart and Input Sheet

Area 1: Parameter Sheet feeds every sheet

The date calendar starts is 1/1/2012
The work calendar is Mondays through Friday
Previous year input is included
Tracking is based on a 40 hour workweek

Each T sheet will depict 2012 days (green - Monday through Friday and white - Saturday/Sunday)

Access the T1 data at the foot of the AttendancePro Sheet

Enter specific employee information
1. Name: Louie DePalma
2. Employee Number: 123456789
3. Employee Date: 2/16/2011
4. Paid Vacations Allowed: 4
54. Paid Other Days Allowed: 3

Under the Summary Area enter previous year (s) information
C = 2 vacation days
E = 4 excused absenses
H = 2 holiday days
I = 2 illness days

As the year progresses, each person's specific information is recorded and will be depicted in graph form for quick analysis,

The Input Sheet tracks weekly work activity (based on 40 hours set on the Parameter Sheet). One excused absence day (8 hours) for a week would be 32P32 (P-means partial). An 8-hour unexcused absence would be a plain 32. A part-time employee who works only 16 hours per week would be input as 16P16 to represent that person was present for all required dates and did not miss work. Overtime of 5 hours could be represented by 45 or 45P40 or 35P30, depending on work loads.

Louie DePalma had a previous year input of 1650 hours worked out of a possible 1800 hours in 45 weeks

"enter Name on Sheet T3" had previous year input of 1860 hours
out of a possible 2080 hours in 50 weeks

Total
Louie DePalma total actual labor hours = 247 hours for 6 weeks
"enter Name on Sheet T3" total hours = 215 hours for 6 weeks

Note: if Parameter Sheet Previous Year is set to "NO", values are not included in calculations (even if entered).

The Detail Sheet will include the previous years values. Louie is now at 1877 hours out of 2032 hours thru 51 work weeks and "enter Name on Sheet T2" is at 2080 hours out of 2296 labor hours thru 58 weeks.

The Effort Chart will depict only the current year values.

The Average Total Hours for this years and all previous years are tabulated. This will be extremely helpful to ascertain if we were increasing the business base and if the workers were keeping up with their hourly obligation to profit.

Through this example: over all years, we see that our technicians worked an average 36.26 hour work week based on 109 weeks of work.

This year, the Overtime work hours are based on 4 weeks by 2 people. The pie chart lists the total hours split by overtime and all other time. The pie breakout lists the:
1. On Plan
2. Below Plan
3. No input

The planned hours are set at 4328 labor hours and actual hours are calculated at 3957 labor hours. The project is currently at 91.43% over 109 work weeks. We also see from the chart below that this year, we are running our actual labor hours slightly below the planned labor hours (6 work weeks).

There are 50 Technician Input Sheets to monitor Attendance for the project. Each "T" sheet allows user input as to the identity, employee number and any previous year attendance issues. The sheet breaks the desired user calendar down into days of the week and identifies Saturdays and Sundays. Observing how attendance issues occur coupled with actual work and overtime hours could provide yearly performance data points.

Insight:

The Effort Comparison and "T" Charts will reflect each person's status on goal attainment (100%). At this point it could be included that when we used these charts we noted our top paid technicians were consistently in the overtime pay category. We formulated the following questions:
Was this required, i.e., top level expertise on overtime, or could we have accomplished the overtime tasks with less expensive people? Why was there overtime? Did we plan the project properly? What were our goal percentages at a specific time? If we put a bonus structure in place, what would the goal percentages look like?
During yearly performance reviews, we noted that some top level (high paid) people wanted significant pay increases. We were able to demonstrate that each person was actually getting paid 120% - 132% of their raw pay rate based on overtime. This changed their view of expected raises as well as what competitors are paying for similar technical expertise.