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.
No comments:
Post a Comment