interface:
  display_name: "IGCE Builder: LH/T&M"
  short_description: "Build auditable LH and T&M estimates"
  default_prompt: "Use $igce-builder-lh-tm to build an auditable Labor-Hour or Time-and-Materials IGCE from my staffing inputs or requirement."

policy:
  allow_implicit_invocation: true

# LH/T&M Data Source Operations and Mapping Reference

Use this reference for SOC selection, BLS wage retrieval, CALC+ positioning, and GSA Per Diem. Keep operation names stable and let the host supply any runtime namespace.

## Contents

1. Constants and runtime checks
2. SOC mapping
3. BLS OEWS operations
4. CALC+ operations
5. GSA Per Diem operations
6. Raw-data recording

## 1. Constants and runtime checks

| Item | Current baseline | Required handling |
|---|---:|---|
| Standard work year | 2,080 hours | Convert annual wage to hourly direct labor |
| Default productive hours | 1,880 hours | Editable workbook assumption; derive shift-coverage FTE from this value |
| BLS wage cap | $239,200 annual or $115 hourly | Reconfirm each release and treat capped values as lower bounds |
| BLS vintage | May 2025 | Call `detect_latest_year` before every estimate |
| First and last day M&IE | 75% | Use the MCP's discounted result once |
| City Pair fare | YCA when available | Use only when origin and destination are known |

Treat published constants as baselines. Runtime data controls the estimate.

### Request pacing

Serialize credentialed federal API calls and leave at least three seconds between them, including capability checks. Do not parallelize keyed BLS or Per Diem calls. Honor any longer server-provided retry interval. If a service reports a rate limit, stop and report it instead of issuing rapid retries. Unkeyed services must still follow their advertised limits and must not be queried in uncontrolled loops.

## 2. SOC mapping

Classify the requirement domain before mapping job titles.

### IT and software

| Common title | SOC | BLS title |
|---|---|---|
| IT Program Manager | 11-3021 | Computer and Information Systems Managers |
| Management Analyst | 13-1111 | Management Analysts |
| Project Manager | 13-1082 | Project Management Specialists |
| Systems Engineer or Analyst, IT | 15-1211 | Computer Systems Analysts |
| Cybersecurity or InfoSec Analyst | 15-1212 | Information Security Analysts |
| Network Architect | 15-1241 | Computer Network Architects |
| Database Administrator | 15-1242 | Database Administrators |
| Systems Administrator | 15-1244 | Network and Computer Systems Administrators |
| Software Developer | 15-1252 | Software Developers |
| QA Tester | 15-1253 | Software QA Analysts and Testers |
| Help Desk or User Support | 15-1232 | Computer User Support Specialists |
| Data Scientist | 15-2051 | Data Scientists |

### Physical and non-IT engineering

Use these codes for physical systems, reactors, aerospace, infrastructure, chemical, mechanical, and hardware integration.

| Common title | SOC | BLS title |
|---|---|---|
| Aerospace Engineer | 17-2011 | Aerospace Engineers |
| Biomedical Engineer | 17-2031 | Bioengineers and Biomedical Engineers |
| Chemical Engineer | 17-2041 | Chemical Engineers |
| Civil Engineer | 17-2051 | Civil Engineers |
| Electrical Engineer | 17-2071 | Electrical Engineers |
| Electronics Engineer | 17-2072 | Electronics Engineers, Except Computer |
| Environmental Engineer | 17-2081 | Environmental Engineers |
| Industrial Engineer | 17-2112 | Industrial Engineers |
| Mechanical Engineer | 17-2141 | Mechanical Engineers |
| Nuclear Engineer | 17-2161 | Nuclear Engineers |
| Petroleum Engineer | 17-2171 | Petroleum Engineers |
| Systems Engineer, non-IT | 17-2199 | Engineers, All Other |

Use 17-2199 when an integration role spans engineering disciplines. Query a specific engineering SOC beside it when useful.

### Science, research, medical, and support

| Common title | SOC | BLS title |
|---|---|---|
| Physicist | 19-2012 | Physicists |
| Chemist | 19-2031 | Chemists |
| Medical Scientist | 19-1042 | Medical Scientists, Except Epidemiologists |
| Registered Nurse | 29-1141 | Registered Nurses |
| Physician Assistant | 29-1071 | Physician Assistants |
| Technical Writer | 27-3042 | Technical Writers |
| Contracting Specialist | 13-1020 | Buyers and Purchasing Agents |

### Program management context

Use the contract domain to map Program Manager:

- Operations or administration: 11-1021, General and Operations Managers
- Physical engineering or R&D: 11-9041, Architectural and Engineering Managers
- IT or software: 11-3021, Computer and Information Systems Managers

Document the choice. Do not default every technical Program Manager to 11-3021.

## 3. BLS OEWS operations

### Runtime vintage

Call `detect_latest_year` before wage retrieval. Record the returned year and use it in Raw Data, the Summary assumption block, and the aging calculation. If it differs from May 2025, use the runtime result.

### Wage retrieval

Call:

```text
get_wage_data(
  occ_code=<six-character SOC>,
  scope=<metro|state|national>,
  area_code=<five-digit MSA or two-digit state FIPS>,
  datatypes=["04", "11", "12", "13", "14", "15"]
)
```

The requested datatypes provide mean plus P10, P25, P50, P75, and P90. Valid operation datatypes include `01`, `03`, `04`, `08`, `11`, `12`, `13`, `14`, and `15`. Do not send `02` or `05`.

Use `list_common_metros` to resolve common MSAs and renumbering notes. If a metro is absent, resolve its current code from BLS before falling back.

Fallback ladder:

1. Metro
2. State, only after the metro series and current MSA code are checked
3. National

Record each fallback and its reason. Do not treat suppressed or unavailable metro data as evidence about market quality.

For a single-category sanity check, `igce_wage_benchmark` may return compact wage and burden information. Use `get_wage_data` for the full workbook so the percentile basis remains visible.

### Seniority convention

When BLS does not distinguish job levels:

- Junior or entry: P25
- Mid or journeyman: P50
- Senior: P50 to P75 based on scope
- Principal, director, or SME: P75 to P90

If no levels are supplied, price all members at P50 and disclose that convention. Do not invent a junior/mid/senior staffing mix.

### Capped and compressed distributions

If a selected percentile hits the BLS cap, treat it as a lower bound. If a selected value is within 10% of the current cap, note that the local market may exceed the reported value and flag the proximity for Contracting Officer review. If P75 is capped:

1. Use the uncapped mean as a senior anchor when appropriate.
2. Cross-reference a separate commercial source if available.
3. Consider national P75 divided by national P50, applied to local P50, and label it as a derivation.
4. Never present the cap as an exact point estimate.

If P75 equals P90 below the cap, disclose a flat upper tail. If P25 equals P10, do not use P25 for a junior anchor without review. A possible derived junior value is national P25 multiplied by local P50 divided by national P50. Label every derived value.

## 4. CALC+ operations

### Silent-wrong-answer signature

The CALC+ keyword route is:

```text
/v3/api/ceilingrates/?keyword=<term>
```

The parameter is `keyword=`. Never use `q=`. The wrong parameter can be accepted while returning the unfiltered corpus.

The labor-category discovery response path is:

```text
aggregations.labor_category.buckets[*].key
aggregations.labor_category.buckets[*].doc_count
```

Record enough of the selected buckets and counts to reproduce the pool.

### Discovery-first flow

1. Call `suggest_contains(field="labor_category", term=<LCAT term>)`.
2. If the top exact buckets form an adequate pool, call `exact_search(field="labor_category", value=<bucket>)` for each selected bucket.
3. If buckets are fragmented, call `keyword_search(keyword=<term>)` and document that keyword matching can include other fields.
4. Use `igce_benchmark(labor_category=<title>, experience_min=<years>, education_level=<level>)` for compact percentile statistics.
5. Do not send `page_size=0`. Use at least 1 when an operation requires the parameter.

Expected benchmark fields include count, minimum, maximum, mean, standard deviation, P10, P25, P50, P75, and P90. Use the returned canonical fields instead of probing large raw payloads.

### Tier matching and dual pools

Match the actual level. For a mid-level developer, prefer `Software Developer II` over an all-level Software Developer pool.

For senior categories, present both:

- Title-match pool, discovered through `suggest_contains` and `exact_search`
- Experience-match pool, returned by `igce_benchmark` with an appropriate minimum experience filter

Report both sample sizes and medians. Do not merge them without disclosure.

Useful fragmented-title alternatives:

- SOC Analyst or Cyber Analyst: Information Security Analyst I, II, and III
- Software Engineer: Software Developer I, II, and III
- Data Engineer with a thin pool: Data Scientist with experience filter, clearly labeled as a proxy

### Workflow B shortcut

Call:

```text
price_reasonableness_check(
  labor_category=<title>,
  proposed_rate=<rate>,
  education_level=<level if known>,
  experience_min=<years if known>
)
```

Use the returned count, bounds, z-score, and percentile only as positioning data. Label a pool under about 25 records as directional.

### LH/T&M positioning bands

- Within 15% of P50: expected comparison range
- Between 15% and 30% from P50: show the full burden, seniority, geography, and pool-composition arithmetic
- More than 30% from P50: show alternate SOC or title pools and direct the result to Contracting Officer review
- Below P25: report the position and ask the Contracting Officer to review the input, level, or pool alignment

LH/T&M burdened hourly labor rates are directly comparable to CALC+ ceiling labor rates, but CALC+ pools can still mix education, experience, geography, and labor-category definitions. Do not translate a band into a fair-and-reasonable conclusion.

Do not translate a band into a fair-and-reasonable conclusion.

## 5. GSA Per Diem operations

### Travel estimate

For one or more nights, call:

```text
estimate_travel_cost(
  city=<civilian locality>,
  state=<state>,
  num_nights=<integer>,
  travel_month=<optional month>,
  fiscal_year=<optional FY>
)
```

Expected fields include nightly lodging, lodging total, daily M&IE, first/last-day M&IE, M&IE total, grand total, and travel days.

Calculate annual travel as:

```text
grand total per trip * trips per year * travelers
```

Use the operation's first/last-day amount once. `get_mie_breakdown` already returns a 75% value. Do not multiply it by 0.75 again.

`estimate_travel_cost` requires at least one night. For a 0-night day trip, do not pass zero to that operation. Call `lookup_city_perdiem` for the locality and fiscal year, use `mie_first_last_day` once, and set lodging to zero. Record one travel day in the workbook.

### Installation to locality crosswalk

GSA uses civilian localities. Translate installations before lookup.

