Supply forecasting: availability, capacity and shortfalls

Supply forecasting estimates which units will be available, where and when, under explicit receipt and capacity assumptions. Compare that schedule with demand to find shortages before the customer delivery date. A purchase order due next month cannot cover this month's requirement unless its arrival changes.

Start with a dated availability schedule

Use an inventory export, open purchase order lines, production orders and a demand file. Keep item, site, unit of measure and period consistent across them. Demand forecasting in supply chain covers the customer requirement; this schedule tests whether supply can meet it.

Save the extraction time and the plan version. Procurement owns supplier date assumptions; production planning owns completion and capacity assumptions. The supply planner owns the combined schedule and its reconciliation.

Document the reason for each manual change. The authors of Forecasting: Principles and Practice recommend documenting and justifying judgmental adjustments. A receipt moved earlier without an owner or explanation is difficult to challenge later.

Calculate available units and the gap

For each item and site, carry the signed balance forward:

Projected closing balance = Opening balance + Planned usable receipts − Demand

Shortfall = MAX(0, −Projected closing balance)

A signed balance retains any uncovered demand. It assumes unmet demand carries forward. If customers cancel instead, track lost demand separately and carry actual physical stock, rather than quietly erasing the shortage.

A US parts distributor has the following illustrative schedule for one item at its Ohio warehouse. All quantities are individual units. Receipts are usable arrivals, after inspection; demand is due during the stated week.

The first week closes at 400 + 600 − 800 = 200 units. The next week closes at 200 + 700 − 1,000 = −100 units. The schedule is short even though every planned receipt arrives.

With the table in columns A:F and its first data row at row 2, enter these Excel formulas in E2, F2 and B3, respectively:

=B2+C2-D2
=MAX(0,-E2)
=E2

Fill the closing balance and shortfall formulas down. The shortfall column is a balance at each date, so do not sum it across weeks: an unresolved shortage could appear repeatedly.

To build receipts from an export named Receipts, with item in column A, site in B, receipt date in C and remaining units in D, use:

=SUMIFS(Receipts!$D:$D,Receipts!$A:$A,$H$1,Receipts!$B:$B,$H$2,Receipts!$C:$C,">="&A2,Receipts!$C:$C,"<"&A2+7)

Here H1 holds the item and H2 the site. Microsoft documents SUMIFS as summing values that meet multiple criteria. Covirage is not affiliated with Microsoft.

Prove the schedule, then test the assumptions

The ending balance must equal the opening balance plus all receipts less all demand: 400 + 600 + 700 − 800 − 1,000 = −100. Do not add the opening balances together; later openings already contain earlier activity.

Then challenge the receipt quantities and dates. Suppose the second week's receipt depends on these production assumptions:

The capacity check is 35 × 20 = 700 units. That establishes an output limit under the supplied rate; it does not prove materials or labor will be available.

If 200 units move from the first receipt to the second, receipts become 400 and 900. Closing balances become 400 + 400 − 800 = 0, then 0 + 900 − 1,000 = −100. The ending shortage is unchanged, but the first week's cushion disappears. A monthly total would hide that exposure. Review daily dates when deliveries and demand occur within the same week.

Compare planned receipts with actual arrivals

Keep the original plan when receipt records arrive. Replacing it with actuals destroys the evidence needed to judge the assumptions.

At unchanged demand, the actual ending signed balance is 400 + 1,050 − 1,800 = −350 units. The difference from the planned −100 is −250, exactly the receipt variance. All calculations here use whole units without rounding.

Separate short deliveries from late deliveries before assigning actions. Match receipt lines to order lines, including partial arrivals. Review the result alongside inventory forecasting, which turns demand and inventory policy into stock requirements, and SKU and site forecast bias, which evaluates demand errors.

Where it goes wrong

• Counting an order and its receipt. Use outstanding quantities after receipts, not the original order total.
• Counting unusable inventory. Quality holds and reserved stock need explicit treatment before promising availability.
• Mixing dates. Supplier shipment dates and usable warehouse receipt dates answer different questions.
• Hiding constraints in averages. Spare capacity on another item or site cannot cover a shortage without a feasible transfer or production change.

Check your supply schedule in Covirage

Bring a sample of your own inventory, receipt and demand exports with the supplied plan. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring your dated schedule.

See Covirage for supply chain teams for the supporting workflow.