Case studies › IF138 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeIF

Case study: reorder point for supplies

Every morning Bayu checks the supplies at his coffee shop in Malang. Suppliers take a few days to deliver, so he has to order before stock runs out, plus a safety buffer for busy days.

Task

Fill in two columns:

F2:F6 Reorder point = daily usage × lead time + safety stock.
G2:G6 Status with IF: write Reorder if the current stock is ≤ the reorder point, otherwise OK.

Cells to fillF2:F6G2:G6

Required functionsIF

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
The reorder point is the stock used up while waiting for delivery (usage × lead time), plus the buffer.
Hint 2
The status condition compares the current stock (B2) with the result in F2 using <=.
Hint 3
Pattern for G2: =IF(B2<=F2, "Reorder", …).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

F2:F6

=C2*D2+E2

Write it in F2, then fill down to F6.

G2:G6

=IF(B2<=F2,"Reorder","OK")

Write it in G2, then fill down to G6.

Explanation

For UHT milk: 12 litres a day × 2 days + 10 litres of buffer = 34 litres. Stock is 30 litres, already below that point, so =IF(B3<=F3,"Reorder","OK") returns Reorder. Coffee beans sit exactly at the reorder point (18 kg), and because the formula uses <=, they are also flagged. With <, the beans would slip through and be ordered too late. Tip: add red conditional formatting for Reorder so it stands out.

Starting worksheet: Sheet1
Row	A	B	C	D	E	F	G
1	Item	Current stock	Daily usage	Lead time (days)	Safety stock	Reorder point	Status
2	Coffee beans (kg)	18	3	4	6
3	UHT milk (litres)	30	12	2	10
4	Palm sugar (kg)	15	1.5	3	2
5	Plastic cups (pcs)	600	150	3	100
6	Straws (pcs)	220	140	2	50
7
8
9
10
11
12
13
14

Fill in the requested cells, then press Check answer.

Validation result