| Installation or site | GSA locality |
|---|---|
| Fort Meade, MD | Annapolis or Anne Arundel County, MD |
| Fort Belvoir, VA | Fairfax or Alexandria, VA |
| Pentagon, VA | Arlington, VA or DC rate |
| Joint Base Andrews, MD | District of Columbia |
| NSA Bethesda or Walter Reed, MD | District of Columbia composite locality |
| Fort Liberty, NC | Fayetteville, NC |
| Peterson SFB, Schriever SFB, or Fort Carson, CO | Colorado Springs, CO |
| Wright-Patterson AFB, OH | Dayton, OH |
| Eglin AFB, FL | Fort Walton Beach, FL |
| JB San Antonio, Randolph, or Lackland, TX | San Antonio, TX |
| Hanscom AFB, MA | Bedford or Boston, MA |
| Redstone Arsenal, AL | Huntsville, AL |
| Offutt AFB, NE | Omaha or Bellevue, NE |
| Cape Canaveral or Patrick SFB, FL | Cocoa Beach or Cape Canaveral, FL |
| Joint Base Lewis-McChord, WA | Tacoma or Pierce County, WA |
| Oak Ridge National Laboratory or Y-12, TN | Knoxville, TN |
| Los Alamos National Laboratory, NM | Santa Fe or Los Alamos County, NM |
| Hanford or PNNL, WA | Richland, WA |
| Sandia National Laboratories, NM | Albuquerque, NM |
| Lawrence Livermore National Laboratory, CA | Livermore or Oakland, CA |
| Idaho National Laboratory, ID | Idaho Falls, ID |
| White Sands Missile Range, NM | Las Cruces, NM |
| NAWS China Lake, CA | Ridgecrest or Kern County, CA |
| Edwards AFB, CA | Lancaster or Palmdale, CA |
| Dugway Proving Ground, UT | Salt Lake City metro standard rate |
| Nellis AFB or Creech AFB, NV | Las Vegas, NV |
| Point Mugu or NBVC, CA | Oxnard or Ventura County, CA |

For another site, call `lookup_city_perdiem` with the nearest civilian locality and verify the returned county.

### Fiscal-year fallback

If the requested fiscal year returns an empty rate list or an error containing `No rates found for FY`, retry the prior fiscal year. Record the requested and fallback years in Methodology. If contract start is within six months of a new fiscal year, query the new year when published or disclose that the current-year baseline must be refreshed.

### Travel interpretation

"Quarterly travel between sites" means four total trips split across destinations unless the user says each way or per destination. Ask when the interpretation materially changes cost.

If origin and destination are in the same metro or within ordinary local-travel distance, flag that lodging per diem may not apply. Use mileage or another user-supplied local-travel basis instead.

## 6. Raw-data recording

Record compact, reproducible inputs and outputs on the Raw Data sheet:

- BLS operation, SOC, scope, area code, datatypes, returned vintage, and selected percentiles
- CALC+ operation, exact buckets or keyword, count, percentiles, and contamination caveat if applicable
- Per Diem locality, state, fiscal year, month, nights, returned lodging, and returned M&IE
- Every fallback, proxy, or derived value

Do not paste full JSON payloads. Record the parameters and summary fields needed to reproduce each call.

# Professional product standard

This file is the canonical source for the copy packaged with each 1102tools skill.
The packaged copies must remain identical to this file.

## Product judgment

Produce a finished professional work product, not a record of the process used
to create it. Write for the person who must understand, use, approve, or act on
the result.

Exercise editorial judgment. Include material that improves the reader's
understanding or decision. Omit material merely because it was collected,
available, or easy to generate. Every page, section, table, and visual must earn
its place.

Match the structure, length, voice, and visual treatment to the assignment.
Do not reuse a universal report outline. A short decision card, formal
contract-file document, analytical workbook, landscape, timeline, or longer
consulting report may all be correct products for different requests.

Lead with the useful output. Research mechanics, process narration,
methodology, limitations, and compliance controls are secondary unless the
reader's purpose makes one of them the product.

## Controlled freedom

Route rules define the substantive outcome and genuine formal boundaries. They
do not prescribe identical headings, page counts, layouts, or section order
unless a law, supplied template, calculation model, or downstream interface
requires it.

Choose the clearest form for each idea:

- prose for explanation and judgment;
- tables for real comparison or repeated fields;
- cards, profiles, matrices, timelines, charts, and callouts when they improve
  comprehension;
- appendices only for material the intended reader may reasonably need.

Do not put paragraph-length narrative in narrow table cells. Do not repeat the
same information as a callout, table, and prose section unless each form serves
a different reader need.

Use a restrained, coherent design with clear hierarchy, readable typography,
comfortable spacing, and accessible contrast. Treat examples as quality
references, not templates to copy.

## Paid-value standard

The primary artifact should contain the analysis, comparison, requirements,
model, or operating guidance the customer is paying to receive. Audit material
must remain subordinate.

Before delivery, remove:

- generic background the intended reader already knows;
- duplicated findings or actions;
- query logs, tool operations, sanitized parameters, and internal record
  mechanics;
- generic owners, gates, scenarios, or warnings invented to fill a template;
- boilerplate disclaimers repeated in multiple sections;
- empty sections and tables that merely announce missing content.

When evidence is insufficient for the promised product, say so plainly and
provide the useful narrower result or acquisition plan for the missing evidence.
Do not pad an evidence gap into a document that resembles a completed analysis.

## Reader-facing source citations

Internal evidence identifiers such as `E001` may remain in a private research
record for backward compatibility, but they never appear in a customer-facing
artifact.

Assign each distinct reader-visible source an identifier in order of first
appearance: `S1`, `S2`, `S3`, with no leading zeros. Reuse the same identifier
wherever that source supports another claim. Cite sources beside the supported
claim using forms such as `[S1]`, `[S1, S4]`, or `[S1-S3]`.

End a sourced report with a concise `Source Register`. Each entry uses the
corresponding identifier and provides enough information to verify the source:
publisher or organization, title or record identity, relevant date, and a
clickable public URL or supplied-document locator. Deduplicate sources. Do not
display internal source-class tokens or a query-by-query research log.

Native legal and acquisition citations such as `FAR 10.001`, a docket number,
PIID, UEI, or document section remain in their ordinary form. Add an `S` citation
when the artifact also needs a link to the supporting source; do not replace the
native citation with an opaque source number.

For workbooks, source notes and benchmark rows may use `S` identifiers that
resolve to a Sources or Raw Data register. Formula cells and internal validation
IDs are not reader-facing source citations.

## Proportionate boundaries

Accuracy, authority boundaries, unresolved decisions, and limitations remain
mandatory when material. Present them once, in the least intrusive form that
keeps the product honest. A concise note or callout is preferable to a recurring
legalistic section when the reader needs the answer more than a compliance
lecture.

Do not weaken a formal SOW/PWS, OT project description, acquisition-policy
status analysis, or auditable cost model for stylistic reasons. Formal and
mathematical requirements remain hard constraints. Apply taste to hierarchy,
selection, explanation, and delivery view, not to the removal of necessary
substance.

## Final editorial review

After technical validation and rendering, review the complete artifact as a
demanding customer:

- Is the useful result apparent immediately?
- Did the author select and synthesize rather than dump everything collected?
- Does each section materially advance the reader's work?
- Are sources integrated credibly without dominating the product?
- Are limitations accurate and proportionate?
- Does the artifact feel composed for this assignment rather than populated
  from a universal template?
- Is this work product an experienced professional could confidently sell?

Revise until the answer to every applicable question is yes. Structural
validation is a technical floor, not the release decision.

# Runtime Adaptation

Use host capabilities instead of hardcoded client names.

## Questions

If the host exposes a structured question tool, use it for short mutually exclusive choices. Otherwise present a numbered list in chat and accept a number, label, or free-text answer. Do not refer to a tool name in user-facing prose.

For Stage A, ask only for decomposition confirmation and stop. For Stage B, batch independent missing fields into one response. Ask one field at a time only when an answer changes the next available choices.

## MCP operations

Inspect the available operations and match their server, operation name, description, and schema. Host prefixes and separators are implementation details. For example, look for the `detect_latest_year` operation from the BLS OEWS server rather than a literal generated namespace.

Declared MCP dependencies do not prove that a server is installed, authenticated, or reachable. Keep the pre-flight check.

## Files and Python

Use the host's file-reading, file-writing, and Python capabilities. Follow any authoritative host spreadsheet workflow and its hard stops. Use Python and openpyxl for workbook authoring only when no governing host spreadsheet workflow prohibits that substitute; never guess dependency paths or bypass a host hard stop. Resolve referenced files relative to this skill directory when possible. If the host does not expose the skill directory, report which reference or script cannot be loaded and stop before a step that depends on it.

Before the first artifact-specific approval, state whether full workbook mode is available. If it is not, preserve all approved inputs and offer the workbook specification as structured JSON plus Markdown or CSV tables, or ask the user to continue in a maintained client surface that supports workbook generation. Do not label the fallback as a completed workbook.

## Formula verification

openpyxl writes formulas and reads cached values; it does not calculate Excel formulas. Validation has three layers:

1. Formula-structure audit with openpyxl.
2. Independent recomputation from raw inputs in Python.
3. Formula execution in a real spreadsheet engine when available.

On macOS and Linux, detect `soffice` and use LibreOffice headless for layer 3. Do not automate desktop Excel on macOS. When no engine is available, use the exact disclosure in Step 8.5.

## Delivery

Use the host's artifact presentation capability when available. Otherwise save to the user-supplied directory or current working directory and report the absolute path. Do not assume `/mnt`, `present_files`, shell launch commands, or a particular desktop application exists.

Never claim that saving a workbook caused formulas to execute. Never call a workbook validated unless the claimed validation layers actually ran.

# LH/T&M Workbook Validation Gates

Run all applicable gates before delivery.

## Automated structure

The bundled validator checks:

- All seven required sheets exist.
- Summary month-gap and aging formulas use the portable `YYYY-MM` pattern.
- Scenario Analysis blocks follow the 13-row layout.
- Direct hourly is the fifth row of each block and cross-sheet formulas do not reference the aged-wage row.
- Low, mid, and high burdened rates use only labor and the stated multiplier.
- Formula text contains no error tokens and annotations are escaped.
- For a multi-period requirement (`assumptions.periods` greater than 1, reconciled with `IGCE Summary!B14`), the summary shows per-period totals for the base and each option period; a one-year headline total for a multi-period requirement fails.
- In a multi-period estimate, the Escalation Rate input is referenced by at least one formula; a dead escalation input fails.
- Every money constant of $1,000 or more on `IGCE Summary` has a matching entry in the `Raw Data` refresh register.
- The Scenario Analysis period-totals table ties to the `IGCE Summary` per-period totals. Using the workbook's calculated values, money-labeled scenario cells that are all zero while the summary totals are nonzero fail (the signature of a stale SUMPRODUCT range), and every current-assumptions (mid) scenario figure must agree with a summary amount for the same period within $1.
- Custom formula assertions from the JSON sidecar pass.

## Independent recomputation

Create a sidecar from raw inputs. Example:

