Demand planning in Excel: forecast to replenishment

Demand planning in Excel compares forecast demand with usable stock, dated receipts, lead times and supply constraints to calculate replenishment and remaining gaps. The workbook tests whether supply covers forecast demand.

Start with the files and definitions

Bring weekly demand by SKU and site, usable inventory, dated open supply, supplier lead times and quantity limits. Add approved safety stock and backlog excluded from the forecast.

Convert each SKU to one planning unit. Retain the original quantities and conversion factors for checking.

Reconcile starting stock with the inventory stock movement workbook. Exclude unusable stock and receipts due beyond the horizon.

See demand planning for logistics for the operating handoffs that follow the calculations.

Establish a demand baseline

A US parts distributor plans SKU A100 at its Reno site using the arithmetic mean of recent complete weeks as an illustrative baseline.

Put the three observations in B2:B4 on a History sheet:

=AVERAGE(History!B2:B4)

The result is 360 / 3 = 120 units per week. Microsoft documents that AVERAGE returns the arithmetic mean and ignores empty cells in a referenced range while including zeros. A missing export week therefore needs investigation; it must not disappear from the planning history.

Sales should review the increasing sequence for trends or promotions before approval. Keep overrides and reasons separate. Check recurring overstatement or understatement with forecast bias by SKU and site.

Translate the forecast into a stock requirement

The protection horizon covers replenishment lead time plus the review interval. Safety stock is an approved input, not an estimate from these history rows.

Inventory position within the horizon = Usable on-hand + Confirmed receipts - Incremental backlog

Target position = Weekly demand × (Lead-time weeks + Review-interval weeks) + Safety stock

Unconstrained replenishment = MAX(0, Target position - Inventory position)

Backlog is incremental to the forecast. The supplier limit applies to the proposed receipt.

The protection horizon is 2 + 1 = 3 weeks, with forecast demand of 120 × 3 = 360 units. Inventory position is 250 + 20 - 10 = 260 units. Target position is 360 + 30 = 390, leaving an unconstrained replenishment requirement of 390 - 260 = 130 units.

Apply the supply limit in Excel

On a Plan sheet, put SKU in A and the input values in B:I in the order shown above. Use J for inventory position, K for target, L for unconstrained replenishment, M for feasible replenishment, N for projected ending balance and O for the safety-stock gap.

Enter these formulas in J2:O2, respectively:

=B2+C2-D2
=E2*(F2+G2)+H2
=MAX(0,K2-J2)
=MIN(L2,I2)
=J2+M2-E2*(F2+G2)
=MAX(0,H2-N2)

MAX prevents a negative order suggestion; MIN applies the supplier limit. Microsoft's formula guide explains their smallest-value and largest-value behavior.

The feasible receipt covers demand with no reserve. All 130 required units would leave 260 + 130 - 360 = 30. No rounding is needed here; apply purchasing multiples before rechecking the supply limit.

Prove that supply arrives in time

Check receipt timing weekly. Carry each closing balance into the next opening balance.

For columns B:F in this table, closing balance in F2 is:

=B2+C2+D2-E2

The next opening cell is =F2. The total check is 250 + 20 + 100 - 370 = 0, matching the summary. Week 1 demand is 120 forecast units plus 10 incremental backlog units; backlog must not be subtracted again in the weekly table.

Keep negative projected balances visible. If the supplier quantity limit falls to 70 with everything else unchanged, the ending balance becomes 260 + 70 - 360 = -30. That means 30 units of uncovered demand and a 60-unit gap to the 30-unit reserve target. The planner must resolve the supply or demand assumption rather than hide the negative balance.

For manufactured items, check demand forecasting and capacity planning before assuming production is feasible.

Where it goes wrong

• Counting booked orders twice. Establish whether the forecast already includes them before adding backlog.
• Using all open supply. A receipt beyond the protection horizon cannot cover demand inside it.
• Ignoring receipt timing. A positive final balance can coexist with an earlier stockout. Use shorter buckets when timing within a week matters.
• Hiding the constrained gap. Show the full requirement alongside feasible supply so the missing quantity remains visible.
• Treating missing history as zero demand. Distinguish an actual zero from an incomplete export or a stockout that suppressed sales.

Covirage is not affiliated with Microsoft.

Review your planning workbook in Covirage

Bring your demand assumptions, inventory snapshot, dated supply and capacity limits in a workbook or Google Sheet. 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 and bring a sample of your own planning file.

See capacity planning with Covirage for related demand and resource comparisons.