Software for Tracking Shop Time and Materials

Business owners discuss spreadsheet and other software solutions for keeping track of job inputs. December 26, 2007

Question
I own an architectural millwork shop (job shop) where every job is new and different. We try to concentrate on entry doors for new residential and historical replications. I try to track every job that goes through the shop. The guys in the shop write down their time every day for each particular project they work on, on a generic time sheet. They hand it in at the end of the day and I enter it into Excel. I then track the expenses such as lumber, locksets, hardware, finish, threshold as the invoices come in. I have searched for software to do this, but all I find is software that offers too many bells and whistles. I just want something simple like I have, but that is easy to enter information into fields. Does anyone have suggestions on tracking projects?

Forum Responses
(Business and Management Forum)
From contributor C:
There are many software providers that offer the type of software you are looking for. As you have found, most are feature intense with unneeded capabilities. I hate to say it, but Excel is a powerful tool when it comes to tracking numerical data. Gathering and preparing data can be a costly process and I am all for tracking costs of a job.

I have to ask, does all the data you collect have a useful purpose in the big picture? For the last ten years or so I have just used QuickBooks to track job cost, assigning labor and materials to each job. Overhead and profit are tracked against actual as a percentage based on time spent on the project. I still have a handful of spreadsheets that I will use when more details are needed.

A few ideas would be to improve your spreadsheet design to make entry easier, or even go to a database setup. Regardless of the software used, the more detailed the data, the more time it will take to enter.



From contributor P:
You might take a look at a simple database like File Maker. The data is useful if you need to know how long that particular task takes to do. With store fixtures, there have been many times where we fabricated hundreds of a certain floor fixture. No 2 floor fixtures are the same; it is nice to know how long the various tasks take to do. Tractivity is a pretty good system.


From contributor T:

The suggestion about Filemaker Pro is a good one. If you can get your head around Microsoft Excel, you can certainly come to grips with the concept of a simple database. You won't become a power user without putting in the time to learn it, but you should be able to generate the information you're getting out of Excel with just a Sunday afternoon curled up with the software.

ERP software has been soundly thrashed on this forum for custom shops, but that doesn't mean you still can't or shouldn't attempt to parse the data, at least some of it.

Job costing tends to be approached as a retrospective on the project. While it is important to measure how well you did on the last job, it is more important that you become more successful on the next one. You don't want to constrain historical information to just being something predictive of how long tasks should take in the future. You want to use this data to ensure things take less time in the future.

If you start simple with your analysis, you can use this information to not just measure your costs, but actually lower them. Perhaps you could measure just one component of your product line. Take the process of trimming and hinging a door. Write down every step from loading the door into a vice, clamping on the hingebutt templates, mortising the first hinge, chiseling the corners, etc.

If you break it out into the 15 steps, you will see that two of them were unnecessary, and three of them could be done in less time if you eliminated the non-value added extra motions.

If something takes a $20 guy sixty minutes to get done, it is not a big reach to see how this could be done by a $15 guy in forty-five minutes. If you do this, your costs go from $20 to $11.25. Just half of that savings is significant. These are the kinds of analyses that you can run with a database program, but would struggle to get out of a spreadsheet.



From contributor A:
Well, your time tracking should come from the same data that payroll comes from if possible, so a system with a job cost module or system like Tracktivity that can link tracked time to payroll software would eliminate handling the data entry twice. The cost for data entry means more depending on how many employees. Data entry for a few employees is not an issue. I don't know if Tractivity has material tracking, but it's a nice system.


From contributor R:
I bought an elegant program called Taskklock that does time tracking through a touch screen entry. I have 55 shop floor employees, so I bought 8 touch screen setups. They clock into a job and into a task that is set up the way we track our time. If we are interested, we can track time down to a specific set of tasks on a single work order of a larger job, but generally we track the 6 main areas of the shop and also non-billable time like training, cleanup, etc. We can also enter materials for each work order for each project. The totals are uploaded to our accounting software every day so we can have a look at real time progress, though we don't very often. But the data goes straight to payroll after review by the production manager and has cut our office payroll processing time very steeply.


