Vermeidung von Zirkelbezügen in Cashflow Modellen

Beim Erstellen von Cashflow Modellen kommt es wegen gegenseitigen Abhängigkeiten häufiger zu so genannten Zirkelbezügen bzw. Zirkelverweisen. Bekannte Beispiele sind die Berechnung von Zinsen auf Cash-Guthaben, Kontokorrentzinsen oder die Zinsermittlung und Berücksichtigung im Fall einer Finanzierung über Darlehen. Bei einem Zirkelbezug hängt die Berechnung einer Formel direkt oder indirekt vom Ergebnis der Zelle ab, in welcher sich die Formel befindet, so dass eine normale Berechnung nicht möglich ist.

Im hier vorgestellten Beispiel wird zur Vermeidung einer liquiditätsmäßigen Unterdeckung am Periodenende ein Darlehen (z.B. ein Kontokorrentkredit) genutzt. Die darauf zu zahlenden Zinsen wirken aber ebenfalls negativ auf die Cash-Position, so dass das Darlehen erhöht werden muss um eine negative Cash-Bilanz zu vermeiden, wodurch sich aber wiederum die Zinszahlungen erhöhen etc. 

Um Zirkelverweise in Excel zu finden und zu untersuchen wird man in der Registerkarte “Formeln” in der Gruppe “Formelüberwachung“ unter „Fehlerüberprüfung“ fündig. Allein diese Einordnung zeigt, dass Zirkelverweise als „Fehler“ zu betrachten sind und schon deshalb in professionellen Excel-basierten Finanzmodellen möglichst zu vermeiden sind. Ein Zirkelbezug in einer Zelle kann die Empfänger und Nutzer ihres Finanzmodells verwirren, indem ein unerwartetes Ergebnis in der Zelle angezeigt wird, und führt darüber hinaus möglicherweise zur Anzeige einer Warnmeldung. Soll ein Finanzmodell sogar auditiert werden, ist ein Zirkelbezug in aller Regel nicht tragbar. 
Im Folgenden zeigen wir ihnen verschiedene Strategien und Alternativen, um Zirkelbezugsproblematiken zu umgehen und dennoch zum gewünschten Ergebnis zu gelangen. Die zugehörige Excel-Datei können Sie unten kostenlos herunterladen.

Drei verschiedene Lösungsmöglichkeiten für Zirkelbezüge


1. Iterative Berechnung

Standardmäßig gibt Excel in einer Zelle “0″ zurück, wenn ein Zirkelbezug angetroffen wird und in der Zelle zuvor kein Wert vorhanden ist. Eine Lösungsmöglichkeit besteht darin, Excel alle involvierten Zellen iterativ berechnen zu lassen. Dazu muss diese Berechnungsoption aktiviert sein. Dies lässt sich im Register „Datei“ => „Optionen“ => „Formeln“ => „Iterative Berechnung aktivieren“ prüfen bzw. einstellen (siehe Bild 1).

Nach Aktivierung bekommen Sie zwar in der Regel korrekte Näherungswerte, als Dauerlösung ist diese Vorgehensweise aber nicht empfehlenswert, insbesondere da möglicherweise andere Nutzer der Datei die Iterationsoption nicht aktiviert haben und durch eine Zirkelbezugswarnung verunsichert werden könnten.

ZirkelbezuegeT07-B01.jpg

2. Zielwertsuche


Ein alternativer zweiter Lösungsansatz besteht in der Verwendung der Zielwertsuche von Excel. Dazu wird die Zelle mit der Darlehensberechnung in eine Inputzelle umgewandelt und auf diese Weise die Zirkularität entfernt.
Nach Festlegung der Startliquidität berechnet Excel die Darlehenshöhe per Zielwertsuche. Die Funktion findet sich in der Registerkarte “Daten” in der Gruppe “Datentools” unter „Was-wäre-wenn-Analyse“ => „Zielwertsuche“.

