PTO Employee Tools

I work for a local high-growth defense contractor called ImSAR. ImSAR develops technology for, as well as manufactures radars for the Air Force. A lot of their radars are also used in UAV’s, which has been a rapidly growing market over the last few years. As a defense contractor ImSAR is subject to a lot of regulation and government audits. They have to be able to show exactly where costs are coming from because they are usually paid by the government through cost plus contracts. Currently we use a QuickBooks solution to keep track of costs and manage the business, but we also have to keep a lot of records in excel because we need extra documentation that isn’t supported by the QuickBooks version we have. This can be unreliable and inefficient because data is kept in different places, for different uses, and the processes may not be defined well enough to keep everything up-to-date. There have been many erroneous entries, which can lead to misstatements in contract billings, or the financial statements. One specific area I looked into improving with a VBA program was payroll.

                The payroll process at ImSAR is somewhat labor intensive. There are over a hundred employees and each turn in a time sheet every week. The time sheet is a hard copy and usually has a number of codes to bill their time to. When I asked my manager what would be the best thing to build a program for she indicated that the Paid Time Off (PTO) process needed to be changed and automated. A set amount PTO is accrued for each hour a salaried employee works up to 80 hours for a two week period (payroll is processed every two weeks).

When an employee turns in the time card, the payroll manager will enter the number of hours worked in their individual worksheet in the workbook. This involved a lot of time just switching sheets, clicking on cells, and typing in the numbers. There was also a problem with entering new employees because sometimes the new employee entered would be formatted incorrectly messing up the calculations. There were actually a number of errors I found while updating this workbook, which have probably led to miscalculations in the past. Another change that would commonly lead to error was when an employees pay rate changed. A change in pay rate would change the total amount of PTO expense because the new pay rate would have to be applied to the total amount of accrued hours for that employee. There was no way to keep track of the difference between the historical amount of PTO expense and the new amount, which led to problems when documenting journal entries. My project is meant to fix these problems by developing a better workbook as well as macros to facilitate entry and calculations. 

Write up Link: http://files.gove.net/shares/files/14w/mcclells/PTO_Employee_Tool_Write_up.pdf

Workbook Link:  http://files.gove.net/shares/files/14w/mcclells/PTO_2014_-_VBA.xlsm