Voorraadbeheer Excel - Gratis Voorbeeld

1. Stap 1 — Open Stamgegevens en vul per artikel een unieke artikelcode, naam, categorie, leverancier, eenheid en inkoopprijs in.
2. Stap 2 — Ga naar Voorraad en kies een artikelcode in kolom A. De gekoppelde productinformatie verschijnt automatisch.
3. Stap 3 — Vul locatie, actuele voorraad, minimumvoorraad en bestelhoeveelheid in. Gebruik hele getallen vanaf 0 voor deze velden.
4. Stap 4 — Leg de laatste telling en eventuele verwachte levering vast als datum in het formaat DD-MM-YYYY.
5. Stap 5 — Zet in kolom Besteld Ja of Nee en noteer bijzonderheden in Opmerking.
6. Stap 6 — Controleer Status en Onder minimum. Bekijk daarna Dashboard voor de voorraadwaarde, besteladviezen en grafieken.
7. Stap 7 — Gebruik Instructies als naslag bij nieuwe artikelen, datumvelden, valuta en de betekenis van de statussen.

Een magazijnmedewerker, inkoper of financieel medewerker gebruikt dit bestand om de dagelijkse voorraadcontrole op één plek vast te leggen. In een groothandel met 80 artikelen kun je bijvoorbeeld iedere maandag de aantallen tellen, de locatie controleren en direct zien welke producten onder de ingestelde grens komen.

Het tabblad Voorraad is het operationele werkblad. Figuur 1 toont de kolommen Artikelcode, Artikelnaam, Categorie, Leverancier, Locatie, Eenheid, Voorraad, Minimumvoorraad, Bestelhoeveelheid, Inkoopprijs, Voorraadwaarde, Status, Laatste telling, Verwachte levering, Besteld, Opmerking en Onder minimum. De gele cellen zijn bedoeld voor invoer; de gekoppelde velden worden berekend.

Artikelgegevens centraal onderhouden

Op Stamgegevens staat de productcatalogus met zes kolommen. Figuur 2 laat onder meer ITM-001 voor een laptopstandaard, ITM-002 voor een USB-C-dockingstation en ITM-003 voor een ergonomische bureaustoel zien. Een wijziging van de inkoopprijs gebeurt daar, niet in iedere voorraadregel afzonderlijk.

Van telling naar bestelactie

Staat er 7 stuks terwijl de minimumvoorraad 8 is, dan verschijnt Bestellen. Bij 10 stuks tegenover een minimum van 8 wordt de status Bijna minimum, omdat 10 niet meer dan 25% boven 8 ligt. Bij 12 tegenover 5 wordt Voldoende. De kolom Besteld blijft een handmatige bevestiging: zet die pas op Ja nadat de bestelling werkelijk is geplaatst.

Voor een kantoor, installatiebedrijf of kleine webshop is deze werkwijze voldoende voor periodieke controle. De instructies op Figuur 4 leggen bovendien uit welke velden je invult en welke waarden automatisch ontstaan.

Dit sjabloon berekent een administratieve voorraadwaarde op basis van actuele aantallen en de ingevoerde inkoopprijs. Dat is praktisch voor interne sturing, maar de uitkomst is niet automatisch een volledige fiscale voorraadwaardering volgens Boek 2 BW, waaronder artikel 2:384 BW over waarderingsgrondslagen en voorzichtigheid.

Figuur 3 toont op het Dashboard de totale voorraadwaarde, de gemiddelde voorraadwaarde per artikel, het aantal artikelen met besteladvies en een indicator voor artikelen op of onder minimum. De formule SOM telt de waarden uit K5:K104 op. Bij 24 laptopstandaarden à € 29,95 is de waarde € 718,80; bij 7 dockingstations à € 119,00 is dat € 833,00.

Wat de formules precies doen

In Voorraad gebruiken de opzoekvelden een bereik van Stamgegevens A5:F104. De formule werkt met VERT.ZOEKEN en een exacte overeenkomst; een niet-bestaande code levert een lege cel op. De voorraadwaarde gebruikt voorraad maal inkoopprijs, terwijl de statusformule de minimumvoorraad vergelijkt met de actuele voorraad.

Het Dashboard gebruikt SOM.ALS om de waarde per categorie te berekenen voor acht vaste categorieën, waaronder Kantoorartikelen, Elektronica en Meubilair. De statusverdeling gebruikt AANTAL.ALS voor Bestellen, Bijna minimum en Voldoende. Er zijn geen draaitabellen of extra gedefinieerde namen aanwezig, dus voeg je categorieën toe buiten de bestaande Dashboardlijst dan moet je die samenvatting handmatig uitbreiden.

Voorraad voor de jaarrekening

