Excel-Szenario-Manager: Die privaten Finanzen planen und prüfen

Info: Szenario-Manager

Der Excel-Szenario-Manager ermöglicht es, komplexe Zukunftsszenarien für voneinander abhängige Zahlen zu entwerfen und zu vergleichen. Im Rahmen einer Tabellenkalkulation werden dabei Rechenmodelle entwickelt, mit deren Hilfe man variable Werte durchspielen kann. Das bedeutet, dass Werte geändert werden, um zu analysieren, in welcher Weise dies zu Änderungen in Ergebniszellen führt.

Szenarien können Sie sowohl für Ihre gesamte finanzielle Situation als auch für Teilbereiche wie die Entwicklung eines Wertpapierdepots, Kosten einer USA-Reise bei schwankenden Wechselkursen, Ausgaben bei steigender oder sinkender Inflation einsetzen. Man kann, je nach Bedarf, Best- Case-, Worst-Case- sowie gemischte Szenarien generieren. Ein Szenariobericht fasst die Resultate übersichtlich zusammen. Obwohl das Prozedere auf den ersten Blick kompliziert wirkt, ist die praktische Umsetzung nicht schwierig.

Hinweis: Calc aus dem LibreOffice ermöglicht die Arbeit mit Szenarien ebenfalls, wenn auch nicht ganz so schön gelöst wie in Excel. Den Calc-Workshop "Szenarien in Calc aus dem Libre Office einrichten" finden Sie am Ende.

Excel Szenario-Manager

1. Ausgangsdaten zusammenstellen und in einem Tabellenmodell vorbereiten

Jedes Szenario ist individuell. Es gibt keine allgemeingültige Lösung. Deshalb erläutern wir die Arbeit mit dem Szenario-Manager nachfolgend anhand eines bewusst einfach gehaltenen Beispiels. Selbstverständlich sind weitaus vielschichtigere Szenarien möglich.

Generell gilt: Bevor Sie das Werkzeug einsetzen, müssen Sie sich darüber klar werden, was genau Sie vergleichen wollen und welche Veränderungen Sie dabei berücksichtigen müssen. Bezogen auf das Beispiel einer monatlichen Einnahme-/Ausgabesituation, entspricht das Ergebnis dem Saldo aus Einkommen und Ausgaben. Die variablen Einnahmen und Ausgaben sind somit Dreh- und Angelpunkt der Analyse. Ziel ist es zu untersuchen, wie sich der Saldo bei realistischen, günstigen und ungünstigen Randbedingungen verändert.

2. Namen für einen besseren Überblick über die Szenarien vergeben

Vergeben Sie idealerweise aussagekräftige Namen für jede einzelne variable Zelle sowie für die Ergebniszelle. Das hat den Vorteil, dass die Namen später sowohl im Eingabedialog als auch in der Berichterstattung anstelle von abstrakten Zellbezeichnungen verwendet werden. So werden die Eingaben erleichtert, Fehlerquellen minimiert, die Berichte übersichtlicher und damit besser lesbar.

Recht einfach funktioniert die Namensvergabe für eine ausgewählte Zelle über Formeln/Namen definieren. Bei komplexen Tabellen können Sie die Namen unter Formeln/Namens-Manager kontrollieren und bei Bedarf korrigieren.

3. Szenario-Manager aufrufen und erstes Szenario anlegen

Wenn Sie alle Vorbereitungen getroffen haben, markieren Sie alle variablen Zellen (hier C3 sowie den Zellbereich C5:C10). Mehrfachmarkierungen nicht zusammenhängender Zellen sind durch Drücken der [Strg]-Taste möglich. Klicken Sie im Menüband Daten in der Gruppe Datentools nacheinander auf Was-wäre-wenn-Analyse/Szenario-Manager. Über die Schaltfläche Hinzufügen erreichen Sie das Fenster Szenario hinzufügen. Im Beispiel werden eine realistische, optimistische und pessimistische Prognose angelegt. Tragen Sie in das Feld Szenarioname einen Namen ein, zum Beispiel Realistisch. Excel übernimmt in das Feld Veränderbare Zellen die in der Tabelle markierten Zellen. In unserem Beispiel sind das nur sieben, ein Szenario kann jedoch bis zu 32 Zellen verarbeiten.

