East Forecast Tracking

Files to upload

Upload all three every week. The tracker is the file being written; the other two are the sources it reads. Names carry a date or version number that changes weekly — match the current cycle.

Forecast Tracking [date] EOD (East).xlsx

Role: The tracker being updated — the deliverable, with the Medley and Main tabs.
Reads: Prior-week notes G–J, in-file material notes K–L (Release Month + Weekly File Comments) when present, Manf Lot, and BF Code.
Writes: Current-week C estimate, D category, E ship, F comment.

Production Planning (#).xlsx → Forecast tab

Role: Batching & filling schedule — sets the fill-finish date and the estimate month.
Reads: Batching Date AJ, Packaging Start AO, Packaging Finish AP, plus stage, BF Code, Release Month.
Gives: Whether a lot / order is on the batching or filling schedule, and when.

East Upcoming Releases (#).xlsx → Summary tab

Role: Release / material corroboration — confirms “Released” and enriches material notes.
Reads: Summary tab.
Gives: Release Month, Weekly File Comments, QA Approved. Primary material source when the tracker has no K–L.

Version check. Confirm the tracker's date matches the week you're building and that Production Planning and East Upcoming Releases are the current pulls — a mismatched tracker means the order list is stale even when the schedule is current.

How the data ties together

Every source is matched to a tracker row on Manf Lot first, then order number as a fallback.

Primary key — Manf Lot. The lot on the Forecast schedule and the East Summary is matched to the tracker's Manf Lot; order number is the fallback when a lot isn't found.
Resolve columns by header name, not position. Manf Lot, BF Code and the material columns move between weekly versions — and the Medley and Main tabs can even carry different layouts in the same file. Columns are always found by header text.
Normalize lot codes. Excel silently turns codes like 60E06 into scientific notation; both sides of every join are normalized so those still match.

What each column becomes

C, D and F are rebuilt fresh each week; E carries the prior week's commercial call.

Column	Field	How it's set
C	estimate	Month of the fill-finish date for scheduled rows (e.g. `September`); `<mon> ptnl` for material-held rows; otherwise the prior estimate is carried.
D	category	`Released` for scheduled / clear items; otherwise the material category — Customer Supplied Material – Missing (No ETA / ETA Within 4 Weeks), PML Material – Missing, Pending MBR, Line Trial, or Customer Feedback Needed – Non Material.
E	Ship	Carried forward from the prior week — a commercial judgment that isn't in the schedules.
F	comment	Built fresh from the decision order in §4 and the templates in §5.

Comment decision order

Each written row is resolved by the first rule it matches, top to bottom.

DONE stays DONE
A row previously marked DONE keeps it — even if its completed fill still shows on the historical schedule.
Resleeve / relabel / analyst note → preserved
Human notes (resleeve, relabel, pack-only, “check with…”) are kept. If the lot is now on the fill schedule, the fill-finish date is appended — the resleeve label is never overwritten, because a resleeve isn't a normal batch.
NST (Not Started) → material / status note only
A not-started order shows its current material blocker, never a fill-finish schedule comment — even if the Forecast lists a tentative date.
Active material blocker → material note
Customer-supplied items, ESDs, holds or missing PML material, when not yet released → the blocker is shown.
On the fill schedule → schedule comment
Rebuilt from the Forecast dates with the batched status (§6) and a delta vs. last week (§5). Estimate = fill month, category = Released.
Released but not yet scheduled
Materials clear, no fill date assigned → newly released - not on schedule, category Released.
Otherwise → carry prior week forward
Nothing new in the sources, so the prior comment stands; blank stays blank when there's no schedule, material or prior note.

Ship (E) carries forward in every case. Released status is confirmed against East Upcoming Releases. Anything already typed into C–F is kept, not overwritten.

Comment templates

Same wording as prior weeks. The delta note compares this week's fill-finish to last week's.

Main — order on schedule batched | not yet batched - fill finish M/D (delta)not yet batched - fill finish 9/15 (delayed v. last week finish 9/8)

Medley — order on schedule (Medley adds “on schedule”) batched | not yet batched - on schedule - fill finish M/D (delta)batched - on schedule - fill finish 8/31 (moved up v. last week finish 9/2)

Material row AUS56 (Cust. - ETA 9/11), RMO03 (Received 9/1)

Released, not scheduled newly released - not on schedule

Delta note	When
`(newly scheduled)`	Prior week had no fill-finish date.
`(delayed v. last week finish M/D)`	New fill date is later than last week's.
`(moved up v. last week finish M/D)`	New fill date is earlier than last week's.
— none —	Fill date unchanged from last week.

Batched status

Read from the row's Status and its BF Code — the value Batch CMP/Closed means the bulk batch is complete.

Condition	Reads as
Status = STD (Started)	batched already batched
Status = RLS + BF Code `Batch CMP/Closed`	batched raw material clear + batch complete
Status = RLS + a real BF code	not yet batched raw material clear, not made
Status = NST (Not Started)	not yet batched never batched — even if a BF code shows CMP

Tab & note conventions

East-specific behaviours that shape the wording.

NST rows are material-first. The key rule: a not-started order shows its blocker, not a speculative fill date. This is what keeps material-held orders from looking “scheduled.”
Medley vs Main phrasing. Medley schedule comments include - on schedule; Main omits it.
Resleeve keeps its label. A resleeve / relabel row that reaches the schedule reads resleeve - fill finish M/D, not batched ….
DONE is terminal. Once marked DONE it carries forward untouched.
A “- kit” suffix is preserved when rebuilding a schedule comment.
Prefilled cells win. Any value already typed into C–F is left exactly as entered.

Data notes & gotchas

Things that change week to week or trip up a naive read.

Column positions move. Manf Lot, BF Code and the material columns land in different letters across versions — and the two tabs can differ — so everything is resolved by header name.
Don't force a recalc. These workbooks use external XLOOKUP links that an offline recalc can't evaluate and would blank out. The file is saved as-is — only C–F change; Excel recomputes Totals and pivots on open.
Scientific-notation lots. Alphanumeric lot codes matching a digit-letter-digit pattern get converted by Excel; both sides of every join are normalized.
Historical schedule dates. A completed fill still shows on the schedule with a past date — that's why DONE takes priority over a schedule match.
Ship is judgment. Column E is never inferred from the schedules; it carries forward for you to adjust.
Deltas need a prior note. They compare against the tracker's own prior comment (column J); when that's blank a scheduled row reads (newly scheduled).

Systematic pass, not every judgment. This reproduces the rules above — it won't catch every manual call (e.g. a Forecast “Hold” stage that the East file still lists as “Released”). Give the flagged and edge rows a quick scan.