Inability to run productivity reports led to inaccurate data and profit forecast problems

The Problem:

One of the companies I worked at measured employee productivity based on billable units produced. Each front line employee had a specific goal metric that there were supposed to meet in terms of productivity, which they calculated as a percentage of the employee’s total hours for the week compared to the number of billable hours they produced. If they worked 40 hours, for example, and produced only 20 billable hours, then they would have a 50% productivity.

The data was pulled from an Electronic health record system. Supervisors would sort the massive data sheets by hand, breaking it out by individual, and then manually calculating the billable hours based on the count and type of services provided and then manually looking up the employee’s hours and manually computing the productivity rating.

The supervisors hated doing this because it was a long process. It took an average of 4 hours to compile this report. HR hated this system because there were allegations that some of the supervisors might have been padding the numbers of their favorite employees. The employees hated it because it was often prone to error, which led to tension and disagreement with management. Management hated it because the financial estimates were often wrong and inconsistent.

The Solution: 

I spent a week building a massive Visual Basic program in Excel. The Clinical leadership could pull the master file from the EHR system and save it on the network drive and then open my program. It would open the data file, copy the contents into the workbook, close the data file, build out pages for every program and every site, automatically parse and filter the data, copy the data to each of the relevant sheets, automatically build the employee rosters, tabulate the service counts, compute the billable hours, add the billable wages, and it would build out pages to summarize these results by program, my payer source, and by service type. The only piece I couldn’t pull was the timesheet hours, but the IT director supplied that link and finalized my work as a web form.

The Results: 

The results were now accurate, automatic, and took approximately 20 seconds to build out the entire report for the company. There were 35 mangers who were spending 4 hours per week on this report before, and their average wages worked out to $40/hr. This equates to an estimated $291,200 in productivity costs saved in the first year alone, not to mention the additional benefit of having quick, accurate reporting at a moment’s notice.