Every day, this company would pull data from QuickBooks Desktop into a spreadsheet, play with it, create PowerPoint slides (30 or more), convert those slides to picture files (JPG) and then load them onto a Roku box for display on TV screens in their office. There were charts and graphs and reports. This took one person 1.5 to 2 hours EVERY DAY to do. Instead of having QuickBooks create a spreadsheet, I used tools to directly access QuickBooks, pull all of the data needed, manipulate it onto spreadsheets, create the charts and reports and save them as JPG files, ready for upload to the Roku box. All of this took about 10 to 15 minutes. It was also accurate. The first time they showed me their process, it turned out the day before they were off by a 100,000 dollars. That type of mistake was eliminated with this new process.
Case Studies
Getting The Data to SharePoint
A company had 30 partners and they each needed a section of the Accounts Receivable sent to them as a report. 7 years ago, we used a web portal to interface with a BI module that came with the accounting package and a bunch of Excel code. Times have changed and with all of the partners working from home, we needed to create a more reliable system. The first step was dumping the module and pulling the data directly from the accounting tables. Then, using PowerShell, we pushed all of the files up to a SharePoint site that we set up. We even automated maintenance by creating a script that would delete all files that are over 60 days old. Originally, we were creating about 60 reports and posting them to the portal in about 1.5 to 2 hours. Now we are creating over 120 reports and posting them within about 20 minutes.
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.
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.
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.
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.