How to Build a Job Costing Sheet in Excel With AI Help

Build the sheet as four linked tables: Rates (loaded labour rate, overhead rate per hour, material prices), Jobs (one row per job with its quote), Cost lines (every cost logged against a job number) and a Summary that compares quote with actual. Ask AI to write and explain the formulas; keep the rates and costs in your own hands.

The formulas are the easy part, and AI gets them right most of the time. What goes wrong is the numbers feeding them: labour entered at the wage rather than what an hour really costs you, an overhead rate nobody has worked out, and hours spent on consultations or remakes that never get logged. A sheet with perfect formulas and those gaps will tell you every job made money when some of them did not. If you'd like to understand the formulas rather than just paste them, the Excel formulas guide explains the lookup and summing functions a sheet like this relies on.

Follow me on Instagram@sagnikteaches

The four tabs a job costing workbook needs

Keep each kind of information in one place and let formulas pull it together. When a rate changes, you change one cell and every open job updates. When you want to know why a job lost money, you filter one table by job number and see every cost line.

Connect on LinkedInSagnik Bhattacharya

Turn each range into an Excel table (select it and press Ctrl+T) and give it a name such as tblRates, tblJobs and tblLines. Named tables grow as you add rows, and formulas that refer to tblLines[LineCost] are far easier for you and an AI assistant to read than Sheet3!$G$2:$G$900.

Subscribe on YouTube@codingliquids

Stage 1: Work out the two rates most sheets get wrong

Allow an hour for this stage, with last quarter's profit and loss report open. It decides whether the margins your sheet shows are real.

The loaded labour rate

An hour of a bench jeweller's time costs more than the hourly wage. Add employer costs such as pension contributions, payroll taxes and paid holiday. If someone earns $24 an hour and those extras come to about 18 per cent, the loaded rate is roughly $28.30. Use your payroll reports for the real percentage; it varies by business and by how much holiday and sick pay you carry.

The overhead rate per productive hour

Overheads are the costs you pay whether or not a job is on the bench: rent, insurance, software, utilities, marketing, the accountant, equipment upkeep. Divide the monthly total by the hours your team actually spends on paid work, not the hours they are paid for. Admin, photography, social media and cleaning are real work, but no customer pays for them directly.

Here is how I would ask an AI assistant to do the sum and challenge it at the same time:

I run a small handmade jewellery business. I want an overhead rate per productive hour
for my job costing sheet.

Monthly overheads (average of last quarter):
Rent 1,650 | Insurance 310 | Software and website 240 | Utilities 280
Marketing 900 | Accountant 250 | Equipment maintenance 400

Two bench jewellers, each paid about 150 hours a month. Roughly 60% of their time
is on customer commissions; the rest is admin, photography and cleaning.

1. Calculate the overhead rate per productive hour. Show the working.
2. List any overhead categories a jewellery workshop usually has that I have left out.
3. Tell me which of my figures looks unusual and why.
Do not invent figures for missing categories; just name them.

An illustrative reply, trimmed:

Total overheads: 4,030 per month.
Productive hours: 2 x 150 x 0.60 = 180 hours.
Overhead rate: 4,030 / 180 = 22.39, so about 22.40 per productive hour.

Possibly missing: bank and card terminal fees (unless you cost them per job),
tools and consumables such as saw blades, polishing compounds and pickle,
phone and broadband, stock insurance if separate from general insurance,
depreciation on the laser welder or CAD computer.

Unusual: marketing is 22% of overheads. That may be right for an online seller,
but check whether any of it is paid ads tied to specific commissions.

The maths is right, and the missing-costs list is useful. What I would fix: consumables such as polishing compounds and saw blades are a real monthly cost, so add a line for them rather than ignoring the prompt. And the 60 per cent is a guess. Two weeks of honest time records will tell you whether it is 60 or 45, and at 45 per cent the overhead rate jumps to about $29.85. That one assumption moves every margin in the sheet.

Stage 2: Ask AI for the table structure and formulas

Give the assistant your column names and three sample rows, not the whole file. Describe the result you want in plain words. This stage takes 30 to 60 minutes including testing.

I'm building a job costing workbook in Excel 365 with three tables:
tblRates: Name, Wage, OnCostPct, LoadedRate
tblJobs: JobID, Customer, Quoted, EstHours, Status
tblLines: JobID, Date, Type, Person, Description, Qty, UnitCost, LineCost

