Business

How to Track Your Locksmith Job Profitability (Simple Spreadsheet)

Updated 2026-05-27. Locksmith School Blog editorial team.

Why Revenue per Call Doesn’t Tell the Whole Story

Most locksmiths track the amount they bill for each call, but that number alone hides the true health of the business. A $150 emergency lockout may look profitable until you factor in travel time, mileage, parts, and the overhead of running a shop. According to the U.S. Bureau of Labor Statistics, the median hourly wage for locksmiths in 2023 was $23.70 (bls.gov). If you spend 1.5 hours on a call, your labor cost alone is $35.55, not counting the hidden expenses that erode profit.

Understanding profitability per job lets you answer three critical questions:

• Which service categories generate the highest net margin?
• Where can you trim costs without sacrificing quality?
• How should you price new services to meet your income goals?

The answer is a simple, repeatable spreadsheet that captures every cost driver for each job. Below you’ll learn how to build, populate, and interpret that sheet so you can make data‑driven decisions.

Designing the Core Spreadsheet: Columns You Can’t Skip

Start with a blank Excel workbook (or Google Sheets). Create a header row that reflects every element that affects profit. The following columns are essential:

1. Date – When the job was completed.
2. Job Type – E.g., residential lockout, commercial rekey, high‑security lock installation.
3. Revenue – Total amount invoiced to the customer.
4. Travel Time (min) – Minutes spent driving to and from the site.
5. Travel Cost – Mileage × IRS standard mileage rate (currently $0.655 per mile) (irs.gov).
6. Labor Hours – Total hands‑on time, including diagnosis and paperwork.
7. Labor Rate – Your effective hourly cost (see “Calculating Your Labor Rate” below).
8. Parts Cost – Cost of cylinders, deadbolts, hardware, etc.
9. Tool/Equipment Depreciation – Portion of tool wear allocated to the job (e.g., $0.10 per use of a torque wrench).
10. Overhead Allocation – Share of rent, utilities, insurance, and marketing assigned to the job (usually a percentage of revenue).
11. Gross Profit – Revenue minus direct costs (Travel, Labor, Parts, Tool Depreciation).
12. Net Profit – Gross Profit minus Overhead Allocation.

Once the columns are set, you can copy the row for each new job and fill in the data. The spreadsheet will automatically calculate gross and net profit using simple formulas.

Calculating Your Labor Rate: From Wage to True Cost

Many locksmiths simply use the amount they bill per hour as their labor rate, but that ignores payroll taxes, workers’ comp, and benefits. To arrive at a realistic labor cost, follow these steps:

1. Determine your gross hourly wage (or the average wage you pay if you have employees). For a solo operator, use the median wage of $23.70 from the BLS as a baseline.
2. Add payroll taxes (Social Security + Medicare ≈ 7.65 %).
3. Add workers’ compensation insurance (average 2 % of payroll for small businesses, per the Small Business Administration) (sba.gov).
4. Add a modest benefits buffer (e.g., health insurance, retirement) – 5 % is a common estimate.

Example calculation for a solo locksmith:

• Base wage: $23.70
• Payroll taxes (7.65 %): $1.81
• Workers’ comp (2 %): $0.47
• Benefits (5 %): $1.19
• Total labor cost per hour: $27.17

Round up to $28 to simplify entry into the spreadsheet. This figure ensures you’re covering all labor‑related expenses before profit is considered.

Gathering Accurate Data: Real‑World Tips for Busy Technicians

Data collection is the most common stumbling block. Here are proven tactics that keep the process painless:

• Use a mobile time‑tracker. Apps like Toggl or Clockify let you start a timer the moment you leave the shop and stop it when you finish the job. Export the minutes to CSV and paste into the spreadsheet.
• Log mileage with a GPS app. Google Maps records distance traveled; copy the mileage into the “Travel Cost” column.
• Keep a parts receipt folder. Scan receipts with your phone and store them in a cloud folder. Summarize the cost per job weekly.
• Standardize tool depreciation. Create a master list of tools, their purchase price, and expected lifespan (e.g., 5 years, 1,000 uses). Divide purchase price by total uses to get a per‑use cost.
• Allocate overhead monthly. Total monthly overhead (rent, utilities, insurance, marketing) divided by the number of jobs performed that month gives a per‑job overhead figure. Apply that figure to each row.

