Confident Cells

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.

Your turn

In D2, work out the value of the stock of each item: the quantity × the unit cost.

ABCD
1ItemQuantityUnit costStock value
2Keyboards36£26.79
3Sticky notes9£14.88
4Spray cleaner43£12.44
5Headsets12£13.41
6Sugar49£28.18
7Bin bags51£8.93
8Mice59£35.76
9Folders36£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

ABCDEFG
1ItemIn stockReorder atPack sizeSupplierFlagOrder
2Pens405012Linden
3Folders1206025Ashby
4Labels153010Linden
5Staples70405Ashby
  1. =IF(B2<=C2,"Reorder","") in F2. Pens (40, reorder at 50) and labels (15, reorder at 30) are at or below their level.
  2. =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.
  3. =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

  1. In D2, work out the value of the stock of each item: the quantity × the unit cost.
  2. 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.
  3. 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.
  4. In H2, total the stock value for the category typed in H1 (Stationery at the moment). Use H1, so changing it changes the total.
  5. In D2, work out how many whole weeks the stock of each item will last at its weekly usage.