Die Szenario-Analyse ist eine Sonderform der Sensitivitätsanalyse. Dabei werden verschiedene als realistisch angenommene Datenkonstellationen (= Szenarien) gebildet und deren Auswirkung auf wichtige Modellergebnisse (Output-Parameter) untersucht. Ein guter Szenario-Manager in einem Planungsmodell erlaubt die Analyse völlig unterschiedlicher Planungsannahmen indem lediglich eine einzige Zelle verändert wird. Wie sich so etwas in ein Excel-Modell integrieren lässt und was dabei zu beachten ist erläutert dieser Beitrag.

Hintergrund

Sowohl für das Management wie auch für Investoren ist es wichtig zu wissen, wie sich zentrale Planungsergebnisse (z.B. Kapitalbedarf, Renditen, Profitabilität, wichtige Kennzahlen etc.) ändern, sofern zentrale Planungsannahmen (Key Driver) angepasst werden.

Dabei stehen in aller Regel sogenannte Downside- bzw. Worst Case-Szenarien im Vordergrund. Wie verändern sich die zentralen Output-Parameter sofern bspw.:

• Die Absatzmenge um 10% geringer ausfällt
• Die Verkäufe sich um 2 Monate verzögern
• Der erzielbare Preis 5% geringer ausfällt
• Die Investitionen 10% teurer werden
• Die Fixkosten 5% höher ausfallen
• Die Fremdkapitalkosten (Zinsen) teurer werden
• oder eine beliebige Kombination der oben genannten Ereignisse eintritt?

Im Folgenden wollen wir uns ansehen, wie eine solche Szenario-Analyse in ein bestehendes Modell integriert werden kann. Diese Anleitung eignet sich daher z.B. auch dazu, um das auf dieser Seite erhältliche Excel-Finanzplan-Tool um eine professionelle Szenario-Funktionalität zu erweitern.

Ziel ist es dabei, innerhalb der Excel-Planungsdatei über eine einzige Auswahlzelle ein frei definierbares Szenario auswählen zu können, welches dann automatisch durch das gesamte Modell fließt und die Abweichungen zur Basisplanung (Base Case) aufzeigt.

Die Erstellung und Integration einer Szenario-Analyse lässt sich in die folgenden 3 Schritte unterteilen:

1. Definition der verschiedenen Szenarien (mit Auswahlmöglichkeit) sowie der veränderbaren Planungsgrößen (= Szenario-Manager)
2. Verlinkung der veränderbaren Planungsgrößen in das bestehende Modell
3. Erstellung einer Output-Tabelle mit den gewünschten Parametern, die für jedes Szenario berechnet bzw. ausgegeben werden sollen (= Szenario-Tabelle)

Szenario-Manager

Der Szenario-Manager sollte auf einem eigenen Tabellenblatt neu angelegt werden und wie im Screenshot unten aufgebaut werden. (Achtung: Nicht verwechseln mit dem in Excel integrierten Szenario-Manager)

• In Spalte C stehen die veränderbaren Planungsgrößen (Key Driver). Beschränken Sie sich hier auf die Wichtigsten. Dies sind i.d.R. maximal vier bis sechs Positionen, abhängig von ihrem Geschäfts- bzw. Planungsmodell.
• In Spalte D geben Sie zur Information an, was die gewählte Eingabe bewirkt, also bspw. prozentuale Erhöhung oder Verminderung, Eingabe von absoluten Werten (Direkteingabe), Base Case + x Monate etc.
• In I7:M7 stehen horizontal die Szenario-Nummern von 1 bis x (beliebig erweiterbar, ggf. gleich zu Beginn einige Reservepositionen vorsehen)
• In I9:M9 stehen die zugehörigen, frei wählbaren Bezeichnungen für die einzelnen Szenarien. Verwenden Sie hier kurze, möglichst aussagekräftige Bezeichnungen, so dass Sie später an anderen Stellen im Modell direkt wissen, um welchen Fall es sich handelt.
  

