Case Studies

Employee Review Fix

We had a client that built a custom spreadsheet for doing employee reviews. There was a spreadsheet for each employee and they were all linked to a master spreadsheet. In year one, it worked fine. They had less than 100 employees at the time. In year 2, they had over 200 employees, the links were for last year’s spreadsheets and the master spreadsheet had over7,000 links back to all of the employee sheets. Add to that, the employee who built it was gone, and they had a serious problem. My goal was to get them through this year as easily as possible and then help them plan for next year. I took out all of the links in the master spreadsheet, wrote an MS Access program what would grab all of the spreadsheets, pull the data and then write it directly into the master spreadsheet. There were no bad links to worry about. The master spreadsheet was modified multiple times by management during the process and we were able to easily respond and adjust it. The reviews were finished on time.

bgcadminEmployee Review Fix
read more

Piece Work Payroll

Piecework payroll can be a very beneficial way to pay non union construction workers, if done fairly. The state of California makes it very difficult to do this though. When I first sat down with my client, they were calculating payroll using a spreadsheet. One of the big problems was that they couldn’t easily calculate certain fractions of an hour and so they were forced to round up. Working with the payroll admin and getting approval from the labor attorneys, we developed a simple system in Access that properly calculated the time, calculated the proper “Bonus” time, generated finalized time sheets for all employees and created the import file for the payroll vendor. The program immediately saved money just on the proper rounding of the time. Also, the reporting made the accounting clerk’s job much easier, giving her time to work in other areas of the business. Later we added the ability to pull the job estimates into the system so that they could quickly report budgets to superintendents.

bgcadminPiece Work Payroll
read more

Custom Invoices

Sage 50 (Peachtree for those of you old enough to remember) is a powerful lower end accounting package… But it can’t do everything. My client has customers with very specific invoicing requirements. Some customers needed more details than the typical invoice template and others needed a very different look altogether. Some customers wanted their invoices emailed, others didn’t. The email, contact name and street addresses in the system weren’t always the ones that we needed to send the invoices to. For pulling data from accounting systems, I love to use MS Access and an ODBC connection (which Sage supports). I created a few tables to keep the additional data needed and then built a process that would pull invoice data when needed, and allow, with just a few clicks, the ability to send and/or print all of the invoices for the time period. The customers are now happy and the client gets the job done faster and in a more organized manner.

bgcadminCustom Invoices
read more

Finding the Right Tools

Their filing cabinets were overflowing. Every week, my client generated a ream of paper reports for their payroll. Not only did they need to digitally save these, but they needed to save them in a fashion different from their standard backups. The first step was digitizing them. I found them a nice scanning package that would work with their existing infrastructure and quickly scan the documents into organized directories. Because these files were numerous, but small, I suggested for reliability and price we set up an AWS account and save them into standard S3 folders and then move the older ones into Glacier folders for permanent storage. Using off the shelf software, we were able to automate the backup and upload process and relieved the client of much of their mess in the office.

bgcadminFinding the Right Tools
read more