How do I compare timecard hours to budgeted labor hours by cost code with AI to get a productivity factor?

Read this labor export [upload CSV or PDF; remove worker names first] and this labor budget [paste: cost code, description, unit, budget quantity, budget hours]. Build a Timecards table with one row per week ending, crew and cost code with hours, copying values exactly and writing NOT SHOWN instead of guessing. List every cost code in the export that is not in the budget. Do not recode anything yourself and do not judge crew performance.

EXAMPLE DATA ONLY. Here are my Budget (with installed quantity to date) and Timecards tabs [paste the example rows]. Write Excel formulas (or a step-by-step Copilot plan) that sum actual hours per code, compute percent complete as installed over budget quantity, earned hours as budget hours times percent complete, productivity factor as earned over actual, and projected hours at completion as actual over percent complete, and flag: factor below my threshold of [type yours], hours with zero installed, installed over budget quantity, and hours on codes with no budget line. Use only my data.

Budget tab (example)
Code | Description | Unit | Budget qty | Budget hrs | Installed to date
03-110 | Wall forms | SFCA | 12000 | 1800 | 5000
03-210 | Rebar placing | TON | 180 | 2160 | 90
03-310 | Slab place and finish | SF | 40000 | 1200 | 10000
03-350 | Slab curing | SF | 40000 | 160 | 0
03-410 | Embeds and anchors | EA | 600 | 300 | 650

Timecards tab (example; names removed)
Week ending | Crew | Code | Hours
2026-10-02 | Forms A | 03-110 | 410
2026-10-09 | Forms A | 03-110 | 410
2026-10-02 | Rebar | 03-210 | 500
2026-10-09 | Rebar | 03-210 | 510
2026-10-02 | Flatwork | 03-310 | 200
2026-10-09 | Flatwork | 03-310 | 210
2026-10-09 | Flatwork | 03-350 | 24
2026-10-02 | Embeds | 03-410 | 140
2026-10-09 | Embeds | 03-410 | 150
2026-10-09 | Forms A | 03-999 | 36

Threshold (placeholder I typed): flag productivity factor below 0.90

EXAMPLE DATA ONLY (not from a real project). Budget tab: 03-110 Wall forms | SFCA | 12000 | 1800 hrs | installed 5000; 03-210 Rebar placing | TON | 180 | 2160 hrs | installed 90; 03-310 Slab place and finish | SF | 40000 | 1200 hrs | installed 10000; 03-350 Slab curing | SF | 40000 | 160 hrs | installed 0; 03-410 Embeds and anchors | EA | 600 | 300 hrs | installed 650. Timecards tab: two weeks of hours by crew and code, including 36 hours on 03-999. Threshold I typed: flag a productivity factor below 0.90. Write Excel formulas (or a step-by-step Copilot plan) that sum actual hours per code, compute percent complete, earned hours, productivity factor and projected hours at completion, and flag factors below my threshold, hours with no installed quantity, installed over budget quantity, and hours on codes with no budget line. Use only my data; do not decide whether the crew is performing or who pays.

Expected results on this example (illustrative — verify every formula yourself)
03-110 Wall forms: 42% complete, earned 750, actual 820, PF 0.91, projected 1968 vs 1800 (+168) → no flag at 0.90, but trending over; watch it
03-210 Rebar placing: 50% complete, earned 1080, actual 1010, PF 1.07, projected 2020 vs 2160 (−140) → no flag
03-310 Slab place and finish: 25% complete, earned 300, actual 410, PF 0.73, projected 1640 vs 1200 (+440) → PF BELOW THRESHOLD (check the installed SF first)
03-350 Slab curing: 24 hrs charged, 0 installed → HOURS WITH NO QUANTITY (early start, or a miscode from 03-310)
03-410 Embeds and anchors: 650 installed vs 600 budget (108%) → INSTALLED OVER BUDGET QTY (added scope or a counting error; earned 325 exceeds budget hours, so decide whether to cap earned at budget)
03-999: 36 hrs, no budget line → NO BUDGET CODE (recode before the cost report)

Suggested Excel logic (write against your own sheet layout)
- Actual hours: SUMIF(Timecards[Code], [@Code], Timecards[Hours]).
- Percent complete: [@Installed] / [@BudgetQty].
- Earned hours: [@BudgetHrs] * [@PctComplete].
- Productivity factor: IF([@Actual]=0, "", [@Earned] / [@Actual]); flag when below your threshold.
- Projected hours at completion: IF([@PctComplete]=0, "", [@Actual] / [@PctComplete]); variance = projected − budget.
- Codes with no budget line: list UNIQUE(Timecards[Code]) and flag any with COUNTIF(Budget[Code], code)=0.

Same check as a short Python script (our own code; reads two CSVs you export)
import csv
from collections import defaultdict
PF_FLAG = 0.90   # type your own threshold; this is a placeholder
budget = {r["Code"]: r for r in csv.DictReader(open("budget.csv"))}
actual = defaultdict(float)
for r in csv.DictReader(open("timecards.csv")):
    actual[r["Code"]] += float(r["Hours"])
for code in sorted(set(budget) | set(actual)):
    if code not in budget:
        print(f"{code}: {actual[code]:.0f} hrs charged, NO BUDGET CODE"); continue
    b = budget[code]; bq, bh, iq = float(b["BudgetQty"]), float(b["BudgetHrs"]), float(b["InstalledQty"])
    a = actual.get(code, 0.0)
    if iq == 0:
        print(f"{code}: {a:.0f} hrs, 0 installed, HOURS WITH NO QUANTITY" if a else f"{code}: not started"); continue
    pct = iq / bq; earned = bh * pct
    pf = earned / a if a else float("nan")
    eac = a / pct
    flags = []
    if iq > bq: flags.append("INSTALLED OVER BUDGET QTY")
    if a and pf < PF_FLAG: flags.append("PF BELOW THRESHOLD")
    print(f"{code}: {pct:.0%} complete, earned {earned:.0f}, actual {a:.0f}, PF {pf:.2f}, "
          f"projected {eac:.0f} vs budget {bh:.0f} ({eac-bh:+.0f}) {' '.join(flags)}")
Do not judge a crew or decide a change order — only flag for human review.