Purchase forecasting: demand, inventory and lead times

Purchase forecasting translates demand and inventory assumptions into expected purchases by item, supplier and period. Begin with the supplied demand plan, subtract usable opening stock and receipts already scheduled, then allow for the target closing stock. Compare that forecast with issued purchase orders and actual receipts on separate dates.

Keep the forecast separate from execution

A demand forecast describes expected usage. A purchase forecast adds inventory and supply timing. Orders and receipts record different stages of the same supply; adding their quantities double counts it.

Preserve the dated forecast with item, supplier, site, purchase unit, required receipt date, lead time, quantity and price. Export inventory by status, existing order schedules, new order lines and linked receipts.

Document how quarantined, damaged and allocated inventory affect usable stock. Keep that adjustment separate from demand changes. A budget and a forecast also differ: expected purchases may depart from the approved spending baseline.

Calculate additional planned purchases

For a defined period, use this simplified inventory balance:

New planned receipts = MAX(Demand + Target closing stock − Usable opening stock − Existing scheduled receipts, 0)

This calculation assumes receipts arrive in time and ignores pack sizes, minimum orders, losses and transfers. Review those constraints separately before deciding what to order.

An illustrative US distributor plans the following three items for one period. Existing receipts are from orders already open at the forecast cutoff; new purchases are additional to them. Columns A through F are item/supplier, demand, usable opening stock, existing scheduled receipts, target closing stock and forecast unit price.

Calculate new planned units in G2 and their value in H2 using these formulas respectively, then fill down:

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

The closing check for each row is =C2+D2+G2-B2-E2. It returns zero for all three items. If the MAX function truncates a negative purchase requirement, closing stock will exceed the target; show that excess rather than forcing the check to zero.

The US State Department's inventory guidance distinguishes operating, lead-time and safety stock. Its definitions are useful for naming separate planning assumptions; the distributor's target stock here is an explicit input, not a universal recommended level.

Compare the plan with orders and receipts

Keep the additional orders in a separate cohort from the orders already included in existing scheduled receipts. At the review date, the new order cohort has the following quantities. All unit prices remain as forecast, and there are no cancellations or returns.

To aggregate receipts in Excel, put PO-line ID in Receipts column A, receipt date in B and quantity in C. On an order-line review sheet, put PO-line ID in A2 and the review cutoff in M1:

=SUMIFS(Receipts!$C$2:$C$10000,Receipts!$A$2:$A$10000,$A2,Receipts!$B$2:$B$10000,"<="&$M$1)

Microsoft documents SUMIFS for totals subject to several conditions. Aggregate receipts to order-line level before joining supplier or item summaries so partial receipts do not duplicate ordered quantities. Covirage is not affiliated with Microsoft.

For each order line, outstanding quantity equals ordered quantity minus receipts in this example. The value reconciliation is:

$15,650 + $1,550 = $17,200. The order value is $300 below the $17,500 forecast even though the additional filter and reduced seal quantities offset in unit counts: 20 × $25 − 20 × $40 = −$300. This is a purchasing mix difference, not a price reduction. All quantities and dollar values here are exact.

Assuming existing scheduled receipts and demand occur as planned, actual closing stock is 180 + 150 + 210 − 500 = 40 filters, zero seals and 30 valves. The seal shortfall needs attention even though the combined new order quantity matches the plan.

Review timing before changing demand

On a timing sheet, put the required receipt date in B2 and assumed calendar-day lead time in C2. Calculate the planned order-release date in D2:

=B2-C2

Check the supplier calendar and receiving availability before accepting that date.

Keep assumed and observed lead times separately. A late receipt changes supply timing without necessarily changing demand. Review recurring demand errors with forecast bias by SKU and site; see forecast analysis for the broader review.

Where it goes wrong

• Existing orders are counted twice. Separate scheduled receipts already deducted from the plan from additional orders issued afterward.
• Order date and receipt date share one period. Label the forecast as expected order releases or expected receipts before comparing it.
• Cases and eaches are mixed. Normalize purchase and stock units with an approved conversion before calculating gaps.
• Partial receipts duplicate order lines. Sum receipts by unique PO-line ID, then reconcile received and outstanding quantities.
• Price and inventory value are confused with cash. The example values purchases at unit price; payment timing, freight and taxes need their own assumptions.

Compare your purchasing files in Covirage

Bring your supplied purchasing plan, inventory export, purchase order lines and receipt detail to Covirage. Covirage maps the columns, its tools compute and check plan deviations and order-to-receipt balances, and 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 export.

See Covirage for procurement teams for the supporting industry view.