THE PROBLEM: Payroll was heavily redundant. Payroll was registered through this system:

  1. Field construction workers sign off their weekly hours
  2. Project managers verify that the hours are correct.
  3. Field secretary translates paper copies into an Excel sheet that was pre-made and e-mails it to company headquarters.
  4. Payroll accountant re-enters bulk data by hand into an existing Excel sheet which used macros to re-format the data. After the data was formatted it was copy-and-pasted into a plain text file and imported to a database for processing.
When encountered with this problem I saw that there was too much room for human error, along with heavily redundant data entry. Step 4 was reported to take almost an entire workday to do, depending on workload at the company.

THE OBSTACLES: Because the field was remote it was not certain that any construction site would have internet access. Guaranteeing internet access would have cost too much money due to the subscription charges over time as well. Because changing the field entry portion would cause no large impact (since the data had to be manually inserted at one point in time anyway) it was decided that this step would not be changed.

THE SOLUTION: The custom sheet used at headquarters was totally reworked. Used as a slate, everything was reworked piece by piece. The first change was simply cleaning up the sheet. The current sheet used merged cells, ActiveX controls, macros that were intolerant of change, and only one workbook. The entire spreadsheet was navigated by automatically scrolling left and right and disabling the scrollbars. These sections were split into individual workbooks for easier and reliable navigation.

After the sheet was cleaned up I began work on importing directly from the field. This step would eliminate the human from an entire step. Because the field sheet was already broken into very specific parts I used it, to my advantage, and had the import simply pull from specific cells. However, the availability of sections was unreliable as some data entry was sometimes done at the bottom and noted with special circumstances. A subroutine was written to check for blank sections, which sped up processing. Total import time was measured in less than 30 seconds. Import time depended on how many entires were being imported. I fell back onto Excel (as opposed to writing my own, possibly slower routines) to match names and numbers to speed things up.

The program handled all possible variations. Job numbers were not always numbers and job code specifications (for accounting records) were not always the same. Job codes could be anywhere from 6 to 13 characters, and the ADP software had specific rules on importing. Complying to ADP's import rules was the most challenging aspect of this program.

An entire workbook as dedicated to employees and their information. This acted as a very crude database. Access was implemented at the end, but will most likely not stay as tends to slow import down, and the time added is not worth it at this point in time. Employee information was added using a custom form and deleted by simply deleting the row. Employee hours were also recorded and would warn the user if an employee logged too many regular hours without going into over time (per union rules). This accounted for varying rules on weekends as well. Per diem information (such as gas money or hotel stays) was added and automatically added approprietly.

THE VERDICT: The program took a previously several-hour process (upwards of 7 hours at times!) to a matter of minutes (including the verification and submitting to ADP!) This program saved many hours in accounting that were increasingly needed with a growing company.