```json
{
  "assumptions": {
    "contract_type": "T&M",
    "burden_low": 1.8,
    "burden_mid": 2.0,
    "burden_high": 2.2,
    "aging_factor": 1.025,
    "productive_hours": 1880,
    "ceiling_price": 900000,
    "periods": 3
  },
  "labor_lines": [
    {
      "name": "Software Developer",
      "annual_wage": 135000,
      "fte": 2,
      "months": 12,
      "ceiling_hours": 3760,
      "workbook_low_rate_cell": "'Scenario Analysis'!B8",
      "workbook_mid_rate_cell": "'Scenario Analysis'!B10",
      "workbook_high_rate_cell": "'Scenario Analysis'!B12"
    }
  ],
  "non_labor_lines": [
    {
      "name": "Cloud hosting",
      "category": "materials",
      "amount": 24000,
      "workbook_total_cell": "'Materials Detail'!B10"
    }
  ],
  "material_handling_assertions": [
    {
      "cell": "'Materials Detail'!G10",
      "equals": 1250,
      "basis": "User-supplied accounting practice dated 2026-08-23"
    }
  ],
  "workbook_low_total_cell": "'IGCE Summary'!B30",
  "workbook_mid_total_cell": "'IGCE Summary'!B31",
  "workbook_high_total_cell": "'IGCE Summary'!B32",
  "workbook_ceiling_price_cell": "'IGCE Summary'!B13"
}
```

The recomputation rejects positive materials in an LH estimate. For continuous coverage, add `annual_coverage_hours`; incompatible FTE and productive hours are rejected.

Material handling defaults to numeric zero. Every nonzero input or formula in a `Material Handling` column must have a matching `material_handling_assertions` entry containing the exact cell, value or formula, and a non-empty user-supplied accounting or solicitation basis. An undisclosed percentage formula is a validation failure.

Run:

```text
python scripts/recompute_expected_values.py validation-input.json
python scripts/validate_workbook.py output.xlsx --expected validation-input.json --engine auto
```

## Real-engine verification

When LibreOffice is present, `--engine auto` executes formulas headlessly and compares cached values with independent Python results. Without a real engine, use the exact disclosure in Step 8.5 and do not call openpyxl recalculation.

## Manual gates

- Contract type is user-confirmed as LH or T&M.
- Labor-category ceiling hours and total ceiling price are shown as inputs.
- The IGCE total is not mislabeled as the binding ceiling.
- Burden applies only to labor.
- T&M materials, including travel and computer usage, remain outside labor burden.
- Material handling is supported indirect cost, not an arbitrary fee or profit percentage.
- Every nonzero material-handling input or formula resolves to the exact sidecar cell, value or formula, and disclosed basis.
- LH has no positive materials amount.
- Day-trip M&IE is discounted exactly once.
- CALC+ comparison uses burdened labor and neutral language.
- BLS vintage is runtime-confirmed and aging is cell-referenced.

## Fault injection

At minimum, confirm rejection of:

1. `DATEDIF` or `YEAR` on text for aging.
2. A summary formula that references the aged-wage row instead of a rate.
3. Positive materials in an LH sidecar.
4. A 4.2 FTE by 1,880-hour basis against 8,760 coverage hours.
5. A material formula multiplied by the labor burden.
6. A material-handling percentage with no disclosed source.

Record automated and manual results in `test.md`.

# LH/T&M Workbook Specification

Build one `.xlsx` workbook with seven sheets in this order:

1. `IGCE Summary`
2. `Scenario Analysis`
3. `Rate Validation`
4. `Travel Detail`
5. `Materials Detail`
6. `Methodology`
7. `Raw Data`

Keep `Materials Detail` in an LH workbook and mark it `Not Applicable` with numeric zero. This preserves a stable structure without implying that LH reimburses materials.

### First-view decision dashboard

The first visible area of `IGCE Summary` must state the requirement, estimate purpose, LH or T&M structure, period, point estimate and useful range, ceiling-hour and materials exposure, major rate drivers, live-source status, and the next acquisition-team action. Keep the dashboard readable without horizontal scrolling and distinguish hourly-rate, ceiling-hour, travel, and T&M materials effects. Do not make a price-reasonableness determination.

Do not deliver a workbook whose sheets merely exist. Populate the summary, scenarios, rate validation, travel/materials treatment, source limitations, and next actions so a reviewer can understand and challenge the estimate without reverse-engineering formulas.

## 1. IGCE Summary

### Assumption cells

| Cell | Label or value |
|---|---|
| A1:B1 | `IGCE Assumptions (LH/T&M)` |
| A2 / B2 | Low Burden Multiplier / input |
| A3 / B3 | Mid Burden Multiplier / input |
| A4 / B4 | High Burden Multiplier / input |
| A5 / B5 | Escalation Rate / input |
| A6 / B6 | Productive Hours per FTE / input |
| A7 / B7 | Base Year Months / input |
| A8 / B8 | BLS Vintage / `YYYY-MM` text |
| A9 / B9 | Contract Start / `YYYY-MM` text |
| A10 / B10 | Months Gap / formula below |
| A11 / B11 | Aging Factor / formula below |
| A12 / B12 | Contract Type / `LH` or `T&M` |
| A13 / B13 | Total Ceiling Price / user input or blank |
| A14 / B14 | Total Periods (Base plus Options) / whole-number input, 1 for a single-period requirement |

Use:

```excel
B10 =MAX(0,(VALUE(LEFT(B9,4))-VALUE(LEFT(B8,4)))*12+VALUE(MID(B9,6,2))-VALUE(MID(B8,6,2)))
B11 =(1+B5)^(B10/12)
```

Format B11 as `0.0000`. Never use `YEAR` on the text cells or rely on `DATEDIF`.

### Labor and period table

Start below row 15. Include:

- Labor category and SOC
- Location and level
- FTE
- Productive hours per FTE
- Estimated hours by period
- Labor-category ceiling hours by period
- Low, mid, and high burdened hourly rates
- Low, mid, and high labor totals
- Travel and materials as separate rows
- Mid IGCE total
- Total ceiling-price input
- Difference between the mid IGCE and ceiling input

Ceiling hours and total ceiling price are inputs or procurement decisions. Do not overwrite them with calculated estimates.

The headline total must cover every period of the stated period of performance. When `Total Periods` is greater than 1, show per-period totals for the base period and each option period (labeled `Base Year`, `Option Year 1`, and so on) plus the all-periods total; the validator rejects a multi-period requirement whose summary carries no per-period breakdown. Only when the user explicitly scoped the estimate to a single period may the workbook show one period, and then the label adjacent to the headline total must state the coverage (for example `TOTAL PLANNING ESTIMATE (BASE YEAR ONLY)`). Never present a one-year total as the planning estimate for a multi-period requirement.

Every highlighted input must feed calculations. In a multi-period estimate the Escalation Rate input must be referenced by the period pricing formulas (directly or through the aging factor); never hardcode escalation factors while the input cell sits dead. The validator rejects an escalation input referenced by zero formulas when the stated period of performance has more than one period.

Every money line on `IGCE Summary` must trace to a detail sheet or an assumptions input, and its source must appear in the `Raw Data` refresh register. Do not hardcode a money amount of $1,000 or more directly on the summary with no backing detail and no register entry; the validator rejects such constants.

## 2. Scenario Analysis

Use a fixed 13-row block per labor category. Block `N` begins at:

```text
base row = 1 + (N - 1) * 13
```

| Offset | Label | Formula or input |
|---:|---|---|
| 0 | `Scenario Analysis: <LCAT>` | header |
| 1 | BLS Base Annual Wage | source input |
| 2 | Aging Factor | `='IGCE Summary'!$B$11` |
| 3 | Aged Annual Wage | base wage times aging factor |
| 4 | Direct Labor Rate Hourly | aged wage divided by 2,080 |
| 5 | blank | separator |
| 6 | Low Multiplier | `='IGCE Summary'!$B$2` |
| 7 | Low Burdened Rate | direct rate times low multiplier |
| 8 | Mid Multiplier | `='IGCE Summary'!$B$3` |
| 9 | Mid Burdened Rate | direct rate times mid multiplier |
| 10 | High Multiplier | `='IGCE Summary'!$B$4` |
| 11 | High Burdened Rate | direct rate times high multiplier |
| 12 | blank | block separator |

In the first block, Direct Labor Rate Hourly is row 5. Cross-sheet formulas must not use row 4, which is Aged Annual Wage.

Below the blocks, show period totals using the Summary productive-hours and month assumptions. Add travel and materials after labor, never inside burden multiplication.

The current-assumptions (mid) figures in the scenario period-totals table must tie to the `IGCE Summary` per-period totals within $1: the Base Year, each Option Year, and the all-periods labor total must each match the corresponding summary amount. Keep the table's SUMPRODUCT ranges pointed at the live summary rows; if summary rows move, update the ranges. The validator reads the calculated values and rejects a scenario table whose money cells are all zero while the summary totals are nonzero, and rejects any current-assumptions figure that disagrees with the summary by more than $1.

## 3. Rate Validation

Use columns for labor category, BLS direct rate, multiplier, BLS burdened low/mid/high, CALC+ P25/P50/P75/P90, sample size, divergence from P50, and neutral note. Add title-match and experience-match columns when pools are thin or ambiguous.

Do not use `reasonable`, `acceptable`, `competitive`, `outlier`, or negotiation recommendations.

## 4. Travel Detail

Use a 17-row block per destination. Calculate zero-night trips with zero lodging and one discounted first/last-day M&IE amount. Sum travel into the Summary without burden.

For T&M, label travel as a materials-category ODC. For LH, state the separate reimbursement or CLIN basis supplied by the user. If no basis exists, show the estimate separately and flag that it is not part of the LH labor amount.

## 5. Materials Detail

For T&M, include one row per item:

- Category and description
- Vendor or estimate source
- Base-year actual or estimated cost
- Credits or discounts
- Applicable material-handling indirect cost
- Escalation by period
- Total

Material handling must be a supported indirect cost clearly excluded from labor rates. It is not fee or profit. Do not apply the labor multiplier, G&A, or a default handling percentage.

Use numeric zero when no supported basis was supplied. When a user supplies a supported accounting or solicitation basis, record every nonzero material-handling cell in the validation sidecar under `material_handling_assertions` with the exact cell, numeric value or formula, and basis text. The bundled validator rejects nonzero or formula-driven material handling that is not disclosed there.

For LH, show `Materials Not Applicable to Labor-Hour Contract` and numeric zero.

## 6. Methodology

Include:

- LH or T&M selection and user confirmation
- FAR 16.601 limitations and the fact that the D&F is outside the skill
- Fixed-hourly-rate content
- Productive and ceiling hours by category
- Total ceiling-price input and comparison with the IGCE
- SOC, BLS vintage, location, aging, and escalation
- Multiplier source and any sensitivity convention
- CALC+ pool construction and limitations
- Travel treatment
- T&M materials and material-handling basis, if applicable
- Coverage calculations
- Exclusions and user overrides
- Validation layers actually run

