Project: inventory decisions

Your brief: prioritize a weekly inventory review

A fictional retailer needs to review service risk and cash tied up in stock. You receive a snapshot with one row per SKU: current usable Stock units, Weekly demand units, supplier Lead weeks and Unit cost in one currency.

Build a review that answers: what is the total stock value, which positive-demand SKUs have less stock than expected demand during lead time, and which SKUs need investigation for zero demand? Your recommendation should separate these questions instead of treating every low or high number as an automatic order instruction.

Define and calculate the measures

Stock value is Stock × Unit cost. Weeks of cover is Stock ÷ Weekly demand when demand is positive. Lead-time demand is Weekly demand × Lead weeks. Supply gap is Lead-time demand − Stock. A positive gap indicates the simplified snapshot is short of the demand expected during lead time.

For A102, Stock is 40, demand is 20 per week and lead time is 3 weeks. Cover is 2 weeks; lead-time demand is 60 units; the gap is 20 units. For A103, stock is zero and demand is 10, so it has zero cover and a positive gap of 10.

A104 has stock 80 and demand zero. Its cover is undefined under this ratio, so label it No demand rather than assigning an arbitrary large number or zero cover.

Build a repeatable analysis

1. Keep the original snapshot in a clearly named table. Confirm unique SKUs, numeric nonnegative inputs and consistent units.
2. Add Stock value, Cover, Lead-time demand and Gap calculations in separate columns.
3. Add a status: No demand when demand is zero; Review when stock is below lead-time demand; otherwise Covered.
4. Use FILTER outside the table to create a review list, or use a table filter in an older Excel version.
5. Summarize total stock value and the count of review records. Keep the detail available.
6. Create a simple stock-value chart and a short action table with owner, question and next review date.

The online tasks calculate independent outputs so one unfinished helper cell does not block all later practice. In Excel, use copied formulas or table calculated columns and verify the last row.

Reconcile and interpret the sample

Stock value is 960 for A101, 480 for A102, 0 for A103 and 1,600 for A104: total 3,040. A102 and A103 have positive supply gaps. A104 holds the largest value while recorded demand is zero.

Investigate A102's supply timing and A103's stockout exposure first; check open purchase orders, inbound dates, customer demand and usable stock before recommending an order. For A104, review whether zero demand reflects a new item, missing forecast, seasonality or discontinued stock before proposing disposal.

Do not add stock units across unrelated products and call that total weeks of cover. Different SKUs have different demand rates and substitution constraints. A portfolio ratio, if used, needs a clearly defined common basis and still cannot replace SKU-level risk checks.

State the assumptions behind the recommendation

This exercise uses constant demand, one lead time and a current stock snapshot. It excludes inbound supply, committed orders, safety stock, demand variability, shelf life, minimum order quantities and capacity. The gap is a screening signal, not an optimized purchase quantity or a full replenishment policy.

Check a changed demand input, zero demand, exactly enough stock for lead time and a SKU with zero stock. Confirm the status rule includes equality under Covered. Refresh any query and PivotTable used in the native workbook, then reconcile stock value to the original snapshot.

Write a short conclusion naming the affected SKU, the observed metric, a plausible question to investigate and the next action. Save the working .xlsx and inspect the shared output. Dynamic-array formulas need a compatible version; ordinary formulas and table filters provide a practical alternative.