Guided project
Inventory reorder alerts in Excel
Know what runs out first and build a live reorder list.
You will use: ROUND, IF, SUMPRODUCT, COUNTIF, FILTER
Starting data
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Stock | Reorder at | Daily sales | Unit cost | Days left | Action | Stock value | |
| 2 | Paper | 120 | 50 | 12 | 4.5 | To reorder | |||
| 3 | Toner | 8 | 10 | 1 | 62 | ||||
| 4 | Pens | 340 | 100 | 25 | 0.8 | Reorder list | |||
| 5 | Folders | 45 | 60 | 6 | 1.2 | ||||
| 6 | Staplers | 14 | 5 | 0.5 | 9 | ||||
| 7 | Labels | 30 | 40 | 5 | 3 |
Build it step by step
Step 1
How many days of stock are left? Divide stock by daily sales, rounded to 1 decimal, in F2. Fill to F7.
Step 2
Show "Reorder" when stock is at or below the reorder point, otherwise "OK". Start in G2, fill to G7.
Step 3
What is all the stock worth? Stock times unit cost, added up, in J1.
Step 4
How many items need reordering? Put it in J2.
Step 5
Build a live list of items to reorder, starting in I5.