Regression analysis for inventory management

Regression analysis in inventory management estimates how a defined outcome, such as weekly requested units, varies with information available before a planning decision. Its practical test is whether predictions hold up in later periods and improve on a simple alternative. A fitted relationship alone does not establish causation or tell a buyer how much to order.

Start with one business question

Consider an Ohio industrial-parts distributor reviewing weekly demand for one SKU at one warehouse. The question is whether customer orders already booked by Friday help estimate the total units customers will request for the following week. That is narrower and more testable than asking regression to improve inventory generally.

Specify the outcome as requested units, including retained backorders, rather than units shipped. Specify the predictor as orders for that week visible in the preceding Friday's saved export. Later changes to the order book must not overwrite those historical snapshots.

The distinction matters because a predictor available only after the week ends cannot support the earlier decision. The authors of Forecasting: Principles and Practice explain that forecasts using regression require future predictor values, assumed scenarios, or predictors already available through lags.

For choosing a different measure, start with the broader inventory analysis methods. Keep cost, supplier reliability and demand questions separate before combining their results in a policy review.

Prepare a history the business can audit

Build one row per SKU, warehouse and target week. Keep the saved decision date, booked units known on that date, final requested units and flags for closures, promotions and stockouts. Use the same unit of measure throughout.

Check the order detail before summarizing. Remove duplicate order-line versions, retain valid cancellations according to a written rule, and distinguish a missing export from a genuine zero. Reconcile requested units back to the underlying customer-request rows.

Reserve the latest periods before fitting anything. For a real review, choose enough history and later observations to cover the replenishment horizon and relevant seasonal conditions. The small table below demonstrates the arithmetic; it is not enough evidence to approve an operational policy.

Fit the earlier periods only

Put week in column A, booked units in B and requested units in C. These are the earlier observations, occupying rows 2 through 7:

Predicted requested units = Intercept + Slope × Booked units

The mean booked quantity is 780 / 6 = 130 units; mean requested quantity is 900 / 6 = 150 units. The sum of squared deviations of x is 7,000, and the sum of paired x and y deviations is also 7,000. Therefore the slope is 7,000 / 7,000 = 1 and the intercept is 150 − 1 × 130 = 20.

In Excel, use Microsoft's SLOPE and INTERCEPT functions to place the slope in H2 and intercept in H3:

=SLOPE(C2:C7,B2:B7)
=INTERCEPT(C2:C7,B2:B7)

Fitted units in D2 and residuals in E2 are:

=$H$3+$H$2*B2
=C2-D2

Fill those formulas through row 7. The residual total is zero, a useful arithmetic check for this least-squares fit with an intercept. It does not prove predictive usefulness. The separate regression in Excel guide covers the spreadsheet mechanics in more detail.

Test later weeks against a baseline

Freeze the coefficients and predict weeks 7 through 9, entered in rows 8 through 10. Compare them with a deliberately simple baseline: the earlier-period mean of 900 / 6 = 150 requested units every week.

In D8, the following formula uses only the earlier rows. Microsoft documents FORECAST.LINEAR as linear-regression prediction from known x and y values:

=FORECAST.LINEAR(B8,$C$2:$C$7,$B$2:$B$7)

Absolute error in E8, baseline in F8 and baseline error in G8 are:

=ABS(C8-D8)
=AVERAGE($C$2:$C$7)
=ABS(C8-F8)

Fill through row 10. Mean absolute error is =AVERAGE(E8:E10): 17 / 3 = 5.67 units, against 53 / 3 = 17.67 for the baseline. Weighted absolute percentage error is =SUM(E8:E10)/SUM(C8:C10): 17 / 445 = 3.82%. Round errors to two decimals; retain full precision in formulas.

These later weeks favor the fitted relationship. They do not establish that it remains useful through a seasonal change. For a seasonal SKU, include a comparable seasonal baseline and repeat the test across successive decision dates.

Inspect errors before changing a policy

Read signed errors as well as absolute errors. Here actual minus predicted is 5, -6 and 6 units, so average underprediction is (5 − 6 + 6) / 3 = 1.67 units. Persistent underprediction matters differently from alternating errors of the same absolute size; review demand forecast bias by SKU and site alongside accuracy.

Plot residuals against time, fitted demand and the predictor. Repeated patterns deserve investigation. NIST's guidance on dependent residuals explains that dependence can distort uncertainty estimates and may require a different statistical approach.

The planning owner decides whether the evidence warrants a broader test. Purchasing supplies lead-time assumptions, and sales validates order-book definitions. A proposed stock reduction still needs a JIT service and cost review, including missed demand and supplier reliability.

Where it goes wrong

• Future information enters the history. Final order bookings replace the quantities actually visible when the forecast would have been made.
• Shipments stand in for demand. Stockouts cap shipments and can teach the fitted relationship that constrained sales were normal demand.
• Aggregate rows hide different relationships. Warehouses, changing pack sizes or unrelated SKUs are combined without a business reason.
• A strong historical fit replaces validation. Later errors and a credible baseline are omitted, or withheld periods are repeatedly used to choose predictors.
• A prediction becomes an order instruction. Lead times, existing stock, open purchases and approved service assumptions are missing from the decision.

Covirage is not affiliated with Microsoft.

Review your inventory history in Covirage

Bring your saved order history, requested-unit export and the dates reserved for validation. Covirage maps the columns, and its tools compute and check the figures within the supplied statistical setup; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See forecast analysis with Covirage for the supporting business workflow.