• In E7 wird ein Auswahlfeld eingefügt, über welches später das gewünschte aktive Szenario gewählt werden kann. Nutzen Sie die Funktionalität „Datenüberprüfung“, um die Auswahlmöglichkeiten auf die verschiedenen Szenarien (hier I7:M7) zu begrenzen und Fehleingaben zu vermeiden.
• I8:M8 (optional): Optische Unterstützung des gewählten Szenarios über Symbole (z.B. Pfeile) und eine bedingte Formatierung (Rotfärbung bei Auswahl => siehe Screenshot). Zusätzlich können Sie über eine weitere bedingte Formatierung die nicht genutzten Zellen bei Auswahl einfärben (hier z.B. grau => J5, J6, J10, J11 etc.).
• Für jede Planungsgröße und jedes Szenario werden Inputzellen eingefügt (Bereich I12:M19) und entsprechend formatiert. Das Base Case Szenario bleibt i.d.R. als Referenz unverändert und sollte deshalb mit einer anderen Zellenformatvorlage formatiert werden.
• Mit der folgenden Formel lässt sich in E9 der Name des jeweils ausgewählten Szenarios anzeigen: =VERWEIS(E7;I7:M7;I9:M9)
• In Spalte E darunter wollen wir jeweils die zum Szenario gehörigen Inputwerte aufrufen und zwar für E12 beispielhaft über die Formel =VERWEIS($E$7;$I$7:$M$7;$I12:$M12) Bei entsprechender Verankerung (siehe $-Zeichen bei den Zellreferenzen) kann die Formel einfach nach unten kopiert werden.

Damit haben wir einen funktionsfähigen Szenario-Manager der es uns erlaubt, verschiedene Inputwerte für beliebige Szenarien einzugeben und ein gewünschtes Szenario auswählen zu können für das dann automatisch die zugehörigen Werte in Spalte E aufgerufen werden.

Verlinkung der veränderbaren Planungsgrößen in das Modell

Im zweiten Schritt gilt es die jeweiligen Werte aus Spalte E in das bestehende Planungsmodell zu verknüpfen. Da die Verlinkung der veränderbaren Planungsgrößen in die Modellinputs i.d.R. erst im Nachhinein stattfindet, dürfen die (bestehenden) Inputzellen nicht einfach gelöscht werden. Diese sind ja bereits ins Modell verknüpft, so dass ein Löschen direkt umfangreiche Modellfehler zur Folge hätte, die Sie nur mit viel Aufwand durch erneute Verlinkung beheben können (Sie dürfen aber bspw. eine neue Formel in bereits verlinkte Zellen schreiben, ohne dass es zu Folgefehlern kommt).

Es empfiehlt sich daher folgende Vorgehensweise (Bsp. Investitionen => Screenshot unten):

1. Fügen Sie zunächst oberhalb der alten Inputwerte eine neue Zeile ein
2. Kopieren Sie dann den/die Inputwert(e) 1:1 in die obere Zeile
3. Anschließend benennen Sie die mittlere Zeile um in „Investitionen verwendet (Szenario)“
4. In Zelle F27 verlinken Sie zu der entsprechenden Zelle aus dem Blatt Szenario-Manager (=Szenarios!E17)
5. Passen Sie nun die Formel in der ursprünglichen Eingabezelle (hier J27) an (neu: =J26*(1+$F27)). Achten Sie auf das $-Zeichen vor dem F, dann können Sie die Formel einfach nach rechts durchkopieren.
6. Schließlich ändern Sie die Formatvorlage der alten Eingabezeile, da diese jetzt ja keine Inputzelle mehr ist, sondern die in Schritt 5 eingefügte Formel enthält.

Ergebnis für das Beispiel Investitionen:

Analog gehen Sie für alle anderen Planungsgrößen aus dem Szenario-Manager vor. Sofern es nicht nur um eine prozentuale Erhöhung bzw. Verminderung geht unterscheiden sich natürlich die Formeln. Für die zeitliche Verschiebung der Umsätze kann bspw. die Funktion BEREICH.VERSCHIEBEN genutzt werden.

In J19 wäre bspw. folgende Formel einzusetzen (siehe Screenshot unten):
=WENN(J5<=$F19;0;BEREICH.VERSCHIEBEN(J18;0;-$F19))

Der erste Teil sorgt dafür, dass in den zu Beginn verschobenen Perioden ein Nullwert ausgewiesen wird (Jan und Feb im Screenshot-Beispiel, da um 2 Monate verschoben wird).

