Excel Practice
Lessons Lesson 102

Project: A Stock Reorder List

About this lesson

A reorder list is an IF flag comparing stock with its reorder level, and a lookup from a code list for the supplier. Conditional functions over the flag column say what to chase.

By the end you can

  • flag what to order, name the supplier, and list what to chase.
  • summarise a flag column with COUNTIF, MAXIFS and COUNTIFS.

Practises IF XLOOKUP COUNTIF MAXIFS COUNTIFS

The idea

The stockroom's sheet. A flag per row from one comparison, a supplier name from a code list, and then the questions. How many to order, which will take longest, which are both needed and slow. Every one of them reads the flag column. That is why it is built first.

The not-found text in the XLOOKUP is doing real work. A supplier code that is not on the list is exactly the row somebody has to look at. Unknown says so, where #N/A would just look broken.

The mistake to watch for

A supplier code that is not on the list showing as #N/A. That row is exactly the one somebody has to look at. An error looks broken, not informative. XLOOKUP's not-found text says Unknown where the person can see it. Build the flag column first, because every summary reads it.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In F2, decide whether the hinge needs ordering: Order if the stock is below the reorder level, ok otherwise. This fills down.

To begin, type it exactly:

=IF(B2<C2,"Order","ok")

F2
Row A B C D E F G H I J
1 Part Stock Reorder at Lead days Supplier Action Supplier name Code Name
2 Hinge 140 50 3 S1 S1 Ironmonger
3 Bracket 32 40 7 S2 S2 Fixings Ltd
4 Bolt M6 910 300 2 S1
5 Castor 8 12 10 S2
6 Handle 25 30 14 S9
7
8
9 Parts to order
10 Longest lead

Every step

  1. 1

    In F2, decide whether the hinge needs ordering: Order if the stock is below the reorder level, ok otherwise. This fills down. To begin, type it exactly: =IF(B2<C2,"Order","ok").

    Hint. Stock against reorder level.

  2. 2

    Filled down, F6 reads =IF(B6<C6,"Order","ok"). What does it show for the handle?

    Hint. Below the level.

  3. 3

    In G2, the supplier's name from the code list in I1:J3. Use XLOOKUP, with Unknown for a code that is not listed. Lock the two columns.

    Hint. Fourth argument for the missing code.

  4. 4

    In B9, how many parts need ordering.

    Hint. "Order".

  5. 5

    In B10, the longest lead time among the parts that need ordering, with MAXIFS.

    Hint. Lead days, filtered by Order.

  6. 6

    In B11, write a formula using COUNTIFS that counts the parts to order whose lead time is more than 7 days. Those are the ones to chase first.

    Hint. "Order" and ">7".