Export six to twelve months of quotes and invoices with the hours and material cost of each job, tag every job by task type, and have an AI assistant work out typical hours per task. Price each task from those hours at your rate, set a minimum charge, test the guide on three recent jobs, and refresh it every quarter.
Generic price guides describe an average business. A guide built from your own jobs reflects your hours, your supplier costs and your drive times, which is the only version that can tell you whether a quote will make money. It needs one thing generic guides do not: a record of the hours each job actually took, not only what you charged.
All the figures here are invented for illustration: an electrician charging $90 an hour, with nine months of jobs to learn from. Swap in your own at every step.
What to export from your invoices, and what to strip out first
Pull one row per job with these columns: date, customer type (domestic or commercial, kept apart because they behave differently), the invoice wording, quoted price, invoiced price, on-site hours counting every person (two people for three hours is six), drive time, materials at what you paid for them, and whether the job came back as a callback. Most accounting and job-management tools can export invoices or timesheets to CSV or Excel somewhere; look for an Export button on the list or under Reports, because the menu names differ. The general version of this idea, turning old invoices into a rate card, is in the tutorial on building a rate card from old invoices. This one applies it to electrical tasks, adds a minimum charge and finishes with a three-job test.
If hours were never recorded, start now. For the old jobs, estimate from diary entries and mark those rows "estimated", and treat them as a stop-gap until you have three months of real hours.
Strip customer names, addresses, phone numbers and email addresses before anything goes into a chat tool, and keep the invoice wording to the work. Consumer ChatGPT and Claude plans have a model-training switch in privacy settings, and business plans do not train on your content by default. Either way, upload nothing you would mind a stranger reading. ChatGPT's free plan is limited to 3 file uploads a day, which is enough for one spreadsheet, and its data analysis reads .csv, .xls and .xlsx files by writing and running Python on them. Claude accepts CSV files too, and XLSX files once code execution and file creation are switched on in its settings.
In the example, nine months produced 142 invoices. Nine had no hours, three were duplicates, and twelve bundled several tasks into one line with a single total of hours, so they could not be separated. That left 118 usable jobs.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowTurning 118 messy invoice lines into task types
Invoice wording is free text: "Fit 2 x dbl sockets kitchen + replace old switch", "new board 10 way". An assistant can sort that into a fixed list of task types faster than you can, as long as you hand it the list and forbid guessing. This is the prompt I would use:
Tag each electrical invoice line below using only these task types:
1 Socket or switch swap, 2 Socket spur, 3 Ceiling light swap, 4 Downlights,
5 Outdoor light or socket, 6 Smoke or heat alarms, 7 Distribution board swap,
8 Fault finding, 9 EV charger, 10 Other.
For each line return: line number, task type, quantity (for downlights and alarms, the
number of fittings), confidence (high, medium, low) and a reason in under 10 words.
If a line contains two tasks, return two rows and flag it "combined".
If you cannot tell, write "Needs a human". Do not guess.One run of that prompt returned this (illustrative):
| Line | Invoice wording | Tag | Qty | Confidence |
|---|---|---|---|---|
| 14 | Fit 2 x dbl sockets kitchen + replace old switch | Socket spur, and Socket or switch swap (combined) | 2 and 1 | High |
| 27 | Breaker tripping, traced to damp outdoor socket | Fault finding | 1 | High |
| 31 | New board 10 way + labels | Distribution board swap | 1 | High |
| 45 | Smoke alarms x3, linked | Smoke or heat alarms | 3 | High |
| 52 | 6 downlights lounge | Downlights | 6 | High |
| 63 | Misc electrics 2 hrs | Needs a human | - | Low |
Line 14 needs a decision. It holds two tasks with one set of hours, and there is no honest way to split those hours between the tags, so combined lines come out of the statistics. That is where the twelve removed lines came from. Line 63 is correctly marked "Needs a human": leave it out rather than guess. Then spot-check ten rows the assistant marked high confidence. If two are wrong, tighten the task list (the usual culprit is a task type that overlaps another) and run it again before going further.
A task needs at least eight jobs before it earns a price. In the example, 105 of the 118 jobs fell into seven task types with enough jobs behind them. Eight were fault-finding, which stays hourly. Three were EV charger installs, too few to price yet, and two were one-offs.
Typical hours: use the median, then read the spread
Take one task. Fourteen jobs that each added a double socket to an existing circuit took, in hours: 0.8, 1.0, 1.0, 1.1, 1.2, 1.2, 1.3, 1.5, 1.5, 1.6, 1.8, 2.0, 2.5 and 4.0. The median, the middle job when you sort them, is 1.4 hours. The average is 1.61, pulled up by the 4.0-hour job, which was a damaged cable found behind a wall. Three quarters of the jobs took 1.75 hours or less.
A median can under-price a task that overruns often, so I compare the 75th percentile with it. If the 75th percentile is less than 1.25 times the median, I price on the median. At 1.25 or more, I use the midpoint of the two. Here 1.75 divided by 1.4 is exactly 1.25, so the hours used are 1.58. That rule is a starting point of mine, not a standard, and the three-job test further down is what tunes it.
Using the cleaned CSV (columns: task_type, hours, materials_cost, customer_type), give me,
for each task type with at least 8 jobs: number of jobs, median hours, 75th percentile hours
and median materials cost. Do not use averages.
List every job you excluded and why. Keep domestic and commercial separate.
Then give me one spreadsheet formula for each figure so I can check it.Asked for "typical hours", an assistant can return the average, and that is how the sockets would come back as 1.61 hours. It may also drop the 4.0-hour job without telling you, or blend commercial jobs into the domestic figures. The prompt above closes all three gaps. Whatever comes back, check one task yourself. With the task type in column C and the hours in column F, these formulas work in Google Sheets and in the versions of Excel that Microsoft lists as having FILTER (Microsoft 365, 2021 and 2024):
=COUNTIF(C2:C119,"Socket spur")
=MEDIAN(FILTER(F2:F119,C2:C119="Socket spur"))
=PERCENTILE(FILTER(F2:F119,C2:C119="Socket spur"),0.75)If your figure and the assistant's disagree, the two of you are looking at different rows. Find out which of you is right before you price anything. The guide to using ChatGPT with Excel covers asking for formulas like these and pasting them in safely.
Quoted hours against actual hours: where your gut price leaks
If your export has the quote as well as the invoice, you can test your own judgement before you replace it. Ask the assistant to work out the hours each quote implied, which is the quoted price minus the materials you charged for, divided by your hourly rate, and put the median of those next to the median of the actual hours. For this electrician the result was uncomfortable:
| Task | Hours implied by quotes | Median actual hours | Gap |
|---|---|---|---|
| Socket or switch swap | 0.40 | 0.50 | +25% |
| Double socket on an existing circuit | 1.10 | 1.40 | +27% |
| Ceiling light swap | 0.50 | 0.60 | +20% |
| Downlight, per fitting | 0.50 | 0.55 | +10% |
| Outdoor light or socket | 2.00 | 2.30 | +15% |
| Smoke or heat alarm | 0.40 | 0.50 | +25% |
| Distribution board swap | 6.00 | 6.50 | +8% |
Every task ran longer than it was quoted, and four of the five quick tasks, which make up most of the job count, ran 20% to 27% over. On the double sockets alone, 0.3 hours of unquoted time at $90 is $27 a job, or $378 across the fourteen jobs. A guide built from the quotes would simply repeat the habit, which is why the export needs actual hours and why the guide starts from them. Treat the implied hours as a rough read, because they depend on how cleanly each quote separated labour from materials. If your quotes show one lump sum, tell the assistant that, and ask it to flag the rows where it had to assume a split.
From hours to a price for each task
The rule is hours used x $90, plus the median materials cost plus 20%, rounded to the nearest $5. The $90 is your charge-out rate, and it should come from your wages, van, overheads and billable hours, not from a rival's website. The 20% on materials is a handling policy, so change it to yours. For the seven tasks with at least eight jobs:
| Task | Jobs | Median hours | 75th percentile | Hours used | Materials | Guide price |
|---|---|---|---|---|---|---|
| Socket or switch swap, like for like | 26 | 0.50 | 0.60 | 0.50 | $9 | $55 |
| Double socket on an existing circuit | 14 | 1.40 | 1.75 | 1.58 | $14 | $160 |
| Ceiling light swap | 19 | 0.60 | 0.70 | 0.60 | $6 | $60 |
| Downlight, per fitting (loft access) | 17 | 0.55 | 0.65 | 0.55 | $11 | $65 |
| Outdoor light or socket with a switch | 9 | 2.30 | 3.00 | 2.65 | $38 | $285 |
| Mains smoke or heat alarm, per alarm | 12 | 0.50 | 0.55 | 0.50 | $24 | $75 |
| Distribution board swap | 8 | 6.50 | 7.50 | 6.50 | $260 | $895 |
Take the board as a check on the arithmetic: 6.5 hours x $90 is $585, materials of $260 x 1.20 is $312, and the total of $897 rounds to $895. Only the double-socket row and the outdoor row used the midpoint rule, because only their 75th percentile ran at 1.25 times the median or more. Where the customer chooses the fitting, quote a fitting allowance as well; the tutorial on fitting allowances and provisional sums covers the wording.
Fault-finding stays hourly at $90, with the minimum charge set below as its floor, because nobody can price a cause they have not found. A task with fewer than eight jobs, like the three EV charger installs, stays "estimate only" on the sheet and gets quoted individually until the count builds up.
Set the minimum charge from your smallest jobs
Small jobs lose money quietly, because the drive and the paperwork cost the same as on a large job. Add up the smallest realistic visit: 0.5 hours on site, 0.67 hours of driving (20 minutes each way) and 0.25 hours for the quote, the parts run and the invoice. That is 1.42 hours, and at $90 an hour it comes to $127.80. I would set the minimum at $125, a round figure within $3 of that.
The export shows why it matters. Sixteen of the 118 jobs, 14%, took 30 minutes or less on site. They had been charged about $60 each, against a cost of about $88: 1.42 hours at $62 an hour, which is what an hour of your time costs you before any margin. That is a loss of roughly $450 over nine months. At $125 each, the same jobs would have made about $590, a swing of $1,040. The tutorial on how much to charge for a service call builds the fee from costs in more detail.
I would word it on the guide like this: "Minimum charge $125, including up to 30 minutes on site. Further time is charged in 15-minute units at $90 an hour. Permit fees and special-order parts are passed on at cost." A minimum charge changes who rings you, and the guide cannot predict how many small enquiries you will lose. Count enquiries and bookings for a month after you introduce it, and note why people walk away.
Test the guide on three jobs it has never seen
Pick three recent jobs that were not in the export: one small combination, one big one and one with an awkward feature. Price each from the guide, then compare the result with what you charged and with what the job cost you, which is actual hours x your cost per hour plus materials. Here your cost is $62 an hour, so labour at $90 carries a 31% margin by design. This prompt does the comparison:
Here is my price guide (task, hours_used, materials, price) and three finished jobs that
were not used to build it. For each job: price it from the guide, then compare with what I
charged, the actual hours and the actual materials. My cost is $62 an hour.
Show the margin at the guide price and at what I charged.
If a job is more than 10% off, say which task row is the likely cause.| Job | Guide price | Charged | Hours | Materials | Cost | Margin at guide | Margin at charged |
|---|---|---|---|---|---|---|---|
| 1. Two double sockets and a switch swap in a home office | $375 | $340 | 3.5 | $41 | $258 | 31.2% | 24.1% |
| 2. Distribution board swap and three smoke alarms | $1,120 | $1,050 | 7.8 | $331 | $815 | 27.3% | 22.4% |
| 3. Six downlights with no loft access, plus two light swaps | $510 | $480 | 7.0 | $84 | $518 | -1.6% | -7.9% |
Jobs 1 and 2 pass. Job 1 runs above the design margin because combined tasks share one set-up and one trip, so a few dollars of discount on combinations would still leave you comfortable. Job 3 fails, and so did your gut price, by more. The reason is in the task name: the downlight median came from jobs with loft access, where a fitting takes 0.55 hours. Here, each fitting took 0.85 hours because every hole was cut and wired from below. The fix is to split the task into two rows. A downlight with no loft access uses 0.85 hours and prices at $90 a fitting, which puts job 3 at $660 and its margin at 21.5%. Add a question to your enquiry form, "Is there loft access above the room?", so the right row gets picked on day one.
My pass mark: a positive margin on every job, and nothing far from the 31% design figure unless you can say why. One failure means fixing that task row, not rebuilding the guide.
When to refresh the guide, and what to ask the AI
Re-run the export every quarter for the last three months and compare it with the guide. Refresh sooner when your supplier prices move by 5% or more across the materials you use most, when your hourly rate changes, when you add a task type, or when a job misses by more than 15%. The prompt stays short:
Here is my guide (task, hours_used, materials, price) and last quarter's tagged jobs.
For each task with at least 5 new jobs, compare the new median hours with hours_used and the
new median materials with the guide. List only the tasks where hours differ by more than 15%
or materials by more than 10%, show how many new jobs each is based on, and show the price
the new figures would give. Do not change any price yourself.A refresh run might return this (illustrative):
| Task | Hours: guide / new | Materials: guide / new | New jobs | Price: guide / new |
|---|---|---|---|---|
| Downlight, per fitting (loft access) | 0.55 / 0.66 (+20%) | $11 / $11 | 6 | $65 / $75 |
| Mains smoke or heat alarm, per alarm | 0.50 / 0.50 | $24 / $28 (+17%) | 9 | $75 / $80 |
| Distribution board swap | 6.50 / 6.50 | $260 / $292 (+12%) | 5 | $895 / $935 |
I would not accept all three. The downlight jump rests on six jobs, and the first thing to check is whether a few no-loft jobs were tagged into the loft task by mistake, which would also explain the rise. The alarm and board changes are plain supplier price rises: confirm the new costs on your latest supplier invoices, then update. The prompt tells the assistant not to change prices itself because a price list is a decision, and the assistant has no way to know which of those rows reflects a trend and which reflects a bad week.
Put the date, the hourly rate and the materials percentage in a header row on the guide, and keep each old version. When a customer disputes a price six months later, you can show how it was built and which version applied.
After a quarter, three numbers tell you whether the guide is earning its keep. Of the jobs priced from it, how many finished with a margin within ten points of the design figure? How many of those misses trace back to one task row, which you can then fix? And how long does a quote take now compared with before? If quoting a standard task has dropped from a calculation to a lookup, the guide is doing its job. If the misses are scattered across rows with no pattern, the problem is usually the data, such as hours logged against the wrong job, not the method.
What a price guide cannot do for you
It prices repeatable tasks. Anything with an unknown condition, such as fault-finding or "it trips sometimes", stays hourly, because the guide has no number for a cause you have not found.
It does not know what the work requires. Code, testing and sign-off stay with the person qualified to give them, and a price built from your hours assumes you do the work correctly, not cheaply. An assistant can help with paperwork wording, but the tutorial on whether ChatGPT can answer code questions safely explains why it should not be your source on what the code asks for.
It cannot tell you what customers will accept. Keep a count of quotes sent and quotes won for each task. If you win nearly every quote, the guide may be leaving money on the table. A run of lost quotes is a reason to compare scope and wording first, and price second.
And it is no substitute for your own numbers. A rival's published prices, or an AI's idea of a typical price for the task, carry someone else's hours, supplier discounts and drive times. Once the price exists, the next job is turning it into a quote the customer can read: the electrician quote template covers the scope and exclusions wording.
Questions before you build the guide
How many past jobs does a task need before it gets a price?
I would want at least eight jobs of the same task type. With fewer, mark the task as estimate only, quote it individually and keep logging hours. Once you pass eight, take the median and the 75th percentile, price it, and let the three-job test and the quarterly refresh show whether the number holds.
Should drive time go into every task price?
Keep it out of the task rows and cover it with the minimum charge for small jobs and a travel line for distant ones. On bigger tasks the drive is a small share of the hours, and burying it in every price hides how far you actually travel and which customers cost you the most to reach.
Can I mix domestic and commercial jobs in one guide?
Keep them apart. Commercial work tends to bring different access, working hours, paperwork and payment terms, so hours per task differ. Tag the customer type in the export, build separate medians, and only merge two groups when their medians are within about 10% of each other.
Do I show customers the hours behind a price?
There is no need. Show the price and what it includes, and keep hours and material costs in your own sheet. If a customer asks why a price is what it is, labour at your rate, materials and a minimum charge are an easy explanation that does not hand over your cost structure.
Further reads
- Is ChatGPT Good for Electrical Estimating? Use It to Check Bids — Use ChatGPT to check a bid you have already priced.
- Electrical Change Orders Written With AI: Ask for Extra Work Clearly — Charge for extra work found once the job has started.
- 10 ChatGPT Prompts for Electricians: Quotes, Emails and T&Cs — Ready-made prompts for the quote text that follows the price.
- Tradify for Electricians: Quotes, Jobs and AI SmartTools Set-Up — Where the guide could live inside job software.
- Van Stock Management for HVAC and Electrical Vans: Reorder Levels With AI — Keep material costs steady by managing van stock.
- Electrical Contractor Proposals: Scope and Exclusions With AI — Turn priced work into a proposal with clear exclusions.
- Xero for Electricians: Capture Van Receipts and Supplier Bills With AI — Capture the supplier costs the guide depends on.
- AI Electrical Quoting Software: Build Three-Option Upgrade Packages — How to build three comparable packages for a panel or consumer unit upgrade: what quoting software and AI draft for you, and the checks before a quote is sent.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: OpenAI help pages on file uploads and data analysis in ChatGPT; Microsoft Support page for the FILTER function. All hours, rates and prices in the examples are invented for illustration.