Recently I’ve been working with a local truck re-finishing company. They work for their customers on a job-by-job basis, providing fixed priced quotes for each job. Obviously their profitability relies on them controlling both the time and materials spent on those jobs. Their billing, invoicing and payroll is controlled with MYOB, while employee time and attendance and tracking time spent on jobs is done via wall mounted Fingertec Timeline 100 time clock and Fingertec’s TCMSV2 time and attendance software. The time clock only allows for tracking of two digit job numbers which means job numbers must be re-used. There is no link between the TCMS software and MYOB.

The Client’s Fingertec Timeline 100 Time Clock

The Problem

The owner of the business has little or no idea of how much time employees have spent on jobs until the job is complete. This has lead to losses being made on jobs where better control could have resulted in less time being spent by employees. It has also lead to problems in quoting as it has been difficult for the owner to access historical cost information for jobs of a similar type. The reasons for the delay in reporting can be pinned down to the following root causes:

1. The employee responsible for monitoring time and attendance and generating job reports had little understanding of the TCMS software and was not willing to learn more. As a result reporting was largely a process of printing hard copy reports and manual calculations that were either wrong or severely delayed.

2. The TCMS limitation of two digit job numbers meant that the historical information stored by the system was useless. For example, job 89 in May could easily be a completely different job in December. The client controlled what each job code represented via a descriptor the TCMS software could store with each code. When a number was re-used the descriptor would be changed. However, the TCMS software only discriminated by job number.

3. Delays in reporting meant the owner was loathe to ask for reports as they took so long to generate and (appeared) to require a lot of work to generate.

The Goal

The goal of the project was to provide the owner of the business with a simple tool that could report on the time his employees spent working on customer jobs. The tool should be easy to use by admin staff and be able to generate reports in minutes rather than hours or days.

The Solution

I spent several hours familiarising myself with the TCMS software. The reason for this was two-fold. First to understand what data integration was possible, and secondly to investigate the TCMS software for automation opportunities to speed up the time and attendance reporting and payroll processing procedures. The second task lead to immediate cost savings within the business via the following:

1. Configuring TCMS to automatically round times to the nearest 15 minutes (previously done manually)
2. Set up overtime within the software to automatically determine daily overtime on week days and penalty overtime for employees working on weekends.

These two changes have immediately reduced the time taken to process payroll by 2-3 hours per week.

The TCMS reporting system can produce reports in a variety of formats. I was hoping the software could generate reports directly to MS Excel as this would have made my job a lot simpler. However, the Excel reporting system in TCMS is severely flawed resulting in a single report taking more than an hour to generate (I suspect due to not setting the Calculation property of Excel to xlCalculateManual). Further digging around indicated that CSV reports could be generated. In particular the TCMS “Job Cost Analysis” report could be generated in CSV format. The format was a simple flat file that included a record for each time punch pair, the employee, the date, and the recorded job number and job descriptor tag.

The Excel Job Reporter

I developed an Excel spreadsheet that could import the Job Cost Analysis CSV file from TCMS and then present the data to the business owner in a useful format. The spreadsheet allows the construction of a master list of current jobs so that monitoring of current spend of jobs is quick and easy. You can see what this job sheet looks like below. This report is generated daily and made available to staff so that they can see how much budget is available on their current jobs.

Excel Job Tracker – Current Job List

The system also allows the owner to easily see the time spent on any historical job by any employee. Incredibly useful for quoting of new jobs. You can see that report below.

Excel Job Tracker – Detailed Job Report

A number of other reports are also available allowing the owner to see who has been spending time on what in the preceding day, week, or month.

The Benefits

The entire system was developed from start to finish in under 20 hours. The cost of the project is going to be recouped in lower payroll processing costs alone in about 3 months. The opportunities for better job cost control are likely to give multiples of project cost in savings and better quoting each month. All of this with some simple business process analysis, better use of the time and attendance software the client already owned, and making use of Excel.