Pharmaceutical demand forecasting: supply and expiry

Pharmaceutical demand forecasting compares expected commercial product demand with channel orders, shipments, and inventory that remains sellable before its cutoff. Keep these signals separate: a shipment shortfall can reflect supply or timing even when customer orders remain strong. Review expiry exposure at the lot level rather than assuming every pack in stock can serve every future order.

Establish the product and unit before comparing periods

Start with product, strength, dosage form, pack configuration, channel, requested period, and location. Use an internal product identifier linked to the appropriate commercial records. FDA describes the National Drug Code as its drug identifier; an NDC listing itself does not establish FDA approval.

Keep pack conversion factors in the product master and apply them before joining demand and inventory files. A forecast in packs cannot be compared directly with receipts recorded as individual containers. Preserve the original unit and converted quantity so the reviewer can trace the difference.

Use aggregate commercial records for this workflow. The general demand forecasting guide covers the choice of demand signal; the task here is product supply planning, channel timing, and inventory exposure.

Assemble the commercial review files

Bring the approved forecast with its issue date and target period; accepted orders with requested delivery dates; shipments; and opening and closing inventory. Add released receipts, returns, transfers, and inventory adjustments as separate movements when present.

For expiry review, include lot identifier, remaining quantity, labeled expiration date, inventory status, and the last date a lot can ship under the company's approved commercial rules. Customer remaining-shelf-life requirements can make that commercial cutoff earlier than labeled expiry. Confirm those requirements with their owner rather than deriving them from a sales trend.

FDA explains that expiration dating reflects stability under labeled storage conditions. A commercial allocation calculation does not establish stability, release held inventory, or authorize a dating change.

Reconcile supply before interpreting forecast error

A US pharmaceutical distributor reviews August 2026 for three products. Quantities below are sellable commercial packs after unit conversion. The example has no transfers, returns, losses, or other inventory adjustments; receipts shown are released receipts.

Place these columns in A:G, with headers in row 1.

The inventory identity is opening packs plus receipts minus shipments equals closing packs. In Excel, calculate expected closing quantity in G2 and fill through G4:

=B2+C2-F2

Compare that calculation with the independent closing inventory export. The company-level proof is 1,000 + 650 - 1,000 = 650 packs. A difference needs an identified movement or a corrected cutoff before the demand review continues.

Add columns H, I, and J for orders minus forecast, shipments minus orders, and shipments minus forecast:

=E2-D2
=F2-E2
=F2-D2

The bridge reconciles: -40 + -90 = -130 packs. Only 40 packs of the shipment gap correspond to lower accepted orders in this comparison; the other 90 sit between orders and shipments. That difference can reflect delivery timing, customer acceptance requirements, or fulfillment constraints. These totals alone cannot identify the cause.

For each product, H + I must equal J. Keep open-order status and promised dates with the exception list before changing future demand downward.

Review expiry exposure by eligible lot

The closing inventory includes the following lots approaching their approved commercial shipping cutoff. This separate worksheet uses A:G for lot, product, remaining packs, allocated demand before cutoff, excess packs, unit cost, and exposure. Allocated demand is supplied by the planner after checking channel eligibility and remaining shelf life; each demand quantity is assigned once.

Calculate excess in E2 and exposure in G2, then fill down:

=MAX(0,C2-D2)
=E2*F2

Microsoft documents MAX as returning the largest supplied value, so the formula prevents a negative excess quantity. Covirage is not affiliated with Microsoft.

For L1, 220 - 140 = 80 packs and 80 × $18 = $1,440. For L2, 120 - 100 = 20 packs and 20 × $18 = $360. Total exposure is $1,800 on 100 packs, using the supplied unit costs; no rounding is needed.

These lots are a subset of closing inventory, not extra inventory. Their 340 packs must reconcile to the relevant lot records within the 650-pack closing balance. The exposure is a commercial planning scenario, not a booked reserve or a confirmed disposal quantity. Finance and the appropriate quality owners determine the subsequent treatment.

Decide what changes in the next cycle

Review the order-to-shipment gap separately from future demand assumptions. Ask whether open orders still require fulfillment, whether channels can accept the available lots, and whether proposed future receipts increase the near-date exposure. Record the evidence behind any revised quantities.

Preserve the original approval using the demand forecasting process. Use intermittent demand forecasting when genuine zero-demand periods make weekly percentage errors unreliable, and bias by SKU and site to investigate repeated directional errors.

Where it goes wrong

• Treating orders and downstream demand as interchangeable. Channel inventory changes can move purchases without an equal change in end demand.
• Calling every shipment shortfall a demand miss. Review unfilled orders, delivery dates, and acceptance constraints first.
• Ignoring commercial shelf-life cutoffs. Product may be within labeled expiry yet fail a particular customer's acceptance requirements.
• Allocating the same demand to several lots. Assign demand once and check eligibility before reducing exposure.
• Mixing held and released quantities. Keep inventory status explicit so a supply calculation does not imply availability that has not been authorized.

Compare your commercial files in Covirage

Bring a sample of your own product forecasts, channel-order export, inventory movements, and lot-level commercial allocations to Covirage. Covirage maps the columns; its tools compute and check the supplied comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and set up your business with your own files.

See Covirage for pharmaceutical teams for the supporting industry context.