Course / Challenges

Challenge: stock room reorder list

Flag low stock, value the shelves, find each supplier from the SKU, and size up the reorder. Intermediate · 8 min · IF, LEFT, XLOOKUP, COUNTIF, SUMIF

You run the stock room for a busy café. Each item has a count, a reorder level and a unit cost. Before Monday's orders go out, you need to know what is running low, who supplies it, and how much money is sitting on the shelves.

Each SKU starts with a two-letter supplier code, and the supplier names are listed in J1:K5.

Steps

1. In F2:F17, show Reorder when an item's stock is at or below its reorder level, and OK otherwise.

2. In G2:G17, work out the value of the stock on hand for each item, formatted as currency.

3. In H2:H17, show each item's supplier name. The code is the first two letters of the SKU; look it up in the supplier table J1:K5.

4. In K7, count how many items need reordering.

5. In K8, total the value of all stock. In K9, total the value of only the items that need reordering.

Remember

• IF with <= turns a comparison into a clear status label.
• Nest LEFT inside a lookup to use part of a code as the key.
• COUNTIF and SUMIF summarise rows by their status.
• Lock lookup tables with $ before filling down.

Previous: Challenge: quarterly sales summary · Next: Challenge: travel budget vs actual