Turn a verified stock balance into a replenishment decision. Copy a complete worksheet that separates the reorder signal, proposed quantity and approval to buy.
A shelf can look comfortably stocked while its next delivery is already too late. It can also look nearly empty while a confirmed order is about to arrive. A useful reorder point template must distinguish those situations instead of turning every low balance into another purchase.
This resource is for a small internal store supplying ordinary, noncritical consumables: stationery, packaging or general-purpose supplies with reasonably steady usage. Everything is inline. Copy the header and formulas into your spreadsheet, and the linked decision record into a document. No download or Workhint subscription is needed.
Set the boundary before calculating
Use one row per item and store, in one consistent stock unit. This is a simple planning worksheet, not an inventory ledger, purchasing authorization, demand forecast or guarantee against shortages. Do not use it unchanged for medicines, hazardous materials, safety-critical parts, expiring stock, seasonal demand or infrequent large requirements.
The underlying reorder-point relationship is average demand during replenishment lead time plus safety stock, as shown in Oracle’s inventory calculation documentation. The daily-review cushion, target quantity and release checks below are a proposed operating design, not a description of Oracle or Workhint inventory behavior.
For this pilot, review the sheet every calendar day. Use calendar-day demand and lead times throughout. If you cannot maintain that cadence, change the review interval and test the policy again. A continuous-review reorder point alone does not allow for time spent waiting for the next manual review.
Copy the worksheet and its input rules
Paste this header into row 1 and split it on commas. Columns A–K are inputs; L–Q calculate; R records the human decision. Keep the evidence record below linked by item, store and review ID. Do not combine different locations just because they use the same item code.
Item and store,Stock unit,Daily usage,Lead days,Review days,Buffer units,Usable on hand,Firm inbound,Overdue unmet demand,Pack units,Extra cover days,Base reorder point,Review trigger,Stock position,Target position,Signal,Proposed units,Decision state| Input | Meaning and validation | Evidence owner |
|---|---|---|
| A–B: identity and unit | Unique item/store pair and a defined unit, such as individual envelopes. Convert supplier packs to that unit using a verified pack size. | Stores lead verifies item master and bin. |
| C: daily usage | Units issued for routine use divided by all calendar days in the selected period. Require a positive value and document the dates. Exclude transfers and count corrections; investigate returns separately. | Planner links issue history and records exclusions. |
| D: lead days | Positive elapsed calendar days from approved order release to usable stock, including receipt and putaway. Use documented supplier experience; add internal pre-release approval delay if it is not already included. | Buyer links the chosen basis and approval-delay allowance. |
| E: review days | Positive maximum interval between reviews. Use 1 only when someone checks every calendar day, including weekends. | Stores lead assigns a named backup. |
| F: buffer units | Nonnegative whole units explicitly approved by the stock owner. Record the rationale and review date; this sheet does not calculate a statistical service level. | Stock owner approves the local buffer. |
| G: usable on hand | Nonnegative whole units physically available for this store’s use. Exclude damaged, quarantined or otherwise unavailable stock. Resolve stale or disputed balances first. | Stores lead links the balance timestamp. |
| H: firm inbound | Nonnegative whole units still outstanding on approved orders for this item/store. Use confirmed quantities and usable-arrival dates, not an unapproved request or the original quantity before partial receipt. | Buyer reconciles each open order line. |
| I: overdue unmet demand | Nonnegative whole units already owed but not issued. Subtract once. Do not put future routine consumption here: it is represented by C. | Stores lead links the backlog. |
| J: pack units | Positive whole number of stock units in the supplier’s order multiple. Use 1 for individual units. A separate minimum order quantity needs manual review. | Buyer verifies current ordering terms. |
| K: extra cover days | Positive number of additional calendar days of demand to include in the target above the review trigger. This is a local stockholding choice, not an economic order quantity. | Stock owner approves space and budget implications. |
Blank is unknown, not zero. Enter an explicit 0 for verified zero buffer, zero inbound or zero backlog. Before running a row, check that all numeric inputs exist, satisfy the rules and share the same units. Hold the row if demand history includes a stockout that suppressed issues: observed issues may understate actual need.
Add the formulas only after validating inputs
For row 2, enter the following formulas in the named cells and fill down for other items. These formulas assume validated numeric inputs; they do not enforce the input rules or verify source records. Spreadsheet locales may require semicolons instead of commas.
L2 = ROUNDUP(C2*D2+F2,0)
M2 = ROUNDUP(C2*(D2+E2)+F2,0)
N2 = G2+H2-I2
O2 = ROUNDUP(C2*(D2+E2+K2)+F2,0)
P2 = IF(N2<=M2,"REVIEW ORDER","MONITOR")
Q2 = IF(P2="REVIEW ORDER",ROUNDUP(MAX(0,O2-N2)/J2,0)*J2,0)- L: the base reorder point covers lead-time demand and the chosen buffer.
- M: the manual-review trigger adds demand for one review interval. Review at equality as well as below the threshold; this is the worksheet’s conservative local rule.
- N: stock position includes firm inbound and subtracts existing unmet demand. It may be negative even though its component quantities are nonnegative.
- O: target position adds K days of demand above the review horizon.
- Q: proposed purchase quantity raises the position to at least O, rounded up to a full pack. Rounding can exceed the target; check capacity, shelf life and budget before release.
Do not treat a MONITOR result as proof that stock will arrive in time. N aggregates inbound quantities without checking their arrival dates. Every row still needs the timing check below.
Keep a linked review and release record
Copy this record for each review that needs a decision. For unchanged MONITOR rows, retain the review ID, source timestamp, timing result, reviewer and next review due. Preserve previous snapshots rather than overwriting the only evidence of a purchase decision.
Review ID / item / store / unit:
Reviewed at / time zone / reviewer / backup:
Usage period / issue-history reference / exclusions:
Lead-time basis / approval-delay allowance / evidence:
Buffer and extra-cover policy / approver / review date:
Balance timestamp / usable exclusions / backlog references:
Inbound lines: PO + line | outstanding units | usable arrival | confirmation
Calculated L / M / N / O / P / Q:
Timing check: dated demand and receipt events / lowest projected balance
Open reorder request checked / reference / remaining quantity:
Decision state / reason / exception owner / deadline:
Approved quantity / supplier / approval reference:
Released PO + line / release time / acknowledged arrival:
Next review due / follow-up owner / closure evidence:Check timing before approving another order
- Start with usable stock G minus overdue demand I at the review timestamp.
- List each confirmed usable receipt in time order. Deduct C multiplied by elapsed calendar days between events, then add that receipt. Use a conservative arrival time when only a date is known.
- Continue through the proposed new order’s earliest credible usable arrival, including approval delay. If no order is proposed, check through the next review plus replenishment lead time.
- If any pre-receipt balance goes below zero, mark Timing exception. Notify the stores lead and buyer before affected work is promised. Agree an expedited delivery, permitted substitute, transfer or demand adjustment; record its owner and confirmation.
This is a coarse constant-demand check, not a reservation engine. A known large job or dated requirement that invalidates average demand needs a separate time-phased plan. Do not solve a shortage on paper by counting an overdue PO as if it were on the shelf.
Worked example: a signal is not permission to buy
Fictional example: Central Stores reviews individual mailing envelopes every calendar day. The last 30 calendar days show 300 routine issues, with no stockout or unusual demand: C = 10 units/day. Effective lead time D = 4 days includes the expected approval delay, E = 1 day, the owner-selected buffer F = 20 units and extra cover K = 7 days.
Usable stock G is 45, confirmed inbound H is 30, overdue unmet demand I is 5 and the supplier pack J contains 25 envelopes. These assumptions are illustrative choices, not recommended universal stock levels.
| Output | Calculation | Result |
|---|---|---|
| Base reorder point L | 10 × 4 + 20 | 60 units |
| Review trigger M | 10 × (4 + 1) + 20 | 70 units |
| Stock position N | 45 + 30 − 5 | 70 units |
| Target O | 10 × (4 + 1 + 7) + 20 | 140 units |
| Signal P | 70 ≤ 70 | REVIEW ORDER |
| Proposed Q | Round (140 − 70) up to a multiple of 25 | 75 units |
If the existing 30-unit receipt becomes usable after two days, the projected balance just before it arrives is 45 − 5 − 20 = 20. After receipt it is 50; another two days of demand leaves 30 before the proposed new order’s day-four arrival. The simplified timing check passes, subject to confirming those dates and quantities.
If that same existing receipt slips to day six, the sheet still shows N = 70. But the projected day-four balance before the new order would be 45 − 5 − 40 = 0, leaving no cushion; record the risk for buyer review. If the new order also cannot arrive until day five, the pre-receipt balance becomes −10: the timing gate fails. A routine 75-unit recommendation alone does not resolve the immediate shortage.
Use explicit decision states and exception ownership
| State | Required evidence and next owner |
|---|---|
| Input hold | Planner records missing, disputed or invalid inputs and assigns a correction owner and deadline. No purchase release from this row. |
| Monitor | Validated inputs, no order signal and timing check completed. Stores lead assigns the next daily review. |
| Timing exception | Potential shortage before a usable receipt. Buyer and stores lead agree an evidenced recovery; unresolved timing blocks routine release. |
| Awaiting approval | Signal, timing, pack size, open requests, supplier terms, capacity and budget checked. Named approver decides the exact quantity. |
| Released | Buyer records the actual PO line and approved quantity once, plus acknowledged arrival. Add it to H only when it meets the firm-inbound rule. |
| Closed or cancelled | Receipt reconciled to the outstanding order, or cancellation confirmed with reason. Stores lead updates the authoritative balance and preserves the linked review. |
A pending reorder request is not firm inbound, but it must still be checked before a second request is created. Link the existing request and update its review evidence; do not issue another purchase just because H is still zero. After a partial receipt, reduce outstanding inbound and update usable stock at the same cutoff so the units are not counted twice.
When the physical balance is disputed, use the cycle count sheet for internal stores to resolve it before trusting the planning result. Keep that stock-accuracy task separate from approving a replenishment purchase.
Where Workhint fits after the worksheet is stable
The sheet calculates a proposed quantity; a workflow makes the review responsibility explicit. Workhint’s Workflow Engine describes routing requests and approvals between defined roles, with required information before work progresses. Its workflow automation software solution describes structured forms, conditional routing and approval stages.
A practical configuration discussion could start with the review ID, validated inputs, timing evidence, buyer, approver and release reference. Confirm the exact fields and rules in your own implementation. These pages do not establish that Workhint forecasts stock, runs these spreadsheet formulas, synchronizes your inventory ledger or automatically places supplier orders.
For the broader distinction between recommendation and authorized action, see our AI inventory replenishment workflow guide. This resource remains usable manually; AI is not required.
Test the resource before using it for purchases
- Reproduce the example: L = 60, M = 70, N = 70, O = 140 and Q = 75.
- Change G to 46: N becomes 71, P becomes MONITOR and Q becomes 0. Still run the timing check.
- Set H to a verified 0 in the original example: N becomes 40 and Q becomes 100, subject to checking existing pending requests.
- Leave any input blank, set pack units to 0 or mix boxes with individual units: the reviewer must stop the row, even if a spreadsheet displays a number.
- Delay the receipts and confirm that a timing failure overrides a routine release decision.
Protect formula cells against accidental edits if your spreadsheet supports it. Have a second person reproduce the example and inspect one real item’s source records before the pilot. Revisit demand, lead time and buffer policy after a supplier change, unusual job, shortage or sustained usage shift; keep a dated approval trail.
Frequently asked questions
Is the reorder point the quantity to purchase?
No. It is a trigger level. The proposed order here is the difference between target position and stock position, rounded up to a pack, and only when the review signal is active. Approval may still be blocked.
Can I set the buffer to zero?
The sheet accepts an explicitly verified zero, but that is a stock-owner decision, not proof that uncertainty has disappeared. Record the rationale and examine the consequence of slower delivery or higher demand.
What if usage is zero or highly irregular?
Stop this routine-demand method. Review dated requirements and item criticality with the owner rather than dividing by an arbitrary period and treating a misleading average as a replenishment policy.
Does copying the sheet update inventory or create an order?
No. It is an editable planning resource. Approved purchases and stock changes still belong in your authoritative purchasing and inventory records, linked back to the review.

Leave a Reply