KHIZHNYAKOVInventur mit Excel: Bestandsliste, Abgleich und Abweichungen berechnen
Inventur in Excel – Vorlage — Zur Vorlage →
Bei einer Inventur ist das Zählen selten das Problem. Anstrengend wird es danach: Zählzettel zusammentragen, Zahlen abtippen, mit dem Buchbestand vergleichen, Abweichungen herausfiltern, Differenzen bewerten und alles für die Unterschrift der Kommission aufbereiten. Mit der richtigen Excel-Tabelle erledigt sich der größte Teil davon von selbst.
Im Folgenden sehen Sie, wie Sie Bestandsliste und Abgleich aufbauen, wie die Formeln aussehen und wie ein kleines Zahlenbeispiel aussieht.
Vorbereitung: Was vor dem Zählen passiert
Eine Inventur gelingt, wenn drei Dinge vorher geklärt sind:
- Stichtag und Umfang: Welches Lager, welche Bereiche, welcher Zeitpunkt? Ab dem Stichtag sollten möglichst keine Warenbewegungen stattfinden.
- Kommission: Wer zählt, wer kontrolliert, wer leitet? Zählen zwei Personen unabhängig voneinander, sinkt die Fehlerquote.
- Buchbestand einfrieren: Exportieren Sie den Sollbestand aus der Warenwirtschaft oder Ihrer Liste zum Stichtag. Nur dann ist der Vergleich sauber.
Welche gesetzlichen oder internen Vorgaben für Ihre Inventur gelten, klären Sie mit Ihrem Steuerberater oder Ihrer Buchhaltung.
Schritt 1: Die Bestandsliste
Legen Sie eine Tabelle mit diesen Spalten an:
| Spalte | Inhalt |
|---|---|
| A | Artikelnummer |
| B | Bezeichnung |
| C | Einheit |
| D | Preis in EUR |
| E | Buchbestand |
| F | Ist-Bestand (wird beim Zählen eingetragen) |
| G | Differenz (berechnet) |
| H | Wert der Differenz (berechnet) |
| I | Status (berechnet) |
Eine Zeile pro Artikel. Wichtig: Der Ist-Bestand bleibt leer, bis der Artikel gezählt wurde. Eine „0“ bedeutet „gezählt, nichts da“, eine leere Zelle bedeutet „noch nicht gezählt“.
Schritt 2: Differenzen berechnen
Die Differenz ist Ist minus Buch. Berechnen Sie sie nur, wenn der Artikel gezählt wurde:
=WENN(F2="";"";F2-E2)
Der Wert der Differenz folgt aus Preis mal Differenz:
=WENN(G2="";"";G2*D2)
Der Status benennt, was los ist:
=WENN(F2="";"offen";WENN(G2>0;"Überbestand";WENN(G2<0;"Fehlbestand";"stimmt")))
Ein positiver Wert heißt: Es ist mehr da als gebucht (Überbestand). Ein negativer Wert heißt: Es fehlt etwas (Fehlbestand).
Ein Zahlenbeispiel
Fünf Artikel eines kleinen Werkstattlagers:
| Artikel | Preis | Buch | Ist | Differenz | Wert in EUR |
|---|---|---|---|---|---|
| Schrauben M6 (Stück) | 0,08 | 1.200 | 1.180 | -20 | -1,60 |
| Dichtmasse-Kartusche | 6,50 | 40 | 44 | +4 | +26,00 |
| Arbeitshandschuhe (Paar) | 1,90 | 150 | 138 | -12 | -22,80 |
| Kabeltrommel | 24,00 | 12 | 12 | 0 | 0,00 |
| Akkuschrauber | 89,00 | 5 | 4 | -1 | -89,00 |
Der Buchwert beträgt 96 + 260 + 285 + 288 + 445 = 1.374 Euro. Der Fehlbestand summiert sich auf 1,60 + 22,80 + 89,00 = 113,40 Euro, der Überbestand auf 26,00 Euro. Per Saldo fehlen also 87,40 Euro. Die Abweichungsquote (Fehl- plus Überbestand im Verhältnis zum Buchwert) beträgt (113,40 + 26,00) / 1.374 = 10,1 %.
Die Summen berechnen Sie mit SUMMEWENN:
=SUMMEWENN(I2:I500;"Fehlbestand";H2:H500)
Schritt 3: Die Abgleichsliste
Für die Kommission interessieren nur die Positionen mit Abweichung. Statt sie mühsam zu kopieren, filtern Sie die Bestandsliste nach Status ungleich „stimmt“ und „offen“, oder nutzen in neueren Excel-Versionen die Funktion FILTER. In diesem Beispiel wären es vier Zeilen, die zu klären sind. Notieren Sie neben jeder Abweichung die Ursache: Bruch, Schwund, falsche Buchung, Lieferung nicht gebucht.
Häufige Fehler
- Zählen während des Betriebs. Wenn gleichzeitig Ware ausgegeben wird, stimmt nichts mehr.
- Leere Zelle und Null verwechseln. Sonst werden ungezählte Artikel als Fehlbestand ausgewiesen.
- Mischeinheiten. Stück, Karton und Palette in einer Spalte führen zu falschen Summen. Legen Sie die Einheit fest.
- Abweichungen ohne Erklärung. Eine Differenz, deren Ursache niemand untersucht, kommt bei der nächsten Inventur wieder.
- Nur eine Zählung. Bei größeren Abweichungen hilft eine Nachzählung der betroffenen Positionen.
Die fertige Excel-Vorlage
Die kostenpflichtige Vorlage „Inventur in Excel“ bringt diese Tabellen fertig mit:
- „Angaben“: Organisation, Anordnung, Daten, Kommission und verantwortliche Person für das Inventar.
- „Bestandsliste“: bis zu 500 Positionen mit Buchbestand und Ist-Bestand.
- „Abgleich“: nur Positionen mit Abweichungen, bis zu 100 Zeilen, druckfertig.
- „Ergebnisse“: Summen nach Buchbestand und Ist-Bestand, Überbestände und Fehlbestände, Abweichungsquote und Unterschriften.
Wer in Etappen prüft, kann das ohne Weiteres tun: Ungeprüfte Positionen ohne Ist-Bestand fließen nicht in die Abweichungen ein, und auf den „Ergebnissen“ sehen Sie, wie viele noch offen sind. Die Datei läuft ohne Makros in Excel 2007 und neuer sowie in LibreOffice und ist auf Deutsch. Im Paket sind eine leere Tabelle und ein ausgefülltes Muster mit fiktiven Daten. Es sind Arbeitsformulare nach dem Muster üblicher Inventurunterlagen, keine amtlichen Vordrucke. Sie eignet sich für Büro- und Lagerverantwortliche, Buchhalter und Kommissionsvorsitzende in Organisationen, Geschäften und Lagern. Prüfen Sie Parameter und Richtwerte vor der Arbeit anhand der geltenden Vorschriften.
FAQ
Wie oft sollte ich eine Inventur machen?
Mindestens einmal im Jahr zum Stichtag. In Betrieben mit hohem Warenwert oder vielen Bewegungen lohnen häufigere Teilinventuren, etwa monatlich für die wertvollsten Artikel.
Wie gehe ich mit Differenzen um?
Erst nachzählen, dann die Buchungen prüfen, dann die Ursache dokumentieren. Erst wenn beides keine Erklärung liefert, wird die Differenz als Bestandsabweichung verbucht. Wie, entscheidet Ihre Buchhaltung.
Brauche ich Barcodescanner?
Nicht zwingend. Für wenige hundert Artikel genügen Zählzettel und eine saubere Eingabe in die Tabelle. Bei mehreren tausend Positionen sparen Scanner Zeit und verringern Tippfehler.
Kann ich Teilbereiche getrennt inventieren?
Ja. Teilen Sie die Liste nach Lagerbereich auf oder vergeben Sie eine Spalte „Bereich“, und filtern Sie danach. So können mehrere Teams parallel zählen.