Do not state that the workbook itself establishes the D&F, fair and reasonable pricing, or the binding contract ceiling.

## 7. Raw Data

Record compact, reproducible parameters and result summaries, not full JSON. Include BLS series inputs and percentiles, CALC+ pool terms and statistics, Per Diem locality and rates, multiplier source, material-cost sources, and every fallback.

The refresh register must carry an entry (with the current value) for every money line on `IGCE Summary`, including ODCs, materials pools, and travel, so a reviewer can refresh each amount from its source.

## Formatting and safety

- Blue font for inputs; black for formulas.
- Currency: `$#,##0.00`; percentage: `0.0%`; aging factor: `0.0000`; multiplier: `0.00x`.
- Freeze panes below assumption and header blocks.
- Escape text beginning with `=`, `+`, `-`, or `@`.
- Use numeric zero, not text `TBD`, inside formula ranges.
- Wrap long notes and cap column widths at readable sizes.

### Column widths and text clipping

Excel and LibreOffice let a text cell overflow into the next cell **only when
that neighbour is empty**. The moment the cell to the right of a label carries a
value or a formula, the label is cut off at its own column boundary in the
printed and rendered output, however much white space appears to follow it on
screen. Setting a column width without accounting for this is what produces
labels such as `Government sh`, `0047900 Washington-Arlin`, and
`Scenario Analysis: Senior Software En` in a delivered workbook.

Every populated text cell must therefore satisfy at least one of the following,
and `validate_workbook.py` fails the workbook when none of them holds:

- **Fit.** The label fits inside its own column width.
- **Wrap.** The cell sets `wrap_text=True` and the row height is tall enough to
  show every wrapped line.
- **Free overflow.** Every cell on the label's overflow side in the same row is
  empty. That is the right-hand neighbour for left-aligned and general text, the
  left-hand neighbour for right-aligned text, and both neighbours for centred
  text.
- **Merge.** The label is merged across the columns it needs. The merged span,
  not the anchor column alone, is what has to fit.

Section headers and block titles are the common failure. A title such as
`Scenario Analysis: Senior Software Engineer` must be merged across the block it
introduces, placed on a row whose neighbouring cells are left empty, or shortened
until it fits. Never rely on visual overflow for a block title that sits beside a
populated cell.

Concrete generator guidance:

- Compute each column width from the **longest label actually written to that
  column**, not from the header text or a fixed guess. Walk the column after the
  data is written, take the widest populated string, and set
  `width = min(cap, needed + 2)`.
- Estimate a label in column-width units rather than characters. One unit is
  about one digit at Calibri 11. Lowercase letters run about 0.96 units,
  uppercase about 1.05, `i`/`l`/`j` and spaces and most punctuation about 0.45,
  `m` and `w` about 1.66, and bold text is about 14 percent wider overall.
- Prefer a merged header cell for every block title, and reserve narrow columns
  for short codes, dates, and numbers.
- Where a column must stay narrow, move the long text into a wrapped narrative
  column with an explicit width and an adequate row height. Cap runaway widths
  in the 60 to 90 unit range and wrap instead of widening past that.
- The audit allows roughly one column-width unit of slack, so a label that is
  genuinely borderline will not fail. Do not aim for the tolerance; aim for the
  fit.

### Delivery-view gate

Before delivery, recalculate and save the final workbook through a calculation
engine. The delivered file must retain cached values for formula cells on `IGCE
Summary`, including labor totals, materials, and the ceiling or scenario totals.
Render the summary and fix any blank total, clipped limitation, unreadable
assumption, or summary that requires horizontal scrolling to understand.

Configure the populated print area on every sheet to fit one page wide with no
fixed page-height limit. Use landscape orientation for wide tables. The complete
`IGCE Summary` title, decision dashboard, assumptions, labor totals, ceiling
comparison, travel, and materials treatment must remain on one page wide when
printed or exported; never split the dashboard horizontally.

# Burden Multiplier Reference

Use multipliers as transparent estimating assumptions. A burden multiplier represents wages, fringe, overhead, G&A, and profit in the fixed hourly labor rate. It is not evidence that a particular contractor has those costs.

## Baseline scenarios

| Environment | Low | Mid | High | Use |
|---|---:|---:|---:|---|
| Commercial professional services or GSA MAS | 1.8x | 2.0x | 2.2x | Default when the user accepts a generic scenario |
| Services-centric multi-agency IDIQ | 1.9x | 2.1x | 2.3x | Directional prior only |
| Agency-specific or cleared-services IDIQ | 2.0x | 2.2x | 2.4x | Use only when the requirement supports added cost |
| DOE management and operations environment | 2.2x | 2.4x | 2.6x | Directional prior only |

Do not automatically apply a clearance, SCIF, or OCONUS increment. The skill's data sources do not measure those premiums. If the requirement includes one, identify the unsupported factor and ask the user for a multiplier, approved rate, or other basis.

## Selection order

Use the strongest available basis:

1. User-supplied fixed hourly rates.
2. Contract-, vehicle-, or solicitation-specific rates.
3. CO-supplied FPRA, FPRR, audited, or bilateral rates.
4. A user-confirmed scenario from the table.
5. The 1.8x, 2.0x, 2.2x generic baseline with explicit disclosure.

If an audited or approved multiplier controls, use it as the point estimate. Do not force it into the table or bookend it by plus or minus 0.2. A sensitivity display may be added only when requested and must say that the approved rate remains authoritative.

## Custom multiplier

When the user supplies a custom multiplier, use it as the primary estimate. Ask whether low and high scenarios are required. If the user accepts a sensitivity convention, plus or minus 0.2 may be used, bounded at zero, and must be labeled sensitivity rather than evidence.

## Dimensional rules

```text
direct hourly rate = aged annual wage / 2,080
burdened hourly rate = direct hourly rate * burden multiplier
period labor = burdened hourly rate * productive hours * FTE * months / 12
```

- Use 2,080 only to convert an annual wage to an hourly direct rate.
- Use the user-supplied or assumption-cell productive hours to price annual labor.
- Apply burden only to labor.
- Do not apply burden to travel, materials, computer usage, licenses, or other direct costs.
- Keep the hourly rate and labor-category ceiling hours visible so the NTE labor amount can be reproduced.

## CALC+ comparison

Compare each burdened hourly labor rate with a level-matched CALC+ pool. Show the underlying direct hourly rate and multiplier so a reviewer can bridge the arithmetic. When the difference from P50 exceeds 15%, inspect seniority, SOC, geography, title-pool composition, and multiplier basis before writing the neutral note.

#!/usr/bin/env python3
"""Independently recompute LH/T&M labor, non-labor, and total estimates."""

from __future__ import annotations

import argparse
import json
import math
import sys
from pathlib import Path
from typing import Any


class InputError(ValueError):
    """Raised when validation inputs are missing or invalid."""


def _number(value: Any, label: str, *, minimum: float | None = None) -> float:
    if isinstance(value, bool) or not isinstance(value, (int, float)):
        raise InputError(f"{label} must be a number")
    result = float(value)
    if not math.isfinite(result):
        raise InputError(f"{label} must be finite")
    if minimum is not None and result < minimum:
        raise InputError(f"{label} must be at least {minimum}")
    return result


def _setting(line: dict[str, Any], assumptions: dict[str, Any], key: str) -> float:
    value = line[key] if key in line else assumptions.get(key)
    if value is None:
        raise InputError(f"missing {key} for {line.get('name', '<unnamed>')}")
    return _number(value, f"{line.get('name', '<unnamed>')}.{key}", minimum=0)


def _period_count(assumptions: dict[str, Any]) -> int:
    """Validate assumptions.periods, the total period count (base plus options)."""
    periods = assumptions.get("periods", 1)
    if isinstance(periods, bool) or not isinstance(periods, int) or periods < 1:
        raise InputError("assumptions.periods must be a whole number of at least 1")
    return periods


def _reference(raw: dict[str, Any], result: dict[str, Any], key: str) -> None:
    if key not in raw:
        return
    value = raw[key]
    if not isinstance(value, str) or not value.strip():
        raise InputError(f"{raw.get('name', '<unnamed>')}.{key} must be a non-empty string")
    result[key] = value


