Module 03 · Deliverable Pack

Excel Tracker Starter Pack

A working production tracker you can open in Excel today — live formulas, conditional formatting, a dashboard that rolls up, and the Claude prompts that built it. Clone it, strip the sample data, and drop in your own operation.

Download the file

The tracker is a real .xlsx. Open in Excel, Numbers, or Google Sheets. Formulas and conditional formatting are preserved on import.

Weekly Production Tracker — Starter Template

5 tabs · 30 sample data rows · live formulas & conditional formatting · ~15 KB

production-tracker-starter.xlsx
Download

What's in the pack

Five tabs, each doing one job. Open them in order the first time and you'll see how the whole workbook ties together.

Tab 01

Tracker

The entry sheet. One row per line × shift × day. Planned, actual, variance, downtime with reason, OEE, status. All formulas live.

Tab 02

Dashboard

Weekly roll-up by line and by shift, plus six KPI tiles — plant totals, plant variance %, downtime hours, avg OEE, rows below target.

Tab 03

Variance

Auto-pulls any row flagged Below on the Tracker tab. The sheet your plant manager reads Monday morning.

Tab 04

Reason Codes

Ten standard downtime reason codes, wired as the dropdown source on the Tracker tab. Edit the list, the dropdown updates.

Tab 05

Instructions

Plain-English directions for customizing to your operation — rename lines, update planned units, swap reason codes.

Smart features already wired in

You don't have to know how these work to use them. But opening the file, inspecting the rules, and asking Claude to explain what it sees is a five-minute learning moment.

On Target
Status column colored by variance. Green when actual ≥ planned, amber when between −5% and 0, red when worse than −5%. Conditional formatting, no manual coloring.
Watch
Variance % and downtime thresholds. Variance % cells flip red below −5%; downtime cells flip amber above 2 hours. You scan the sheet, not each cell.
Below
Auto-populated variance report. The Variance tab runs a row-by-row IF against the Tracker tab — any Below row copies across with date, line, shift, supervisor, and reason. Sort on it to build a weekly review list.
Dropdown
Downtime Reason dropdown. Data validation sourced from the Reason Codes tab. Edit the list there, the dropdown updates. No free-text drift across weeks.
Frozen
Frozen header row. You can scroll to row 200 and still see the column names. A small thing that makes the difference between a spreadsheet people use and one they abandon.

The formulas inside

If you want to rebuild the tracker by hand — or you're adapting the formulas to a different shape — here's the full reference. Row references use row 5 as the example.

Column Formula What it's doing
Variance (#) =G5-F5 Actual − Planned. Unit-level gap for the row.
Variance (%) =IFERROR((G5-F5)/F5,0) Gap as a percent of plan. IFERROR handles a zero-planned row without breaking.
OEE (units-based) =IFERROR((G5/F5)*((8-J5)/8),0) Performance × Availability. Assumes an 8-hour shift; edit the 8 to match your shift length.
Status =IF(I5>=0,"On Target",IF(I5>=-0.05,"Watch","Below")) Nested IF on variance %. Three states feed the conditional formatting.
Dashboard · Plant variance % =IFERROR((SUM(Actual)-SUM(Planned))/SUM(Planned),0) Plant-total percent, computed from the Tracker's totals — never from averaged row percentages.
Dashboard · By line (Actual) =SUMIF(Tracker!C5:C34,"Line A",Tracker!G5:G34) Conditional sum. Repeats for each line and for each shift on the Dashboard tab.
Dashboard · Rows below target =COUNTIF(Tracker!M5:M34,"Below") How many shift-rows this week came in below the −5% threshold.
Variance tab · row mirror =IF(Tracker!M5="Below",Tracker!B5,"") Per-column IF on the status of the source row. Works in every Excel version — no FILTER() required.

The prompts that built it

This is what you paste into Claude when you want a tracker built for your operation — not this one. Three prompts. Run them in order.

Prompt 01 — Structure

Structure prompt · run first 
ROLE: You are a senior operations analyst who builds production trackers in Excel. You design structure before you write formulas.

CONTEXT: [Describe your operation in one paragraph — what you make, how many lines or work centers, shifts, who fills in the tracker, who reviews it, how often.]

TASK: Propose the column structure for a weekly production tracker. Tab layout, column names, and what each column represents. Do not write formulas yet — just the structure.

STANDARD: Columns should map to fields that an operator can actually fill in or that can be computed from filled fields. No vanity columns. If something can be derived, mark it as a formula column. Propose at most two tabs for now — the entry tab and a dashboard.

Prompt 02 — Formulas

Formula prompt · after you approve the structure 
ROLE: Same — senior operations analyst building in Excel.

CONTEXT: The structure you proposed is approved. [Paste the approved column layout here so Claude has it in-scope.]

TASK: Write the Excel formulas for every computed column. For each formula, give me:
  1. The column name
  2. The formula, using row 5 as the first data row
  3. One sentence explaining what the formula is doing

Cover at minimum: variance (# and %), OEE (performance × availability), status (traffic-light IF), and any summary row or total.

STANDARD: Formulas must be paste-ready — no pseudocode, no Python, no "add a helper column" unless you explicitly note it. Use IFERROR to guard divisions. Every formula must work in Excel 2019+ and Google Sheets. Flag any formula that requires Excel 365-only functions.

Prompt 03 — Smart features

Smart features prompt · after the formulas run clean 
ROLE: Same — senior operations analyst.

CONTEXT: The tracker has structure and formulas. Now I want the small features that make it usable for a real team.

TASK: Give me the step-by-step setup for three features:
  1. Conditional formatting for the Status column — green for "On Target", amber for "Watch", red for "Below". Colors applied to the cell, bold text.
  2. A data validation dropdown on the Downtime Reason column, sourced from a list on a separate tab named "Reason Codes".
  3. Frozen header row plus column widths tuned so everything fits on one screen at 1080p without horizontal scrolling.

STANDARD: Give me clicks-and-menus directions for Excel. If a step differs in Google Sheets, flag it. No macros. No VBA. Anything I can't set up in five minutes from the ribbon isn't in scope.
The discipline from Module 02

Notice what every prompt does: role, context, task, standard. That's the Module 02 structure. Every deliverable in this course is built with the same skeleton — only the specifics change. Save these three prompts to your prompt library; adapt them per client.

What to do next

The fastest way to turn this starter into your own tracker:

  1. Open the file. Look at the Tracker tab. Note the formulas, the conditional formatting, the dropdown on Reason.
  2. Upload it to Claude with a description of your operation. "Here's a starter tracker. I run [X lines] with [Y shifts], tracking [these metrics]. Rebuild it for me."
  3. Paste the new formulas into a fresh copy. Verify one row by hand.
  4. Enter two weeks of real data. Not sample data — your actual last two weeks. The tracker will expose something you hadn't seen.
  5. Save your final prompts. They become reusable across the rest of your client work.

That's the whole Module 03 deliverable. Ten to twenty minutes of real work gets you a tracker your team will still be using in a year.