Simulating a WMS in Excel isn't cheating: it's what's left when the real tool costs as much as a car. The catch is that a colour-coded table only teaches students to colour a table. Here's how to turn it into an exercise that prepares them for the job — without buying anything.
Keep the method next to your spreadsheet.
The BTS GTLA syllabus expects students to master a WMS. But dedicated teaching tools are rare, and many classrooms work on a makeshift spreadsheet. That's not a lack of skill — it's a lack of resources.
Good news: three constraints are enough to take that spreadsheet from "colouring" to "flow reasoning". No licence required.
Starting structure
Two sheets in the workbook: Movements (each in/out entered row by row) and Stock (the recalculated state, never typed by hand). The three constraints below apply to those two sheets.
Three tweaks, and the student reasons about addressing, transactions and a picking rule instead of filling in cells.
A mandatory Location column, in aisle-bay-level format (e.g. A-03-2). No more "back left corner": the student learns addressing, which is the heart of any WMS.
Data validation (Excel) to enforce the format:
Données > Validation des données > Personnalisé =ET(NBCAR(B2)=6; STXT(B2;2;1)="-"; STXT(B2;5;1)="-")
Rejects any entry that isn't letter-digits-digit. Adjust the positions to your own coding scheme.
You record in Movements (with a Direction column = +1 in / -1 out). The Stock sheet recalculates on its own: the student experiences the difference between a transaction and a stock state — exactly the logic of a WMS.
Stock quantity per reference (Stock sheet):
=SOMME.SI.ENS(Mouvements!D:D; Mouvements!A:A; A2)
D = signed Quantity column (quantity × direction), A = Reference. Stock is never typed: it's derived.
An Entry date column (or use-by date) and a sort that enforces the picking order. The student no longer recites the first-in, first-out rule: they live it, so they understand it.
Identify the batch to pick first (FIFO):
=INDEX(Lots[Lot]; EQUIV(MIN(SI(Lots[Réf]=A2; Lots[DateEntrée])); Lots[DateEntrée]; 0))
Array formula (Ctrl+Shift+Enter on older versions). Returns the oldest batch for the requested reference — the one the student must pick.
The spreadsheet stays a spreadsheet. But with these three constraints, the student reasons about addressing, transactions and a picking rule — the three reflexes they'll meet in front of a real WMS in a company. You're assessing on a professional tool; at least you'll have trained them on the same logic.
Dataset tip
To fill the Movements sheet quickly, ask an AI assistant: "generate 120 realistic stock rows (reference, description, quantity, aisle-bay-level location, entry date), with 5 inconsistencies for students to find, and give me the list of inconsistencies separately". Two minutes instead of an evening.
If you'd like to talk it through without a sales pitch — see what a real professional tool adds, or just compare notes on your practical sessions — book 30 minutes. Just sharing, nothing else.
About this resource
Method written by Frédéric Kulas, founder of KLS-Concept. Deliberately published without a logo in the body: usefulness comes before the brand. The formulas adapt to Excel, LibreOffice Calc and Google Sheets (adjust the function names to your spreadsheet's language). Source: BTS GTLA 2024 syllabus.
Updated: June 2026 — free to use.