def calculate(payload: dict[str, Any]) -> dict[str, Any]:
    assumptions = payload.get("assumptions")
    labor_lines = payload.get("labor_lines")
    non_labor_lines = payload.get("non_labor_lines", [])
    if not isinstance(assumptions, dict):
        raise InputError("assumptions must be an object")
    if not isinstance(labor_lines, list) or not labor_lines:
        raise InputError("labor_lines must be a non-empty array")
    if not isinstance(non_labor_lines, list):
        raise InputError("non_labor_lines must be an array")

    contract_type = assumptions.get("contract_type")
    if not isinstance(contract_type, str) or contract_type.upper() not in {"LH", "T&M", "TM"}:
        raise InputError("assumptions.contract_type must be LH or T&M")
    contract_type = "T&M" if contract_type.upper() in {"T&M", "TM"} else "LH"

    labor_results: list[dict[str, Any]] = []
    totals = {"low": 0.0, "mid": 0.0, "high": 0.0}
    estimated_hours_total = 0.0
    ceiling_hours_total = 0.0

    for index, raw in enumerate(labor_lines):
        if not isinstance(raw, dict):
            raise InputError(f"labor_lines[{index}] must be an object")
        name = raw.get("name")
        if not isinstance(name, str) or not name.strip():
            raise InputError(f"labor_lines[{index}].name must be a non-empty string")
        wage = _number(raw.get("annual_wage"), f"{name}.annual_wage", minimum=0)
        aging = _setting(raw, assumptions, "aging_factor")
        hours = _setting(raw, assumptions, "productive_hours")
        fte = _number(raw.get("fte"), f"{name}.fte", minimum=0)
        months = _number(raw.get("months", 12), f"{name}.months", minimum=0)
        period_multiplier = _number(
            raw.get("period_multiplier", 1), f"{name}.period_multiplier", minimum=0
        )
        estimated_hours = hours * fte * (months / 12.0) * period_multiplier
        estimated_hours_total += estimated_hours

        if "annual_coverage_hours" in raw:
            coverage = _number(
                raw["annual_coverage_hours"], f"{name}.annual_coverage_hours", minimum=0
            ) * (months / 12.0) * period_multiplier
            if not math.isclose(estimated_hours, coverage, rel_tol=0.005, abs_tol=1.0):
                raise InputError(
                    f"{name} estimated hours {estimated_hours:.4f} do not reconcile "
                    f"to coverage hours {coverage:.4f}"
                )

        ceiling_hours = _number(
            raw.get("ceiling_hours", estimated_hours), f"{name}.ceiling_hours", minimum=0
        )
        ceiling_hours_total += ceiling_hours
        aged_wage = wage * aging
        direct_rate = aged_wage / 2080.0
        result: dict[str, Any] = {
            "name": name,
            "aged_annual_wage": aged_wage,
            "direct_hourly_rate": direct_rate,
            "estimated_hours": estimated_hours,
            "ceiling_hours": ceiling_hours,
            "ceiling_variance_hours": ceiling_hours - estimated_hours,
        }
        for scenario in ("low", "mid", "high"):
            multiplier = _setting(raw, assumptions, f"burden_{scenario}")
            rate = direct_rate * multiplier
            amount = rate * estimated_hours
            result[f"burdened_{scenario}_rate"] = rate
            result[f"labor_{scenario}_total"] = amount
            totals[scenario] += amount
        for key in (
            "workbook_low_rate_cell",
            "workbook_mid_rate_cell",
            "workbook_high_rate_cell",
            "workbook_mid_total_cell",
        ):
            _reference(raw, result, key)
        labor_results.append(result)

    non_labor_total = 0.0
    non_labor_results: list[dict[str, Any]] = []
    for index, raw in enumerate(non_labor_lines):
        if not isinstance(raw, dict):
            raise InputError(f"non_labor_lines[{index}] must be an object")
        name = raw.get("name")
        if not isinstance(name, str) or not name.strip():
            raise InputError(f"non_labor_lines[{index}].name must be a non-empty string")
        amount = _number(raw.get("amount"), f"{name}.amount", minimum=0)
        category = raw.get("category", "other")
        if not isinstance(category, str):
            raise InputError(f"{name}.category must be a string")
        if contract_type == "LH" and category.lower() == "materials" and amount > 0:
            raise InputError(f"{name} is a positive materials amount in an LH estimate")
        non_labor_total += amount
        result = {"name": name, "amount": amount, "category": category}
        _reference(raw, result, "workbook_total_cell")
        non_labor_results.append(result)

    output: dict[str, Any] = {
        "periods": _period_count(assumptions),
        "contract_type": contract_type,
        "labor_lines": labor_results,
        "non_labor_lines": non_labor_results,
        "labor_low_total": totals["low"],
        "labor_mid_total": totals["mid"],
        "labor_high_total": totals["high"],
        "non_labor_total": non_labor_total,
        "low_estimated_total": totals["low"] + non_labor_total,
        "mid_estimated_total": totals["mid"] + non_labor_total,
        "high_estimated_total": totals["high"] + non_labor_total,
        "estimated_hours_total": estimated_hours_total,
        "ceiling_hours_total": ceiling_hours_total,
    }
    ceiling_price = assumptions.get("ceiling_price")
    if ceiling_price is not None:
        ceiling = _number(ceiling_price, "ceiling_price", minimum=0)
        output["ceiling_price"] = ceiling
        output["ceiling_price_variance"] = ceiling - output["mid_estimated_total"]
    for key in (
        "workbook_low_total_cell",
        "workbook_mid_total_cell",
        "workbook_high_total_cell",
        "workbook_ceiling_price_cell",
    ):
        if key in payload:
            value = payload[key]
            if not isinstance(value, str) or not value.strip():
                raise InputError(f"{key} must be a non-empty string")
            output[key] = value
    return output


def load_payload(path: Path) -> dict[str, Any]:
    try:
        payload = json.loads(path.read_text(encoding="utf-8"))
    except OSError as exc:
        raise InputError(f"cannot read {path}: {exc}") from exc
    except json.JSONDecodeError as exc:
        raise InputError(f"invalid JSON in {path}: {exc}") from exc
    if not isinstance(payload, dict):
        raise InputError("top-level JSON value must be an object")
    return payload


def main() -> int:
    parser = argparse.ArgumentParser(description="Recompute expected LH/T&M totals.")
    parser.add_argument("input", type=Path)
    parser.add_argument("--output", type=Path)
    args = parser.parse_args()
    try:
        result = calculate(load_payload(args.input))
    except InputError as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 2
    rendered = json.dumps(result, indent=2, sort_keys=True) + "\n"
    if args.output:
        try:
            args.output.write_text(rendered, encoding="utf-8")
        except OSError as exc:
            print(f"ERROR: cannot write {args.output}: {exc}", file=sys.stderr)
            return 2
    else:
        print(rendered, end="")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

#!/usr/bin/env python3
"""Validate an LH/T&M workbook structurally and with optional engine execution."""

from __future__ import annotations

import argparse
import json
import math
import re
import shutil
import subprocess
import sys
import tempfile
from pathlib import Path
from typing import Any

from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string, get_column_letter

from recompute_expected_values import InputError, calculate, load_payload


DEFAULT_SHEETS = [
    "IGCE Summary",
    "Scenario Analysis",
    "Rate Validation",
    "Travel Detail",
    "Materials Detail",
    "Methodology",
    "Raw Data",
]

CELL_REF = re.compile(
    r"^(?:'((?:[^']|'')+)'|([^!]+))!\$?([A-Za-z]{1,3})\$?([1-9][0-9]*)$"
)
SCENARIO_B_REF = re.compile(
    r"(?:'Scenario Analysis'|Scenario Analysis)!\$?B\$?([1-9][0-9]*)",
    re.IGNORECASE,
)
BASE_PERIOD_LABEL = re.compile(r"\bBASE[\s-]*(?:YEAR|PERIOD)\b")
OPTION_PERIOD_LABEL = re.compile(
    r"\bOPTION\s*(?:YEAR|PERIOD)?\s*0*([1-9][0-9]*)\b|\bO[YP]\s*0*([1-9][0-9]*)\b"
)
TOTAL_PERIODS_CELL = "B14"
SUMMARY_CONSTANT_THRESHOLD = 1000.0
MONEY_KEYWORDS = (
    "TOTAL",
    "COST",
    "PRICE",
    "AMOUNT",
    "ODC",
    "OTHER DIRECT",
    "TRAVEL",
    "MATERIALS",
    "ESTIMATE",
)
NON_MONEY_KEYWORDS = ("HOUR", "FTE", "MONTH", "SOC", "PERIODS")
PERIOD_TOTAL_LABEL = re.compile(
    r"\bTOTAL\b.*\bALL\s+(?:PERIODS|YEARS)\b|\bALL\s+(?:PERIODS|YEARS)\b.*\bTOTAL\b"
)
SCENARIO_MONEY_KEYWORDS = MONEY_KEYWORDS + ("LABOR",)
SCENARIO_NON_MONEY_HEADERS = (
    "FACTOR",
    "MULTIPLIER",
    "RATE",
    "HOUR",
    "FTE",
    "ESCALATION",
    "PERCENT",
)
SUMMARY_PERIOD_SKIP = ("MONTH", "HOUR", "FTE", "SOC")
PERIOD_TIE_TOLERANCE = 1.0


def normalize_formula(value: Any) -> str:
    return re.sub(r"\s+", "", str(value)).upper()


def parse_cell_ref(reference: str) -> tuple[str, str]:
    match = CELL_REF.fullmatch(reference.strip())
    if not match:
        raise InputError(f"invalid workbook cell reference: {reference}")
    sheet = (match.group(1) or match.group(2)).replace("''", "'")
    coordinate = f"{match.group(3).upper()}{match.group(4)}"
    return sheet, coordinate


def cell_value(workbook: Any, reference: str) -> Any:
    sheet, coordinate = parse_cell_ref(reference)
    if sheet not in workbook.sheetnames:
        raise InputError(f"cell reference uses missing sheet: {reference}")
    return workbook[sheet][coordinate].value


def check_formula(
    failures: list[str],
    sheet: Any,
    coordinate: str,
    *,
    expected: str | None = None,
    contains: list[str] | None = None,
    not_contains: list[str] | None = None,
) -> None:
    value = sheet[coordinate].value
    if not isinstance(value, str) or not value.startswith("="):
        failures.append(f"{sheet.title}!{coordinate} is not a formula")
        return
    normalized = normalize_formula(value)
    if expected is not None and normalized != normalize_formula(expected):
        failures.append(f"{sheet.title}!{coordinate} formula does not match expected structure")
    for item in contains or []:
        if normalize_formula(item) not in normalized:
            failures.append(f"{sheet.title}!{coordinate} formula is missing {item}")
    for item in not_contains or []:
        if normalize_formula(item) in normalized:
            failures.append(f"{sheet.title}!{coordinate} formula contains forbidden text {item}")


def _is_number(value: Any) -> bool:
    return not isinstance(value, bool) and isinstance(value, (int, float))


def _nearest_header(sheet: Any, row: int, column: int, limit: int = 40) -> str:
    """Return the closest non-formula text above a cell in the same column."""
    for row_index in range(row - 1, max(0, row - limit), -1):
        value = sheet.cell(row_index, column).value
        if isinstance(value, str) and value.strip() and not value.startswith("="):
            return value.upper()
    return ""


def effective_periods(workbook: Any, payload_periods: int, failures: list[str]) -> int:
    """Reconcile the Total Periods assumption cell with the validation input."""
    if "IGCE Summary" not in workbook.sheetnames:
        return payload_periods
    value = workbook["IGCE Summary"][TOTAL_PERIODS_CELL].value
    if value is None:
        if payload_periods > 1:
            failures.append(
                f"IGCE Summary!{TOTAL_PERIODS_CELL} must record Total Periods (Base plus "
                f"Options) for a {payload_periods}-period requirement"
            )
        return payload_periods
    if not _is_number(value) or float(value) != int(value) or int(value) < 1:
        failures.append(
            f"IGCE Summary!{TOTAL_PERIODS_CELL} Total Periods (Base plus Options) must be "
            "a whole number of at least 1"
        )
        return payload_periods
    declared = int(value)
    if payload_periods > 1 and declared != payload_periods:
        failures.append(
            f"IGCE Summary!{TOTAL_PERIODS_CELL} Total Periods is {declared} but the "
            f"validation input states {payload_periods} periods"
        )
    return max(declared, payload_periods)


def period_coverage_audit(workbook: Any, periods: int) -> list[str]:
    """Require per-period totals on IGCE Summary for a multi-period requirement."""
    failures: list[str] = []
    if periods <= 1 or "IGCE Summary" not in workbook.sheetnames:
        return failures
    base_found = False
    option_numbers: set[int] = set()
    for row in workbook["IGCE Summary"].iter_rows():
        for cell in row:
            value = cell.value
            if not isinstance(value, str):
                continue
            upper = value.upper()
            if BASE_PERIOD_LABEL.search(upper):
                base_found = True
            for match in OPTION_PERIOD_LABEL.finditer(upper):
                option_numbers.add(int(match.group(1) or match.group(2)))
    if not base_found or len(option_numbers) < periods - 1:
        found = ", ".join(f"Option {n}" for n in sorted(option_numbers)) or "none"
        failures.append(
            f"the stated period of performance has {periods} periods (base plus "
            f"{periods - 1} options) but IGCE Summary shows no per-period totals (base "
            f"label found: {'yes' if base_found else 'no'}; option-period labels found: "
            f"{found}); a headline planning estimate must cover every period or carry an "
            "explicit single-period coverage annotation for a single-period scope"
        )
    return failures


