Count each demand once

Define the demand population

Northstar sells reusable bottles. A forecast is a statement about a population and period, not automatically an extra order. Label whether the forecast includes confirmed orders. If next week’s total forecast is 51 and confirmed orders of 20 are already within it, total next-week demand stays 51. A useful reconciliation is 20 confirmed plus 31 unconfirmed, with customer and date references where available.

Backlog is demand whose original due date has passed. In our case six overdue units are explicitly excluded from the 51. Add them once to obtain 57 units of demand to cover. When those six ship, close the backlog record. Do not also keep them as an open order in the current-week total. A clear definition prevents a neat spreadsheet from making an expensive false shortage.

Net only eligible supply

Usable opening stock is 18 bottles. Another ten arrive and pass inspection before the demand event. With no safety-stock target, additional supply is max of zero and 57 minus 28, which is 29. This is a requirement, not evidence that a supplier can deliver 29. Keep required quantity and confirmed feasible quantity in separate columns so a recommendation does not become an accidental promise.

Stock status matters as much as quantity. Damaged, quarantined, reserved, or location-inaccessible units may not be eligible. Every subtraction needs a reason that it can serve this demand. Use the same unit throughout; cases and bottles cannot be added without a conversion. If a purchase order changes from planned to confirmed, update its status instead of adding a second receipt record.

Make reconciliation visible

A planner should be able to trace each total back to unique records. Create columns for source identifier, original due date, required date, quantity, inclusion rule, and supply status. A simple control checks that confirmed demand plus residual forecast equals the intended total. Another checks that opening stock plus usable receipts minus fulfilled demand equals closing stock.

A negative projected balance is a shortage signal, not physical negative stock. You may keep signed balances in a planning view, but an execution view should show zero physical stock and a separate backlog. Choose one representation for calculations and explain the translation. Carrying a negative balance and adding the same backlog again in the next bucket counts the shortage twice.

Build, test and explain a plan

Use the example for week 1. Weeks 2–4 have new demand 40, 46, 38; scheduled receipts 12, 0, 20. Begin with 18 stock and six backlog. All receipts precede weekly demand, new planned receipts are unconstrained for this arithmetic exercise, and target closing stock is zero. Preserve a separate order identifier for each row. The eight-hour allocation is a self-paced estimate, not experimentally validated; it includes model building, scenario testing, a memo, and teach-back.

Use paper or a spreadsheet. The estimates below include the study and worked example already on this page.

Your deliverables

• An auditable multi-period workbook with baseline and named stress cases.
• A decision memo with numerical evidence and explicit limitations.
• An original explanatory diagram and a correction log.

Keep these three ideas

• Reconcile forecast, orders, backlog, and usable supply before proposing a purchase.
• Keep the unit, timing, and denominator visible.
• Treat model outputs as conditional on their stated assumptions.

Common mistakes

• Changing an input definition without changing all affected formulas.
• Presenting an expected or proposed quantity as confirmed usable supply.