The brief
Toko Kita is a small Indonesian online shop that sells coffee, kitchen equipment, stationery, home goods and a few electronics. The warehouse keeps one sheet with every product on it: what it sells for, what it costs, how many are on the shelf, how many sold in the first half of the year, and the level at which someone should order more.
Right now that sheet is just numbers. Every Monday, somebody goes down the list with a calculator to see what to reorder, and it takes most of the morning. The owner has asked you to turn it into a sheet that does the work by itself, so that when the stock numbers change, everything else updates without anyone touching it.
How the tasks work
This is a little different from the module questions. Each task gives you the stock sheet with an editor next to it, and you write cells the same way the lessons do, one per line:
H1: Stock value
H2:H11: =E2*D2 | #,##0The first line types a heading into H1. The second writes a formula into H2 and fills it down to H11, just like dragging the fill handle, and | #,##0 sets the number format. The sheet on the right recalculates as you type, so you can see straight away whether a formula does what you meant.
The checks look at the values your formulas produce, and sometimes at which function you used. They don't accept typed-in answers where a task asks for a formula, because the whole point is that the sheet keeps working when the numbers change. There are hints if you get stuck, but try each task without opening the solution first.
The tasks
| # | Task | Main skills |
|---|---|---|
| 1 | Stock value and margin | Formulas, filling down, SUM, number formats |
| 2 | What needs reordering? | IF, AND, COUNTIF |
| 3 | A lookup box | XLOOKUP (or INDEX/MATCH), absolute references, a fallback |
| 4 | A category summary | UNIQUE, SORT, SUMIFS, COUNTIF, AVERAGEIF |
The sheet
Every task starts from this sheet, with the products in rows 2 to 11:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Cost | Stock | Sold | Reorder at |
| 2 | French Press 600ml | Kitchen | 185000 | 110000 | 24 | 2 | 10 |
| 3 | Pour-over Kettle | Kitchen | 320000 | 210000 | 6 | 7 | 8 |
| 4 | Arabica Gayo 250g | Coffee | 95000 | 52000 | 80 | 7 | 30 |
| 5 | Robusta Lampung 250g | Coffee | 65000 | 35000 | 120 | 4 | 30 |
| 6 | Toraja Kalosi 250g | Coffee | 110000 | 64000 | 25 | 8 | 30 |
| 7 | Ceramic Mug | Kitchen | 55000 | 22000 | 150 | 0 | 40 |
| 8 | Gel Pen Set | Stationery | 38000 | 15000 | 0 | 2 | 20 |
| 9 | Wireless Mouse | Electronics | 175000 | 98000 | 60 | 1 | 10 |
| 10 | USB-C Hub | Electronics | 289000 | 170000 | 4 | 12 | 10 |
| 11 | Scented Candle | Home | 75000 | 30000 | 90 | 8 | 25 |
Price and Cost are in rupiah per unit: what the customer pays and what the shop paid. Stock is how many are on the shelf today, Sold is how many sold from January to June, and Reorder at is the stock level at which the warehouse wants to order more.
When you're ready, open Task 1.