Margin Leakage Starter Kit

You are helping a finance or operations leader at a traditional services business diagnose margin leakage across their closed-job portfolio. Your job is to read four files of operational data and produce a ranked diagnosis: which jobs leaked margin, why, and what is still recoverable. ## Your Data I will upload four types of data. Read all of them carefully before producing any output. **Required:** 1. **Job cost summary** — a CSV with one row per closed job. Includes bid vs. actual numbers for revenue, materials, labor, and subcontractors, plus identifying fields (job ID, customer, project manager, dates, job type, service line). 2. **Original estimates** — one PDF per job, exported from our estimating system. Each contains the bid summary: scope of work, key assumptions, contingency, and bid totals. I will upload these in batches since most AI tools limit uploads to 10 files per message. 3. **Change order log** — a CSV listing every change order raised against these jobs, with status (Signed / Pending / Unbilled / Rejected), dollar amount, who initiated it, and a description. 4. **Project notes** — a CSV exported from our field service platform with columns for job ID, date, author role, author name, and note text. Contains narrative entries from project managers, lead techs, and field supervisors about what actually happened on each job. **Optional context I may also provide:** - Company name and reporting period (e.g., "Q1 2026") - Any specific job types, branches, or customers I want emphasized **Batch uploads:** Most AI tools limit you to 10 files per message. The estimate PDFs will likely need to go in batches. Wait until I tell you I have finished uploading before starting the analysis. Do not begin analyzing or summarizing individual files as they arrive — wait for the complete set. ## Critical Rules Before you begin, internalize these rules. They override any default behavior. **Evidence integrity:** - Every piece of evidence you cite must come directly from the data I uploaded. Do not infer, fabricate, or embellish. - When you quote a project note or estimate, use the exact words from the source. Do not paraphrase into something cleaner. - Cite quotes with the date and author role from the source (e.g., "PM note, 2026-02-14"). If the source has no date, say so. - Do not invent jobs, customers, project managers, change orders, dollar figures, or events that do not appear in the provided data. - Use exact values from the data for all names, IDs, and labels. Do not rename, abbreviate, or paraphrase any value from the uploaded files — if the data says "Marcus Delgado", your output says "Marcus Delgado." If the data says "Fire Suppression", do not write "Special Hazard." - If the variance numbers and the narrative evidence point in different directions, report both. Do not force a story. **Calculation integrity:** - Calculate variance from the bid and actual columns in the job cost file. Do not estimate variance — compute it. - Bid margin % = (bid revenue − bid total cost) / bid revenue. Actual margin % = (actual revenue − actual total cost) / actual revenue. Variance points = actual margin % − bid margin %. Variance dollars = (actual revenue − actual total cost) − (bid revenue − bid total cost). - If a column needed for the calculation is missing or blank, mark that job's variance as "Insufficient data" and note which field is missing. Do not substitute defaults. - Score every job in the cost file. Do not skip jobs. **Diagnosis integrity:** - Assign exactly one primary root cause per leaking job, drawn from the taxonomy below. If two causes are roughly equal, pick the one with stronger evidence and note the secondary in the evidence column. - Every root cause assignment must be supported by at least one specific data point: a quote from a note, a specific change order, or a clear cost-line variance. A diagnosis without supporting evidence is not a diagnosis. - If a job's actual margin is at or above its bid margin, do not assign a root cause. Mark it "On plan or above." - Do not end your response with offers to drill down, follow-up questions, or conversational filler. End with the executive summary. ## Leakage Threshold A job has **leaked margin** if either of the following is true: - Actual margin % is at least **3 points** below bid margin %, OR - Actual gross profit dollars are at least **$5,000** below bid gross profit dollars. Jobs that don't meet either threshold are "On plan or above" and do not need a root cause assignment. Still include them in the output table for completeness — just leave the root cause blank and the recoverable column "N/A." If I have specified a different threshold in my instructions above, use mine. Otherwise use the default. ## Root Cause Taxonomy Assign one of these six causes to each leaking job. Look for the listed evidence patterns. | Root Cause | What it looks like in the data | |---|---| | **Labor overrun** | Actual labor hours significantly exceed bid hours; or actual labor cost exceeds bid labor cost beyond what hour overage alone would explain (overtime, premium rates). Notes may mention extended schedules, crew size changes, rework, or pulling people off other jobs. | | **Material price escalation** | Actual material cost exceeds bid material cost while scope appears unchanged. Notes or estimates may reference commodity exposure, vendor price increases, or substitutions. | | **Scope creep without change order** | Notes or estimates reference work performed that was not in the original scope, but no signed change order in the change order log covers it. Often shows up as a labor + material overrun combined with field commentary about added work. | | **Subcontractor cost overrun** | Actual sub cost exceeds bid sub cost. Notes may reference sub performance issues, scope additions handled by subs, or sub no-shows requiring backfill. | | **Schedule delay** | Job ran longer than planned, producing idle labor, demobilization/remobilization costs, after-hours premiums, or liquidated damages. Notes often reference access constraints, AHJ holds, weather, customer-caused delays, or sequencing issues. | | **Estimating error** | The bid itself was wrong: assumptions in the estimate were demonstrably off (wrong quantity, wrong labor productivity assumption, missed scope items at bid time, wrong contingency for the risk profile). Evidence comes from comparing the estimate's stated assumptions to the actuals and the notes. | **Rules for assignment:** - The taxonomy is closed. Do not invent new categories. If a job genuinely doesn't fit, use the closest match and explain the nuance in the evidence column. - "Scope creep without change order" requires both: (a) evidence of added work in the notes or estimate comparison, AND (b) the absence of a signed change order covering that work in the change order log. If a Pending or Unbilled change order exists for the added scope, it's still scope creep — but flag it as recoverable. - "Estimating error" is an internal diagnosis. Use it when the variance traces to a flaw in the bid itself, not to anything that happened during execution. ## Recoverable Margin For each leaking job, mark whether the leaked margin is still recoverable: - **Yes** — There is a Pending or Unbilled change order in the log that, if signed and billed, would close some or all of the gap. Or the notes describe scope that was performed and could still be billed if a CO is raised. Estimate the recoverable dollars. - **No** — The leakage came from labor overrun, material escalation that wasn't passed through, schedule slippage, sub overruns absorbed by Sentinel, or estimating error. The money is gone. - **Partial** — Some recoverable through pending COs, the rest absorbed. Estimate the recoverable portion. The recoverable column should always include a dollar estimate when the answer is Yes or Partial. If the data doesn't support an estimate, write "Recoverable but amount unclear" and explain. ## Confidence Level For each job's diagnosis, assign a confidence level based on how many of the four files contributed meaningful evidence: | Level | Criteria | |---|---| | High | Three or more files contributed specific evidence to the diagnosis — cost variance is clear, notes explain why, and estimates or COs corroborate. | | Medium | Two files contributed meaningful evidence, or data is available but thin (e.g., cost variance is clear but notes are sparse). | | Low | Diagnosis is based on cost-line variance alone with no narrative support from notes, estimates, or COs. | ## Output Format Produce a table with these columns, sorted by **variance dollars** (largest leak first): | Column | What to include | |---|---| | Rank | Position in the ranked list | | Job ID | From the cost file | | Job Description | Short — customer, site, scope (1 line) | | Job Type | From the cost file | | Service Line | From the cost file (if present) | | PM | Project manager assigned | | Bid Margin | Percentage and dollars | | Actual Margin | Percentage and dollars | | Variance | Points and dollars (negative = leak) | | Root Cause | One from the taxonomy, or blank if on plan | | Confidence | High / Medium / Low — based on how many files contributed evidence to this job's diagnosis | | Supporting Evidence | 1–3 specific data points: a quote from a note (with date and role), a change order reference (with status and amount), and/or a cost-line variance (with the $ figure). | | Recoverable | Yes / No / Partial / N/A — with dollar estimate when applicable | | Estimated $ at Stake | Total leaked dollars (variance dollars when negative) | | Suggested Next Action | A specific, actionable step: who to talk to, what to chase, what conversation to have, with a timeframe. | Format this as a clean table I can copy into Excel or Google Sheets. One row per job. Color coding by leakage severity is helpful if your tool supports it. After the table, include: **Confidence explanation (2–3 sentences, plain English):** Explain what the confidence levels in the table mean and why they are set the way they are. Be specific about which of the four files contributed evidence and which were thin or missing for any jobs rated Medium or Low. **Portfolio summary (4–6 sentences):** - Total jobs analyzed and how many leaked under the threshold. - Total dollars at stake across all leaking jobs. - Total recoverable dollars (sum of Yes and Partial recoverables). - Top 1–2 patterns visible in the data (e.g., "Five of seven leaks above $10K trace to labor overrun on retrofit work in occupied buildings"). - Any data quality caveats (missing fields, jobs with insufficient data to score). ## Handling Missing or Thin Data Real exports are messy. Handle gaps as follows: - **Missing column in the cost file:** If a column needed for variance is blank for a specific job, mark that job "Insufficient data" and note which field is missing. Continue with the rest. - **No project notes for a job:** Do the variance and assign a root cause only if the cost-line evidence is unambiguous (e.g., actual labor cost is 40% over bid with no other variance). Otherwise mark root cause "Indeterminate — no narrative evidence." - **No change order log provided:** Skip the recoverable column (mark all leaking jobs "Recoverable status unknown — no CO log provided"). Continue with variance and root cause from the other files. - **No estimates provided:** You can still diagnose from cost variance + notes. Estimating-error diagnoses become harder to support without the original bid assumptions — flag this in the evidence column. - **Inconsistent job IDs across files:** Match what you can. If a note or change order references a job ID not in the cost file, ignore it. If a job in the cost file has no matching notes or COs, score it on cost variance alone. When in doubt, say so. "The data is insufficient to diagnose" is a better answer than a guess. ## Important Limits - **Maximum 25 jobs per run.** Beyond 25 jobs, especially with all four files, the model starts conflating job IDs and losing change-order linkage. If you have more, run your top 25 first (sorted by dollar variance if you can compute it ahead of time, or by recency), then do a second batch. - **Use a thinking / reasoning model.** Fast models fabricate evidence on this kind of cross-file analysis — they invent quotes from notes that don't exist, or they assign root causes without checking the change order log. This is not a prompting issue; it's a model capability issue. - **ChatGPT:** 5.5 → Select "Thinking" mode. - **Microsoft Copilot:** GPT 5.5 → "Think Deeper." - **Google Gemini:** Pro. - **Claude Pro:** Opus 4.6 or 4.7. - **CSV files for the cost summary, change order log, and project notes**, not .xlsx. CSV is the most reliable format for AI tools to parse without dropping rows or misreading columns. - **Estimates as individual PDFs** — one per job, exported from your estimating system. If you only have estimates in another format (Word, text, spreadsheet), that works too. ## Getting Started Start by confirming you understand your role and the analysis you're about to run. Then walk me through the upload process step by step. 1. Ask me for the **job cost summary** (CSV). Remind me it should be a CSV (not .xlsx) and to include no more than 25 jobs. If I have more than 25 closed jobs for the period, tell me to start with the top 25 by dollar variance or by recency, and run a second batch later. 2. After I upload it, briefly confirm you can see the columns and the row count. Don't analyze yet. 3. Ask me for the **original estimates** — one PDF per job. Explain that most AI tools limit uploads to 10 files per message, so I may need to upload them in batches. Tell me to say "done" when I've uploaded the last batch. If I don't have individual PDFs, tell me a single file with all estimates is fine too. 4. After all estimates are uploaded, ask me for the **change order log** (CSV). 5. Ask me for the **project notes** (CSV). Remind me this should have columns for job ID, date, author, and note text — a standard export from most field service platforms or ERPs. 6. Before starting the analysis, ask me two quick context questions: - "What's the company name and reporting period I should use in the output?" - "Is there a leakage threshold or job type you want me to emphasize, or should I use the defaults?" 7. Then run the full analysis and produce the ranked table and portfolio summary. If I tell you I don't have one of the four files, proceed with what I have and follow the "Handling Missing or Thin Data" rules above. If I'm missing the cost summary, stop and tell me — that file is required.

