Bedingte Formatierungen erzeugen Übersicht und Transparenz

Good To Great 3Das Thema bedingte Formatierung in Excel ist ein Umfangreiches. An dieser Stelle wollen wir uns auf die für das Financial Modelling interessanten Varianten konzentrieren. Im Wesentlichen geht es bei bedingten Formatierungen darum, bestimmte Zellen bzw. Werte optisch hervorzuheben, um auf diese Weise die Transparenz, Übersichtlichkeit und Orientierung innerhalb von komplexen Modellen zu verbessern bzw. zu erleichtern. Nach den Beiträgen “Datenüberprüfung” und “Benutzerdefinierte Zahlenformate” ist dies das dritte Tutorial der Trilogie von “guten zu großartigen Finanzmodellen”.

Bedingte Formatierungen – Die häufigsten Anwendungen im Detail

Die häufigsten Anwendungen im Rahmen des Financial Modelling sind:

  1. Flags/Markierungen
  2. Schalter und Kontrollen
  3. Kennzeichnung von Minimum- bzw. Maximumwerten sowie
  4. Hervorhebung bzw. Ausblendung von Werten und Auswahlen.

Bedingte Formatierungen – Die häufigsten Anwendungen im Detail

Sowohl bei den Flags und Markierungen, wie auch bei den Schaltern und Kontrollen handelt es sich in aller Regel um „binäre Zellen“, d.h. es sind lediglich zwei Zustände relevant (z.B. Ja/Nein; An/Aus, kleiner als/größer als; berücksichtigen/nicht berücksichtigen etc.), die durch 1 oder 0 gekennzeichnet werden.
Den jeweiligen Wert (1 oder 0) erhält die Zelle durch eine individuelle Formel. Mittels der bedingten Formatierung wird dann diesen beiden Werten ein individuelles Format zugewiesen.

Flexibles Projekttiming

Um eine bedingte Formatierung zu erstellen muss/ müssen zunächst der/die Zellbereich(e) markiert werden. Anschließend können Sie über die Registerkarte „Start“ in der Gruppe „Formatvorlagen“ das Symbol „Bedingte Formatierung“ wählen. Anschließend müssen Sie sich noch eine gewünschte Bedingung bzw. Formatierung aussuchen.

T05 - B01_web

Besonders empfehlenswert ist der Einsatz bei der Umsetzung von flexiblen Zeitskalen. Eine essentielle Basis für alle Excel-basierten Finanzmodelle. Im hier vorgestellten Beispiel (siehe Bild) wird für die Flags die jeweilige Formatierung (gelb od. blau) angewendet, wenn der Zellwert gleich 1 ist.
Die oben sichtbaren Daten (Periodenbeginn und -ende) enthalten jeweils 2 bedingte Formatierungen da beide Zahlen gelb sein sollen, wenn in Zeile 10 (Bauphase) eine 1 auftaucht, bzw. beide blau in der Betriebsphase (Zeile 11). Das Modellende wird optisch sofort sichtbar, weil danach keine Färbung mehr aktiv ist. Wird die Länge einer oder beider Phasen verändert, passt sich die farbliche Formatierung automatisch an.

Markierung von Zeiträumen und -punkten

Weitere häufig verwendete Beispiele für Flags sind die Kennzeichnung von Darlehenslaufzeiten oder die Markierung von bestimmten Zeitpunkten, z.B. Modellende um einen Terminal Value zu berechnen (siehe zugehörige Excel-Datei). Der Einsatz derartiger Flags unterstützt Anwender nicht nur optisch, sondern ein weiterer wichtiger Zweck liegt in der Möglichkeit mit diesen auch „rechnen“ zu können. So kann das Laufzeitflag im Falle eines Darlehens dafür sorgen, dass Zinsen nur während der Darlehenslaufzeit berechnet werden, da bei Multiplikation mit Null (außerhalb der Darlehenslaufzeit) keine Zinsen kalkuliert werden.

Schalter und Kontrollen

T05 - B04_web

Auch Schalter und Kontrollen enthalten jeweils nur eine 1 oder 0 bzw. eine Formel, die diese Werte generiert. Individuelle Texte werden über ein entsprechendes benutzerdefiniertes Zahlenformat generiert (siehe Screenshot). Schauen Sie sich auch mein ausführliches Tutorial zu benutzerdefinierten Zahlenformaten in diesem Blog an.
Schließlich erreicht man über eine zusätzliche bedingte Formatierung ein optisch auffälliges Format (hier z.B. Fehlermeldung in rot), sofern die Zelle ungleich 0 ist.

T05 - B05_web

Minimumwerte hervorheben – DSCR, LLCR, LPCR

Im Beispiel Minimumwerte hervorheben kommen ebenfalls wieder mehrere Formatierungsoptionen in Kombination zur Anwendung. Klassische Anwendungen in Finanzmodellen findet man z.B. bei der Berechnung diverser Fremdkapitalkennziffer wie Schuldendienstdeckungsgrad (DSCR), Darlehenslaufzeitdeckungsgrad (LLCR) oder Projektlaufzeitdeckungsgrad (LPCR). Mehr Informationen zu diesen Kennzahlen erhalten Sie in einem Extratutorial in diesem Blog sowie in den angebotenen Schulungsvideos.

T05 - B06_web