def escalation_input_audit(workbook: Any, periods: int) -> list[str]:
    """Reject a dead escalation input when multi-period pricing exists."""
    failures: list[str] = []
    if periods <= 1 or "IGCE Summary" not in workbook.sheetnames:
        return failures
    summary = workbook["IGCE Summary"]
    target = None
    for row in summary.iter_rows():
        for cell in row:
            value = cell.value
            if (
                isinstance(value, str)
                and "ESCALATION" in value.upper()
                and len(value.strip()) <= 30
            ):
                for column in range(cell.column + 1, min(cell.column + 6, summary.max_column) + 1):
                    candidate = summary.cell(cell.row, column)
                    if _is_number(candidate.value):
                        target = candidate
                        break
                if target is None and _is_number(summary.cell(cell.row + 1, cell.column).value):
                    target = summary.cell(cell.row + 1, cell.column)
            if target is not None:
                break
        if target is not None:
            break
    if target is None:
        failures.append(
            "IGCE Summary has no numeric Escalation Rate input; a multi-period estimate "
            "must carry a live escalation input"
        )
        return failures
    column_letter = target.column_letter
    row_number = target.row
    bare_reference = re.compile(
        rf"(?<![A-Z0-9_$])\$?{column_letter}\$?{row_number}(?![0-9])"
    )
    sheet_reference = re.compile(
        rf"(?:'IGCE SUMMARY'|IGCE SUMMARY)!\$?{column_letter}\$?{row_number}(?![0-9])"
    )
    referenced = False
    for sheet in workbook.worksheets:
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if not isinstance(value, str) or not value.startswith("="):
                    continue
                normalized = normalize_formula(value)
                if sheet.title == "IGCE Summary":
                    if bare_reference.search(normalized):
                        referenced = True
                elif sheet_reference.search(normalized):
                    referenced = True
            if referenced:
                break
        if referenced:
            break
    if not referenced:
        failures.append(
            f"IGCE Summary!{target.coordinate} escalation input "
            f"({target.value}) is referenced by zero formulas; multi-period pricing must "
            "apply the escalation input rather than displaying a dead cell"
        )
    return failures


def summary_constant_audit(workbook: Any) -> list[str]:
    """Reject hardcoded summary money amounts absent from the refresh register."""
    failures: list[str] = []
    if "IGCE Summary" not in workbook.sheetnames or "Raw Data" not in workbook.sheetnames:
        return failures
    register_numbers: list[float] = []
    for row in workbook["Raw Data"].iter_rows():
        for cell in row:
            value = cell.value
            if _is_number(value):
                register_numbers.append(float(value))
            elif isinstance(value, str):
                for token in re.findall(r"\d[\d,]*(?:\.\d+)?", value):
                    register_numbers.append(float(token.replace(",", "")))
    summary = workbook["IGCE Summary"]
    for row in summary.iter_rows():
        label = next(
            (c.value for c in row if isinstance(c.value, str) and c.value.strip()), ""
        )
        label_upper = str(label).upper()
        for cell in row:
            value = cell.value
            if not _is_number(value) or abs(float(value)) < SUMMARY_CONSTANT_THRESHOLD:
                continue
            header = _nearest_header(summary, cell.row, cell.column)
            if any(keyword in header for keyword in NON_MONEY_KEYWORDS):
                continue
            if not any(
                keyword in header or keyword in label_upper for keyword in MONEY_KEYWORDS
            ):
                continue
            if any(abs(float(value) - entry) <= 1.0 for entry in register_numbers):
                continue
            failures.append(
                f"IGCE Summary!{cell.coordinate} hardcodes {float(value):,.2f} "
                f"({str(label).strip() or 'unlabeled line'}) with no matching entry in "
                "the Raw Data refresh register; every summary money line must trace to a "
                "detail sheet or assumptions input and appear in the register"
            )
    return failures


def _period_key(label: str) -> tuple[Any, ...] | None:
    """Classify a row label as a period key: total, option N, or base."""
    if PERIOD_TOTAL_LABEL.search(label):
        return ("total",)
    match = OPTION_PERIOD_LABEL.search(label)
    if match:
        return ("option", int(match.group(1) or match.group(2)))
    if BASE_PERIOD_LABEL.search(label):
        return ("base",)
    return None


def _period_name(key: tuple[Any, ...]) -> str:
    if key[0] == "total":
        return "the all-periods total"
    if key[0] == "option":
        return f"option period {key[1]}"
    return "the base period"


def _row_label(row: tuple[Any, ...]) -> str:
    return next(
        (c.value for c in row if isinstance(c.value, str) and c.value.strip()), ""
    ).upper()


def scenario_period_tie_audit(values_workbook: Any) -> list[str]:
    """Require Scenario Analysis period money figures to tie to IGCE Summary.

    Runs on calculated (data_only) values, so a stale formula whose text looks
    plausible but whose result is zero is still caught. Money-labeled scenario
    period cells that are all zero while the summary per-period totals are
    nonzero fail outright; otherwise every current-assumptions (mid) scenario
    figure must agree with an IGCE Summary amount for the same period within $1.
    Cells with no calculated value available are skipped.
    """
    failures: list[str] = []
    if (
        "Scenario Analysis" not in values_workbook.sheetnames
        or "IGCE Summary" not in values_workbook.sheetnames
    ):
        return failures

    summary_periods: dict[tuple[Any, ...], list[tuple[str, float]]] = {}
    for row in values_workbook["IGCE Summary"].iter_rows():
        label = _row_label(row)
        key = _period_key(label)
        if key is None or any(word in label for word in SUMMARY_PERIOD_SKIP):
            continue
        for cell in row:
            if _is_number(cell.value):
                summary_periods.setdefault(key, []).append(
                    (cell.coordinate, float(cell.value))
                )

    scenario = values_workbook["Scenario Analysis"]
    money_cells: list[tuple[tuple[Any, ...], Any, str]] = []
    for row in scenario.iter_rows():
        key = _period_key(_row_label(row))
        if key is None:
            continue
        for cell in row:
            if not _is_number(cell.value):
                continue
            header = _nearest_header(scenario, cell.row, cell.column, limit=10)
            if any(word in header for word in SCENARIO_NON_MONEY_HEADERS):
                continue
            if not any(word in header for word in SCENARIO_MONEY_KEYWORDS):
                continue
            money_cells.append((key, cell, header))
    if not money_cells:
        return failures

    summary_nonzero = any(
        abs(amount) > PERIOD_TIE_TOLERANCE
        for entries in summary_periods.values()
        for _, amount in entries
    )
    if summary_nonzero and all(
        abs(float(cell.value)) < 0.005 for _, cell, _ in money_cells
    ):
        coordinates = ", ".join(cell.coordinate for _, cell, _ in money_cells)
        failures.append(
            f"Scenario Analysis period-totals money cells ({coordinates}) are all zero "
            "while IGCE Summary per-period totals are nonzero; the scenario table is "
            "disconnected from the live summary figures (check the formulas for stale "
            "ranges that point at the wrong rows)"
        )
        return failures

    has_mid = any("MID" in h or "POINT" in h for _, _, h in money_cells)
    for key, cell, header in money_cells:
        if has_mid and "MID" not in header and "POINT" not in header:
            continue
        entries = summary_periods.get(key)
        if not entries:
            continue
        value = float(cell.value)
        if not any(
            abs(value - amount) <= PERIOD_TIE_TOLERANCE for _, amount in entries
        ):
            closest = min(entries, key=lambda item: abs(value - item[1]))
            failures.append(
                f"Scenario Analysis!{cell.coordinate} shows {value:,.2f} for "
                f"{_period_name(key)} under current assumptions but no IGCE Summary "
                f"amount for that period is within $1 (closest is IGCE Summary!"
                f"{closest[0]} at {closest[1]:,.2f}); the scenario period totals must "
                "tie to the IGCE Summary per-period totals"
            )
    return failures


def material_handling_audit(workbook: Any, payload: dict[str, Any]) -> list[str]:
    """Reject undisclosed material-handling amounts or formulas.

    Zero remains the default. A nonzero numeric input or formula is allowed only
    when the validation sidecar identifies the exact cell and value/formula and
    records the user-supplied accounting or solicitation basis.
    """

    failures: list[str] = []
    if "Materials Detail" not in workbook.sheetnames:
        return failures

    assumptions = payload.get("assumptions", {})
    contract_type = assumptions.get("contract_type", "") if isinstance(assumptions, dict) else ""
    is_tm = isinstance(contract_type, str) and contract_type.upper() in {"T&M", "TM"}
    sheet = workbook["Materials Detail"]

    headers: list[Any] = []
    for row in sheet.iter_rows():
        for cell in row:
            label = re.sub(r"[^A-Z0-9]+", " ", str(cell.value or "").upper()).strip()
            if label in {
                "MATERIAL HANDLING",
                "MATERIAL HANDLING COST",
                "MATERIAL HANDLING INDIRECT",
                "MATERIAL HANDLING INDIRECT COST",
                "APPLICABLE MATERIAL HANDLING INDIRECT COST",
            }:
                headers.append(cell)
    if is_tm and not headers:
        failures.append("Materials Detail has no Material Handling column")
        return failures

    raw_assertions = payload.get("material_handling_assertions", [])
    if not isinstance(raw_assertions, list):
        raise InputError("material_handling_assertions must be an array")

    assertions: dict[str, dict[str, Any]] = {}
    for index, assertion in enumerate(raw_assertions):
        if not isinstance(assertion, dict):
            raise InputError(f"material_handling_assertions[{index}] must be an object")
        reference = assertion.get("cell")
        basis = assertion.get("basis")
        expected = assertion.get("equals")
        if not isinstance(reference, str):
            raise InputError(f"material_handling_assertions[{index}].cell must be a string")
        if not isinstance(basis, str) or not basis.strip():
            raise InputError(
                f"material_handling_assertions[{index}].basis must be a non-empty string"
            )
        if isinstance(expected, bool) or not isinstance(expected, (int, float, str)):
            raise InputError(
                f"material_handling_assertions[{index}].equals must be a number or formula string"
            )
        if isinstance(expected, (int, float)) and (
            not math.isfinite(float(expected)) or float(expected) < 0
        ):
            raise InputError(
                f"material_handling_assertions[{index}].equals must be finite and non-negative"
            )
        if isinstance(expected, str) and not expected.startswith("="):
            raise InputError(
                f"material_handling_assertions[{index}].equals string must be a formula"
            )
        sheet_name, coordinate = parse_cell_ref(reference)
        if sheet_name != "Materials Detail":
            raise InputError(
                f"material_handling_assertions[{index}].cell must reference Materials Detail"
            )
        normalized_reference = f"Materials Detail!{coordinate}"
        if normalized_reference in assertions:
            raise InputError(f"duplicate material-handling assertion: {reference}")
        assertions[normalized_reference] = {
            "equals": expected,
            "basis": basis.strip(),
        }

    inspected: set[str] = set()
    for header in headers:
        for row_number in range(header.row + 1, sheet.max_row + 1):
            cell = sheet.cell(row_number, header.column)
            value = cell.value
            if value is None or value == "":
                continue
            reference = f"Materials Detail!{cell.coordinate}"
            inspected.add(reference)
            assertion = assertions.get(reference)

            if isinstance(value, bool):
                failures.append(f"{reference} must be a numeric input or disclosed formula")
                continue
            if isinstance(value, (int, float)) and float(value) == 0 and assertion is None:
                continue
            if not is_tm:
                failures.append(f"{reference} must be zero for a Labor-Hour estimate")
                continue
            if assertion is None:
                failures.append(
                    f"{reference} contains undisclosed material handling {value!r}; "
                    "zero is required unless the sidecar records the exact value/formula and basis"
                )
                continue

            expected = assertion["equals"]
            if isinstance(expected, str):
                if not isinstance(value, str) or normalize_formula(value) != normalize_formula(expected):
                    failures.append(f"{reference} does not match its disclosed material-handling formula")
            elif isinstance(value, bool) or not isinstance(value, (int, float)):
                failures.append(f"{reference} is not the disclosed numeric material-handling input")
            elif not math.isclose(float(value), float(expected), rel_tol=0, abs_tol=1e-9):
                failures.append(
                    f"{reference} is {float(value):.6f}, expected disclosed input {float(expected):.6f}"
                )

    for reference in sorted(set(assertions) - inspected):
        failures.append(f"material-handling assertion references no populated handling cell: {reference}")
    return failures


