KHIZHNYAKOVVerfallsdatum-Kontrolle in Excel: Chargen, Resttage und Rabatte
Verfallsdatenkontrolle für Waren in Excel: Chargen und Rabatte — Excel-Vorlage — Zur Vorlage →
Im Laden, im Café oder im Lager passiert das immer wieder: Hinten im Regal steht eine Charge, deren Datum seit gestern abgelaufen ist. Entsorgen heißt Geld wegwerfen. Hätte man die Ware drei Tage früher reduziert, wäre sie verkauft worden.
Eine einfache Excel-Liste verhindert das. Sie rechnet für jede Charge die Tage bis zum Ablauf aus, bewertet die Dringlichkeit und schlägt einen Rabatt vor. Aufbau und Beispiel finden Sie hier.
Schritt 1: Erfassen Sie Chargen, nicht nur Artikel
Ein Artikel kann in mehreren Lieferungen mit verschiedenen Daten im Regal stehen. Deshalb führen Sie eine Zeile pro Charge. Die Spalten:
- Ware und Kategorie
- Charge oder Lieferungsnummer
- Menge
- Einkaufspreis und Verkaufspreis
- Verfallsdatum (MHD)
- Resttage, Status, Rabatt, reduzierter Preis (berechnet)
Eine Zeile pro Charge ist etwas mehr Arbeit beim Wareneingang. Dafür sehen Sie genau, welche Menge zu welchem Datum abläuft.
Schritt 2: Resttage und Status berechnen
Die Resttage sind der Abstand zum heutigen Tag:
=F2-HEUTE()
Der Status ergibt sich aus zwei Schwellen, die Sie in Parameterzellen festlegen, zum Beispiel 14 Tage für „Bald“ und 3 Tage für „Kritisch“:
=WENN(G2<0;"Abgelaufen";WENN(G2<=Kritisch;"Kritisch";WENN(G2<=Bald;"Bald";"OK")))
Die Schwellen unterscheiden sich je nach Ware. Frischfleisch braucht andere Werte als Konserven. Wenn Ihr Sortiment sehr gemischt ist, führen Sie die Schwellen je Kategorie in einer Tabelle und holen sie per SVERWEIS.
Schritt 3: Rabatt vorschlagen
Zu jedem Status gehört eine Aktion. Ein Beispiel für ein Lebensmittelgeschäft:
| Status | Resttage | Aktion | Rabatt (Beispiel) |
|---|---|---|---|
| OK | über 14 | normal verkaufen | 0 % |
| Bald | 4 bis 14 | leicht reduzieren | 20 % |
| Kritisch | 0 bis 3 | stark reduzieren | 50 % |
| Abgelaufen | unter 0 | aussondern |
Die Prozentsätze sind Beispielwerte, legen Sie Ihre eigenen fest. Der reduzierte Preis ist =Verkaufspreis*(1-Rabatt), und den Rabatt holt SVERWEIS aus der Statustabelle.
Schritt 4: Ein Beispiel mit Zahlen
Heute ist der 1. November.
| Ware | Menge | MHD | Resttage | Status | Einkauf | Verkauf | reduziert | Wert (Einkauf) |
|---|---|---|---|---|---|---|---|---|
| Joghurt | 20 | 10.11. | 9 | Bald | 0,60 | 1,29 | 1,03 | 12,00 |
| Frischkäse | 15 | 03.11. | 2 | Kritisch | 1,10 | 2,49 | 1,25 | 16,50 |
| Hähnchenbrust | 8 | 31.10. | −1 | Abgelaufen | 4,20 | 7,90 | 33,60 | |
| Milch | 40 | 25.11. | 24 | OK | 0,70 | 1,19 | 1,19 | 28,00 |
Der Einkaufswert der gefährdeten Ware (Bald und Kritisch) beträgt 12,00 + 16,50 = 28,50 Euro. Abgelaufen sind 33,60 Euro. Diese Zahl tut weh und ist deshalb nützlich: Sie zeigt, was das Abwarten kostet. Berechnung mit SUMMENPRODUKT:
=SUMMENPRODUKT((E2:E300="Abgelaufen")*C2:C300*H2:H300)
Dabei ist C die Menge und H der Einkaufspreis.
Schritt 5: Eine tägliche Aktionsliste
Der größte Gewinn liegt in einem Blatt, das nur zeigt, was heute zu tun ist: alle Chargen mit Status „Kritisch“, „Bald“ oder „Abgelaufen“, sortiert nach Resttagen. In neueren Excel-Versionen erledigt die Funktion FILTER das, in älteren der Autofilter mit Sortierung. Ein Blick morgens beim Öffnen genügt.
Häufige Fehler
- Nur Artikel, keine Chargen. Dann sieht man nur das früheste Datum und weiß nicht, wie viel davon betroffen ist.
- Mindesthaltbarkeitsdatum und Verbrauchsdatum verwechseln. Nach dem Verbrauchsdatum darf bestimmte Ware nicht mehr abgegeben werden. Klären Sie die Regeln für Ihr Sortiment.
- Neue Ware vor die alte stellen. Sortieren Sie nach dem Prinzip „first in, first out“: Älteres nach vorn.
- Zu späte Reduzierung. Wer erst am letzten Tag reduziert, verkauft kaum noch etwas.
- Liste nicht pflegen. Abverkaufte Chargen sollten gelöscht oder archiviert werden, sonst wächst die Liste unübersichtlich.
Die fertige Excel-Vorlage
Die kostenpflichtige Vorlage „Verfallsdatenkontrolle für Waren“ enthält:
- „Einstellungen“: Wie viele Tage vor Ablauf als „Bald“ und „Kritisch“ gelten (standardmäßig 14 und 3), und der Rabatt für jeden Status.
- „Chargen“: Ware, Kategorie, Charge, Menge, Einkaufs- und Verkaufspreis, Verfallsdatum. Resttage, Status, Rabatt und reduzierter Preis werden automatisch berechnet. Bis zu 300 Chargen.
- „Heute handeln“: automatische Liste der Chargen, die reduziert oder ausgesondert werden sollten, mit Rabatt und gefährdetem Betrag.
- „Übersicht“: wie viele Chargen abgelaufen, kritisch und bald fällig sind, Wert der gefährdeten Ware und ein Diagramm.
Ein Barcode wird nicht benötigt, die Chargen werden nach Name und Lieferungsnummer erfasst. 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. Sie eignet sich für Ladeninhaber, Lagerleiter, Verkäufer von Lebensmitteln, Cafés und Apotheken. Schwellenwerte und Normwerte sind Parameter: Prüfen Sie sie vor der Arbeit anhand der geltenden Vorschriften.
FAQ
Wie oft sollte ich die Liste prüfen?
Bei Frischware täglich, bei Trockenware einmal pro Woche. Die tägliche Aktionsliste macht die Kontrolle zur Sache weniger Minuten.
Welche Schwellen sind sinnvoll?
Das hängt von der Ware ab. Als Ausgangspunkt dienen 14 und 3 Tage, für Frischprodukte setzen Sie die Werte niedriger. Beobachten Sie, ab wann die Ware tatsächlich nicht mehr verkauft wird.
Was mache ich mit Ware kurz vor Ablauf, die nicht mehr verkauft wird?
Je nach Sortiment kommen Spende, Verarbeitung im eigenen Betrieb oder Entsorgung infrage. Was zulässig ist, hängt von der Warenart ab, erkundigen Sie sich bei Ihrer Behörde oder Ihrem Verband.
Lohnt sich das für ein kleines Geschäft?
Ja, schon bei wenigen Dutzend Chargen. Eine einzige gerettete Lieferung bringt oft mehr, als die Pflege der Liste an Zeit kostet.
