Voorraad voorspellen in Excel, stap voor stap
Je hebt geen software nodig om je voorraad te voorspellen. Je hebt één export uit Shopify nodig, vier formules en een half uurtje. Hieronder de hele route in Excel of Google Sheets, en onderaan het ingevulde sjabloon gratis. Of pak hem meteen:
Excel en Google Sheets, formules zitten er al in. Geen e-mail nodig.
Wat voorspellen hier eigenlijk betekent
Vergeet het woord even. Je beantwoordt gewoon twee vragen per product: bij welke voorraad moet ik bestellen, en hoeveel koop ik dan in. Krijg je die twee goed, dan grijp je niet meer mis op je bestsellers zonder je geld vast te zetten in langzame lopers. Alles hieronder dient alleen die twee getallen.
De vijf stappen
- 1Exporteer je verkoop uit Shopify
Ga naar Analytics, dan Reports, en open "Sales by product variant SKU". Zet de periode op de laatste 30 dagen en exporteer naar CSV. Je wilt verkochte stuks per SKU, geen omzet, want een prijswijziging vertekent je vraagbeeld.
- 2Houd twee kolommen over
Gooi alles weg behalve SKU en verkochte stuks. Meer verkoopdata heeft de berekening niet nodig. Verkoop je ook aan de groothandel of via bol.com, tel die stuks dan op bij dezelfde regel. Je koopt in op je totale vraag, niet op één kanaal.
- 3Zet je leverancier ernaast
Voeg per SKU drie getallen toe die je al kent: levertijd in dagen (van bestellen tot verkoopklaar, en neem wat er echt gebeurt in plaats van wat er in de offerte stond), een buffer in dagen, en hoe lang je wilt dat een bestelling meegaat.
- 4Voeg de vier formules toe
Dit is de hele motor, en het zijn vier kolommen: gemiddelde per dag, veiligheidsvoorraad, bestelpunt en bestelhoeveelheid. Trek ze door over al je regels en de sheet doet de rest.
- 5Zet er een signaal op en check wekelijks
Eén extra kolom vergelijkt je voorraad met je bestelpunt en zegt JA of nee. Sorteer daarop, bestel wat eruit springt, en zet een kwartier per week in je agenda om de verkoopkolom bij te werken.
De vier formules
In gewone woorden in plaats van celverwijzingen, zodat je ze kunt neerzetten waar jouw kolommen toevallig staan.
| Kolom | Formule | Wat het je zegt |
|---|---|---|
| Gemiddelde per dag | = verkochte stuks / 30 | Je basisvraag. |
| Veiligheidsvoorraad | = gemiddelde per dag * bufferdagen | Marge voor drukke weken. |
| Bestelpunt | = gemiddelde per dag * levertijd + veiligheidsvoorraad | Hier bestel je bij. |
| Bestelhoeveelheid | = gemiddelde per dag * (levertijd + dekkingsdagen) - voorraad - onderweg | Hoeveel je koopt. |
Je verkocht 360 stuks in de laatste 30 dagen, dus 12 per dag. Je leverancier doet er 14 dagen over en je houdt 7 dagen buffer aan. Veiligheidsvoorraad is 12 x 7 = 84. Bestelpunt is 12 x 14 + 84 = 252. Zodra dat product op 252 stuks staat, bestel je. Wil je dat een bestelling weer 28 dagen meegaat, dan is de hoeveelheid 12 x (14 + 28) = 504, min wat je hebt en wat al onderweg is.
Verander nu alleen je leverancier en de uitkomst schuift flink op. Bij 60 dagen levertijd met 21 dagen buffer bestel je hetzelfde product al bij 972 stuks in plaats van 252. Je bestelpunt gaat vooral over je leverancier, niet over je product.
Let op als je Excel Nederlands is
Twee dingen waar Nederlandse ondernemers standaard op stuklopen, dus het is handig om ze vooraf te weten.
- Andere functienamen
In een Nederlandse Excel is IF gewoon ALS, en ROUND wordt AFRONDEN. Dezelfde formule, andere naam.
- Puntkomma in plaats van komma
Nederlandse Excel scheidt argumenten met een puntkomma. Kopieer je een formule van een Engelstalige site, dan moet je de komma's omzetten.
Ons sjabloon is een xlsx-bestand en daarin worden formules taalonafhankelijk opgeslagen. Je Excel laat ze dus vanzelf in het Nederlands zien en ze rekenen meteen goed. Dat is ook precies waarom we geen kaal CSV-bestand aanbieden: daarin zouden de formules bij jou stukgaan.
Je buffer kiezen
De buffer is het enige getal dat je zelf moet inschatten. Een simpel startpunt dat prima werkt:
- 7 dagen
Nederlandse leverancier, ongeveer 14 dagen levertijd.
- 14 dagen
Elders in de EU, ongeveer 30 dagen.
- 21 dagen
Azië, ongeveer 60 dagen.
De logica is simpel: hoe langer je op voorraad wacht, hoe meer er ondertussen mis kan gaan, dus hoe groter je marge moet zijn. Heeft een leverancier je al eens laten zitten, wees dan ruimhartig. Wil je je buffer liever netjes bepalen op basis van je schommeling en een gekozen servicegraad, gebruik dan de herbestelpunt-calculator.
Het ingevulde sjabloon
In plaats van zelf bouwen kun je de onze pakken. Alle formules hierboven zitten er al in, er staan een paar voorbeeldregels in zodat je het ziet werken, en op een tweede tabblad staat per kolom uitgelegd wat er gebeurt. Werkt in Excel en Google Sheets.
Geen e-mail, geen account. Gooi de voorbeeldregels weg en plak je eigen SKU's erin.
Wanneer Excel het niet meer redt
Even eerlijk, want we zien liever dat je de sheet gebruikt dan dat je betaalt voor iets wat je niet nodig hebt. Bij een simpele situatie houdt Excel het prima vol. Het gaat wringen zodra:
- Je voorraad op meerdere plekken ligt
Eigen magazijn plus een 3PL betekent een bestelpunt per locatie in plaats van per product, plus de vraag wat je waarheen verplaatst.
- Je via meerdere kanalen verkoopt
Webshop plus groothandel betekent vraag uit twee systemen optellen voordat je iets kunt berekenen.
- Je meerdere leveranciers hebt
Elke leverancier heeft zijn eigen levertijd, en die schuift. Je sheet weet alleen wat je er als laatste in typte.
- Seizoen en acties meespelen
Een plat gemiddelde over 30 dagen ziet Sinterklaas niet aankomen, en een product dat net begint te lopen ook niet.
Merk op dat geen van die punten over je aantal producten gaat. Tien SKU's over twee magazijnen en drie kanalen is meer werk dan honderd in één magazijn met één kanaal.
Of laat het vanzelf gaan
OrderBee maakt precies deze berekening, voor elke SKU, elke dag, rechtstreeks uit je Shopify-verkoophistorie. Geen exports, geen kopieerwerk. Het rekent met de echte levertijd per leverancier, trekt af wat al onderweg is, rondt af op hele dozen en geeft je de datum waarop je moet bestellen. Daarnaast ziet het trend en seizoen in plaats van een plat gemiddelde, en telt het je groothandel- en B2B-verkoop uit Moneybird, WeFact of CSV mee.
Veelgestelde vragen
Ja, en voor veel kleine webshops is dat ook gewoon genoeg. Je exporteert je verkoop van de laatste 30 dagen uit Shopify, rekent je gemiddelde verkoop per dag uit, telt je levertijd en een buffer erbij en je hebt een bestelpunt per product. Dat is in de kern precies wat betaalde voorraadsoftware doet. Waar Excel op stukloopt is bijhouden: de cijfers veranderen elke week en je moet het handmatig opnieuw doen.
Ga naar Analytics, dan Reports, en open "Sales by product variant SKU". Zet de periode op de laatste 30 dagen en exporteer naar CSV. Dat rapport geeft je verkochte stuks per SKU, en meer verkoopdata heb je niet nodig. Gebruik geen omzetrapport, want een prijswijziging vertekent dan je vraag.
Ja, maar let op twee dingen. In een Nederlandstalige Excel heten functies anders (ALS in plaats van IF, AFRONDEN in plaats van ROUND) en scheid je argumenten met een puntkomma in plaats van een komma. Ons sjabloon is een xlsx-bestand, en daarin worden formules taalonafhankelijk opgeslagen. Je Excel toont ze dus gewoon in het Nederlands en ze rekenen meteen goed.
Dertig dagen is een werkbaar minimum en dat gebruiken we in deze gids. Negentig dagen is beter, omdat één uitzonderlijk goede of slechte week dan minder zwaar meetelt. Verkoop je seizoensgebonden, kijk dan ook naar dezelfde periode vorig jaar in plaats van alleen naar een voortschrijdend gemiddelde.
Zodra het aantal combinaties oploopt. Voorraad op meerdere locaties, verkoop via meerdere kanalen, of inkopen bij meerdere leveranciers met verschillende levertijden vermenigvuldigen allemaal het aantal regels dat je moet onderhouden. Het aantal producten zegt daarbij minder dan je denkt: tien SKU’s over twee magazijnen en drie kanalen is meer werk dan honderd in één magazijn met één kanaal.