# --- Rendered-text clipping audit -------------------------------------------
# A text cell overflows into the next cell only when that neighbour is empty.
# When the neighbour is occupied the label is cut off in the printed workbook,
# so every such label must fit its column, wrap, or be merged across the block.

CLIPPING_GLYPH_WIDTHS = {
    " ": 0.45,
    ".": 0.45,
    ",": 0.45,
    ";": 0.45,
    ":": 0.45,
    "'": 0.35,
    "`": 0.45,
    "!": 0.45,
    "|": 0.45,
    "(": 0.55,
    ")": 0.55,
    "[": 0.55,
    "]": 0.55,
    "{": 0.60,
    "}": 0.60,
    "-": 0.60,
    "/": 0.55,
    "\\": 0.55,
    '"': 0.60,
    "%": 1.50,
    "@": 1.70,
    "$": 1.00,
}
CLIPPING_LOWER_NARROW = "ijl"
CLIPPING_LOWER_SEMI = "frt"
CLIPPING_LOWER_WIDE = "mw"
CLIPPING_UPPER_NARROW = "I"
CLIPPING_UPPER_WIDE = "MW"
CLIPPING_BOLD_FACTOR = 1.14
CLIPPING_ABSOLUTE_TOLERANCE = 0.75
CLIPPING_RELATIVE_TOLERANCE = 0.04
CLIPPING_DEFAULT_WIDTH = 8.43
CLIPPING_MESSAGE_TEXT_LIMIT = 120


def glyph_width(character: str) -> float:
    """Width of one glyph in Excel column-width units (1.0 = one digit)."""
    if character in CLIPPING_GLYPH_WIDTHS:
        return CLIPPING_GLYPH_WIDTHS[character]
    if character in CLIPPING_LOWER_NARROW:
        return 0.48
    if character in CLIPPING_LOWER_SEMI:
        return 0.59
    if character in CLIPPING_LOWER_WIDE:
        return 1.66
    if character in CLIPPING_UPPER_NARROW:
        return 0.45
    if character in CLIPPING_UPPER_WIDE:
        return 1.55
    if character.islower():
        return 0.96
    if character.isupper():
        return 1.05
    return 1.0


def estimated_text_width(text: str, font: Any = None) -> float:
    """Estimated rendered width of a label in column-width units."""
    lines = str(text).split("\n")
    units = max((sum(glyph_width(character) for character in line) for line in lines), default=0.0)
    size = getattr(font, "size", None) or 11.0
    if float(size) != 11.0:
        units *= float(size) / 11.0
    if getattr(font, "bold", False):
        units *= CLIPPING_BOLD_FACTOR
    return units


def column_width_map(sheet: Any) -> tuple[dict[int, float], float]:
    """Explicit column widths by index plus the sheet default width."""
    widths: dict[int, float] = {}
    for letter, dimension in sheet.column_dimensions.items():
        if dimension.width is None:
            continue
        # In-memory dimensions created by a generator carry no min/max, so fall
        # back to the column the dimension is keyed under.
        try:
            own = column_index_from_string(letter)
        except ValueError:
            own = None
        first = dimension.min or own or 1
        last = dimension.max or own or first
        for index in range(first, last + 1):
            widths[index] = float(dimension.width)
    default = sheet.sheet_format.defaultColWidth or CLIPPING_DEFAULT_WIDTH
    return widths, float(default)


def merged_ranges_by_anchor(sheet: Any) -> tuple[dict[tuple[int, int], Any], set[tuple[int, int]]]:
    """Merged-range lookup keyed by anchor cell, plus every covered cell."""
    anchors: dict[tuple[int, int], Any] = {}
    covered: set[tuple[int, int]] = set()
    for merged in sheet.merged_cells.ranges:
        anchors[(merged.min_row, merged.min_col)] = merged
        for row in range(merged.min_row, merged.max_row + 1):
            for column in range(merged.min_col, merged.max_col + 1):
                covered.add((row, column))
    return anchors, covered


def _is_blank(value: Any) -> bool:
    if value is None:
        return True
    return isinstance(value, str) and not value.strip()


def text_clipping_audit(
    workbook: Any,
    *,
    sheets: list[str] | None = None,
    exempt: set[str] | None = None,
) -> list[str]:
    """Flag text that is cut off in print because an occupied neighbour blocks overflow."""
    failures: list[str] = []
    skipped = exempt or set()
    for sheet in workbook.worksheets:
        if sheets is not None and sheet.title not in sheets:
            continue
        widths, default_width = column_width_map(sheet)
        anchors, covered = merged_ranges_by_anchor(sheet)
        max_column = sheet.max_column
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if not isinstance(value, str) or not value.strip() or value.startswith("="):
                    continue
                position = (cell.row, cell.column)
                if position in covered and position not in anchors:
                    continue
                alignment = cell.alignment
                if alignment.wrap_text or alignment.horizontal in {"fill", "distributed"}:
                    continue
                if f"{sheet.title}!{cell.coordinate}" in skipped:
                    continue
                merged = anchors.get(position)
                first_column = cell.column
                last_column = merged.max_col if merged is not None else cell.column
                available = sum(
                    widths.get(index, default_width)
                    for index in range(first_column, last_column + 1)
                )
                needed = estimated_text_width(value, cell.font)
                tolerance = max(
                    CLIPPING_ABSOLUTE_TOLERANCE,
                    CLIPPING_RELATIVE_TOLERANCE * available,
                )
                if needed <= available + tolerance:
                    continue
                blockers = []
                if alignment.horizontal in {"right", "center", "centerContinuous"}:
                    left = first_column - 1
                    if left < 1:
                        blockers.append("the left sheet edge")
                    elif not _is_blank(sheet.cell(row=cell.row, column=left).value):
                        blockers.append(sheet.cell(row=cell.row, column=left).coordinate)
                if alignment.horizontal != "right":
                    right = last_column + 1
                    if right <= max_column and not _is_blank(
                        sheet.cell(row=cell.row, column=right).value
                    ):
                        blockers.append(sheet.cell(row=cell.row, column=right).coordinate)
                if not blockers:
                    continue
                shown = value.strip()
                if len(shown) > CLIPPING_MESSAGE_TEXT_LIMIT:
                    shown = shown[: CLIPPING_MESSAGE_TEXT_LIMIT - 3] + "..."
                span = (
                    cell.column_letter
                    if merged is None
                    else f"{cell.column_letter}:{get_column_letter(last_column)}"
                )
                failures.append(
                    f"{sheet.title}!{cell.coordinate} is clipped in print: '{shown}' needs about "
                    f"{needed:.1f} column-width units but column {span} gives {available:g} and "
                    f"{', '.join(blockers)} blocks the overflow; widen the column to at least "
                    f"{math.ceil(needed):g}, enable wrap text with adequate row height, merge the "
                    f"label across the block, or shorten it"
                )
    return failures


