Material demand forecasting from production plans

Material demand forecasting translates planned production into component quantities, then reconciles those quantities with usable stock and timely receipts. The answer is a remaining requirement for each material, site and consumption date. It is a schedule analysis that supports procurement decisions; it does not execute material requirements planning.

Start with the production plan and approved component usage

Use the production quantities operations intends to make, including any approved inventory build. Customer orders alone miss that adjustment. The broader demand forecasting guide covers estimating customer requirements; production planning forecasting covers translating them into finished output.

For an illustrative US pump manufacturer, the review covers production starting November 2, 2026. The approved plan contains 120 Pump A units and 80 Pump B units. Engineering provides the current bill of materials, with quantities expressed in each component's stocking unit. These assumptions already reflect normal consumption; no additional scrap allowance applies.

Bring separate exports for planned output, component usage, opening stock and outstanding receipts. Retain product, component, site, revision, quantity, unit, required date and snapshot date. Record which receipt quantities are confirmed and when they will become usable, including any inspection delay.

Calculate gross material requirements

Use a worksheet called BOM, with the following columns A through F. Store the required date as an actual spreadsheet date. Each product-component pair appears once for this production run.

In F2, enter and fill down:

=D2*E2

Steel requires 120 × 2 + 80 × 3 = 480 kg. Seals require 120 × 2 + 80 × 4 = 560 each. Those are separate component totals. Adding kilograms to individual seals would produce a number with no operational meaning.

Do not total the planned-output column here. The finished production quantities repeat because each product uses more than one component. Tie those quantities to the original production-plan rows by product before multiplying them.

Deduct usable stock and timely receipts

Build a Materials worksheet with columns A through H as shown below. The stock balances are usable quantities immediately before this run. Confirmed receipts are available before consumption starts. Target closing stock is an approved planning input, rather than an estimate derived from this example.

Aggregate the gross requirement in C2 and fill down:

=SUMIFS(BOM!$F$2:$F$5,BOM!$C$2:$C$5,$A2,BOM!$B$2:$B$5,$B2)

Microsoft's SUMIFS documentation describes adding values that meet multiple criteria. This example matches component and required date; add site as another criterion when the workbook includes multiple locations.

The net requirement and projected closing formulas in G2 and H2 are:

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

Microsoft's MAX documentation confirms that the function returns the largest argument. Comparing the calculated shortage with zero prevents surplus stock from becoming a negative purchase requirement. Covirage is not affiliated with Microsoft.

Steel's additional requirement is 480 + 50 − 180 − 100 = 250 kg. Seals require 560 + 100 − 200 − 150 = 310 each. These are exact whole-unit calculations, so no rounding is applied.

Prove the balances before acting

Every material must satisfy the stock identity:

Opening usable stock + timely receipts + additional supply = consumption + projected closing stock.

For steel, 180 + 100 + 250 = 480 + 50 = 530 kg. For seals, 200 + 150 + 310 = 560 + 100 = 660 each. Test the identity in I2 and fill down:

=D2+E2+G2-C2-H2

The result must be zero. Also tie opening balances to the filtered stock report and receipts to the confirmed purchase-order lines. The identity alone cannot detect an incorrect source balance.

Now change one input: only 50 of the 150 expected seals arrive before consumption. The additional requirement becomes 560 + 100 − 200 − 50 = 410 seals, an increase of 100. A receipt arriving after the run cannot close that earlier gap. Procurement must identify feasible supply or agree a production change.

For recurring periods, carry projected closing stock forward once as the next opening balance. Keep the frozen plan for comparison with revisions; budget vs forecast explains why approved versions should remain distinguishable.

Where it goes wrong

• Unavailable inventory reduces need. Quarantined or allocated stock creates a false surplus when treated as usable.
• Receipts have the wrong timing. A weekly total can hide a shortage before a late delivery. Review required dates inside the week where timing matters.
• Usage and stocking units differ. Convert pounds, kilograms, packs and individual pieces before comparing quantities, using an approved conversion.
• Scrap is counted twice. Check whether the approved component quantity already includes the allowance before adding another factor.
• A shared material is deducted repeatedly. Aggregate all product requirements at the site before allocating the same stock balance. Investigate persistent planning errors by product and site through a forecast bias review.

Reconcile your material files in Covirage

Upload a file or Google Sheet containing production quantities, component usage, stock and dated receipts; 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, set up your business and bring a sample of your own data; creating the account takes about a minute.

See Covirage for industrial manufacturers for the industry context.