Achten Sie darauf die Umsätze zuerst betragsmäßig anzupassen (Zeile 18) und dann ggf. zusätzlich zeitlich zu verschieben (Zeile 19).

Szenario-Tabelle

Mit Hilfe der ersten beiden Schritte sind wir bereits in der Lage, mit nur einem Klick (Szenariowahl in Zelle E7) unser komplettes Planungsmodell gemäß der jeweiligen Szenario-Annahmen durchrechnen zu lassen.

Manchmal ist es aber darüber hinaus interessant, ausgewählte Parameter (wie bspw. Kapitalbedarf, Renditen, wichtige Kennzahlen) für alle Szenarien im direkten Vergleich zu sehen und zwar ohne dass die einzelnen Szenarien über die Auswahlzelle manuell gewechselt werden müssen. Dies lässt sich in Excel mit Hilfe einer sogenannten Datentabelle realisieren.

Zur Vorbereitung wird eine Rohtabelle geschaffen, welche in den Zeilen die gewünschten zentralen Output-Parameter enthält und in den Spalten die verschiedenen Szenarien 1 bis x (siehe Screenshot).

• Die Werte in Spalte H kommen aus dem Modell, sind also direkt verlinkt.
• H25 wird mit Zelle E9 verlinkt, so dass hier der Szenario-Name angezeigt wird.
• H26 bspw. =Modell!I34
• H27 bspw. =Modell!U45
• Die Bezeichnungen in den Zellen I24:M24 können Sie direkt nach oben verlinken, also zu den Zellen I9:M9.
• Die Szenario-Nummern in den Zellen I25:M25 entweder ebenfalls von oben verlinken (also von I7:M7) oder direkt als absolute Werte eingeben (1 bis x).

Nach diesen Vorbereitungen kommt der entscheidende Schritt, dass Einfügen der eigentlichen Datentabelle (Mehrfachoperation).

Gehen Sie dazu wie folgt vor:

1. Markieren Sie den Bereich H25:M30 (also inklusive Spalte H und Zeile 25)
2. Wählen Sie nun im Register „Daten“ in der Gruppe „Datentools“ das Symbol „Was-wäre-wenn-Analyse“ und dann den Befehl „Datentabelle“. In dem erscheinenden Dialogfeld tragen Sie im Textfeld „Werte aus Zeile“: den Zellnamen E7 (also die Szenario-Auswahlzelle) ein. Das Textfeld „Werte aus Spalte“ bleibt leer, da wir nur eine eindimensionale Datentabelle erstellen.
3. Nach Klick auf „OK“ sollte sich ihre Tabelle wie von Geisterhand füllen.

Anmerkungen und Hintergrundinformationen

• Bei der Mehrfachoperation handelt es sich um eine Matrixfunktion (das können Sie sehr leicht in der Bearbeitungsleiste erkennen, wenn Sie einen der vielen Ergebniswerte auswählen => siehe Screenshot unten)
• In unserem Fall benötigen wir nur eine eindimensionale Tabelle, da wir lediglich einen Wert, nämlich die Nummer des gewünschten Szenarios ändern wollen. Wichtig: In der Datentabelle wird zwar nur dieser eine Wert verändert, aber durch die Wahl eines anderen Szenarios werden ggf. alle veränderbaren Plangrößen die sie definiert haben im Modell entsprechend angepasst. D.h. die Auswirkungen betreffen durchaus mehrere Inputfaktoren.
• Die Ergebniswerte werden nicht automatisch formatiert. Sie müssen selbst eine Formatierung wählen (%, Zahl etc.). Dies kann auch schon vorher durchgeführt werden.

Die Werte in Spalte H dürfen nicht gelöscht werden, da diese für die Mehrfachoperation benötigt werden. Gezeigt werden hier immer die Werte des ausgewählten Szenarios. Damit diese nicht doppelt mit den Spalten rechts daneben auftauchen, können Sie die Spalte H in Excel einfach ausblenden (Gruppierung => dann oben auf das „Minuszeichen“ klicken).

Wie üblich haben wir auch zu diesem Tutorial eine entsprechende Excel-Datei vorbereitet, die wir ihnen gerne kostenlos zur Verfügung stellen.