def structural_audit(workbook: Any, payload: dict[str, Any]) -> list[str]:
    failures: list[str] = []
    required_sheets = payload.get("required_sheets", DEFAULT_SHEETS)
    if not isinstance(required_sheets, list) or not all(
        isinstance(item, str) for item in required_sheets
    ):
        raise InputError("required_sheets must be an array of strings")
    for sheet_name in required_sheets:
        if sheet_name not in workbook.sheetnames:
            failures.append(f"missing required sheet: {sheet_name}")

    if "IGCE Summary" in workbook.sheetnames:
        summary = workbook["IGCE Summary"]
        check_formula(
            failures,
            summary,
            "B10",
            contains=[
                "VALUE(LEFT(B9,4))",
                "VALUE(MID(B9,6,2))",
                "VALUE(LEFT(B8,4))",
                "VALUE(MID(B8,6,2))",
            ],
            not_contains=["DATEDIF", "YEAR("],
        )
        check_formula(
            failures,
            summary,
            "B11",
            contains=["B5", "B10", "^"],
        )
        if summary["B11"].number_format != "0.0000":
            failures.append("IGCE Summary!B11 must display the aging factor as 0.0000")

    formula_count = 0
    formula_error_tokens = ("#REF!", "#NAME?", "#VALUE!", "#DIV/0!")
    for sheet in workbook.worksheets:
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if isinstance(value, str) and value.startswith("="):
                    formula_count += 1
                    upper = value.upper()
                    if any(token in upper for token in formula_error_tokens):
                        failures.append(f"{sheet.title}!{cell.coordinate} contains a formula error token")
                elif isinstance(value, str) and value[:1] in {"+", "-", "@"}:
                    failures.append(
                        f"{sheet.title}!{cell.coordinate} starts with a formula-trigger character"
                    )
    if formula_count == 0:
        failures.append("workbook contains no formulas")

    if "Scenario Analysis" in workbook.sheetnames:
        buildup = workbook["Scenario Analysis"]
        starts: list[int] = []
        for row_index in range(1, buildup.max_row + 1):
            label = buildup.cell(row_index, 1).value
            if isinstance(label, str) and label.startswith("Scenario Analysis:"):
                starts.append(row_index)
        if not starts:
            failures.append("Scenario Analysis contains no recognized labor blocks")
        for block_index, base in enumerate(starts):
            expected_base = 1 + block_index * 13
            if base != expected_base:
                failures.append(
                    f"Scenario Analysis block {block_index + 1} starts at row {base}, expected {expected_base}"
                )
            if buildup[f"B{base + 5}"].value is not None:
                failures.append(f"Scenario Analysis!B{base + 5} must be the blank separator row")
            if buildup[f"B{base + 12}"].value is not None:
                failures.append(f"Scenario Analysis!B{base + 12} must be the blank block separator")
            check_formula(
                failures,
                buildup,
                f"B{base + 2}",
                contains=["'IGCE SUMMARY'!$B$11"],
            )
            if buildup[f"B{base + 2}"].number_format != "0.0000":
                failures.append(
                    f"Scenario Analysis!B{base + 2} must display the aging factor as 0.0000"
                )
            formulas = {
                base + 3: f"=B{base + 1}*B{base + 2}",
                base + 4: f"=B{base + 3}/2080",
                base + 6: "='IGCE Summary'!$B$2",
                base + 7: f"=B{base + 4}*B{base + 6}",
                base + 8: "='IGCE Summary'!$B$3",
                base + 9: f"=B{base + 4}*B{base + 8}",
                base + 10: "='IGCE Summary'!$B$4",
                base + 11: f"=B{base + 4}*B{base + 10}",
            }
            for row_number, expected_formula in formulas.items():
                check_formula(
                    failures,
                    buildup,
                    f"B{row_number}",
                    expected=expected_formula,
                )

    for sheet_name in ("IGCE Summary", "Rate Validation"):
        if sheet_name not in workbook.sheetnames:
            continue
        sheet = workbook[sheet_name]
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if not isinstance(value, str) or not value.startswith("="):
                    continue
                for match in SCENARIO_B_REF.finditer(value):
                    referenced_row = int(match.group(1))
                    if (referenced_row - 4) % 13 == 0:
                        failures.append(
                            f"{sheet_name}!{cell.coordinate} uses Scenario Analysis row {referenced_row} "
                            "as a cross-sheet input; that row is Aged Annual Wage, not Direct Labor"
                        )

    failures.extend(material_handling_audit(workbook, payload))

    assertions = payload.get("formula_assertions", [])
    if not isinstance(assertions, list):
        raise InputError("formula_assertions must be an array")
    for index, assertion in enumerate(assertions):
        if not isinstance(assertion, dict) or not isinstance(assertion.get("cell"), str):
            raise InputError(f"formula_assertions[{index}] must contain a cell string")
        sheet_name, coordinate = parse_cell_ref(assertion["cell"])
        if sheet_name not in workbook.sheetnames:
            failures.append(f"formula assertion references missing sheet: {sheet_name}")
            continue
        contains = assertion.get("contains", [])
        not_contains = assertion.get("not_contains", [])
        if not isinstance(contains, list) or not all(isinstance(item, str) for item in contains):
            raise InputError(f"formula_assertions[{index}].contains must be an array of strings")
        if not isinstance(not_contains, list) or not all(
            isinstance(item, str) for item in not_contains
        ):
            raise InputError(
                f"formula_assertions[{index}].not_contains must be an array of strings"
            )
        equals = assertion.get("equals")
        if equals is not None and not isinstance(equals, str):
            raise InputError(f"formula_assertions[{index}].equals must be a string")
        check_formula(
            failures,
            workbook[sheet_name],
            coordinate,
            expected=equals,
            contains=contains,
            not_contains=not_contains,
        )
    failures.extend(text_clipping_audit(workbook))
    return failures


def find_soffice() -> Path | None:
    discovered = shutil.which("soffice")
    if discovered:
        return Path(discovered)
    mac_path = Path("/Applications/LibreOffice.app/Contents/MacOS/soffice")
    return mac_path if mac_path.is_file() else None


def recalculate_with_libreoffice(source: Path, executable: Path) -> tuple[tempfile.TemporaryDirectory[str], Path]:
    temp = tempfile.TemporaryDirectory(prefix="lh-tm-workbook-validation-")
    temp_root = Path(temp.name)
    input_dir = temp_root / "input"
    output_dir = temp_root / "output"
    input_dir.mkdir()
    output_dir.mkdir()
    copied = input_dir / source.name
    shutil.copy2(source, copied)
    command = [
        str(executable),
        "--headless",
        "--convert-to",
        "xlsx",
        "--outdir",
        str(output_dir),
        str(copied),
    ]
    completed = subprocess.run(command, capture_output=True, text=True, timeout=120, check=False)
    recalculated = output_dir / source.name
    if completed.returncode != 0 or not recalculated.is_file():
        temp.cleanup()
        detail = (completed.stderr or completed.stdout).strip()
        raise InputError(f"LibreOffice recalculation failed: {detail or 'no output file'}")
    return temp, recalculated


def close_enough(actual: float, expected: float, tolerance: float) -> bool:
    return math.isclose(actual, expected, rel_tol=tolerance, abs_tol=max(0.01, tolerance))


def compare_results(
    workbook: Any,
    expected: dict[str, Any],
    tolerance: float,
) -> list[str]:
    failures: list[str] = []

    def compare(reference: str, target: float, label: str) -> None:
        try:
            value = cell_value(workbook, reference)
        except InputError as exc:
            failures.append(str(exc))
            return
        if isinstance(value, bool) or not isinstance(value, (int, float)):
            failures.append(f"{label} at {reference} has no calculated numeric value")
            return
        if not close_enough(float(value), target, tolerance):
            failures.append(
                f"{label} at {reference} is {float(value):.6f}, expected {target:.6f}"
            )

    for line in expected["labor_lines"]:
        mappings = (
            ("workbook_low_rate_cell", "burdened_low_rate", "low rate"),
            ("workbook_mid_rate_cell", "burdened_mid_rate", "mid rate"),
            ("workbook_high_rate_cell", "burdened_high_rate", "high rate"),
            ("workbook_mid_total_cell", "labor_mid_total", "mid labor total"),
        )
        for reference_key, expected_key, label in mappings:
            if reference_key in line:
                compare(line[reference_key], line[expected_key], f"{line['name']} {label}")
    for line in expected["non_labor_lines"]:
        if "workbook_total_cell" in line:
            compare(
                line["workbook_total_cell"],
                line["amount"],
                f"{line['name']} amount",
            )
    totals = (
        ("workbook_low_total_cell", "low_estimated_total", "low estimated total"),
        ("workbook_mid_total_cell", "mid_estimated_total", "mid estimated total"),
        ("workbook_high_total_cell", "high_estimated_total", "high estimated total"),
        ("workbook_ceiling_price_cell", "ceiling_price", "ceiling price"),
    )
    for reference_key, expected_key, label in totals:
        if reference_key in expected and expected_key in expected:
            compare(expected[reference_key], expected[expected_key], label)
    return failures


def cached_error_audit(workbook: Any) -> list[str]:
    failures: list[str] = []
    for sheet in workbook.worksheets:
        for row in sheet.iter_rows():
            for cell in row:
                value = cell.value
                if isinstance(value, str) and value.startswith("#"):
                    failures.append(f"{sheet.title}!{cell.coordinate} has cached error {value}")
    return failures


def main() -> int:
    parser = argparse.ArgumentParser(
        description="Audit an LH/T&M workbook and optionally verify formula execution with LibreOffice."
    )
    parser.add_argument("workbook", type=Path, help="Workbook to validate")
    parser.add_argument("--expected", required=True, type=Path, help="Validation-input JSON")
    parser.add_argument(
        "--engine",
        choices=("auto", "none", "libreoffice"),
        default="auto",
        help="Formula execution engine policy",
    )
    parser.add_argument("--tolerance", type=float, default=0.01, help="Relative comparison tolerance")
    parser.add_argument("--json", action="store_true", help="Emit a JSON result")
    args = parser.parse_args()

    if not math.isfinite(args.tolerance) or args.tolerance < 0:
        print("ERROR: tolerance must be finite and non-negative", file=sys.stderr)
        return 2
    if not args.workbook.is_file():
        print(f"ERROR: workbook not found: {args.workbook}", file=sys.stderr)
        return 2

    try:
        payload = load_payload(args.expected)
        expected = calculate(payload)
        formula_workbook = load_workbook(args.workbook, data_only=False)
        structural_failures = structural_audit(formula_workbook, payload)
        periods = effective_periods(formula_workbook, expected["periods"], structural_failures)
        structural_failures.extend(period_coverage_audit(formula_workbook, periods))
        structural_failures.extend(escalation_input_audit(formula_workbook, periods))
        structural_failures.extend(summary_constant_audit(formula_workbook))
        failures = list(structural_failures)
        values_workbook = load_workbook(args.workbook, data_only=True)
        failures.extend(scenario_period_tie_audit(values_workbook))
    except (InputError, OSError, ValueError) as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 2

    engine_used: str | None = None
    engine_note: str
    temp: tempfile.TemporaryDirectory[str] | None = None
    try:
        if args.engine == "none":
            engine_note = "Formula execution was not requested."
        else:
            soffice = find_soffice()
            if soffice is None:
                if args.engine == "libreoffice":
                    failures.append("LibreOffice was required but soffice was not found")
                    engine_note = "LibreOffice was required but unavailable."
                else:
                    engine_note = "Formula execution was not independently verified in Excel or LibreOffice."
            else:
                temp, recalculated = recalculate_with_libreoffice(args.workbook, soffice)
                calculated_workbook = load_workbook(recalculated, data_only=True)
                failures.extend(cached_error_audit(calculated_workbook))
                failures.extend(compare_results(calculated_workbook, expected, args.tolerance))
                for failure in scenario_period_tie_audit(calculated_workbook):
                    if failure not in failures:
                        failures.append(failure)
                engine_used = "LibreOffice"
                engine_note = "LibreOffice formula execution and cached-value comparison ran."
    except (InputError, OSError, subprocess.SubprocessError, ValueError) as exc:
        failures.append(str(exc))
        engine_note = "LibreOffice execution failed."
    finally:
        if temp is not None:
            temp.cleanup()

    result = {
        "status": "pass" if not failures else "fail",
        "formula_structure": "pass" if not structural_failures else "fail",
        "independent_recomputation": "pass",
        "independent_mid_estimated_total": expected["mid_estimated_total"],
        "engine": engine_used,
        "engine_note": engine_note,
        "failures": failures,
    }
    if args.json:
        print(json.dumps(result, indent=2, sort_keys=True))
    else:
        if failures:
            print("VALIDATION FAILED")
            for failure in failures:
                print(f"- {failure}")
        elif engine_used:
            print("Formula structure, independent calculations, and LibreOffice formula execution passed.")
        else:
            print(
                "Formula structure and independent calculations passed. "
                "Formula execution was not independently verified in Excel or LibreOffice."
            )
        print(f"Independent mid estimated total: {expected['mid_estimated_total']:.2f}")
        print(engine_note)
    return 0 if not failures else 1


if __name__ == "__main__":
    raise SystemExit(main())