Type is one of: Labour, Materials, Outwork, Other.
For Labour lines, Qty is hours and UnitCost should come from tblRates using Person.
For other lines, UnitCost is typed in.
Overhead per productive hour is in a named cell called OverheadRate.

Write formulas for:
1. tblLines[UnitCost] for labour lines (typed value otherwise)
2. tblLines[LineCost]
3. A Summary table with one row per JobID: actual hours, labour, materials,
   outwork, other, overhead, total cost, margin, margin %, hours over estimate %.
Use structured references. Explain each formula in one sentence.

A typical reply gives you something close to this (illustrative):

UnitCost (labour lookup):
=IF([@Type]="Labour", VLOOKUP([@Person], tblRates, 4), [@UnitCost])

LineCost:
=[@Qty]*[@UnitCost]

Summary, actual hours for the job in A2:
=SUMIFS(tblLines[Qty], tblLines[JobID], A2, tblLines[Type], "Labour")

Summary, overhead:
=B2*22.40

Three problems, all common. The VLOOKUP has no fourth argument, so Excel uses an approximate match and can quietly return the wrong person's rate when the names are not sorted. The UnitCost formula refers to itself for non-labour lines, which creates a circular reference. And the overhead formula has the rate typed into it instead of pointing at the OverheadRate cell, so next quarter's rate change will not flow through.

Ask for a fix in the same chat and say exactly what is wrong. A cleaner version splits typed prices from calculated ones:

tblLines gets a new column, TypedCost, where people enter prices for non-labour lines.

UnitCost:
=IF([@Type]="Labour", XLOOKUP([@Person], tblRates[Name], tblRates[LoadedRate], "CHECK NAME"), [@TypedCost])

LineCost:
=[@Qty]*[@UnitCost]

Summary overhead (actual hours in column B):
=B2*OverheadRate

Margin %:
=IF(C2=0, "", (Quoted-TotalCost)/Quoted)

The "CHECK NAME" text shows up in the cell whenever someone types a person who is not on the Rates tab, which turns a silent error into a visible one. XLOOKUP needs Excel 2021, Excel 365 or Excel for the web; if you are on an older version, ask the assistant for an INDEX and MATCH equivalent.

Stage 3: Log costs against job numbers as they happen

A job costing sheet is only as good as the discipline of logging. Make the logging quick:

• Drop-down job numbers. Use Data Validation on tblLines[JobID] with a list drawn from open jobs, so nobody logs time against "Smith ring" one day and "SMITH-R" the next.
• Drop-down types and people. The same trick for Type and Person stops misspellings from breaking the lookups.
• Log at the end of each bench session. Hours remembered on Friday for a Tuesday job are guesses. If you can, capture time automatically and paste it in weekly.
• Log remakes as their own lines. Description "Recast: porosity" is worth more than folding the cost into the original casting line, because it tells you how often remakes happen.
• Log consultation and design time. Client calls, sketch revisions and CAD changes are labour on the job. They are also the hours most often left out.

A bespoke engagement ring costed from quote to finish

Take an illustrative commission at a two-person handmade jewellery business: a bespoke ring quoted at $2,400. The estimate on the Jobs tab looked like this, using the rates from Stage 1 (loaded labour $28.30 an hour, overhead $22.40 an hour).

The ring still made money, just much less than the quote suggested. The sheet points at the cause. Hours ran 50 per cent over, and most of the overrun was time the estimate never included: consultations and design changes. The recast was bad luck, but bad luck that happens often enough deserves a line in the estimate.

Two changes followed. Future bespoke quotes include three hours for consultations and revisions as a standard line, and casting estimates carry a 10 per cent contingency. Re-running this job with those changes (12 estimated hours, casting at $71.50, card fees still 3 per cent of the price), hitting the same 19.4 per cent target margin needs a quote of about $2,600. That is the number the owner needed to know before saying yes.

The same sheet for a brewery or a roaster

The four-tab shape works for any business that does jobs, but the cost driver for overhead changes with the business. Two illustrations:

A contract brew for a pub's house beer

A small craft brewery taking on a 20-hectolitre contract batch is not limited by people's hours so much as by fermenter time. The tank sits occupied for, say, 18 days whether anyone touches it or not. So the Rates tab carries a cost per tank-day (the fermenter's share of rent, depreciation, cleaning chemicals and cooling, divided by the tank-days available in a year) alongside the labour rate. Cost lines then include malt, hops, yeast, CO2, kegs or cans, lab tests, brewing hours and tank-days. A contract that looks profitable on ingredients and hours can turn out thin once 18 tank-days at, say, $35 each ($630) go on the job. Ask the AI assistant to add a TankDays column and a TankDayRate named cell, the same way it handled OverheadRate.

