Most locksmith businesses measure themselves one job at a time. You know the 2am lockout on the north side took forty minutes to reach, you know the Ford transponder job paid better than three counter key cuts, you know last week’s smart-lock install went in clean. What is much harder is answering the question an owner, a partner or a bank actually asks – was last month any good? – because the answer lives in a dispatch log, a till roll, a technician’s day sheet and a folder of survey texts, and none of them is a page.
The Locksmith Business KPI Scorecard in Excel is that page. Ten locksmith-specific KPIs across six groups, a month picker, an MTD / YTD switch and traffic lights that understand which direction is good. It ships with a full 2025 sample year already typed in, so you can see exactly how it behaves before you replace a single number.
Before anything else, one clarification, because NextGenTemplates ships three families with near-identical names. This is the KPI Scorecard line: a monthly reporting page built on a month picker and a fixed, renameable KPI list. It is not the slicer-driven Excel KPI Dashboard line and it is not the analytical Excel Dashboard line. If you want to slice a transaction table interactively, one of those two is the right buy. If you want a monthly close on one page, keep reading.
Key Features of the Locksmith Business KPI Scorecard
- Ten locksmith KPIs on one page, grouped into Emergency, Service Ops, Key & Auto, Sales, Security and Customer.
- A month picker that runs Jan-2025 to Dec-2025, driven by a single reporting-year cell.
- MTD / YTD option buttons – report the month or the year to date without touching a formula.
- A Vs. selector with three comparison bases: Target, the same period last year (PY), or the Prior Month.
- Direction-aware traffic lights. Nine of the ten KPIs are Upper-the-Better; Avg Lockout Response Time is Lower-the-Better, so it scores correctly when it falls.
- A 12-month sparkline on every tile, under the value, the target, the absolute change and the percentage change with a coloured arrow.
- A KPI Analysis page with achievement by group, a matching column chart, Green / Amber / Red counts and Top 5 / Bottom 5 tables.
- A KPI Trend page that draws four charts for whichever single KPI you pick.
- Twenty KPI slots, ten filled, with a KPI 1-10 / KPI 11-20 picker in the Scorecard header.
- Editable RAG bands on their own sheet, separately for Upper-the-Better and Lower-the-Better KPIs – and they are the live thresholds, not a printed description of them.
- No macros. Formulas, conditional formatting, camera pictures and sparklines only, in a plain .xlsx.
The Ten Locksmith KPIs, and Why They Are the Ten
These are not generic business metrics with the word “lock” glued on. Each one answers a question a locksmith actually gets asked.
| # | KPI | Group | Unit | Direction | Formula as shipped |
|---|---|---|---|---|---|
| 1 | Avg Lockout Response Time | Emergency | Minutes | LTB | AVERAGE(Arrival Time – Call Received Time) |
| 2 | Emergency Call Volume | Emergency | Count | UTB | COUNT(Emergency Lockout Jobs) |
| 3 | Jobs Completed | Service Ops | Count | UTB | COUNT(Completed Work Orders) |
| 4 | First Visit Fix Rate | Service Ops | % | UTB | Jobs Resolved On First Visit / Total Jobs |
| 5 | Keys Cut | Key & Auto | Count | UTB | SUM(Keys Cut) |
| 6 | Auto Key Programming Jobs | Key & Auto | Count | UTB | COUNT(Transponder & Fob Jobs) |
| 7 | Service Revenue | Sales | USD (000s) | UTB | SUM(Invoiced Service Revenue) |
| 8 | Avg Ticket Value | Sales | USD | UTB | Total Revenue / Jobs Completed |
| 9 | Security Install Jobs | Security | Count | UTB | COUNT(Lock & Access Installs) |
| 10 | Customer Satisfaction | Customer | % | UTB | AVERAGE(Post-Job Survey Score) |
The pairing matters more than any single row. Avg Lockout Response Time next to Emergency Call Volume tells you whether a busier month broke the call-out promise. Service Revenue next to Avg Ticket Value separates a month of many small key cuts from a month of fewer, larger installs. Keys Cut next to Auto Key Programming Jobs keeps the counter trade and the automotive trade from hiding each other. And First Visit Fix Rate next to Jobs Completed is the only honest way to tell a quality slip from a run of harder jobs.
Everything in that table is a label you can overwrite on the KPI Definition sheet – name, group, unit, formula text, definition, direction and YTD basis. Rename a row and every page follows, because each page looks its KPI up by name.
Page by Page: What Each Sheet Does
Eleven sheets: nine visible, two hidden calculation sheets. You type on exactly two of them.
Scorecard
The tile wall, and the page you will actually print. Ten tiles, each with a traffic light, the value, the target, the absolute change, the percentage change with a coloured arrow, and a 12-month sparkline. The header carries Select Month, the MTD / YTD option buttons, the Vs. dropdown and the KPI set picker.
KPI Analysis
The management summary. Achievement by KPI Group as both a table and a column chart, the Green / Amber / Red / total counts, and Top 5 and Bottom 5 tables that rank all ten KPIs by achievement against the comparison basis you selected.
KPI Trend
One KPI at a time. Pick it from the dropdown and the page prints its group, unit, direction and the formula and definition you wrote for it, then draws four charts: MTD Actual Vs Target, MTD Actual Vs PY, YTD Actual Vs Target and YTD Actual Vs PY. This is the page you open when somebody disputes a number.
Input Data
The sheet you maintain. Twenty numbered blocks, one per KPI slot, each holding twelve month rows with MTD Actual / Target / PY and YTD Actual / Target / PY. Ten blocks are filled with the 2025 sample year and ten sit empty, labelled “(empty slot)” until you name them. The month column uses three-letter labels – Jan through Dec – and so does the picker in the Scorecard header, so the two always agree.
KPI Definition
The other sheet you maintain, and the one to get right first: number, group, name, unit, formula, definition, Type (UTB or LTB) and YTD Basis, plus a Check column that reports “OK” per row and warns you if you create a duplicate name.
Color Settings, Read Me and Get More Templates
Color Settings holds the RAG bands – Green at or above target, Amber within 10%, Red beyond that – as editable cells, separately for Upper-the-Better and Lower-the-Better KPIs, plus the report title and reporting year. These six cells are wired straight into every traffic light, every Status on KPI Analysis and the Green / Amber / Red counts, so changing 10% to 5% recolours the workbook immediately. Read Me is nine short sections explaining the wiring. Get More Templates is the catalogue page and the customisation contact.
Reading the September 2025 Sample Month
Open the file, leave the month on Sep-2025 with MTD and Vs. Target selected, and this is what the workbook says:
- 4 Green, 4 Amber, 2 Red across the ten KPIs.
- Emergency is the weakest group at 91.2% and Amber. Emergency Call Volume came in at 189 against a 222 target – 85.1%, the second-worst KPI of the month – while Avg Lockout Response Time ran 24.7 minutes against a 24.0-minute target. Because response time is Lower-the-Better, that small overshoot shows as a red rising arrow with an Amber light, not a false green.
- Key & Auto is Amber at 91.9% and split down the middle: Keys Cut was Green at 1,423 against 1,396, while Auto Key Programming Jobs was the worst KPI in the month at 154 against a 188 target – 81.9%.
- Service Ops is Amber at 99.0%. Jobs Completed was Green at 624 against 612, but First Visit Fix Rate slipped to 90.8% against a 94.6% target.
- Sales is Amber at 98.3% – Service Revenue Green at $149.5K against $147.3K, Avg Ticket Value Amber at $178.2 against $187.5.
- Security is Amber at 92.3% – Security Install Jobs 72 against 78.
- Customer is the only Green group at 101.0% – Customer Satisfaction 97.0% against a 96.0% target.
That is a recognisable month: the counter and the service side both delivered, and revenue held up, but automotive programming and emergency volume both missed and the ticket softened. The Bottom 5 table puts Auto Key Programming Jobs and Emergency Call Volume at the top of the list, which is where a management conversation should start.
How to Set It Up for Your Own Business
- Color Settings first. Type your report title and reporting year. The year feeds the month picker so it reads Sep-2025 rather than a bare Sep. Widen or narrow the Amber and Red bands if 10% is not your tolerance – they are live cells, and the Upper-the-Better and Lower-the-Better sets move independently.
- KPI Definition second. Decide your ten (or twenty) KPIs and, above all, get the Type column right. UTB or LTB drives both the light and the arrow, and changing it after three months of reports is confusing for everyone who has been reading them. Response times, callback rates and cost-per-job are LTB; almost everything else on a locksmith scorecard is UTB.
- Input Data third. Fill the numbered block for each KPI: twelve rows, MTD Actual / Target / PY and YTD Actual / Target / PY. The workbook does not roll YTD up for you – a sum is right for keys cut and job counts, an average is right for a response time or a satisfaction score – so type the finished YTD figure and record the rule you used in the YTD Basis column.
- Then just read it. Scorecard for the wall, KPI Analysis for the group story, KPI Trend when a single number is challenged.
- Save-as each January. One cell changes the reporting year, so a new year is a copy of the file, not a rebuild.
Who Should Buy This – and Who Should Not
Buy it if you run a locksmith business doing emergency lockouts, residential and commercial service, key cutting, automotive transponder work or security installation, and you report monthly to an owner, a partner, a bank or yourself; if your numbers currently live in a dispatch log, a till and an inbox; or if you want a response-time metric and a margin metric on the same sheet of paper for the first time.
Do not buy it if you want live data. This workbook has no connector to any dispatch platform, POS or accounting package, no import routine and no API. You type the numbers. If you need automated job costing or live technician tracking, buy field service software instead – this is a reporting page, and it is honest about that.
Limitations You Should Know Before Buying
- Manual entry. Twelve months multiplied by six columns per KPI is the setup cost. After that it is six numbers a month per KPI.
- YTD is yours. Deliberate, but it does mean you calculate the roll-up yourself.
- Single user. It is a desktop .xlsx. Two people cannot edit it simultaneously. If you need shared editing, a Google Sheets scorecard is the answer – though there is no Locksmith edition in Sheets yet.
- Twenty KPI slots, ten shown at a time. The Scorecard is a fixed layout of ten tiles with a set picker; it does not grow to fifteen tiles.
- No key control, no code storage. This is a totals-only reporting page. It keeps no customer table, no job list, no key register, no bitting records and no lock combinations, and it makes no licensing, bonding or insurance compliance claim of any kind.
- No units conversion. “Minutes” and “USD (000s)” are text labels. Change them and type figures to match; nothing converts anything.
- The header controls are desktop controls. The dropdowns and option buttons work best in Excel for Windows or Microsoft 365. Excel mobile will read the file but is uncomfortable to drive.
Best Practices
- Set targets once a year, not once a month. A target you edit whenever you miss it is not a target, and the Vs. Target comparison stops meaning anything.
- Use the PY column properly. Locksmith work is seasonal – the sample year peaks for lockouts in the winter months and for key cutting in spring – so month-on-month tells you less than the same month last year. That is what the Vs. PY option is for.
- Write real definitions. The definition you type on KPI Definition is printed on the KPI Trend page. Spelling out whether “response time” starts at the call or at dispatch saves an argument six months later.
- Get the LTB flag right on every time-based KPI. A response time flagged UTB will reward you for being slower. It is the single most common way a scorecard goes quietly wrong.
- Do not over-tune the RAG bands. If everything is green every month, the page has stopped doing its job.
- Print the Scorecard, then open KPI Trend live. One page for the room, one page for the challenge that follows.
Explore Relevant Templates
Locksmith Business KPI Scorecard in Excel is the template described here.
There is no Google Sheets edition of the locksmith scorecard yet – this Excel workbook is the only edition on the site today. If you need a browser and shared access, the closest live equivalent on the same month-picker layout is the Electrical Contractor KPI Scorecard in Google Sheets.
Neighbouring service trades on the same scorecard line: Electrical Contractor KPI Scorecard in Excel, Plumbing Business KPI Scorecard in Excel, HVAC Contractor KPI Scorecard in Excel, Roofing Contractor KPI Scorecard in Excel and Drywall Contractor KPI Scorecard in Excel.
Want slicers and an interactive dataset instead of a month picker? That is the KPI Dashboard line: Electrical Contractor KPI Dashboard in Excel, Plumbing Business KPI Dashboard in Excel and HVAC Contractor KPI Dashboard in Excel.
More on this blog: Bakery KPI Scorecard in Excel, Winery KPI Scorecard in Excel and Pet Care KPI Scorecard in Excel.
Frequently Asked Questions
Does it connect to my dispatch software, POS or QuickBooks?
No. There is no connector, no import routine and no API. You type each month’s figures on Input Data.
Do I need to enable macros?
No. The file is a plain .xlsx – formulas, conditional formatting, camera pictures and sparklines. There is nothing to enable.
Which Excel versions work?
Excel 2016 and later on Windows, and Microsoft 365. It opens on Excel for Mac and Excel mobile for reading, but the header dropdowns and option buttons are most comfortable on desktop.
Can I use my own KPIs instead of these ten?
Yes, all twenty rows are yours. Overwrite the name, group, unit, formula, definition, type and YTD basis on KPI Definition, then fill the matching numbered block on Input Data. The Check column warns you if two KPIs end up with the same name, which would otherwise make the lookups add them together.
Can I really move the amber and red thresholds?
Yes – they are live cells on Color Settings, not documentation. Every traffic light on the Scorecard, every Status on KPI Analysis and the RAG counts read those six cells directly, and the Upper-the-Better and Lower-the-Better bands are independent, so you can be strict on response time and generous on call volume.
Is this the same as your Locksmith KPI Dashboard?
No – and at the time of writing there is no locksmith edition on the KPI Dashboard line at all. In general: this is the KPI Scorecard line, a monthly reporting page with a month picker, a fixed KPI list and traffic lights. The KPI Dashboard line is slicer-driven and built for interactive analysis; the Dashboard line is analytical reporting over a full transaction table. Three different products, three different jobs.
Does it store key codes, key control records or customer details?
No. It holds monthly totals only – ten KPIs, twelve months, six figures each. There is no customer table, no job list, no key register, no bitting or code storage, and it makes no licensing or bonding compliance claim.
What is in the download?
One ZIP containing the .xlsx workbook and the Excel KPI Scorecard user manual as a PDF. Nothing else.
Conclusion
A locksmith business does not fail because nobody measured it. It drifts because the measurements were never on the same page at the same time, in the same shape, month after month. Ten KPIs, six groups, one month picker and a set of traffic lights that know which way is up will not open a single door – but they will make next month’s owner meeting take twenty minutes instead of two hours, and they will tell you which of response time, service quality, key work, automotive, security installs or customer satisfaction is the one to fix first.
Get the Locksmith Business KPI Scorecard in Excel on NextGenTemplates.com