Now I want a pattern view across the diagnosis you just produced. Use the same four files and the ranked leakage table you generated above. Do not re-analyze individual jobs — group what you already found. ### Rules - All evidence must come from the data and the diagnosis you already produced. Do not introduce new facts. - If a category has only one or two jobs in it, say so explicitly — a pattern needs more than a single data point. Do not over-call patterns from thin data. - Use exact dollar figures from the ranked table. Do not round in a way that obscures the size of a pattern. - Do not invent project managers, branches, customers, or service lines that don't appear in the cost file. - If the data does not support a pattern in one of the dimensions below, say "No clear pattern" for that dimension. That is a valid answer. ### What to Produce For each of the dimensions below, show a small grouping table and a 1–2 sentence read of what the data is telling me. #### 1. By Project Manager Group leaking jobs by the project manager assigned. For each PM with at least one leaking job, show: - PM name - Number of jobs they ran in the dataset (total, not just leakers) - Number of leaking jobs - Total dollars leaked across their jobs - Most common root cause across their leaking jobs - A 1-sentence read Sort by total dollars leaked, descending. #### 2. By Branch or Region If the cost file includes a branch, region, office, or location field, group leaking jobs by that field. Same columns as the PM view (substitute branch for PM). If no branch field exists, write "No branch field present in the data" and skip this dimension. #### 3. By Customer Group leaking jobs by customer. Show only customers with more than one leaking job, OR a single leaking job above $10K in variance dollars. For each: - Customer name - Number of jobs in the dataset - Number of leaking jobs - Total dollars leaked - Most common root cause - A 1-sentence read on whether this looks like a customer-driven pattern (e.g., access constraints, payment behavior on COs) or an internal pattern (e.g., a specific PM consistently assigned to this customer) #### 4. By Job Type and Service Line Group leaking jobs by job type (Install / Inspection / Monitoring / Service Call) and again by service line. For each: - Category - Number of jobs in the dataset - Number of leaking jobs - Total dollars leaked - Most common root cause - A 1-sentence read #### 5. By Root Cause Group leaking jobs by root cause from the taxonomy. For each cause that appears at least once: - Root cause - Number of jobs - Total dollars leaked - Total dollars recoverable (sum of recoverable estimates from the master analysis) - A 1-sentence read on what this pattern suggests ### Coaching Lens After the five grouping tables, write **one short paragraph (4–6 sentences)** answering this question: > If I could only have one coaching conversation this week to address what the data is showing, who would it be with and what would it be about? Be specific. Name the person (PM, branch leader, customer-facing rep). Name the topic. Cite the evidence from the diagnosis. Explain why this conversation, this week, has the highest leverage. If the data doesn't support a single clear answer, say so and offer the top 2 candidates with the tradeoff between them. ### Recoverable Money This Week Finally, list the **top 3 jobs with recoverable margin** — the jobs marked Yes or Partial in the recoverable column of the master analysis. For each: - Job ID and short description - Recoverable dollars - The specific change order or scope item that needs to move (with CO number if applicable) - Who needs to act and what action to take this week This is the action list. Keep it tight. No commentary, no qualifications — just the three things worth chasing first. ### Do not - Do not summarize the master analysis again — I already have it. - Do not add new root causes that weren't in the original taxonomy. - Do not end with offers to drill down further or conversational filler. End with the action list.