Demand forecasting for perishable goods: shelf-life checks

Demand forecasting for perishable goods matches expected demand to the window when a product can still be sold. Compare the supplied forecast with sales, availability, and spoilage within that window, rather than against a calendar total that includes demand after the stock's last-sale date. The useful result is a quantity reconciliation that explains what expired and why.

Start with the usable selling window

A forecast can be accurate for a month and still leave inventory unsold. Demand arriving after a lot's last-sale date cannot absorb that lot, and a late delivery cannot satisfy customers who needed it earlier.

Use the last-sale date approved by your company's quality or food-safety process. Forecasting does not establish a safe shelf life or authorize a date extension. For food businesses, the FDA Food Code provides a reference for retail and food-service safety provisions; the analysis here takes approved dates as inputs.

Define the product, location, unit of measure, availability start, and last-sale date before comparing figures. Keep temperature-related holds and other unavailable inventory outside sellable supply. For the broader planning question, use the demand forecasting guide; this check concerns timing within usable life.

Step 1: bring the exports to the same grain

Bring a dated forecast export, lot receipts, sales or fulfilled-order rows, spoilage adjustments, and opening and closing inventory. Retain the forecast issue date so later revisions do not replace the forecast that informed the purchase.

Match the forecast to the product and location, then sum its daily quantities across the usable window. Do not divide a monthly total evenly unless that is an explicit assumption: selling patterns may differ across weekdays, promotions, and delivery days.

For a first check, use nonoverlapping windows so a sale can belong to only one window. When lots overlap, keep the forecast at product-location-day level and use the recorded lot allocation for inventory reconciliation. Copying the entire product forecast onto every open lot double counts expected demand.

Availability is also essential. A window without stockouts is a cleaner forecast comparison than one with unfilled orders. The separate CPG demand forecasting guide addresses the broader packaged-goods context.

Step 2: build a closed-window table

A US refrigerated-food distributor reviews the same yogurt SKU at one location across two completed, nonoverlapping windows. The table contains every unit available in each window, including any opening inventory. There are no transfers, returns, other adjustments, or recorded stockouts. Each unit has a cost of $2.50.

Use these columns in rows 2 and 3. Dates are inclusive, and both windows have closed before the review.

The supply definition must match the inventory records. If transfers or returns exist, show them explicitly in the balance instead of absorbing an unexplained difference into spoilage.

Step 3: calculate the balance and performance measures

In J2, check available units less sold, spoiled, and ending units:

=D2-F2-G2-H2

Both rows must return zero. A nonzero balance means the inventory movements are incomplete, so investigate that difference before interpreting waste.

Sell-through in K2 and spoilage rate in L2 both use available units as the denominator:

=IF(D2=0,NA(),F2/D2)
=IF(D2=0,NA(),G2/D2)

Forecast minus sales in M2, spoilage cost in N2, and absolute forecast error in O2 are:

=E2-F2
=G2*I2
=ABS(M2)

Fill each formula down. Positive signed error means forecast demand exceeded observed sales. Percentages below are rounded to one decimal place; calculations retain full precision.

Total sell-through is 540 / 700, and total spoilage is 160 / 700. Divide summed quantities; do not average the displayed lot percentages.

The weighted absolute percentage error across the windows is 40 / 540 = 7.4%, using:

=IF(SUM(F2:F3)=0,NA(),SUM(O2:O3)/SUM(F2:F3))

Absolute errors prevent positive and negative misses from canceling. The forecast accuracy chapter in Forecasting: Principles and Practice explains absolute-error measures and why evaluation uses observations outside the fitting period. Here, the supplied forecasts must have been issued before these windows occurred. For choosing a business threshold, see what makes forecast accuracy good.

Step 4: prove whether supply or demand created the waste

For these closed windows, ending stock is zero and the only exits are sales and spoilage. That gives an exact identity:

Spoiled units = (Available units - Forecast units) + (Forecast units - Sold units)

Waste of 160 units is therefore consistent with 120 units supplied above the forecast plus a forecast overstatement of 40 units. This is an accounting bridge, not proof of the operational cause. Delivery restrictions or purchase minimums may explain the supply choice.

As a sensitivity check, reduce only lot A's available quantity from 400 to 340 units. Assume the same timing and sales remain feasible. Lot A's spoilage becomes 340 - 280 = 60 units; combined spoilage becomes 100 units on 640 available, or 15.6%. At the same unit cost, waste costs $250 rather than $400, a reduction of $150. The forecast error has not changed.

The planner owns the forecast comparison, purchasing explains receipt quantities, and operations verifies the inventory movements. Repeated signed misses belong in the separate SKU and site forecast-bias review.

Where it goes wrong

• Demand after expiry counts toward the lot. A calendar forecast includes sales the inventory cannot serve. Align the comparison to approved usable dates.
• Unavailable stock looks like weak demand. A sell-through figure cannot reveal purchases customers could not make. Preserve availability flags and unfilled-order evidence.
• Overlapping lots repeat the forecast. Keep demand at product-location-day level and reconcile lot movements separately when selling windows overlap.
• Open windows are judged as finished. Remaining sellable stock is ending inventory, not spoilage. Compare only closed windows or label the review as incomplete.
• All waste is called a forecast failure. Reconcile receipts, ending stock, and actual sales first. Supply above the forecast may explain much of the difference.

Check your own shelf-life windows in Covirage

Upload your forecast, sales, lot, and spoilage file or Google Sheet; Covirage maps the columns, and its tools compute and check the comparisons. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See forecast analysis for the supporting workflow.