Ausgangspunkt sind die berechneten Werte für die DSCR in Zeile 40 (hier willkürlich eingegeben und nicht berechnet). Neben einem benutzerdefinierten Zahlenformat um die Werte mit „x“ darzustellen kommen zwei weitere bedingte Formatierungen hinzu. 1. Dort wo nicht berechnet (d.h. Werte = Null) werden die Zellwerte hellgrau formatiert und damit in den Hintergrund gerückt. 2. Dort wo das Minimum-Flag gleich 1 ist, wird ein spezielles rotumrandetes Format zur Hervorhebung genutzt (siehe Bild).
Die Formel für das Minimum Flag bezieht sich auf den zuvor separat berechneten Minimumwert. Dabei kommt eine sog. Matrixfomel zum Einsatz, die dafür sorgt, dass nur von den Werten die ungleich 0 sind das Minimum berechnet wird. Nach Eingabe der Formel muss die Zelle mit „Shift + Strg + Return“ verlassen werden. Matrixformeln erkennt man an den speziellen geschweiften Klammern {   }, welche die Formel einrahmen. Berechnet man das Minimum lediglich mit der „einfachen“ Formel „=MIN(I40:P40)“, würden nur die (hier uninteressanten) Nullwerte markiert.

Auswahlen und spezielle Markierungen

Bedingte Formatierungen helfen auch bei der Visualisierung von Auswahlen. Häufige Anwendungen beim Financial Modelling sind z.B. Szenariomanager oder die hier gezeigte Auswahl einer Währung (siehe Screenshot unten).

T05 - B07
Pfeile bzw. Kreuze sind Bestandteil der professionellen Formatvorlagen (siehe zugehörige Excel-Datei => Blatt Formate). Da diese häufiger verwendet werden wurde ihnen zum einfachen Aufruf ein entsprechender Name zugewiesen. Grundsätzlich können Sie aber auch beliebige andere Zeichen (z.B. Sonderzeichen aus dem Zeichensatz Wingdings) verwenden.
Die Hervorhebung erfolgt über eine entsprechende bedingte Formatierung (Bild unten).

T05 - B08_web
Mehr Details zur Funktionsweise dieses Beispiels (Einsatz verschiedener Währungen innerhalb eines Projektfinanzierungsmodells) finden Sie in einem eigenen umfangreichen Tutorial.

Selektive Berücksichtigung bzw. Ausblendung von Werten

In diesem Beispiel wird die bedingte Formatierung dazu benutzt, Werte die nicht berücksichtigt werden sollen „auszublenden“, d.h. so zu formatieren, dass sie in den Hintergrund treten (siehe Screenshot).

T05 - B09_web

Die Auswahl erfolgt hier über ein Dropdownmenu für jede Kostenart. Dieses wird über „Datenüberprüfung“ auf 0 und 1 beschränkt und über ein benutzerdefiniertes Zahlenformat wird jeweils Ja bzw. Nein angezeigt. Mehr dazu erfahren Sie auch in den beiden separaten Tutorials Datenüberprüfung und Benutzerdefinierte Zahlenformate in diesem Blog.
Verwendet man in der Summenzeile eine spezielle SUMMEWENN-Funktion, berechnet sich die Gesamtsumme in Abhängigkeit der getroffenen Auswahl.
Die Einstellungen für die bedingte Formatierung zur Abblendung nicht ausgewählter Zellwerte:

T05 - B10_web
Zum gleichen Ergebnis führt auch die alternative Regel „gleich 0“.

Auszahlungsprofil bei unterschiedlich langen Bauphasen

Um ihnen eine weitere Anregung zu geben, was mit dem „Dreiklang“ Datenüberprüfung, benutzerdefinierte Zahlenformate und bedingte Formatierung im Rahmen des Financial Modelling alles möglich ist, finden Sie in der zugehörigen Excel-Datei ein Beispiel für ein Auszahlungsprofil, häufig genutzt z.B. für Investitionen während der Bauphase (siehe Bild).

Wollen Sie ihre Investition in der Bauphase nach einem bestimmten %-Schlüssel verteilen, sind bei nachträglicher Veränderung der Länge dieser Phase zwei Dinge wichtig:

  1. Werte der Betriebsphase (hier ab Monat 4) interessieren nicht mehr und sollen ausgegraut werden, um nicht zu verwirren.
  2. Die Investitionskosten müssen vollständig, d.h. genau zu 100% verteilt werden, ansonsten ist der in ihrem Modell ermittelten Kapitalbedarf nicht korrekt. Dies wird über eine entsprechende, variable Kontrollsumme in Kombination mit einer gut sichtbaren Fehlermeldung erreicht.

Die Länge der Investitionsphase kann im gezeigten Beispiel frei per Dropdown ausgewählt werden. In einem vollständigen Modell würde diese direkt mit den Eingaben zum Modelltiming verknüpft.

T05 - B11_web

Die Umsetzung ist analog den bereits weiter oben gezeigten Beispielen. Weitere Details sehen Sie in der zugehörigen Excel-Datei, die Sie unten auf dieser Seite anfordern können. Zum leichteren Verständnis „spielen“ Sie einfach ein wenig mit den dort erstellten Beispielen.

Abschließende Bemerkung

Im Gegensatz zu den benutzerdefinierten Zahlenformaten, lassen sich bedingte Formatierung nicht im Rahmen von Zellformatvorlagen abspeichern. Sie werden aber problemlos bei Copy&Paste-Aktionen („Strg + C“ anschließend in Zielzelle „Strg + V“) mitübertragen. Will man nur die jeweilige bedingte Formatierung übertragen, kann dies über „Einfügen“ => „Inhalte einfügen“ und anhaken der entsprechenden Option erreicht werden („Strg + C“ anschließend in Zielzelle „Strg + Alt + V“).
Die zu diesem Tutorial gehörige Excelvorlage enthält bereits zahlreiche direkt verwendbare bedingte Formatierungen, die einfach in die entsprechenden Zielzellen kopiert werden können. Anschließend müssen lediglich noch die gewünschten Formeln ergänzt werden.

Holen Sie sich zum Ausprobieren am besten gleich die von uns bereitgestellte Exceldatei.

Datei anfordern →