A private-label roast for a café group

A specialty coffee roaster doing a private-label blend has a different trap: roast loss. Green coffee loses weight in the roaster, so 100 kg of roasted coffee needs noticeably more than 100 kg of green beans. Your own roast logs give the loss percentage for each profile. The cost line for green coffee should be calculated as roasted kilos ÷ (1 − roast loss %) × green price per kilo, not roasted kilos × green price. At an illustrative 16 per cent loss, 100 kg roasted needs about 119 kg green; costing it at 100 kg understates the biggest cost line by about a sixth. This is a good question for the assistant: "Add a RoastLossPct column to tblRates and change the Materials line cost for green coffee to account for it."

Asking AI about the finished jobs

Once 20 or 30 jobs are closed, the sheet becomes something you can question. You have three routes, depending on what you pay for.

• Copilot in Excel. Open the Copilot pane from the Copilot icon in Excel and ask in plain words. Full Copilot in Excel needs a Microsoft 365 Copilot licence (Copilot Business lists at $21 per user a month on annual billing) or, on a personal account, Microsoft 365 Premium at $19.99 a month. Note that Microsoft retired the =COPILOT() worksheet function on 14 September 2026, so older guides telling you to type it into a cell no longer work; use the pane instead.
• ChatGPT or Claude with an uploaded copy. Upload the Summary and Cost lines tabs as a file. Both assistants can run calculations on the data rather than estimating by eye. Remove customer names first.
• Claude inside Excel. If you would rather work in the workbook itself, the Claude for Excel add-in reads and edits the open sheet.

A question worth asking once a quarter, with an illustrative answer:

Using the Summary and Cost lines tables, which job types lost the most margin
against their estimates in the last three months? For each, tell me which cost
type caused most of the gap. Show the figures you used.

Across 31 closed jobs, estimated margin averaged 21% and actual margin 12%.

Bespoke rings (9 jobs): estimated 20%, actual 8%. Labour caused 71% of the gap;
actual hours averaged 38% over estimate. 4 of 9 had consultation time logged
that was not in the estimate.
Repairs (14 jobs): estimated 24%, actual 22%. Close to estimate.
Earring collections for stockists (8 jobs): estimated 18%, actual 14%.
Metal price changes caused most of the gap.

Before acting on that, check two numbers yourself: filter tblLines to the nine bespoke rings and total the labour hours, and confirm the 31-job count matches the closed jobs on your Jobs tab. Assistants occasionally drop rows with blank cells or read a text-formatted number as zero. The limits of Copilot with your numbers are worth knowing before you rely on its summaries.

Five ways a job costing sheet quietly misleads you

1. Labour at the wage, not the loaded rate. This is the most common one. It shows up as margins that look several points healthier than your profit and loss account at month end. If the jobs say 20 per cent and the business barely broke even, check this first.
2. Overhead counted twice. If you add a flat overhead percentage to materials and also charge overhead per hour, you have counted rent twice. Pick one method.
3. Stock used but never costed. Findings, chain and packaging pulled from the drawer rarely get logged. Either log them or add a small fixed "consumables" line per job based on a month's usage divided by jobs.
4. Quote changed, sheet not. A customer adds engraving and you agree $60 by message. If the Jobs tab still says $2,400, the margin looks worse than it is. Add a "Quote revisions" column and a note.
5. Jobs closed before the last cost arrives. Outwork invoices can land a fortnight after delivery. Keep a status of "Delivered, costs open" and only mark a job Closed when every supplier bill is in.

Testing the sheet before you trust it

Spend 20 minutes on these checks after building the workbook and after any big change the AI makes to it:

1. Run a test job with known answers. Create job TEST-01 with 2 labour hours for one person, one $100 material line and nothing else. Work out by hand what the Summary should show (2 × loaded rate + $100 + 2 × overhead rate). If the sheet disagrees, a formula is wrong.
2. Break it on purpose. Log a labour line for a person not on the Rates tab. You should see CHECK NAME, not a zero and not somebody else's rate.
3. Reconcile to your accounts once a month. Total material and outwork cost across all jobs finished in the month should be close to the matching lines in your profit and loss account. A big gap means costs are being bought but not logged against jobs.
4. Compare total hours logged with hours paid. If the team was paid for 300 hours and the sheet has 120 hours of job time, either your productive-hours assumption is wrong or logging is patchy. Both change the overhead rate.

