How do I turn closed-job cost reports into a historical unit-cost table you can actually reuse with AI?
Most estimating departments have years of final job cost reports and almost no reusable unit costs, because every job was coded a little differently and nobody had time to divide cost by quantity after closeout. Export the final cost report and the installed quantities for each closed job, have Claude or ChatGPT map each job's cost codes to one shared code list, and have Copilot in Excel or Gemini in Google Sheets compute cost and labor hours per unit by code, with tags for job type, year and size so you only compare like with like. The AI does the sorting and arithmetic; which jobs count as comparable, how you escalate old costs and what number goes in a bid stay with you.
Estimator · Construction · Claude · ChatGPT · Microsoft Excel · Microsoft Copilot · Google Sheets
Content updated · Tool links listed below; vendor features and pricing change, so confirm with the vendor
Short answer
Use Claude or ChatGPT, then Copilot in Excel or Gemini in Google Sheets: Claude or ChatGPT proposes a mapping from each closed job's cost codes to your one shared code list and flags uncertain rows; Copilot or Gemini computes cost and labor hours per unit by job and code and summarizes low, median and high values across comparable jobs you filter. Most estimating departments have years of final job cost reports and almost no reusable unit costs, because every job was coded a little differently and nobody had time to divide cost by quantity after closeout. Export the final cost report and the installed quantities for each closed job, have Claude or ChatGPT map each job's cost codes to one shared code list, and have Copilot in Excel or Gemini in Google Sheets compute cost and labor hours per unit by code, with tags for job type, year and size so you only compare like with like. The AI does the sorting and arithmetic; which jobs count as comparable, how you escalate old costs and what number goes in a bid stay with you. A person still checks: every code mapping, a hand check of a few per-unit values against the cost report and takeoff, which jobs are comparable, and any escalation or adjustment before a number is used in a bid.
AI tie-in
Tool: Claude or ChatGPT, then Copilot in Excel or Gemini in Google Sheets
Claude or ChatGPT proposes a mapping from each closed job's cost codes to your one shared code list and flags uncertain rows; Copilot or Gemini computes cost and labor hours per unit by job and code and summarizes low, median and high values across comparable jobs you filter.
Prompt 1
Here is my shared cost code list [paste code and description] and the cost code list from one closed job [paste code and description]. Propose a mapping from each job code to one shared code in a table: job code, job description, shared code, shared description, and CONFIDENT or REVIEW. Mark REVIEW whenever the descriptions do not clearly match or one job code could belong to two shared codes. Do not merge or split codes and do not add codes that are not on my list.
Prompt 2
Here are the headers and a few sample rows of my CostReport, Quantities, Map and Jobs tabs [paste]. Write Excel formulas (or a step-by-step Copilot plan) that build cost per unit and labor hours per unit by job and shared code, and a summary by shared code with job count, low, median and high, filtered by the job type and year range in Settings. Add CHECK where cost has no quantity, quantity has no cost, a code is unmapped, or units differ within a shared code. Use only my data; do not supply unit costs or productivity from memory and do not escalate anything.
Human check: Every code mapping, a hand check of a few per-unit values against the cost report and takeoff, which jobs are comparable, and any escalation or adjustment before a number is used in a bid.
The problem
Turn closed-job cost reports into a historical unit-cost table you can actually reuse with AI. This is a common, time-consuming task for a estimator.
What this workflow does
Most estimating departments have years of final job cost reports and almost no reusable unit costs, because every job was coded a little differently and nobody had time to divide cost by quantity after closeout. Export the final cost report and the installed quantities for each closed job, have Claude or ChatGPT map each job's cost codes to one shared code list, and have Copilot in Excel or Gemini in Google Sheets compute cost and labor hours per unit by code, with tags for job type, year and size so you only compare like with like. The AI does the sorting and arithmetic; which jobs count as comparable, how you escalate old costs and what number goes in a bid stay with you.
How it works: AI drafts or analyzes. A person operates the tools, checks the work, and takes action.
Your checkpoint: Historical unit costs describe what happened on past jobs, including their mistakes, scope gaps and site conditions. The table does not tell you what the next job will cost. Code mapping, which jobs are comparable, escalation, productivity adjustments and every number that goes into a bid are the estimator's decisions.
Before you start
- Access to the named tool; confirm license, version and cost with the vendor.
- A small, non-confidential sample to try the workflow on first.
- A qualified person to review the output before it is used.
Tools and access
Follow the steps
- Pick three to five recently closed jobs you trust. For each, export the final job cost report by cost code (labor cost, labor hours, material, equipment, subcontract) and, separately, the installed quantity and unit for the codes you want to track, from the takeoff or the as-built quantities.
- Keep a Codes tab with your one shared code list. Paste each job's code list into Claude or ChatGPT and have it propose a mapping from that job's codes to the shared list, marking anything it is not sure about as REVIEW. Fix the REVIEW rows yourself.
- Add a Jobs tab with one row per job: an internal job ID (no client or project name needed), job type, completion year, size and anything that made it unusual, such as winter work, an occupied building or a heavy change order load.
- Have Copilot in Excel or Gemini in Google Sheets build a table by job and shared code with total cost by type, quantity, cost per unit, and labor hours per unit, plus CHECK flags for codes with cost but no quantity, quantity but no cost, or units that do not match the shared list.
- Build a summary by code that shows the low, median and high per-unit values and how many jobs sit behind each, filtered by the job type and year range you choose. Do not average across mismatched units or job types.
- Keep the historical table separate from the rates you bid with. When you use a historical number, note which jobs it came from and any escalation you applied yourself.
Try a small example
Replace the bracketed fields with your own project details. No tested sample run is published for this workflow yet.
Sample input
Fill in the bracketed [fields] in the text below with your project data.
Starter prompt
Here are the headers and a few sample rows of my CostReport tab [paste: job ID, job cost code, description, labor cost, labor hours, material, equipment, subcontract], my Quantities tab [paste: job ID, job cost code, installed quantity, unit], my Map tab [job ID, job cost code, shared code] and my Jobs tab [job ID, job type, completion year, size]. Write Excel formulas (or a step-by-step Copilot plan) that build a table by job and shared code with total cost by type, quantity, unit, cost per unit and labor hours per unit, and a summary by shared code showing count of jobs, low, median and high per-unit values, filtered by job type and a year range I type in Settings. Add CHECK where cost exists with no quantity, quantity exists with no cost, a job code has no mapping, or units differ within a shared code. Use only my data; do not supply any unit cost or productivity from memory and do not apply escalation.
Expected output
Not published yet. Treat any output as a draft and check it against the source documents.
Check the result
- Every value, citation or quantity traces back to a source document.
- Missing information is marked as missing, not filled in.
Limits and recovery
- Historical unit costs describe what happened on past jobs, including their mistakes, scope gaps and site conditions. The table does not tell you what the next job will cost. Code mapping, which jobs are comparable, escalation, productivity adjustments and every number that goes into a bid are the estimator's decisions.
- Check your organization’s data policy before sharing project files with third-party tools.
Stop or switch when: The source cannot be verified, permissions are missing, or the result contains unsupported facts.
Frequently asked questions
How does AI help with this?
Claude or ChatGPT, then Copilot in Excel or Gemini in Google Sheets: Claude or ChatGPT proposes a mapping from each closed job's cost codes to your one shared code list and flags uncertain rows; Copilot or Gemini computes cost and labor hours per unit by job and code and summarizes low, median and high values across comparable jobs you filter. A person still checks: Every code mapping, a hand check of a few per-unit values against the cost report and takeoff, which jobs are comparable, and any escalation or adjustment before a number is used in a bid.
How do I how do I turn closed-job cost reports into a historical unit-cost table you can actually reuse with AI??
Pick three to five recently closed jobs you trust. For each, export the final job cost report by cost code (labor cost, labor hours, material, equipment, subcontract) and, separately, the installed quantity and unit for the codes you want to track, from the takeoff or the as-built quantities. Keep a Codes tab with your one shared code list. Paste each job's code list into Claude or ChatGPT and have it propose a mapping from that job's codes to the shared list, marking anything it is not sure about as REVIEW. Fix the REVIEW rows yourself. Add a Jobs tab with one row per job: an internal job ID (no client or project name needed), job type, completion year, size and anything that made it unusual, such as winter work, an occupied building or a heavy change order load. The full step list is above.
Does this use an AI agent or regular software?
It is labeled AI assists: AI drafts or analyzes. A person operates the tools, checks the work, and takes action.
What tools do I need?
Claude, ChatGPT, Microsoft Excel, Microsoft Copilot, Google Sheets. Confirm license, version and cost with the vendor, and try it on a small non-confidential sample first.
Has this workflow been tested?
Workflow guide · not independently tested. Written from vendor documentation and practitioner use. Labels apply to the steps described here, not every feature of a product. Links were checked when the page was written.
Evidence and maintenance
Written from vendor documentation and practitioner use. Labels apply to the steps described here, not every feature of a product. Links were checked when the page was written.