In der Eingabemaske wird als Zielzelle die Liquidität am Ende der Periode eingegeben (H25). Diese soll Null sein (es sind auch andere positive Werte einstellbar). Die veränderbare Zelle ist die Eingabezelle (H32) für die Darlehenshöhe (siehe Bild unten).

ZirkelbezuegeT07-B02.jpg 

Hinweis: Eine Hinweisbox (Aufforderung zur Durchführung einer Zielwertsuche) lässt sich einfach mit einer Wenn-Funktion in Kombination mit einem benutzerdefinierten Zahlenformat für die Textmeldung hinzufügen (siehe auch Excel-Datei).

Zwar hat man auf diese Weise das Zirkularitätsproblem beseitigt, allerdings muss bei jeder Veränderung der Startliquidität die Zielwertsuche erneut manuell ausgeführt werden. Dies lässt sich zwar grundsätzlich mit einem einfachen VBA-Makro bequem lösen (hier nicht dargestellt), allerdings gibt es noch eine elegantere Lösung, die auf die Verwendung von häufig nicht gewünschten Makros in der Datei verzichtet.

3. Empfohlene algebraische Lösung

Die dritte Lösungsmöglichkeit löst das Problem algebraisch, vermeidet auf diese Weise die aufgezeigten Einschränkungen der ersten beiden Lösungsansätze und wird deshalb empfohlen. Es sei:

CFvF = Cashflow vor Finanzierung
D = Darlehenshöhe
Zi = Zinsen
r = Zinssatz

Dann lassen sich für eine Zielliquidität von Null folgende zwei Gleichungen aufstellen:

1.    D – Zi = -CFvF
2.    Zi = D * r

Setzt man die zweite Gleichung in die erste ein und löst nach D auf, so ergibt sich:

D – Zi = -CFvF
D = -CFvF + Zi
D = -CFvF + D * r
-r * D + D = -CFvF
(1-r) * D = -CFvF
D = -CFvF / (1-r)

Wir können nun den Darlehensbetrag per Formel berechnen ohne auf die Zinsen rückgreifen zu müssen und vermeiden so einen Zirkelbezug bzw. die Verwendung der Zielwertsuche oder Makros.

Hinweis: Die oben abgeleitete Formel zur Darlehensberechnung wurde im Excel-Download-Beispiel so modifiziert, dass der Wert nicht negativ werden kann. Dies ist sinnvoll, da ein Kontokorrentdarlehen nicht erforderlich ist, sofern zu Beginn gen ügend Liquidität vorhanden ist (siehe Screenshot unten).

ZirkelbezuegeT07-B03.jpg

Anwendungsbeispiel – Automatische Kontokorrentberechnung

Das Beispiel in der Datei baut auf der algebraischen Lösung auf und zeigt, wie eine automatische Finanzierung via Kontokorrentkredit korrekt in Excel modelliert werden kann. Im vorliegenden Beispiel kann für den Kontokorrentkredit einen Limit (= Maximalbetrag) definiert werden. Über ein entsprechendes Bilanzkonto wird die Ausschöpfung in jeder Periode berechnet und so der Maximalbetrag ggf. begrenzt. Zusätzlich wird in der Cashflow-Übersicht ggf. eine Warnmeldung ausgegeben, wenn die Kontokorrentlinie nicht ausreicht, um eine negative Cash-Position am Periodenende zu verhindern.

Die Beispielhafte Fehlermeldung im Jahr 2017 verschwindet, wenn entweder die Kontokorrentlinie erhöht wird (E7) oder die Kosten im entsprechenden Jahr verringert werden (K10). Die Position „Kosten“ ist aus Vereinfachungsgründen als Saldo zu verstehen und kann in einer realen Cashflow-Kaskade durch zahlreiche eigenständige Positionen wie bspw. Umsätze, Sonst. betr. Erlöse, verschiedene Kostenarten, Working Capital etc. ersetzt werden.

Als Weiterentwicklungsmöglichkeiten sind für jede Periode unterschiedliche Zinssätze oder bei Berücksichtigung von Erlösen auch die Möglichkeit der automatischen Rückführung des „teuren“ Kontokorrentkredites denkbar.


