Weekly Payroll Management System - Jordan Kerr

 Executive Summary

I currently work for AMP Security in the payroll department. AMP is a summer sales security company. AMP has been in business for about 5 years and has relatively new processes in place for most of its accounting procedures. In the payroll department, I am required to determine our sales representatives pay on a weekly basis. The sales reps get paid on a commission basis and have quite a few unique rules to calculate their pay. Right now we have relatively few tools to help with this process, and most of the process is all done manually. Essentially, our payroll department work load is currently equivalent to about 60 to 80 hours per week. My project reduces the time of our payroll to about 30 to 40 hours per week.

With our current ERP system, we have to export the sales data and format it to our needs. After we have done that, we have to go through each sales rep in our ERP system and determine how many sales they have made, what pay level they should be at for that week, and determine if they have any special arrangements about their pay with their manager or an owner of the company. Additionally, we have to determine what their taxable income would be, if applicable.

My project automates most of the manual processes of the weekly payroll. The project builds a formatted workbook from the given data, based upon the rules and policies of payroll. The project calculates what the reps’ pay level should be. It also provides a workbook that can be used throughout the entire payroll process as a control to verify each rep’s pay is calculated correctly. The project also builds a formatted workbook from the given data for the security technicians as well.

My project also includes several other macros that sets up several other worksheets needed to perform the payroll process. Specifically, one macro will filter an exported employee list from our ERP to show those reps/techs who do not have a commission scheme set up in our ERP. The second macro formats the exported employee list into a simplified dataset that can be used for VLOOKUPs into other datasets. The last macro does the VLOOKUPs from the simplified employee list into the payroll process so that all of the relevant employee information is available in the payroll worksheets.

Again, this project has turned our payroll workload from a 60 to 80 hour week down to a 30 to 40 hour week. This tool is meant to help us reconcile rep commission schemes, build a template to reconcile rep pay, and build a template for tech payroll.