Bei Bedarf haben Sie die Möglichkeit, im Feld Kommentar Informationen zum Szenario zu hinterlegen. Darüber hinaus werden dort automatisch Bearbeitungsdatum und Modifikationen an den Szenarien dokumentiert. In komplexen Modellen ist dies durchaus sinnvoll, für das Beispiel sind zusätzliche Informationen nicht erforderlich. Im Bereich Schutz legen Sie fest, ob Sie Änderungen an Ihrem Szenario zulassen oder nicht.

Ist das Kontrollkästchen Änderungen verhindern abgehakt, wird das Szenario im Zusammenhang mit dem Blattschutz (Überprüfen/Blatt schützen) geschützt. Ist Ausblenden aktiviert, kann das Szenario in einer geschützten Tabelle nicht angezeigt werden. Sie dürfen für jede Prognose anders vorgehen, Sie können zum Beispiel die pessimistische Variante schützen, während Sie die optimistische Prognose freigeben. Verlassen Sie das Fenster über OK, erreichen Sie den Dialog Szenariowerte.

4. Szenariowerte im zugehörigen Dialog erfassen

Erfassen Sie nun die gewünschten Werte für die veränderbaren Zellen (vgl. Abbildung unten rechts). Achten Sie darauf, bei Prozentzahlen das Prozentzeichen zu erfassen. Alternativ tippen Sie Dezimalzahlen ein. Excel wandelt Angaben in Prozent ohnehin in Dezimalzahlen um. Verlassen Sie das Fenster Szenariowerte über OK.

5. Weitere Wertegruppen für noch mehr Vergleichsmaterial erfassen

Sie gelangen erneut in das Fenster Szenario hinzufügen. Über diesen Dialog können Sie nacheinander unbegrenzt weitere Szenarien (Wertegruppen) anlegen, wie zuvor beschrieben.

6. Per Mausklick einen Szenariobericht für mehr Transparenz erstellen

Überblick über die verschiedenen Szenarien verschafft Ihnen der Szenariobericht. Klicken Sie im Fenster Szenario-Manager auf Zusammenfassung. Im folgenden Fenster erkennt Excel dann, dass es um den Saldo in Zelle D12 geht. Sollte dies ausnahmsweise nicht der Fall sein, tippen Sie die Ergebniszelle manuell ein. Entscheiden Sie sich für Szenariobericht, und bestätigen Sie mit OK. Excel generiert dann umgehend eine Übersicht in einem eigenen Tabellenarbeitsblatt.

7. Szenarien bearbeiten, nachträglich Wertegruppen hinzufügen, löschen

Um später ein Szenario zu bearbeiten oder weitere Szenarien hinzuzufügen, öffnen Sie das Fenster Szenario-Manager. Für weitere Szenarien gehen Sie auf Hinzufügen, zur Bearbeitung markieren Sie den zugehörigen Namen und klicken anschließend auf Bearbeiten. Die gewünschten Änderungen werden im Dialogfeld Szenariowerte erfasst und bestätigt. Wenn nach Änderung eines Szenarios dessen Name beibehalten wird, werden die veränderbaren Werte im ursprünglichen Szenario durch die neuen Werte ersetzt.

Löschvorgänge werden ebenfalls im Dialog Szenario-Manager durchgeführt (Schaltfläche Löschen). Beachten Sie, dass der Löschvorgang ohne Sicherheitsabfrage erfolgt und sich Löschbefehle im Zusammenhang mit Szenarien nicht zurücknehmen lassen. Achtung: Die Änderungen fließen nicht in bereits erstellte Szenarioberichte ein. Sie müssen zwingend einen neuen Bericht generieren, um neue Anpassungsarbeiten zu berücksichtigen.