Beispiel-Excel-Datei zum Download >>


Download des vollständigen Beitrages: Download

letzte Änderung ).D.G. am 09.09.2019
Autor(en):  Dirk Gostomski

RSS

Der Autor:
Herr Dirk Gostomski (fimovi.de)
Dirk Gostomski ist seit mehr als 15 Jahren selbständiger Berater und Trainer im Bereich Financial Modelling mit Excel. Mit www.financial-modelling-videos.de bietet er auch Intensiv-Video-Workshops an, in denen Schritt für Schritt die Erstellung von professionellen Finanzplanungs, Projektfinanzierungs- und Cashflow-Modellen in Excel erläutert wird.
Homepage | Club-Profil | weitere Fachbeiträge | Forenbeiträge | Blog | Fotogalerie

Weitere Fachbeiträge zum Thema

Premium-Stellenanzeigen


Fraunhofer-Institut für Intelligente Analyse- und Informationssysteme IAIS
Sankt Augustin
HUK-COBURG Versicherungsgruppe
Coburg
Captrain Deutschland GmbH
Berlin
Knauf Insulation GmbH
Simbach am Inn
Meteor GmbH
Bockenem

Eigenen Fachbeitrag veröffentlichen? 

Sie sind Autor einer Fachpublikation oder Entwickler einer Excel-Vorlage? Gern können Sie sich an der Gestaltung der Inhalte unserer Fachportale beteiligen! Wir bieten die Möglichkeit Ihre Fachpublikation (Fachbeitrag, eBook, Diplomarbeit, Checkliste, Studie, Berichtsvorlage ...) bzw. Excel-Vorlage auf unseren Fachportalen zu veröffentlichen bzw. ggf. auch zu vermarkten. Mehr Infos >>

Kommentar zum Fachbeitrag abgeben

05.08.2013 15:58:05 - Gast

Datei kann nicht heruntergeladen werden. Ansonsten eine sehr hilfreiche Information, die ich gut umsetzen kann.
[ Zitieren | Name ]

06.08.2013 08:15:53 - Dana Dossow

Vielen Dank für den Hinweis. Wir werden den Autor informieren und die Datei schnellstmöglich austauschen. Die Controlling-Portal.de-Redaktion
[ Zitieren | Name ]

06.08.2013 09:29:26 - Dana Dossow

Die Datei ist jetzt downloadbar.
[ Zitieren | Name ]

30.11.2014 21:53:27 - Mike Beh.