Bij handelsvoorraad moet je aantallen, inkoopfacturen, retouren en eventuele incourantheid aansluiten op de administratie. Een bestand met 100 regels kan de operationele telling ondersteunen, maar vervangt geen grootboek, voorraadmutatie-administratie of onderbouwde waarderingsgrondslag in de jaarrekening.

De meest risicovolle fout is een artikelcode dubbel invoeren. Stamgegevens controleert A5:A104 met een unieke-waarderegel, maar een bestaande code overschrijven kan nog steeds verkeerde productinformatie veroorzaken. Als ITM-002 per ongeluk aan een verkeerd artikel wordt gekoppeld, wordt ook de inkoopprijs van € 119,00 bij alle verwijzingen verkeerd weergegeven.

Minimumvoorraad verkeerd interpreteren

Een minimumvoorraad is geen bestelhoeveelheid. Bij een actuele voorraad van 7, minimum 8 en bestelhoeveelheid 12 geeft het bestand Bestellen, maar het verhoogt de voorraad niet automatisch met 12. De gebruiker moet de order plaatsen, Besteld op Ja zetten en na ontvangst de voorraad handmatig bijwerken.

Let ook op de grens van 25%. Bij minimum 20 is 25 precies Bijna minimum, omdat 25 maximaal 25% boven 20 ligt. Bij 26 wordt de status Voldoende. Die grens is technisch correct volgens de formule, maar kan operationeel te ruim zijn voor artikelen met een levertijd van 30 dagen.

Datums en aantallen

Een datum als tekst, bijvoorbeeld 25/09 zonder geldig Excel-datumtype, kan buiten de ingestelde datumcontrole vallen. Gebruik altijd DD-MM-YYYY. De velden Voorraad, Minimumvoorraad en Bestelhoeveelheid accepteren gehele waarden vanaf 0; voer dus geen 2,5 dozen in als je de eenheid dozen gebruikt.

Een tweede fout is uitsluitend naar Dashboard kijken. De grafieken zijn gebaseerd op de bereiken Voorraad G5:G104, B5:B104 en H5:H104, plus de vaste Dashboardlijsten. Controleer daarom altijd de bronregel, vooral bij lege regels, gewijzigde categorieën of een levering die nog niet in de telling is verwerkt.

Ten slotte wordt de indicator Artikelen op of onder minimum berekend als gemiddelde van de 0/1-kolom Onder minimum. Dat resultaat is een percentage in cel E5, geen absoluut aantal. Gebruik voor het aantal bestelacties de waarde bij Artikelen met besteladvies in B5.

Begin met een vaste artikelcodering. Gebruik bijvoorbeeld ITM-001 tot en met ITM-100 en leg de betekenis vast in Stamgegevens. Voor een assortiment van 60 artikelen blijven dan 40 voorbereide regels beschikbaar zonder dat je de structuur hoeft te wijzigen.

Minimum en bestelhoeveelheid bepalen

Neem gemiddeld verbruik en levertijd als uitgangspunt. Verbruik je 4 dozen per week en duurt levering 2 weken, dan is een minimum van 8 een logisch startpunt; voeg daar bij kritische artikelen bijvoorbeeld 2 dozen veiligheidsvoorraad aan toe. Leg de gekozen waarde vast en evalueer na vier tellingen.

• Gebruik Locatie voor magazijnvak, kantoorruimte of vestiging, zoals Amsterdam, Utrecht en Rotterdam.
• Gebruik Categorie consequent; het Dashboard rekent met acht vaste categorieën.
• Noteer uitzonderingen in Opmerking, bijvoorbeeld schade, retour of een geplande telling.
• Gebruik Besteld alleen als Ja wanneer een order daadwerkelijk is geplaatst.

Dashboard uitbreiden

Het Dashboard bevat drie grafieken en gebruikt geen interactieve slicers. Je kunt de bestaande categorieën in H5:H12 onderhouden en de statussen in H16:H18 laten staan. Voor een organisatie met 3 locaties is het praktischer om Locatie in Voorraad te filteren dan om aparte dashboards per locatie te maken.

Wil je voorraadmutaties volgen, voeg dan niet zomaar kolommen toe aan de berekende structuur. Houd dit bestand voor de actuele stand en leg ontvangsten en uitgiften vast in een afzonderlijk mutatieoverzicht. Bij 20 mutaties per werkdag zijn dat ongeveer 5.200 regels per jaar; daarvoor is een apart logboek of voorraadpakket betrouwbaarder dan handmatig overschrijven.

Gebruik Instructies als interne werkinstructie. Spreek af wie de wekelijkse telling uitvoert, wie bestellingen bevestigt en wie de inkoopprijzen op Stamgegevens bijwerkt. Zo blijft het onderscheid tussen invoer en berekende informatie duidelijk.