Inventory forecasting: demand, lead times and stock needs

Inventory forecasting estimates the stock needed to meet expected demand while waiting for replenishment. Start with dated demand, usable on-hand inventory, expected receipts and a stated buffer, then check the balance over time. A positive total is not enough if the next delivery arrives after customers need the units.

Define the stock question

Decide whether the file answers a continuous replenishment question or a periodic purchasing review. The horizon must cover the time the business needs to protect. A periodic review may need demand through the next review and the replenishment lead time, rather than lead time alone.

Keep expected demand separate from the buffer policy. Microsoft's inventory-planning guidance distinguishes demand during lead time from safety stock used for fluctuations. The calculation below uses a fixed, explicitly assumed buffer; it does not demonstrate any particular service level.

Inventory and forecasting meet at an item, location and date. Units at another warehouse are not immediately usable unless the transfer and its timing are included. Damaged stock, inspection holds and expired goods also need separate treatment.

Assemble the exports and remove overlaps

Bring an inventory snapshot, open purchase orders with expected usable dates, customer commitments and a demand forecast at the same unit of measure. Record the snapshot date. Keep canceled receipts and already-received purchase orders out of the open supply total.

Before summing demand, determine whether the forecast already includes booked customer orders. Adding every order on top of an inclusive forecast double counts demand. Likewise, subtract a reservation from available stock only when the demand it serves is outside the forecast being compared.

If the demand estimate is repeatedly high or low, first examine forecast bias by SKU and site. A larger buffer cannot explain away a biased baseline.

Work the stock requirement

A US packaging distributor reviews one carton SKU at one warehouse. Its replenishment lead time is four weeks. The demand worksheet uses columns A:E and data rows 2:5; all quantities are cartons. The 150-carton receipt becomes usable at the start of week B.

The warehouse has 450 usable cartons, but 100 are reserved for a separate commitment excluded from the forecast below. That leaves 450 - 100 = 350 cartons available for this schedule.

In E2, enter the first formula; in B3, enter the second. Fill the rolling balance down:

=B2+C2-D2
=E2

The separate summary uses these cells:

The position is 450 + 150 - 100 = 500 cartons. Demand plus the assumed buffer is 400 + 100 = 500 cartons. This simplified horizon comparison therefore shows no additional units needed, subject to the receipt timing and policy assumptions.

Reconcile the schedule and test a delay

The closing balance must equal opening available stock plus receipts minus demand: 350 + 150 - 400 = 100 cartons. That matches E5 and the assumed buffer. The check cell is:

=B2+SUM(C2:C5)-SUM(D2:D5)-E5

It must return zero. Also check every intermediate balance, because a correct final total can conceal an earlier shortage.

Now change only the receipt date: the 150 cartons arrive after the horizon. Available supply within the window falls to 450 - 100 = 350 cartons. Closing stock becomes 350 - 400 = -50 cartons, representing 50 units of unmet demand rather than physical negative stock. Restoring the assumed closing buffer requires 500 - 350 = 150 additional cartons within the window.

For an actual receipt export, put expected usable dates in column J, open quantities in K, and horizon start and end dates in M2:M3:

=SUMIFS(K2:K1000,J2:J1000,">="&M2,J2:J1000,"<="&M3)

Microsoft documents SUMIFS as summing cells that meet multiple criteria. Apply item and location criteria too when the receipt export contains multiple groups.

Use the result for a purchasing review

The delayed-receipt variation calls for a supply decision, not an automatic increase to customer demand. Check whether the receipt date can be confirmed, a transfer can arrive in time, or customer commitments need discussion. Procurement owns supply assumptions; demand planning owns the demand version; operations confirms usable stock.

Keep this operational requirement separate from the financial target described in budget versus forecast. For tool selection after the worksheet is defined, see inventory forecasting software. Broader forecast reviews are grouped under forecast and pipeline.

Where it goes wrong

• Counting late receipts. Open purchase orders must be assigned to the dates stock becomes usable.
• Double counting commitments. Do not subtract reservations and add the same demand again.
• Mixing units. Cases, cartons and individual pieces need an explicit conversion before aggregation.
• Treating the buffer as a guarantee. A policy quantity does not establish a service level without checking uncertainty.

Covirage is not affiliated with Microsoft.

Compare stock needs in Covirage

Bring a sample of your own inventory snapshot, dated receipts and demand file. Covirage maps the columns, its tools compute and check the supplied figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your exports.

See Covirage for supply-chain teams for the supporting industry context.