I’m looking for an experienced Excel freelancer to improve an existing revenue and payroll workbook for my service company.
Current Setup
We already have an Excel workbook where we record jobs and revenue every day.
Each job has things like:
Date
Client/job name
Job revenue
Our technicians are paid a percentage of the revenue from the jobs they complete, generally between 20% and 30%.
Payroll is paid weekly, and I currently have to manually calculate how much revenue each technician completed and then calculate their percentage every Friday.
What I Need Built
I want to keep my current revenue sheet and add a simple system that connects it directly to a Payroll Calculator sheet.
For every job on the Revenue sheet, I want a Technician dropdown/tag where I can select which employee completed that job.
Example:
DateJob Revenue Technician
8/24 Client A $549 Alex
8/24 Client B $499 Ben
8/24 Client C $200 Aukahi
The employee list should be stored in one location so technicians can easily be added or removed later.
Payroll Calculator
I want a separate payroll page that automatically calculates each technician's numbers for a selected week.
It should show:
Technician name
Technician's pay percentage
Total job revenue completed that week
Number of jobs completed
Percentage payout / commission owed
Any manual adjustments or bonuses
Total weekly payroll owed
For example:
Alex
Weekly completed revenue: $5,000
Pay percentage: 20%
Payroll owed: $1,000
I want to be able to select/change the week start and week end dates, and the payroll totals should automatically update.
Important
I do not want to enter jobs or revenue twice.
The payroll calculator must pull directly from the revenue we are already entering on our existing Revenue sheet.
The finished system should be:
Simple to use
Reliable
Easy for someone without advanced Excel knowledge
Mostly automatic
Easy to add/remove technicians
Easy to change technician pay percentages
Able to handle hundreds/thousands of job rows
Designed so payroll takes only a few minutes to review each Friday
Please use appropriate Excel formulas, tables, dropdowns/data validation, and/or Power Query if necessary.
I will provide the existing Excel workbook. I would prefer that you modify and improve the existing workbook rather than rebuild our entire revenue tracking system from scratch.
Please include examples of similar Excel automation/payroll work you have completed.