Coffee Inventory Reorder Point: A Planning Worksheet for Distributors

Plan in individual retail packs. Convert case quantities into packs before calculating demand, stock, or purchase quantities. Keep a separate row for each SKU and location unless stock can move between locations within the required time.

Copy the table below into a spreadsheet. The variable names can serve as named cells; replace period and review placeholders with actual dates.

Shipment history needs interpretation before it becomes a demand input. Shipments during a stockout understate demand; a promotion may overstate the demand expected after it ends. Annotate those periods, along with returns and forecast adjustments.

Keep purchase-order and lot details in two supporting tables. Their dates establish whether the quantities in the main worksheet will be usable when needed.

Mark assumptions explicitly, especially for new SKUs without reliable history. An assumed lead time or demand rate should remain identifiable when the worksheet is reviewed.

Two calculations establish the balance used for the reorder trigger:

UsableOnHand = PhysicalOnHand − BlockedStock

InventoryPosition = UsableOnHand + OpenInbound − Commitments

Exclude quality-held, damaged, and otherwise ineligible units from usable stock. For inbound, count only the confirmed, unreceived purchase-order balance. When a receipt becomes usable, move its quantity from inbound to on-hand. Counting it in both fields overstates inventory position and can delay replenishment.

Apply one consistent convention to customer commitments. This worksheet subtracts outstanding commitments from inventory position and excludes those same orders from the residual demand forecast. Subtracting a reservation while retaining it in forecast demand counts the requirement twice.

A total-demand forecast can also work, but it requires different allocation treatment. Do not combine the two conventions.

The aggregate balance answers whether replenishment needs review. It does not establish whether stock will be available on every dispatch date. Check that separately:

ClosingAvailable = OpeningAvailable + UsableReceipts − CommitmentsDue − ResidualDemand

Run the calculation across receipt and dispatch dates, applying demand for each interval. A receipt that arrives after a shortage cannot prevent it. Inventory position can therefore sit above the reorder point while a near-term customer order remains uncovered.

Use the coffee supply chain planning resource alongside the worksheet to review the handoffs behind these records.

Full replenishment lead time ends when coffee is available for dispatch. Measure it from order release, using calendar days throughout the worksheet.

Depending on the route, the interval can include preparation, documentation, freight booking, transport, clearance, receiving, and warehouse release. A vessel arrival date does not capture the full interval. Measure elapsed time rather than adding stages that overlap.

Where history is limited, a declared buffer keeps the calculation transparent:

SafetyStock = AverageDailyDemand × BufferDays

Buffer days are a planning assumption, not a guaranteed service level. Increasing them adds stock protection, but also ties up cash and leaves more coffee aging in storage.

With reliable, matched replenishment cycles, consider a buffer based on observed lead-time demand:

SafetyStock = MAX(0, SelectedPercentileLeadTimeDemand − AverageLeadTimeDemand)

Measure demand during each actual lead-time interval and document the selected percentile. Keep the lead-time-demand baseline in the reorder point consistent with that cycle history. Sparse or unrepresentative observations cannot support precise protection claims.

The basic reorder-point formula assumes continuous monitoring. If stock is reviewed every seven days, expected demand must cover lead time plus the seven-day review interval, with a buffer suited to that longer protection period.

All figures below are illustrative planning assumptions, not supplier specifications or partner performance data.

First, calculate usable stock, the buffer, and the trigger:

Inventory position is 920 packs, below the 1,000-pack reorder point, so replenishment needs review. The existing 240-pack inbound order is already included in that balance.

To calculate quantity, set an order-up-to target:

TargetStock = AverageDailyDemand × (LeadTimeDays + CycleDays) + SafetyStock

For this SKU:

20 × (40 + 20) + 200 = 1,400 packs

Subtract inventory position from the target:

RawQuantity = MAX(0, TargetStock − InventoryPosition)

1,400 − 920 = 480 packs

For a triggered, positive order, apply MOQ and round up to full cases:

OrderQuantity = CEILING(MAX(RawQuantity, MOQUnits), CasePack)

The result is 480 packs, or 20 cases. It meets the 240-pack MOQ and is already a multiple of the 24-pack case size. If no order is triggered, or raw quantity is zero, return zero before applying MOQ.

The 1,400-pack target represents inventory position immediately after adding the new purchase order. It is not the expected physical stock at arrival: demand continues during the 40-day lead time.

The 480-pack calculation needs two further checks: whether stock lasts until arrival and whether the new lot can sell within its channel window.

For the example, assume the 120 committed packs leave on day 0 and the existing inbound order becomes usable on day 20.

After the commitments leave, uncommitted on-hand is 680 packs. Residual demand consumes 400 packs by day 20, leaving 280. The 240-pack receipt raises availability to 520. Another 400 packs sell before day 40, leaving 120 when the proposed 480-pack order arrives.

That schedule avoids a shortage under the stated assumptions. Recalculate it if a receipt is delayed.

For each lot, calculate:

RemainingLifeAtReceipt = BestBeforeDate − ExpectedUsableReceiptDate

ChannelSellingWindow = RemainingLifeAtReceipt − RequiredLifeAtCustomerReceipt − DownstreamTransitDays

Assume the proposed lot has 180 days remaining at receipt, customers require 60 days of remaining life, and downstream transit is zero. The selling window is 120 days. At 20 packs per day, gross demand over that window is 2,400 packs. That figure alone does not establish how much to buy: existing lots will satisfy part of the same demand.

Allocate eligible stock by FEFO: first expired, first out. Use the earliest best-before dates, rather than receipt order, to determine picking priority.

The following table uses the same example, with day numbers measured from day 0. All four lots use the illustrative 60-day customer requirement and zero downstream transit.

At constant residual demand, FEFO clears lot A by day 19, lot B by day 34, existing inbound by day 46, and the proposed order by day 70. Each clears before its latest customer receipt day. The 480-pack purchase therefore fits this illustrative schedule.

Customer freshness requirements, storage eligibility, and legal eligibility require separate checks. Use verified lot records and the best-before planning resource for imports when documenting assumptions.

If case rounding or MOQ pushes the quantity beyond feasible sell-through, flag an exception. Seek smaller quantities, staggered deliveries, or revised terms before approving stock the channel cannot absorb within its required window.

Reconcile counts, receipts, commitments, and lot dates on a documented schedule. Review demand, lead time, and buffers after material changes. Track shortages, late usable receipts, forecast error, and aging stock so the next adjustment addresses the assumption behind the problem.

If your team is considering MR.VIET branded products for retail distribution, share your target market, retail channel, product format, and expected volume through the wholesale page. Those details provide a practical starting point for a purchasing discussion.