If you find yourself maintaining dozens of formulas, several people logging from phones and supplier bills retyped from the accounts, that is usually the sign to read about when to move beyond manual Excel. Until then, a well-built sheet and a clear quoting habit tell you most of what you need. For pricing the next job from what this one taught you, the checks in whether AI can price a job accurately pair well with the Summary tab, and if the AI's arithmetic ever looks off, why AI slips on maths explains what to check.

Job costing in Excel: follow-up questions

Should I cost jobs in Excel or buy job costing software?

Excel is fine while you run up to a few dozen jobs a month and one or two people log costs. Once several people need to log time from phones, or you want costs flowing in from purchase invoices automatically, a job or project tool linked to your accounting software saves more time than the spreadsheet does. Build the Excel version first anyway: it teaches you which cost lines matter.

How often should I update the overhead rate?

Recalculate it every quarter from your latest profit and loss figures, and immediately after any big change such as a rent rise, a new hire or a drop in chargeable hours. Keep the rate in one cell on the Rates tab so every job picks up the new figure, and note the date it changed so older jobs can still be compared fairly.

Can I paste real job data into ChatGPT or Claude?

Job costs are usually less sensitive than customer records, but they still reveal your margins and supplier prices. Use a business plan that does not train on your content by default, or switch off the model-training setting on a personal plan, and remove customer names before uploading. For most formula questions you only need column headings and three sample rows.

What margin should a job costing sheet flag?

Pick the threshold from your own history rather than a rule of thumb. Look at the last twenty finished jobs, find the margin below which a job was not worth doing once you count your time, and flag anything under that figure. Many owners also flag any job whose actual hours ran more than a quarter over the estimate, because hours drift first.

Further reads

• How to Track Profit per Project in a Service Business With AI — The same idea for service projects billed over weeks rather than single jobs.
• Break-Even Analysis With AI: A Worked Example for a New Product — Uses the same fixed and variable cost split to price a new product.
• Capacity Planning With AI: Know When Your Team Is Full — Your chargeable hours figure drives the overhead rate; plan them properly.
• Quote to Cash: Connect Quotes, Invoices and Payments With AI — Connect the quote in your costing sheet to the invoice and payment.
• How to Use Claude With Excel and Google Sheets Safely — What to share and what to keep out when AI works on your workbook.
• AI Time Tracking for Small Teams: Hours Without Timesheets — Get accurate labour hours into the sheet without chasing timesheets.
• How Event Planners Use AI to Build Budgets and Run Sheets — Turn an event brief into a line-item budget and a timed run sheet with AI, while supplier quotes and spreadsheet formulas keep the numbers honest.
• Turning On-Site Voice Notes Into Job Sheets and Quotes With AI — A spoken site checklist, the free transcription already on your phone, and two prompts that turn a rambling voice note into a job sheet and a draft quote.
• How Electricians Use AI to Count Fittings From Drawings — How symbol search and trained detection count electrical fittings, which tools do it and what they cost, and the checks that stop a miscount reaching a tender.
• Can AI Produce Accurate Estimates for a Small Building Firm? — Where AI speeds up a builder's estimate, where its prices drift, and a rate-library method that keeps the numbers yours.
• Where Should a Small Manufacturer Start With AI? — Why a small manufacturer's first AI project belongs in the office, how to score the options, and a six-week RFQ pilot with real numbers.
• AI Shift Scheduling vs a Rota Spreadsheet for Small Teams — A rota spreadsheet is fine until swaps, availability and rules outgrow one person. Costs, a tested AI-drafted rota, and the team size where apps win.
• How to Check Your Margins Product by Product With AI — Work out what each product really earns after discounts, shipping, fees and returns, with AI doing the sums in code and you checking three products by hand.
• How to Compare Supplier Quotes Side by Side With AI — Use AI to pull three messy quotes into one like-for-like table, spot what each leaves out, and recheck the totals in a spreadsheet before you choose.
• AI Quality Control for Small Manufacturers: Where to Start — Cost your quality problems, digitise defect records, let AI find the few defects that matter and their causes, then decide between a process fix and a camera.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Support page for the COPILOT function (retirement notice) and Copilot in Excel; Microsoft 365 Copilot plan pricing; Microsoft Support documentation for XLOOKUP, SUMIFS and Excel tables.