Excel als Liquiditätsprophet: So sagt es Ihre Zahlungseingänge voraus
Herausforderungen bei der Ermittlung der Zahlungseingänge
In der Finanzplanung und im Liquiditätsmanagement besteht häufig die Aufgabe, auf Basis geplanter Umsätze und vereinbarter Zahlungsziele den voraussichtlichen Zahlungseingang zu ermitteln.
Klingt auf den ersten Blick trivial, erweist sich in der Praxis jedoch als komplex, insbesondere dann, wenn die zugrunde liegenden Perioden unterschiedlich lang sind und sich bspw. Wochen- und Monatslogiken überlagern.
Wir sind auf diese Herausforderung gestoßen, da wir derzeit ein spezielles Excel-Planungsmodell für Sanierungsfälle entwickeln. Bspw. fordert der Standard IDW S 11 typischerweise eine zunächst wochenweise Planung (i.d.R. 13 Wochen) die dann in eine längerfristige, monatliche Finanzplanung übergeht.
Die 13-Wochen-Planung liefert die notwendige Aktualität, um auf kurzfristige Probleme reagieren zu können, während die sich anschließende monatliche Planung einen umfassenderen Überblick liefert und die strategische Ausrichtung zeigt. In dieser Kombination hat man dann ein robustes Werkzeug zur Überwachung und Steuerung der Unternehmensfinanzen, das insbesondere in Krisenzeiten unerlässlich ist.
Eine weitere Herausforderung für eine möglichst taggenaue Berechnung ist die Tatsache, dass die ersten Wochen-Perioden zwar immer jeweils 7 Tage umfassen (also identisch lang sind), die dann folgenden Monate aber unterschiedlich viele Tage haben (der erste (Übergangs-)Monat wird häufig sogar nur teilweise berücksichtigt, um anschließend saubere „Monatsscheiben“ zu erhalten). Damit wird eine tagesgenaue Verschiebung um ein beliebiges Zahlungsziel (z. B. 16, 26 oder 42 Tage) erforderlich.
Die gängigen Lösungsansätze in Excel basieren meist auf einer Vielzahl von Hilfszeilen oder -spalten, um die Überlappungen zwischen Umsatz- und Zahlungsperioden zu bestimmen. Das führt zu unübersichtlichen Tabellen, Fehleranfälligkeit, erhöhtem Wartungsaufwand und einer eingeschränkten Flexibilität.
Ziel der hier vorgestellten Lösung war daher eine einzige, kompakte Formel bzw. Funktion, die alle Berechnungen automatisch übernimmt. Das Zahlungsziel in Tagen soll dabei für jede Umsatzzeile frei variierbar sein, beliebige Periodenlängen (Woche, Teilmonat, Monat) sollen korrekt berücksichtigt werden und die Verschiebung der Geldeingänge muss tagesgenau und quotal erfolgen.
Allgemeiner Lösungsansatz
Die Grundlage der Berechnung bildet die Annahme, dass die Umsätze innerhalb einer Periode gleichmäßig über alle Tage dieser Periode verteilt sind. Das heißt, der Umsatz einer Woche oder eines Monats wird auf die einzelnen Kalendertage dieser Periode quotal aufgeteilt (= gleichverteilt).
Für jede Ausgangsperiode (= Umsatzperiode) werden Start- und Enddatum ermittelt. Anschließend wird das gesamte Zeitintervall um das in Tagen definierte Zahlungsziel in die Zukunft verschoben (z. B. 18 Tage, vgl. Bsp. in Abb. 1). Dadurch ergibt sich für jede Umsatzperiode ein neuer, verschobener Zeitraum, in dem die zugehörigen Zahlungseingänge stattfinden.
Im nächsten Schritt wird für jede Zielperiode (= Zahlungsperiode) die Schnittmenge der beiden Zeitintervalle bestimmt – also der Überlappungsbereich in Tagen zwischen der verschobenen Umsatzperiode und der aktuellen Zahlungsperiode.
Je größer dieser Überlappungsbereich, desto höher der anteilige Zahlungseingang aus der entsprechenden Umsatzperiode.
Die eigentliche Berechnung der Höhe des Zahlungseingangs erfolgt am Ende durch Multiplikation des Umsatzes pro Tag (im Bsp. 70 € Umsatz geteilt durch 7 Tage (Woche 1) = 10 €) mit der Anzahl der überlappenden Tage in der bzw. den Zielperiode(n). Im Bsp. 3 x 10 € in Woche 3 und 4 x 10 € in Woche 4. Die Summe dieser Anteile (3/7 und 4/7) über alle Perioden ergibt schließlich 100% und entspricht damit dem ursprünglichen Umsatz in der Ausgangs- bzw. Umsatzperiode. Abb. 1 zeigt schematisch die Vorgehensweise mit Hilfe eines einfachen Zahlenbeispiels.
Für jede Ausgangsperiode (= Umsatzperiode) werden Start- und Enddatum ermittelt. Anschließend wird das gesamte Zeitintervall um das in Tagen definierte Zahlungsziel in die Zukunft verschoben (z. B. 18 Tage, vgl. Bsp. in Abb. 1). Dadurch ergibt sich für jede Umsatzperiode ein neuer, verschobener Zeitraum, in dem die zugehörigen Zahlungseingänge stattfinden.
Im nächsten Schritt wird für jede Zielperiode (= Zahlungsperiode) die Schnittmenge der beiden Zeitintervalle bestimmt – also der Überlappungsbereich in Tagen zwischen der verschobenen Umsatzperiode und der aktuellen Zahlungsperiode.
Je größer dieser Überlappungsbereich, desto höher der anteilige Zahlungseingang aus der entsprechenden Umsatzperiode.
Formelmäßige Umsetzung
Die gesamte Berechnungslogik lässt sich in einer einzigen LET-Formel abbilden, die die modernen Array-Funktionen von Excel 365 nutzt. Dadurch sind weder Kopierarbeit noch Hilfszeilen erforderlich. Die Formel wird einmal – zum Beispiel in Zelle J13 – eingegeben und liefert automatisch ein „Spill-Ergebnis“ über alle Zielspalten J bis Z (vgl. Abb. 2).
Die hier gezeigte Excel-Datei stellen wir Ihnen mit allen Formeln zum eigenen Studium bzw. für eigene Berechnungen am Ende des Beitrags kostenlos zum Download zur Verfügung.
Die Berechnung folgt dabei sechs einfachen Schritten:
1. Zahlungsziel übernehmen
Das Zahlungsziel (ZZ) in Tagen wird aus der Eingabezelle (z. B. D13) übernommen und als Variable ZZ gespeichert.
Damit wird später jeder Umsatzzeitraum um diese Anzahl von Tagen in die Zukunft verschoben.
2. Zeitachsen und Umsatzdaten laden
Start- und Enddaten der Perioden (Umsatzperioden), deren Länge in Tagen sowie der Umsatz werden als Arrays eingelesen:
UEnd; J$4:BU$4
Tage; UEnd-UStart+1
Umsatz; J12:BU12
Die Anzahl der Tage wird dabei direkt aus den beiden zuvor definierten Variablen ermittelt (+1 am Ende, da auch der Starttag enthalten sein soll!). Damit die Formel später ggf. nach unten kopiert werden kann, sollten die Bereiche UStart und UEnd mit absoluten Bezügen versehen werden ($-Zeichen).
3. Zielperioden iterieren
NACHSPALTE(…; LAMBDA(…)) läuft einzeln über jede Zielspalte (J, K, L, … Z). D.h. für jede Spalte (Zahlungsperiode) wird eine Teilberechnung ausgeführt, so das jede Zielperiode ihr eigenes Start- und Enddatum (TStart, TEnd) erhält.
4. Umsatzperioden verschieben
Jede Umsatzperiode wird um das definierte Zahlungsziel verschoben:
ZZEnd; UEnd + ZZ
5. Überlappungstage berechnen
Für jede Zielperiode wird die Zahl der Tage berechnet, an denen sich die verschobene Umsatzperiode mit der Zielperiode überschneidet.
Die Formel lautet:
wobei für
low; (ZZStart>TStart)*ZZStart+(ZZStart<=TStart)*TStart gilt.
Das Ergebnis ist die Anzahl der überlappenden Kalendertage. Wenn keine Überschneidung besteht, ergibt das 0 (Null).
6. Zahlungseingang berechnen
Die anteilige Zahlung ergibt sich aus der Multiplikation des Tagesumsatzes mit der Zahl der überlappenden Tage.
Damit werden alle verschobenen Umsatzperioden auf die Zielperiode addiert – auch wenn mehrere Umsätze in unterschiedlichen Wochen oder Monaten liegen. Der Ausdruck „Tage>0“ in der Formel verhindert #DIV0-Fehler, falls eine Periode 0 Tage hat, was aber – zumindest in unserem Beispiel – eigentlich nicht vorkommen kann.
Ergebnis: Zahlungseingänge je Zielperiode
Das Ergebnis ist ein dynamisches Array über alle Zielspalten, das direkt in J13:Z13 „ausläuft“. Jede Zelle zeigt, wie viel vom gesamten geplanten Umsatz (aus Zeile 12) in dieser Woche bzw. diesem Monat eingeht. Alles passt sich automatisch an, sobald Umsätze, Periodenlängen oder das Zahlungsziel geändert werden.
Kontrolllogik
Eine einfache Kontrollzelle (vgl. F13) zeigt, ob die jeweiligen Zeilensummen der Umsätze (I12) und die der Zahlungseingänge (I13) gleich hoch sind. Die Kontrollformel funktioniert natürlich nur, solange alle Einzahlungen noch innerhalb des betrachteten Zeitraums liegen (im Bsp. nur bis 31.12.2026).
Hier nochmal die finale LET-Formel aus Zelle J13. Da die Formel nach rechts einen dynamischen Spill-Bereich generiert, stehen in den Zellen K13, L13 etc. keine Formeln. Diese Zelle bleiben leer, sollten aber vorformatiert werden:
=LET(
ZZ; $D13;
UStart; J$3:BU$3;
UEnd; J$4:BU$4;
Tage; UEnd - UStart + 1;
Umsatz; J12:BU12;
NACHSPALTE(
J$3:BU$3;
LAMBDA(tcol;
LET(
pos; SPALTE(tcol) - SPALTE(J$3) + 1;
TStart; INDEX(J$3:BU$3; 1; pos);
TEnd; INDEX(J$4:BU$4; 1; pos);
ZZStart; UStart + ZZ;
ZZEnd; UEnd + ZZ;
low; (ZZStart > TStart) * ZZStart + (ZZStart <= TStart) * TStart;
high; (ZZEnd < TEnd) * ZZEnd + (ZZEnd >= TEnd) * TEnd;
overlap; (high >= low) * (high - low + 1);
SUMMENPRODUKT(
Umsatz * overlap / WENN(Tage > 0; Tage; 1) * (Tage > 0)
)
)
)
)
)
Von der LET-Formel zur eigenen Excel-Funktion mit LAMBDA
Eine der leistungsstärksten Neuerungen in Excel 365 (und auch in Excel 2021) ist die Möglichkeit, eigene Funktionen direkt in Excel zu definieren – ganz ohne VBA. Über die Kombination von LET und LAMBDA lassen sich komplexe Formeln kapseln und wie Standardfunktionen (z. B. SUMME oder WENN) aufrufen.
Im Beispiel der Zahlungsziel-Logik wird aus der zuvor entwickelten LET-Formel eine universell einsetzbare Funktion namens Zahlungseingang, die jederzeit – ohne den riesigen Ballast der obigen LET-Funktion – ganz einfach in beliebigen Tabellen eingesetzt werden kann.
So machen Sie aus der LET-Formel eine individuelle Excel-Funktion:
1. Ausgangspunkt: Die fertige LET-Formel
Im ersten Schritt liegt die komplette Logik bereits als funktionierende LET-Formel vor, die alle Zwischenschritte enthält (z. B. Start-/Enddatum, Overlap-Berechnung, SUMMENPRODUKT etc.). Diese Formel steht z. B. in Zelle J13 und funktioniert dort bereits korrekt (siehe Quelltext weiter oben).
2. Umwandlung in eine LAMBDA-Struktur
Damit die Formel als eigene Funktion genutzt werden kann, muss sie in eine LAMBDA-Struktur überführt werden (vgl. dazu auch unseren Blogbeitrag „Automatisierte Liquiditätsplanung in Excel mit LAMBDA-Funktion … nur eine einzige Formel in einer Zelle!“.
Der Grundgedanke ist einfach:
- Die Variablen, die bisher fest in der Formel referenziert waren (nämlich Umsatz, Zahlungsziel, Periodenstart, Periodenende), werden zu Funktionsparametern.
- Diese Parameter stehen in der LAMBDA-Klammer in genau der gewünschten Reihenfolge.
Beispielhafte Syntax:
=LAMBDA(Umsatz;Zahlungsziel;Periodenstart;Periodenende;
LET(
[eigentliche Berechnung von oben]
)
)
=LAMBDA(Umsatz;Zahlungsziel;Periodenstart;Periodenende;
LET(
[eigentliche Berechnung von oben]
)
)
Das bedeutet: Anstelle fester Zellbezüge (z. B. $D20 oder J$3:Z$3) nutzt man nun die Namen der Funktionsargumente (Zahlungsziel, Periodenstart usw.), die später beim Aufruf übergeben werden.
3. Einfügen in den Namensmanager
Vorgehensweise:
- In Excel die Registerkarte Formeln öffnen.
- Dort auf Namensmanager
- Über .. einen neuen Namen anlegen, hier bspw.: Zahlungseingang
- In das Feld „Bezieht sich auf“ den kompletten LAMBDA-Ausdruck einfügen
- Mit OK bestätigen und den Namensmanager schließen.
- Aufruf der eigenen Funktion
4. Aufruf der eigenen Funktion
Nach dem Speichern steht die Funktion Zahlungseingang wie jede andere Excel-Funktion zur Verfügung. Sie kann direkt in einer Zelle aufgerufen werden, zum Beispiel so:
Dabei gilt für dieses Beispiel:
- J19:Z19 => Umsatz je Periode
- D20 => Zahlungsziel in Tagen
- J$3:Z$3 => Startdatum jeder Periode
- J$4:Z$4 => Enddatum jeder Periode
Die Formel liefert automatisch einen Spill-Bereich über alle Zielperioden (Wochen und Monate) und zeigt für jede Spalte den korrekten anteiligen Zahlungseingang. Wenn die Start- und Enddaten absolut verlinkt werden ($-Zeichen) können Sie später den 2-zeiligen Block (aus Umsatz und Zahlungseingang) mit dieser Formel beliebig oft nach unten kopieren und müssen nichts anpassen.
5. Vorteile der LAMBDA-Methode
- Die Funktion kann überall in der Arbeitsmappe verwendet werden.
- Änderungen an der Logik erfolgen zentral (nur einmal) im Namensmanager, nicht in jeder Zelle mit komplexer LET-Formel.
- Die Formel bleibt lesbar: Statt einer langen LET-Struktur steht im Tabellenblatt nur noch =Zahlungseingang(…).
- Die Verwendung ist einfach (auch für Laien, welche ggf. die komplette Logik nicht nachvollziehen möchten oder können): Nach Eingabe von =Zahlungseingang( wird automatisch eine Hilfe mit den einzugebenden Parametern sichtbar:
- Die LAMBDA-Funktion kann auch in anderen Dateien wiederverwendet werden – durch Export über den Namensmanager oder einfaches copy&paste.
Pro und Kontra der vorgestellten Lösung
Vorteile:
- Nur eine einzige Formel erforderlich:
Alle Berechnungsschritte sind in einer kompakten LET-Funktion integriert, die automatisch über alle Spalten hinweg ausgegeben wird.
- Einfachheit:
Bei Überführung in eigene Lambda-Funktion (=Zahlungseingang) sehr einfache Nutzung möglich (verhindert Berechnungsfehler).
- Hohe Flexibilität:
Das Zahlungsziel kann beliebig verändert werden, ohne dass Anpassungen in der Struktur oder Formel notwendig sind.
- Beliebige Periodenlängen:
Die Formel berücksichtigt Wochen, Teilmonate und Monate gleichermaßen, auch wenn Zeiträume unterschiedliche viele Tage beinhalten.
- Keine Hilfszeilen:
Sämtliche Berechnungen erfolgen innerhalb der Formel; es sind keine zusätzlichen Zwischenrechnungen nötig.
- Übertragbarkeit:
- Tagesgenauigkeit:
Die Ergebnisse berücksichtigen exakt die Überlappung in Kalendertagen, was eine präzise Liquiditätsvorschau ermöglicht.
Nachteile:
- Komplexität der Formel:
Die genaue Logik ist anspruchsvoll und ohne Kenntnis der zugrunde liegenden Array-Mechanismen schwer nachzuvollziehen.
- Eingeschränkte Kompatibilität:
Ältere Excel-Versionen (z. B. Excel 2016) unterstützen weder LET noch LAMBDA oder Spill-Bereiche. Für diese ist eine alternative SUMMENPRODUKT-Variante erforderlich (siehe Legacy-Formel in zugehöriger Download-Datei).
- Erschwerte Fehlerprüfung:
Aufgrund der verschachtelten Struktur ist eine Zwischenauswertung einzelner Schritte nur über Teilkopien der Formel möglich (nur für Experten).
- Leistungsbedarf:
Bei sehr großen Tabellen mit vielen Perioden und Zeilen kann die Rechenzeit leicht steigen, da die Formel alle Kombinationen von Quell- und Zielperioden prüft.
Fazit und Ausblick
Die vorgestellte Lösung zeigt eindrucksvoll, wie leistungsfähig moderne Array- und LET-Funktionen in Excel 365 geworden sind. Komplexe zeitliche Abhängigkeiten, wie die tagesgenaue Verteilung von Zahlungseingängen über unterschiedliche Perioden, lassen sich mit einer einzigen Formel vollständig automatisieren.
Wir haben in unserem Beispiel zwar Umsätze und damit Debitoren-Zahlungsziele betrachtet. Die Funktion kann aber unverändert auch für die Planung von Zahlungsausgängen an Kreditoren verwendet werden. Der Vollständigkeit halber sei erwähnt, das die Umsatz- und auch Kostenplanungen i.d.R. netto (also ohne Umsatzsteuer) erfolgen. Aus diesem Grunde darf eine analoge Berechnung der Zahlungseingänge für die zugehörige Mehrwertsteuer auf keinen Fall vergessen werden. Schließlich überweist der Kunde diese auch erst mit zeitlichem Verzug. Bei Soll-Versteuerung (also nach vereinbarten Entgelten) muss die Umsatzsteuer bei langen Zahlungszielen oft sogar schon abgeführt werden, bevor diese von ihren Debitoren überwiesen wird. Aber das ist ein anderes Thema.
Während frühere Berechnungsansätze oft Dutzende Hilfszellen benötigten, hat dieser Beitrag gezeigt, dass heute eine klar strukturierte LET- bzw. Lambda-Formel ausreicht, um die gesamte Logik transparent und wiederverwendbar abzubilden.
Damit gehört unsere neue Funktion „Zahlungseingang“ zu einem Paradebeispiel für den Fortschritt in Excel: weg von mechanischen Zellverweisen hin zu modularen, funktionalen Berechnungen, die Controlling und Finanzplanung wesentlich effizienter machen.
Wie üblich haben wir auch zu diesem Tutorial eine entsprechende Excel-Datei vorbereitet, die wir ihnen gerne kostenlos zur Verfügung stellen.
