Free workbook from Construction Metrics and AutomationTactics
The AI Business Review for contractors
Eight questions about your company, the report that answers each one, a prompt that has an AI assistant run the numbers, and a check you can do in Excel before you trust the result.
What is inside
| # | Question | Report you need | How often |
|---|---|---|---|
| 1 | Which types of jobs make the most money?Group closed jobs by type, size, client and project manager, and compare the gross profit and margin each group earned. | Closed-job report from job cost | Quarterly |
| 2 | Do job margins hold from bid to closeout?Compare each job's bid gross profit with this month's estimate, find the jobs that faded, and see whether fade follows an estimator or a project manager. | This month's and last month's WIP schedules, and the original estimates | Monthly, after the WIP is final |
| 3 | Are we overbilled or underbilled, and on which jobs?Run the WIP arithmetic on every open job, find the underbillings that need a call this week, and tie the net position to cash. | Job cost ledger, accounts receivable and the project managers' estimates at completion | Monthly, at close |
| 4 | Which bids are worth the estimating time?Measure win rate by client type, job size and estimator, in counts and in dollars, and find where estimating hours go without a return. | Bid log or preconstruction tracker | Quarterly |
| 5 | Where do change orders come from, and how long do they take to approve?Split approved change orders by cause and job, measure the days from submission to approval, and age the change orders still pending. | Change order log, with the original contract for each job | Monthly |
| 6 | How long does it take to get paid?Measure days sales outstanding with and without retainage, the days each customer takes to pay, and the retainage still held on finished jobs. | Accounts receivable aging and 12 months of paid invoices | Monthly |
| 7 | Are active jobs on schedule and on budget?Compute earned value, SPI and CPI for every active job from the pay application, the baseline schedule and job cost, and rank the jobs that need attention. | Latest pay applications, baseline schedule or planned billing curve, and job cost | Monthly, after pay applications go out |
| 8 | How many months of work are in backlog, and at what margin?Turn signed contracts into backlog in months, the gross profit still to be earned, and a month-by-month view of revenue for the next year. | Signed contracts list, this month's WIP, and last year's revenue and overhead | Monthly |
| Run the review every monthPut the eight questions on a calendar with an owner for each, save the prompts, and automate the steps that repeat every month. |
How each chapter works
- ExportThe report that answers the question, with the columns it needs.
- PrepareRemove subtotals and personal data, and fix formats.
- PromptA prompt that spells out every formula and asks for totals.
- CheckRecompute one figure in Excel and tie the totals to the report.
- TrackThe figure to carry into next month's meeting.
A sample prompt, from question 3
I attached our open jobs as of [month-end date]. Each row is one job with these columns: job number, project manager, contract, estimated total cost, cost to date and billed to date. All amounts are in dollars and include approved change orders.
Use the cost-to-cost method and compute, for each job:
1. Percent complete = cost to date divided by estimated total cost.
2. Earned revenue = contract times percent complete.
3. Over (under) billing = billed to date minus earned revenue. Positive is overbilled, negative is underbilled.
4. Estimated gross profit = contract minus estimated total cost.
5. Gross profit to date = earned revenue minus cost to date.
6. Cost to complete = estimated total cost minus cost to date.
Then report:
- The full schedule as a table, sorted by over (under) billing from most underbilled to most overbilled.
- Totals for contract, earned revenue, billed to date, total overbillings, total underbillings and the net position.
- A list of exceptions: jobs underbilled by more than [$ amount] or more than [percent] of contract; jobs where cost to date exceeds estimated total cost; jobs where percent complete is above 95% and cost to complete is still more than [$ amount]; and jobs with a negative estimated gross profit.
- Totals by project manager.
Rules:
- Use only the formulas above. If a value is missing, leave the job out and list it with the reason.
- Show percent complete to one decimal place and dollars without cents.
- After the tables, write up to five findings. Each must name the job and cite its numbers.The full prompt, the check in Excel and a worked example are in the workbook. Get the workbook
Who it is for
Owners, CFOs, controllers and operations leads at general contractors and specialty contractors. You will need exports from your accounting system or ERP, Excel, and an AI assistant your company has approved for business data, such as Microsoft 365 Copilot, ChatGPT or Claude.
The workbook assumes you know construction finance and are new to working with an AI assistant on it. Every prompt spells out its formulas, and every chapter ends with a check you can do in Excel.
Who made it
Construction Metrics publishes two posts a day on construction data and AI in construction, written for owners, CFOs and technology staff at contractors. The measures in this workbook come from its how-to posts, each linked from the chapter that uses it.
AutomationTactics is based in Conshohocken, Pennsylvania, and sets up AI on the tools construction companies already own, such as Microsoft 365, Copilot, Procore and the ERP. Its engagements start by documenting how the work runs, and the monthly routine at the end of the workbook follows that order.