AttendancePro Design

AttendancePro Design

The AttendancePro 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).

A good (project) manager or coach promotes hard work and rewards dedication. We sometimes overlook our favorite peoples' indiscretions, even though it affects the project profit. However, a team cannot function at peak performance if some people do not pull their goal weight.

Make the Tracking Personal
As an example, on a weekly basis you can track the following:
1. Hours worked on a project
2. Hours worked
3. Homes Closed
4. Customer Contacts
5. Golf Scores
6. Lines of Code Written
7. Pints of Blood at a Hospital

The AttendancePro workbook incorporates seven (8) topic-planning sheets: Parameter, Input, Detail, Total, Attainment Chart, Goal Chart, Attendance Chart and 50 Daily Worker/Technician Pages.

As on overview, AttendancePro is broken down into 3 areas:
Area 1: Parameter Sheet feeds every sheet
Area 2: Input feeds Detail, Total, Attainment and Goal Charts
Area 3: The 50 Daily Worker/Technician Sheets feed the Attendance Chart

Area 1: Parameter Sheet feeds every sheet

The year is set to 2007
The work calendar starts Sundays
Previous year input is included
Tracking is based on a 40 hour workweek

Calendar Year

2007

Start Date of 1st Week of Year

Monday, January 1, 2007

Include Previous Year(s) results?

yes

Type of Comparison(Hours Worked)

Hours

Worked

Comparisons per Week

Week

# of desired Hours Worked per week

Designator

Daily Tracking Item

Accident

Bereavement

Vacation

Discipline

Excused

Family Leave

Work Stoppage

Holiday

Illness

Unexcused

Tardy

Leave

Other

Example for year 2007, "Technician 1" had 15 absences
-4 bereavements
-2 vacation days
-2 illness
-7 Holiday

no previous year absences were recorded.

Both Technician absences will filter to the Attendance Chart for overall review.

The Input Sheet tracks weekly work activity (based on 40 hours). One excused absence 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 18 hours per week would be input as 18P18 to represent that person was present for all required dates and did not miss work. Overtime of 8 hours could be represented either by 48 or 48P40.

Mr. Al Einstein had previous year input of 1860 hours
out of a possible 2080 hours in 52 weeks

Total 2007
Technician 1 total hours = 416 hours out of possible 416 hours for 11 weeks
Al Einstein total hours = 424 hours out of possible 424 hours for 11 weeks

For Al Einstein
Previous 1860 hours out of possible 2080 hours for 52 weeks
2007 424 hours out of possible 424 hours for 22 weeks

Total 2284 hours out of possible 2504 hours for 63 weeks

The Totals Sheet will list all technicians/workers and tally their hours worked by category

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

Total All Years, Average Hours Worked per Week

36.49

Total All Years, Top Performer
by Average Total Hours Worked per Week Position

This Year Number of Weeks at 100%

Technician 1

37.82

Total All, Bottom Performer
by Average Total Hours Worked per Week Position

This Year Number of People at 100%

Al Einstein

36.25

The final chart will depict percent of goal attainment. One high school football coach used this program to chart wins based on 3rd down attainment, excuses on why it did not work and team member attendance. He found it very revealing and changed the approach team incentives.

The individual Goal Chart will reflect each person's status on goal attainment (100%). At this point it could be included that when we used this chart we noted our top paid technicians were consistently in the overtime pay category. We formulated the following questions:
Was this required, 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.

AttendancePro Individual Goal Attainment

Technician 1 had achieved 100% of the total hours worked.Al Einstein had achieved 91.21% of the total hours worked.The full 50 technicians will be depicted on the Goal Chart