Start with something familiar
A household budget can look wrong because a card purchase was imported twice, not because spending suddenly grew. A blank amount also differs from a verified zero. Before predicting next month, you would compare receipts, remove verified duplicates, and record any estimates. Demand tables need the same care. A better model cannot repair a definition that changes from row to row, and a polished chart can make an unnoticed data defect more persuasive.
Words to know: Data grain, Censoring, Sales.
Choose a row key
Use one row per SKU, location, and week. Raw orders may be finer, so retain event identifiers while aggregating. Two exports of the same event are duplicates; two different orders with the same date and quantity may both be valid. Remove records based on verified identity, not just similar appearance.
Keep the raw file and a transformation log with original value, replacement, reason, and evidence. Moving an event between weeks changes weekly totals but not the grand total. Removing a duplicate changes both. Reconcile these effects so a colleague can trace the model back to source records.
Separate zero and missing
A confirmed zero means the period was observed and no target demand occurred. A blank means the measure is absent. Closure can produce zero sales without revealing normal open-store demand. Stockout sales of eight establish eight sold, not total demand of eight. Do not replace every ambiguity with zero.
Keep observed values, quality flags, and modeled values in separate fields. Neighboring clean weeks of twelve and fourteen suggest thirteen as a retrospective interpolation scenario, not a measured fact. That interpolation would also be unavailable to a forecast issued before the later week. Preserve alternative estimates and their timing.
Make checks operational
Check unique keys, continuous weeks, known product codes, units per pack, and impossible signs. Twelve bottles and twelve cases are not interchangeable. Returns need their own event type. Reconcile totals by product and period before and after transformations; a correct overall total can conceal quantities assigned to the wrong week.
Decide which checks block publication and which warn. A duplicate key may block release, while one documented estimate may allow a qualified forecast. Give unresolved items owners and deadlines. Recurrent defects should lead to changes in capture or export processes, not a permanent dependence on private spreadsheet fixes.
- Define target
- Check evidence
- Calculate
- Challenge assumptions
- Record decision
Build an auditable demand table — worked case
All inputs below describe fictional Northstar bottles. Keep the stated units and timing when reproducing the calculation.
| Week | ID | Units | Status |
|---|---|---|---|
| 1 | A | 10 | valid |
| 1 | A | 10 | duplicate |
| 2 | B | 12 | valid |
| 3 | C | None | missing |
| 4 | D | 14 | valid |
- 1. Remove repeated A only.
- 2. Known unique total = 10 + 12 + 14 = 36.
- 3. Interpolation scenario gives week three (12 + 14)/2 = 13 and total 49.
Known observed total 36; scenario total 49 is not a verified actual.
Assumptions
- Periods and quantities use the definitions stated in this example.
- Only explicitly supplied events or adjustments are included.
Where this can fail: The arithmetic checks the stated assumptions; it does not establish that uncertain future events will occur.
Build, test and explain a plan
Weeks 1–8: A observations [10,12,missing,14,0,8,13,15], with a stockout in week six and verified zero in week five. B observations [5,6,7,8,6,7,8,9] are clean. A duplicate of A week two adds twelve in the raw export. All units are bottles unless stated otherwise. Use a spreadsheet or ruled paper. Eight hours includes construction, comparison, writing, and teach-back; this is a planning estimate, not an experimentally validated completion time.
Use paper or a spreadsheet. The estimates below include the study and worked example already on this page.
| Estimated hours | Activity |
|---|---|
| 0.5 | Study the lesson and write a data dictionary defining every field, time bucket, unit, and cutoff. Separate observed facts from assumptions. Identify two ways a colleague could misread the target and rewrite its definition to prevent those errors. |
| 0.5 | Reproduce the worked example independently, showing formulas and intermediate values. Compare against the answer and keep a correction log explaining each difference. Check one result a second way using a total, balance, or direct manual calculation. |
| 3 | Create raw, unique, quality-flag, and modeled tables. Main modeled A replaces week three with 13 and week six with 12 as explicit assumptions. Preserve an observed-only series with the blank. Keep inputs separate from formulas, label every origin and target, and add independent row and total checks. Produce an annotated chart with at least two observations tied to specific rows; include a brief explanation of why each check matters. |
| 2 | Test week-three estimates ten and sixteen crossed with week-six estimates eight and eighteen. Keep the real zero. Prepare a historical version for a forecast at the end of week four and explain what later information must be excluded. Preserve each variant separately. Create an output comparison table and explain which changed assumption caused each movement. Identify a scenario that affects timing differently from totals and state what the planner should do with that distinction. |
| 1 | Write a 400-word decision memo using your own numerical outputs. State the proposed action, evidence, two limitations, an owner for the next check, and a measurable condition that would change your recommendation. Attach the model so a reader can audit every cited value. |
| 1 | Prepare a five-minute teach-back using one worked row and one scenario comparison. Explain it aloud to a colleague or recording. Write three skeptical questions, answer them with evidence, and revise the memo or model where your explanation exposes an unsupported assumption. |
Your deliverables
- Auditable input and formula model with checks
- Scenario comparison and annotated chart
- Decision memo, correction log, and teach-back notes
Check your practice work
- Unique known A sum 72; B sum 56. The duplicate adds twelve only to raw data.
- Main modeled A = 72 + 13 + (12 - 8) = 89; combined modeled total 145.
- Alternative A totals for (10,8),(10,18),(16,8),(16,18) are 82,92,88,98.
- Week-five zero stays zero; week-six demand remains uncertain.
- A week-four origin cannot use weeks five to eight or later quality resolutions.
Keep these three ideas
- Repair duplicates and missing observations while preserving real zeros and uncertain stockouts.
- Keep original evidence distinct from modeled values and assumptions.
- A precise output remains conditional on definitions and timing.
Common mistakes
- Overwriting the original evidence after a revision.
- Reporting a number without its unit, period, cutoff, and assumptions.
Optional video study
Repair duplicates and missing observations while preserving real zeros and uncertain stockouts.
- Choose a row key
- Separate zero and missing
- Make checks operational
One half of the module narration, with a distinct Northstar calculation and a bridge to the paired lesson.
Open the module media & quizPut this idea to use
Carry your assumptions into a practical tool, then compare your result with a short guide.
Open a related planning toolRead the related planning guidePractice with the planning gameThis saves a completion marker in this browser only. It does not verify time spent.