WIP Job & Variance Analysis Copy link

🚨 Blitz data = Excel worksheet UDFs, not MCP tools/connectors. Extract data only by writing =br_...() into a cell (via execute_office_js) and reading the spill — never tool_search for br_*, never refresh_mcp_connectors, and never conclude "no Oracle EBS / Blitz connector is set up" (the Blitz Report add-in is an Excel add-in, not an MCP connector, so it never appears in the connector list — its absence there is expected). Load the extraction mechanism first: read_skill("br-run-reports").

Performance: Read br-analytics-standards at the start of every run (read_skill("br-analytics-standards")). All pipeline steps must follow those rules. Target runtime: under 7 minutes.

Produce a WIP health analysis in one workbook: which jobs are stuck, where the value sits, what blocks completion, and which jobs are ready to close. You own scoring and the register; br-run-reports does extraction and br-data-dashboard the build. Run steps in order, keeping intermediate sheets. Unclosed complete jobs are also a period-close drag — hand that verdict to br-inv-cost-close-readiness when this run feeds a close assessment.

Requires: br-run-reports, br-data-dashboard.

Division of responsibility — READ FIRST

You do NOT run Blitz reports yourself. Extraction is by br-run-reports; never call br_* functions or touch a result sheet mid-run. Read-and-recommend only — this skill never closes a job, changes a status, or transacts material; it ranks and recommends.

When to use

"Which WIP jobs are stuck", "WIP value by job", "jobs past their completion date", "what's blocking the shop floor", "which jobs can we close", "WIP aging". For creating/updating jobs, the WIP Job Upload exists — different skill territory, do not act here.

Inputs

• Organization Code (manufacturing org) — required. (Optional: schedule group, assembly, status filter.)*

Reports used (authoritative names — do NOT broad-search for them)

Pass the documented name directly; search only on #BLITZ_REPORT_NOT_FOUND.

CONFIG

Job classes

Defaults (edit to the customer): aging threshold 14 days past scheduled completion; unreleased threshold 30 days; value floor for ranking $1,000.

Output sheets & tab order (left to right)

WIP_Analysis (first), Job_Register (every job, classed and ranked), WIP_Jobs (the entities extract), WIP_Value, WIP_Shortages, [WIP_Accounts] (last).

Pipeline

1. Extract — DELEGATE. br-run-reports runs WIP Entities → WIP_Jobs; WIP Value (Summary for large orgs) → WIP_Value; WIP Discrete Job Shortage → WIP_Shortages. Wait for each.
2. Anchor. Record open-job count by status, total WIP $, past-due count, shortage-line count, close-candidate count. All healthy → short verdict, skip the heavy register.
3. Classify (single execute_office_js call). Join value and shortages onto the job register by job name in memory; apply CONFIG classes; rank inside each class by WIP $ descending. Write Job_Register in one .values = call: job, assembly, status, class, dates, days overdue, WIP $, shortage count, recommendation.
4. Dashboard (single call; charts second) (delegate to br-data-dashboard if it fires; else inline). KPI cards (open jobs, WIP $, past due, close candidates), value-by-class and aging charts, top-10 stuck jobs table linking to the register. NEW sheet at position 0; don't rename report sheets; no multi-row copyToRange; clear strays; widen amount columns.
5. Narrative. ≤5 bullets: exposure headline, the biggest stuck job and its cause, the close-candidate payoff (jobs × $ that would leave WIP), first action.
6. Arrange tabs + final validation. Order per CONFIG; WIP_Analysis active. One permitted verification read: register class counts and WIP $ tie to the Step-2 anchor.

Add-on: cost-variance breakdown (OPT-IN — extra extract, offer once before Step 1)

Answers "WHERE is the variance coming from", a different question from job health — so it runs only when the request implies it ("cost variance", "why are variances so high", "variance by job") or the user accepts a single up-front offer. Never re-ask mid-pipeline.

• Extract WIP Account Distribution for the org + period alongside the standard pulls.
• Decompose per job: charges vs relief by cost element (material / resource / overhead / outside processing) — which element drives each job's net variance, and the org-level pattern (one assembly family, one element, one period spike).
• Driver patterns to call out: material-heavy variance on jobs that also sit on the shortage list (substitutions/over-issue), resource-heavy variance concentrated in one department (rate or routing suspect — hand assembly-level suspects to br-bom-validator for the bill-side check), variance parked on open Complete jobs (the close-candidate payoff quantified).
• Adds a Cost_Variance sheet after WIP_Value and a dashboard section; both degrade cleanly when the add-on didn't run. WIP Completion Report is pulled per-job only when a yield question needs it — never wide-open.
• Variance judgment is a REVIEW flag with the element and $ named — root cause belongs to costing; never assert "the standard is wrong".

Validation

Register ties to raw sheets; every Stuck/Blocked call is backed by a date or shortage the user can see; jobs missing from the value report show blank $, not zero (say which). When the add-on ran, Cost_Variance element totals tie to the account-distribution extract.

Closed-loop (roadmap, not executed here)

Close candidates route to the WIP close flow; job changes to the WIP Job Upload. This skill recommends; a human executes.