These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Sunday, April 13, 2014

Timecard Reporting System

Scott Henderson
IS 520
Winter 2014

Executive Summary

My wife is the HR manager for a small, but growing company in the valley. They currently have around 60 employees, but anticipate this to grow substantially over the next few years. I found they were currently using a very long and tedious process in order to record and process their payroll. My wife would email each employee asking for their hours. They would then reply (sometimes with text in an email, sometimes by filling out a timecard template in Microsoft Excel and attaching it their return email. She would then take that data, manually convert the hours into decimal format and calculate overtime hours for each employee. This data would then have to be entered manually into a master spreadsheet.

Before the spreadsheet could be sent to the contracted payroll company for payment processing, all of the reported hours needed to be verified by each of the employee’s respective supervisor. So again these reports were manually created and sent to each supervisor for approval.  

This process is extremely repetitive. It is prone to calculation errors, along with many other minor errors that could cause the payroll to be completed incorrectly. When something has to be done in exactly the same way, multiple times over, it is a sure sign that it should be automated.

The owners of the company did not want to spend a lot of money creating a web portal in-house, nor did they desire to use an external online service. My solution is completely free. Using a custom built google form, and Microsoft VBA I was able to automate the process.
What would usually have taken the HR manager hours can now be done by clicking a button. I have generated a google form where the employees can go to report their hours for the week. These responses are stored on a google spreadsheet which can only be accessed by those authorized. My solution will automatically download this raw data file from google drive, import the raw data, calculate the regular hours, overtime hours and total hours for the week, and generate a summary spreadsheet in the desired format.

At this point a report can be automatically generated for each supervisor, listing all the information for each of the employees they manage. These reports are now able to be sent to the supervisors for validation.

This solution is a much more effective way to gather the information needed fir the company to process their payroll, and effectively saves more than 4 hours of work each week for the HR manager.

1 comment:

  1. Quantum Binary Signals

    Professional trading signals delivered to your mobile phone daily.

    Follow our signals right now & make up to 270% daily.


Blog Archive