Tutorials

Altersstruktur‑Analyse (Ageing Analysis) für offene Rechnungen in Excel – Schritt‑für‑Schritt mit der WENN‑Funktion

Melden

Dieses Tutorial zeigt dir Schritt für Schritt, wie du eine Altersstruktur-Analyse (Ageing Analysis) für offene Rechnungen (Debitor oder Kreditor) in Excel erstellst. Wir nutzen dafür primär die WENN-Funktion (IF).

Was ist eine Ageing Analysis?

Sie dient dazu, offene Beträge nach ihrer Überfälligkeit zu sortieren (z. B. 0–30 Tage, 31–60 Tage usw.). Das hilft Unternehmen, das Mahnwesen zu steuern und den Cashflow zu überwachen.

Schritt 1: Die Datengrundlage vorbereiten

Zuerst benötigst du eine Tabelle mit folgenden Spalten:

  1. Rechnungs-Nr.
  2. Kunde
  3. Fälligkeitsdatum
  4. Betrag
  5. Verzugstage (Hilfsspalte)

Zusätzlich solltest du in einer Zelle (z. B. H1) das aktuelle Datum mit der Formel =HEUTE() fixieren.

Schritt 2: Verzugstage berechnen

In der Spalte „Verzugstage“ berechnen wir, wie viele Tage die Rechnung überfällig ist. Angenommen, das Fälligkeitsdatum steht in Zelle C2 und das heutige Datum in H1.

Formel in E2: =$H$1 - C2 (Hinweis: Wenn das Ergebnis negativ ist, ist die Rechnung noch nicht fällig).

Schritt 3: Die Altersklassen mit der WENN-Funktion erstellen

Wir erstellen nun für jede Altersklasse eine eigene Spalte (z. B. „0–30 Tage“, „31–60 Tage“, „61–90 Tage“, „>90 Tage“).

Spalte F: 0–30 Tage

Hier soll der Betrag erscheinen, wenn die Verzugstage zwischen 1 und 30 liegen. Formel in F2:

=WENN(UND(E2>0; E2<=30); D2; 0)

Erklärung: Wenn die Tage > 0 UND <= 30 sind, nimm den Betrag aus D2, sonst zeige 0.

Spalte G: 31–60 Tage

Formel in G2:

=WENN(UND(E2>30; E2<=60); D2; 0)

Spalte H: 61–90 Tage

Formel in H2:

=WENN(UND(E2>60; E2<=90); D2; 0)

Spalte I: Über 90 Tage

Formel in I2:

=WENN(E2>90; D2; 0)

Spalte J: Noch nicht fällig (Optional)

Formel in J2:

=WENN(E2<=0; D2; 0)

Schritt 4: Alternative – Alle Kategorien in EINER Spalte (Verschachteltes WENN)

Falls du nicht mehrere Spalten möchtest, sondern nur eine Spalte, die den Status anzeigt, nutzt du ein verschachteltes WENN (oder die neuere WENNS-Funktion).

Formel für den Status:

=WENN(E2<=0; "Nicht fällig"; WENN(E2<=30; "0-30 Tage"; WENN(E2<=60; "31-60 Tage"; WENN(E2<=90; "61-90 Tage"; ">90 Tage"))))

Schritt 5: Optische Aufbereitung (Pro-Tipps)

  1. Summen bilden: Erstelle am Ende der Tabelle eine Summenzeile, um zu sehen, wie viel Geld in welcher Altersklasse „gefangen“ ist.
  2. Bedingte Formatierung: Markiere Zellen in der Spalte „Über 90 Tage“ automatisch rot, damit sie sofort ins Auge springen.
    • Markieren -> Bedingte Formatierung -> Regel zur Hervorhebung -> Größer als 0 -> Rot füllen.
  3. Pivot-Tabelle: Wenn du viele Daten hast, markiere deine gesamte Tabelle und füge eine Pivot-Tabelle ein. Ziehe die „Kunden“ in die Zeilen und die verschiedenen „Altersklassen-Spalten“ in die Werte. So hast du sofort eine Übersicht pro Kunde.

Zusammenfassung der Logik

  • WENN(Bedingung; Dann; Sonst) ist die Basis.
  • UND() hilft uns, Zeiträume (von... bis...) einzugrenzen.
  • Absolute Bezüge ($H$1) sind wichtig, damit sich das Vergleichsdatum beim Herunterziehen der Formel nicht verschiebt.

Viel Erfolg beim Erstellen deiner Analyse!

0