Stock and ordering: practice exercises
Stock value, reorder flags, whole packs, SUMIF, weeks left. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In D2, work out the value of the stock of each item: the quantity × the unit cost.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Quantity | Unit cost | Stock value |
| 2 | Keyboards | 36 | £26.79 | |
| 3 | Sticky notes | 9 | £14.88 | |
| 4 | Spray cleaner | 43 | £12.44 | |
| 5 | Headsets | 12 | £13.41 | |
| 6 | Sugar | 49 | £28.18 | |
| 7 | Bin bags | 51 | £8.93 | |
| 8 | Mice | 59 | £35.76 | |
| 9 | Folders | 36 | £3.62 |
How it works
A stock sheet keeps track of what's on the shelf. Value is quantity × unit cost. A reorder flag uses IF with the item's own reorder level.
Orders often come in whole packs: CEILING(amount, pack size) rounds up to the next whole pack. ROUNDDOWN(…,0) gives whole weeks, because a part-week of stock won't last a full week.
=B5*D5 =IF(B5<=C5,"Order now","") =CEILING(C5-B5,D5) =SUMIF(E2:E5,"Ashby",B2:B5) =ROUNDDOWN(B5/D5,0)
When to reorder
Each item has a reorder level: when stock falls to it or below, it's time to order. =IF(B2<=C2,"Reorder","") flags those rows.
Suppliers sell in packs, so round the amount you need up to a whole number of packs with CEILING(amount, pack size). CEILING(10, 12) is 12, and CEILING(25, 12) is 36.
Worked example
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | In stock | Reorder at | Pack size | Supplier | Flag | Order |
| 2 | Pens | 40 | 50 | 12 | Linden | ||
| 3 | Folders | 120 | 60 | 25 | Ashby | ||
| 4 | Labels | 15 | 30 | 10 | Linden | ||
| 5 | Staples | 70 | 40 | 5 | Ashby |
=IF(B2<=C2,"Reorder","")in F2. Pens (40, reorder at 50) and labels (15, reorder at 30) are at or below their level.=CEILING(C2-B2,D2)in G2. To get pens back to 50 you need 10 more, but they come in packs of 12, so order 12.=SUMIF(E2:E5,"Linden",B2:B5)in G4. SUMIF adds the stock from one supplier: Linden's pens and labels, 55 in all.
Values and whole packs
- Stock value is quantity times cost:
=B2*C2. - How many full boxes can you make up? Divide and round down:
ROUNDDOWN(B2/C2, 0). 70 items in boxes of 12 make 5 full boxes, not 6. - Round up (CEILING, ROUNDUP) when you must have enough; round down when only whole ones count.
What you'll practise
- In D2, work out the value of the stock of each item: the quantity × the unit cost.
- Each item is reordered when its stock falls to its reorder level or below. In D2, show Reorder for those items, and leave the cell blank otherwise.
- Each item is topped up to its target level, but it only comes in packs of the size in column D. In E2, work out how many items to order: enough to reach the target, in whole packs.
- In H2, total the stock value for the category typed in H1 (Stationery at the moment). Use H1, so changing it changes the total.
- In D2, work out how many whole weeks the stock of each item will last at its weekly usage.