By integrating these habits into your daily routine, the spreadsheet stays current without requiring a separate bookkeeping session.

Case Study: Turning a “Low‑Margin” Service into a Profit Center

John, a locksmith in Phoenix, AZ, noticed that residential lockouts were his highest volume but also his lowest profit. He entered ten recent lockout jobs into the spreadsheet with the following average data:

Using the formulas, John’s average gross profit per lockout was $120.80, but after overhead the net profit dropped to $105.80. While still positive, the margin (≈ 70 %) was lower than his commercial rekey jobs, which averaged a net profit of $180 on a $250 invoice (≈ 72 %).

John experimented with two changes based on the spreadsheet insights:

1. Increase the lockout rate by $20. The extra $20 raised net profit to $125.80 per job, improving the margin to 84 %.
2. Reduce travel cost by consolidating calls. By scheduling lockouts in geographic clusters, average travel time fell from 20 min to 12 min, cutting travel cost to $2.34 and net profit to $114.46 per job.

Within a month, John’s average net profit per lockout rose by 8 %, and his overall weekly profit increased by $350. The spreadsheet made the impact of each tweak visible, allowing him to prioritize the changes that delivered the biggest return.

Integrating Profitability Data with Customer‑Acquisition Strategies

Knowing which jobs are most profitable informs where you should spend marketing dollars. For example, if commercial rekeying yields a 72 % net margin, allocate a larger share of your budget to channels that attract commercial clients.

Two acquisition tactics that align well with profitability data are:

• Targeted local SEO. A simple one‑page website optimized for “commercial rekey services + [city]” can rank quickly. Why a Free 1‑Page Website Beats Google Ads for New Locksmiths explains how this approach delivers leads at a fraction of the cost per click.
• Referral incentives for high‑margin services. Offer existing residential customers a discount on a commercial rekey if they refer a business client. Track the referral source in your spreadsheet to confirm the ROI.

By matching marketing spend to the jobs that generate the highest net profit, you avoid the trap of chasing volume at the expense of margin.

Maintaining the Spreadsheet: Monthly Review Routine

Data is only useful if it’s current. Set aside 30 minutes at the end of each month for a “Profitability Review.” Follow this checklist:

1. Reconcile all entries. Verify that every job logged in your invoicing software appears in the spreadsheet.
2. Update overhead allocation. Adjust rent, insurance, or marketing costs if they changed during the month.
3. Calculate average net profit per job type. Use Excel’s PIVOT TABLE feature to group by “Job Type” and display average net profit.
4. Identify outliers. Highlight jobs where net profit fell below 50 % of the average for that category. Investigate whether the issue was excessive travel, parts waste, or pricing errors.
5. Set action items. For each outlier, create a brief note (e.g., “Reduce travel distance for lockouts in Zone 3”) and assign a deadline.

Consistent review turns raw numbers into a strategic roadmap, ensuring you continuously improve profitability.

Scaling Your Profitability System: From Spreadsheet to Pro Course

If you’ve mastered the basic spreadsheet and want to automate reporting, integrate it with accounting software like QuickBooks or Xero. Both platforms allow you to import CSV files and generate profit‑and‑loss statements by job class.

For locksmiths ready to deepen their financial acumen, the Locksmith School Blog Pro Course offers modules on advanced cost accounting, pricing psychology, and digital marketing analytics—all for $79.99 per month. Graduates report a 15‑20 % increase in net profit after applying the course’s pricing frameworks, which build on the concepts introduced in The Locksmith Pricing Matrix: How Top Earners Structure Rates.

Remember, profitability is a moving target. Regulations, material costs, and market demand shift over time. Periodically revisit your labor rate, overhead allocation, and pricing strategy to stay ahead of the curve.

Ready to take control of your bottom line? Start the Locksmith School Blog free signup and gain access to templates, video walkthroughs, and a community of locksmiths focused on profit.