Author: AILesson9 min setupTested with:ChatGPTReviewed: 2026-08-28
Quick answer
Track usable stock, demand, lead time, open supply, reorder logic, and exception ownership without inventing purchasing decisions. Provide: Inventory operation and decisions, Fields, sources, and sample values, Reorder and exception policy. Expected result: A controlled inventory workbook with reorder formulas, exception queue, and reconciliation checks.
1
Add your context
Your text stays in this browser. AILesson Prompts does not send it to a model or server.
2
Your prompt
Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt
Design an Excel inventory and reorder alert workbook using the supplied operation, data, and approved policy.
Items, locations, review, demand, service, authority, supplier constraints, and alert action:
[operation]
Grain, stock, reservations, unusable units, open supply, dates, demand, lead time, ordering constraints, cost, refresh, and quality:
[data]
Availability, demand horizon, safety stock, reorder/target logic, rounding, stale/late handling, overrides, approval, and exclusions:
[policy]
Define one row per SKU-location and separate source facts, calculated fields, policy parameters, recommended action, and approved order. Reconcile on-hand components and distinguish available, inventory position, projected stock at lead time, reorder point, and order quantity. Include only open supply expected within the approved horizon, preserving late or uncertain deliveries as exceptions. Apply pack size and MOQ after calculating need, and do not recommend an order when required demand, lead time, unit, supplier, or freshness inputs are missing. Do not treat an alert as purchase authorization. Return: sheet architecture; data dictionary; parameter table; Excel Table formulas; alert precedence; conditional-format rules; exception and approval workflow; sample rows; inventory/PO reconciliation; refresh controls; and tests for no need, reorder, MOQ/pack rounding, excess, stockout, missing/stale data, late PO, zero demand, and override.
Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.
From input to outcome
A worked example
See how concrete context turns this recipe into a usable result
Actual input
Inventory operation and decisions
Small repair-parts storeroom, one central location, 180 SKUs. Buyer reviews every Monday. Goal is avoid stockout during supplier lead time, not optimize service statistically. Workbook raises Review/Reorder/Stockout/Data issue alerts; buyer approves and creates PO outside Excel. Suppliers have pack sizes and MOQ. No automatic orders.
Fields, sources, and sample values
One row per SKU at Main. Fields SKU, Description, Unit, OnHand, Reserved, Damaged, OpenPOQty, EarliestConfirmedDue, AvgWeeklyDemand, LeadTimeWeeks, SafetyStock, PackSize, MOQ, Supplier, LastStockRefresh, LastDemandRefresh, OverrideTarget, OverrideReason. Example Filter-F9: each, OnHand 42, Reserved 8, Damaged 2, OpenPO 24 due 8 Sep 2026, average weekly demand 15, lead 2 weeks, safety 12, pack 6, MOQ 24, supplier North Parts, refresh 31 Aug 2026. Review date 1 Sep 2026. No duplicate SKU. Dates and quantities can be blank.
Reorder and exception policy
Available=OnHand-Reserved-Damaged. InventoryPosition=Available+open PO arriving on or before review date + lead time; late/unconfirmed PO excluded and flagged. LeadDemand=AvgWeeklyDemand×LeadTimeWeeks. ReorderPoint=LeadDemand+SafetyStock. Target=LeadDemand+SafetyStock+one review week's demand unless approved OverrideTarget exists with reason. If InventoryPosition<ReorderPoint, raw need=Target-InventoryPosition; proposal rounds up to PackSize and at least MOQ. If Available<0, Stockout/record issue outranks reorder. Required data older than 7 days is Data issue. Zero demand with stock is No reorder, not divide error.
Example output
Sheets: InventoryInput, OpenPO detail, Parameters, ReorderReview, Exceptions, ApprovalLog, and Reconciliation. Keep calculated proposal separate from BuyerDecision, ApprovedQty, PONumber, and ApprovedAt.
For Filter-F9, Available=42−8−2=32. Confirmed PO due 8 September is within the two-week horizon ending 15 September, so InventoryPosition=32+24=56. LeadDemand=15×2=30; ReorderPoint=30+12=42; default Target=30+12+15=57. Since 56 is not below 42, status is No reorder, even though it is one unit below target; the policy triggers at reorder point, not target.
Table formulas: Available =[@OnHand]-[@Reserved]-[@Damaged]; EligibleOpenPO must be summed from PO detail with status and due-date criteria rather than trusting one aggregate when multiple POs exist; InventoryPosition=Available+EligibleOpenPO; LeadDemand=AvgWeeklyDemand*LeadTimeWeeks; ReorderPoint=LeadDemand+SafetyStock. RawNeed when triggered is MAX(0,Target-InventoryPosition). Rounded proposal can be =MAX([@MOQ],CEILING.MATH([@RawNeed],[@PackSize])), but only after validating positive PackSize/MOQ.
Alert precedence: Duplicate/required field/type/stale data → Data issue; Available<0 → Stockout or record issue; late/unconfirmed PO affecting horizon → Review supply; position below reorder point → Reorder review; otherwise No reorder. Conditional formatting follows status text, not nested color-only logic. Tests include a position of 35 producing raw need 22 and proposal 24; raw need 25 rounds to 30; demand 0 returns no reorder under policy; PO one day after horizon is excluded and flagged; missing supplier or 8-day-old refresh blocks proposal; override is used only with target, reason, reviewer, and date. Reconcile total OnHand to source count/sum and OpenPO eligible/excluded quantities to the PO register each refresh.
Why this works
1
Separating inventory position from physically available stock prevents open orders from hiding today's shortage.
2
An approval field keeps a calculated recommendation from becoming an unauthorized purchase instruction.
Check the result
Are stock, reservations, unusable units, due supply, lead-time demand, safety stock, and units defined consistently?
Do missing, stale, late, duplicate, and uncertain inputs stop or downgrade the reorder recommendation?
Are calculated need, rounded proposal, reviewer decision, and actual order distinct fields?
Use it with confidence
Frequently asked questions
Practical answers about when to use this recipe, what to provide, and where human review still matters
What should I prepare before using “Design an inventory and reorder alert workbook”?
For “Design an inventory and reorder alert workbook,” prepare Inventory operation and decisions, Fields, sources, and sample values, and Reorder and exception policy. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.
When is the “Design an inventory and reorder alert workbook” result not ready to use?
The result is not ready if it does not yet deliver the stated outcome—A controlled inventory workbook with reorder formulas, exception queue, and reconciliation checks—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.
Which AI tools have recorded tests for “Design an inventory and reorder alert workbook”?
The published test record for “Design an inventory and reorder alert workbook” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.
ChatGPT