Als erfahrener Excel-Anwender in genau diesem Bereich kann ich Folgendes anbringen: Die CF-Rechnung basiert ja sowohl auf der GuV als auch auf der Bilanz. Sie hat ebenso wiederum Einfluss auf diese, sei es über die Zinsen (Erträge und Aufwendungen) oder über die Bestände, Neuaufnahmen und Tilgungen an Darlehen bzw. die Inanspruchnahme von Bank-Kontokorrents. Zusätzlich schreibt die Ermittlung des Liquiditätsbestandes, der sich daraus ergebenden Darlehensbewegungen und der Zinsen wiedrum auf die Gewerbe- und die Körperschaftsteuerermittlung in GuV und per sonst. VG/St.-Rückstellungen auf die Bilanz zurück. Ergibt also weitere Zirkelbezüge. Man kann sich grob damit helfen, diese Auswirkungen auf den 1. der Folgeperiode zu berücksichtigen. Damit umgeht man die Zirkelbezugsrechnungen. "Genauere" Kollegen würden dann die Perioden verkürzen um so genauer an die echten Werte heran zu kommen, im Sinne also einer einfachen Integralrechnung. Ein guter Buchhalter/Controller/Bewerter will die Auswirkungen kaufmännisch korrekt behandelt sehen, also periodengerecht. Da die "Rückgabe" dieser Werte zeitnah, also in der betrachteten Periode, kommt man um die Zirkelbezüge nicht herum. Eine "Umleitung" wie im dritten Vorschlag halte ich nur für eingeschränkt machbar, da z.B. nicht immer die aufzunehmende Darlehenshöhe eine mögliche Spielgröße sein kann. Eine Zielwertsuche oder den Solver zu bemühen ist nur bei kleineren Berechnungen möglich. Eine z.B. mehrere Tabellenblätter und viele Perioden umfassende vollständige Planungsrechnung lässt sich damit nicht bewerkstelligen. Schlussendlich können oft bestimmte Berechnungsergebnisse auch "Zielgrößen" sein, oder der Bearbeiter hat ein "Gespür" für die Abhängigkeiten dieser Endgrößen von den Einzelberechnungen. Er/Sie kann also bei Veränderung einer einzigen Formel/Variable und der Beobachtungen der Veränderung der Endgrößen gleich ("live") die Korrektheit der Berechnungen prüfen. Das ist im Falle einer Zielwertsuche/Solverlösung unglaublich aufwendig. Die Iteration ist hier die einzig gängige Lösung. Allerdings kann diese Lösung erhebliche Schwierigkeiten mitbringen. Ebenso wie einige andere Funktionen sind iterative Berechnungen in freigegebenen Arbeitsmappen eingeschränkt. Bis Excel 2003 (aktuelles Excel ungetestet) kam es häufig vor, dass bei Bearbeitung durch mehrere Nutzer gleichzeitig und aktivierter Iteration die Datei später nicht mehr zu Öffnen und damit defekt war. Äußerst ärgerlich. Abhilfe hier schafft nur eine sorgfältige und abgestimmte Schliessen-Prozedur unter den Kollegen, jeder setzt die Iteration inaktiv, speichert und schliesst nacheinander, der Hauptbearbeiter zuletzt. Auch zerbomben einem bei aktiver Iteration Eingabe-/Berechnungsfehler häufig regelrecht die ganze Datei, z.B. fehlende #Div0-Prüfungen, oder die allseits beliebten #Bezugs-Fehler. Auf einmal stehen in vielen Zellen/Zeilen diese Fehler. Von diesem Status zurück ist es oft ein beschwerlicher Weg. Hilfreich ist hier die goldene Regel des regelmäßigen Speicherns, aber nicht immer ist die letzte Sicherung auch wirklich minutengenau aktuell und damit doch wieder einige Arbeit verloren. :o :cry: Die Rückgängig-Funktion hilft aus Fehlerwerten nicht wieder heraus, selbst bei dann komplett richtigen Berechnungen. Man muss dann eine oder mehrere bestimmte fehlerhafte Zwischenberechnungen aufspüren, diese Zellen/Zeilen löschen und rückwärts wieder einfügen. Das kann bei "verschränkten" Zirkelbezügen (Zinsaufwand/Gewerbesteuer/Rückstellungen/Liquiditätsveränderung) ziemlich "tricky" sein. Nicht mit diesen Berechnungen vertraute Bearbeiter (z.B. Sekretärinnen, die das "mal eben" ganz schnell für den Chef ausdrucken sollen) bekommen dann regelmäßig "Anfälle". :evil: Den Aufschrei hört man dann auch ohne Telefon bis sonstwo. :green: Hier sollte man freundlichst PDF-Dateien des bestreffenden Berechnungsstandes mitschicken. Ansonsten kann so eine Sekretärin in Ihrer Not auch einfach mal die Berechnungs-Fehlerwerte in der Datei manuell mit denen auf ihrem letzten Ausdruck überschreiben. Das kommt sehr gut wenn man dann natürlich mit genau dieser Datei weiterarbeiten soll und sich plötzlich trotz großer Variablen-Änderungen das Endergebnis nicht mehr ändert. :denk: Viel Spaß beim Suchen! :klatschen: Weiterhin gutes Gelingen, Mike Beh.
[ Zitieren | Name ]

Nur registrierte Benutzer können Kommentare posten!