From contributor P:
We used one called Etc in the past. It worked well and you could track material usage. What I didn't like about it was the walking back and forth to the time clock. And somehow guys would often enter the start time twice - this would either make the time on the task very short or very long, depending. This was a pain. Since we had to check the data anyway, I decided to dump the Etc system. We went to a timesheet and a clipboard. This eliminated the walking back and forth to the time clock. And when the data is entered into the Erp software, it is checked willy-nilly. This data is the payroll data. It is then entered as a total into the payroll software (Adp online). This does not take much time.


From contributor N:
I too tried ETC, which I think has had a name change recently. We use swipe cards for when the guys stop and start work. The job cost software wasn't that flexible and required staff to go to a time clock to change their task or job number.

We recently tried a program from New Zealand called Empower. That has basic computers set up in the factory and uses an Access database that allows log on and off jobs and tasks within those jobs. However, I had a few issues with it and stopped using it. They have over 70 users, so it may have been just been me. Could be worth a Google.

I employed a computer programmer to write something basic in house. With modern software languages now, they pump out the programs fairly quickly. I keep track of what time everyone spends on the jobs and compare this to what I have allowed. I get this allowed time info from my Planit drawing program.



From contributor D:
I like having all my data on a spreadsheet. This way, I am in full control of how I want to process the info, look at it, eliminate this or that, play "what if" games. I like all my data in front of me being added, subtracted, and multiplied by my own formulas, in a manner in which I understand. I just don't trust the accuracy of calculations running in the background. With my spreadsheet, I can check accuracy of anything on the screen with a hand calculator in about 15 seconds. Also, with a spreadsheet there are no limitations to what you can do. I find other programs to be very restrictive as to how they process info.


From contributor P:
Database: a usually large collection of data organized especially for rapid search and retrieval (as by a computer).

Spreadsheet: a computer program used chiefly for accounting, in which figures arranged in a grid can be manipulated and used in calculations.

One is good for managing lots of data; the other is good for making calculations on a limited amount of data. I have tried the spreadsheet idea, and in my opinion, it is cumbersome and ineffective.



From contributor I:
We use a time clock that is PC based and it has been a pain in the butt to set up and use. (Time force by QQest) They have been the least pleasant company I have ever had to deal with. Incompetent help desk, long (30 minute or more) waits for help, buggy program, incompatible hardware (their own), etc.

But more important is to have a system that I can manipulate. One that I can troubleshoot, one that does not do things in the background that are not apparent, one that is based on a common data base. That way my data is mine, and I am not married to a system that sucks.

So here is my wish list.....
1.0) Make it File Maker Pro based. That way we can see what is going on and change it, trouble shoot it.


2.0) Set it up for reporting. Based on found sets?
3.0) Bar code for the time cards - employee, job number, task number.
4.0) Set up to punch in using cell phones or PDA for when we are out of the shop.


From contributor N:
The program I use is C++ to create the database. After trying a commercial program in Access it may cause problems and lockup computers. Also, it may grow into other areas you haven't thought of yet, thus pushing the database to its max. When you have a number of staff clocking in and out, the last thing you want to hear is that the computer is not working. The staff doesn't like being tracked, so any excuse not to use it will be used.

With our program we have added staff notes to try and personalize it, so on a Friday the office staff sends the workers a message such as "Thank goodness it's Friday" with a smiley face. The original reason was to say to staff, "Your sink has arrived for Mrs. Brown's kitchen. It's in bay A." Funny how things get warped for the better.



From contributor D:
Contributor B, that is why I love my spreadsheets so much. If I had the time, I could certainly program the software to perform every task you described. Sheesh! I would like to have that system for my own shop. Just curious as to what one would pay for a customized system like that. If I felt there was enough demand for it, and they were willing to pay a decent price, I may bite the bullet and do it. That is about 500-1000 man hours of programming alone. That does not include any hardware. I think you had better try to learn your QQest system.