8. Szenarien aus anderen Tabellen und Arbeitsmappen übernehmen

Sie verfügen bereits über Szenarien, in denen Sie Ihre finanzielle Situation in anderen Tabellen oder Arbeitsmappen analysiert haben? Diese Einzelszenarien können Sie in einem komplexen Szenario nutzen. Damit das problemlos klappt, sollten die verschiedenen Szenarien mit einer identischen Zellstruktur arbeiten, was jedoch häufig nicht der Fall ist. Für unser Beispiel müssten sich die variablen Werte in C3 sowie C5:C10 befinden. Eventuell können Sie versuchen, die Strukturen durch Hinzufügen beziehungsweise Entfernen von Zeilen/Spalten anzugleichen.

So geht’s: Vor der Zusammenführung öffnen Sie alle Arbeitsmappen, die die gewünschten Szenarien enthalten. In welcher Mappe die Zusammenführung erfolgt, ist in Excel unerheblich. Klicken Sie im Dialog Szenario-Manager auf Zusammenführen. Das folgende Fenster listet alle verfügbaren Tabellen auf, die Szenarien enthalten, und Sie können auswählen.

9. Alternative: Mehr als 32 Werte in einer Datentabelle analysieren

Die Datentabelle arbeitet ähnlich wie Szenarien mit veränderbaren Werten, ist aber auf zwei Variablen begrenzt. Vorteil: Die Variablen können ihrerseits verschiedene Werte annehmen, was die Zahl der zu untersuchenden Werte erhöht. Prädestiniert ist das Instrument zum Beispiel für die Analyse von Krediten. Entscheidend ist bei diesem Tool, wo genau die variablen Werte in der Tabelle stehen.

Die Austauschwerte müssen zeilen- beziehungsweise spaltenförmig angeordnet werden. Ein Wertepaar (hier Periode) steht unter, das zweite Wertepaar (hier Zinssätze) neben der Formel. Erfassen Sie zunächst die Formel zur Berechnung der Ausgangssituation (vgl. unten "Nützliche Funktionen rund um das Thema Finanzen").

Markieren Sie den Zellbereich ausgehend von der Formel (C2) bis zum Ende der Inhalte der variablen Werte (im Beispiel C2:H22). So erfasst die Markierung automatisch den Bereich mit den Angaben zu den variablen Werten samt der Ergebniszellen. Wählen Sie Daten/Was-wäre-wenn-Analyse/Datentabelle, und geben Sie unter Werte aus Zelle die Zelle B4, unter Werte aus Spalte B3 an. Mit OK bestätigen, fertig.

Übersicht: Nützliche Funktionen rund um das Thema Finanzen

Im Zusammenhang mit dem Thema Sparen und Kredite gibt es verschiedene finanzmathematische Funktionen, die Sie bei Rechenvorgängen unterstützen und sich selbstverständlich in Szenarien nutzen lassen. Dazu gehören zum Beispiel:

• BW: Eine Art „Rentenfunktion“ die ermittelt, wie viel Kapital Sie benötigen, um in einer vorgegebenen Anzahl von Perioden regelmäßig einen bestimmten Betrag entnehmen zu können.
  Beispiel:
  =BW(3%/12;20*12;-500) liefert 90.155,46 € (Betrag der zur Verfügungstehen muss, um 20 Jahre lang eine monatliche Zahlung von 500 Euro zu erhalten."
• KAPZ: Ermittelt die Tilgung für einen Kredit. Achtung: Die Angaben beziehen sich immer nur auf eine bestimmte Periode, also ein Jahr, ein Quartal, einen Monat oder einen Tag (vgl. ZINSZ).
  Beispiel:
  =KAPZ(4%/12;1;3*12;3000) weist für den ersten Monat -78,57 Euro aus (Betrag der bei einem Zinssatz von 4 %, einer Kredithöhe von 3.000 Euro und einer Laufzeit von 3 Jahren (getilgt) zu zahlen ist.
• RMZ: Berechnet, wie viel Sie monatlich sparen müssen, um ein bestimmtes Wunschkapital zu erhalten. Die Funktion eignet sich auch zur Ermittlung der Höhe einer Annuität im Rahmen einer Kreditaufnahme.
  Beispiel:
  =RMZ(2 %/12;25*12;0;75 000) ergibt -192,89 Euro (Betrag, den Sie bei einem Zinssatz von 2 % monatlich sparen müssen, um nach 25 Jahren 75 000 Euro zur Verfügung zu haben.
• ZINSZ: Nennt Ihnen die Höhe von Zinsen sowohl im Zusammenhang mit Krediten als auch mit Geldanlagen. Achtung: Wie bei KAPZ beziehen sich die Angaben nur auf eine bestimmte Periode.
  Beispiel:
  =ZINSZ(4 %/12;5;5*12;20 000;0) liefert -62,62 Euro (Zinsen für den ersten Monats eines Kredits in Höhe von 20 000 Euro bei einer Laufzeit von 5 Jahren und einem Zinssatz von 4 %; für den letzten Monat fallen -1,22 Euro Zinsen an (=ZINSZ(4 %/12;60;5*12;20 000;0)).
• ZW: Steht für "zukünftiger Wert" und wird zur Berechnung von Endwerten bei regelmäßig eingehenden Beträgen eingesetzt. Tipp: Um diese Funktion auch bei einer einmaligen Zahlung zu verwenden, lassen Sie das Argument RMZ (Regelmäßige Zahlung) frei.
  Beispiel:
  =ZW(3 %/12;120;-100) liefert 13 974,14 Euro (Betrag, der bei einem Zinssatz von 3 % nach 10 Jahren verfügbar ist, wenn monatlich 100 Euro gespart werden.

Workshop: Szenarien in Calc aus dem Libre Office einrichten

Wenn Sie Szenarien in Calc generieren wollen, müssen Sie zunächst ein geeignetes Tabellengrundgerüst wie in Microsoft Excel erstellen. Die weitere Vorgehensweise weicht jedoch ab. Um beispielsweise zu analysieren, wie der Bestand von Wertpapieren sich in einem Depot mit drei Wertpapieren (WP 1: 10 000 Euro, WP 2: 5000 Euro, WP 3: 8000 Euro) entwickeln könnte, gehen Sie wie folgt vor:

1. Legen Sie zunächst das Tabellengrundgerüst fest (vgl. Abbildung rechts). In Spalte C werden mögliche prozentuale Änderungen erfasst.
2. Um die aus den Spalten B und C resultierenden Werte zu ermitteln, tragen Sie in D2 die Formel =B2+B2*C2 ein und kopieren sie in die beiden nachfolgenden Zellen.
3. Der Gesamtbestand des Depots ergibt sich in D5 (=Summe(D2:D4)). Nachdem Sie die Formel erfasst haben, sind alle Vorbereitungen getroffen.
4. Markieren Sie die veränderbaren Zellen (im Beispiel C2:C4), und wählen Sie Extras/Szenarien. Achtung: Bei fehlender Markierung erscheint der Befehl abgeblendet und ist entsprechend nicht verfügbar.
5. Vergeben Siefür das Szenario einen aussagekräftigen Namen wie Best Case, und bestätigen Sie mit OK.
6. Erfassen Sie die gewünschten variablen Werte, beispielsweise optimistische Zahlen, und legen Sie über Extras/Szenarien ein weiteres Szenario an, wie zuvor beschrieben. Wiederholen Sie den Vorgang bei Bedarf.
7. Über ein Auswahlfeld können Sie anschließend die unterschiedlichen Szenarien vergleichen. Das Ergebnis ändert sich in Abhängigkeit der Auswahl des Szenarios.

Tipp: Wenn Sie im Vorfeld das Kontrollkästchen Ganze Tabelle kopieren abhaken, wird für jedes Szenario ein eigenes Tabellenarbeitsblatt erstellt.