Was-wäre-wenn-Analyse mit Scenario Manager

Der Szenariomanager ist nützlich, wenn Sie mehr als zwei Variablen in der Sensitivitätsanalyse haben. Der Szenariomanager erstellt Szenarien für jeden Satz von Eingabewerten für die betrachteten Variablen. Mithilfe von Szenarien können Sie eine Reihe möglicher Ergebnisse untersuchen und Folgendes unterstützen:

• Variiert bis zu 32 Eingabesätze.
• Zusammenführen der Szenarien aus mehreren verschiedenen Arbeitsblättern oder Arbeitsmappen.

Wenn Sie mehr als 32 Eingabesätze analysieren möchten und die Werte nur eine oder zwei Variablen darstellen, können Sie Datentabellen verwenden. Obwohl es nur auf eine oder zwei Variablen beschränkt ist, kann eine Datentabelle so viele verschiedene Eingabewerte enthalten, wie Sie möchten. Weitere Informationen finden Sie unter Was-wäre-wenn-Analyse mit Datentabellen in diesem Lernprogramm.

Szenarien

Ein Szenario besteht aus einer Reihe von Werten, die Excel speichert und die automatisch in Ihrem Arbeitsblatt ersetzt werden können. Sie können verschiedene Wertegruppen als Szenarien in einem Arbeitsblatt erstellen und speichern und dann zwischen diesen Szenarien wechseln, um die verschiedenen Ergebnisse anzuzeigen.

Beispielsweise können Sie verschiedene Budgetszenarien haben, in denen verschiedene mögliche Einnahmen und Ausgaben verglichen werden. Sie können auch verschiedene Kreditszenarien aus verschiedenen Quellen verwenden, die verschiedene mögliche Zinssätze und Kreditlaufzeiten vergleichen.

Wenn die Informationen, die Sie in Szenarien verwenden möchten, aus verschiedenen Quellen stammen, können Sie die Informationen in separaten Arbeitsmappen sammeln und dann die Szenarien aus den verschiedenen Arbeitsmappen zu einer zusammenführen.

Nachdem Sie alle benötigten Szenarien erstellt haben, können Sie einen Szenario-Zusammenfassungsbericht erstellen.

• Das beinhaltet Informationen aus allen Szenarien.
• Auf diese Weise können Sie die Szenarien nebeneinander vergleichen.

Szenariomanager

Der Szenario-Manager ist eines der Was-wäre-wenn-Analyse-Tools in Excel.

Um einen Analysebericht mit Scenario Manager zu erstellen, müssen Sie die folgenden Schritte ausführen:

Step 1 - Definieren Sie den Satz von Anfangswerten und identifizieren Sie die Eingabezellen, die Sie variieren möchten, die sich ändernden Zellen.

Step 2 - Erstellen Sie jedes Szenario, benennen Sie das Szenario und geben Sie den Wert für jede sich ändernde Eingabezelle für dieses Szenario ein.

Step 3- Wählen Sie die Ausgabezellen aus, die als Ergebniszellen bezeichnet werden, die Sie verfolgen möchten. Diese Zellen enthalten Formeln im ursprünglichen Wertesatz. Die Formeln verwenden die sich ändernden Eingabezellen.

Der Szenariomanager erstellt einen Bericht, der die Eingabe- und Ausgabewerte für jedes Szenario enthält.

Anfangswerte für Szenarien

Bevor Sie mehrere verschiedene Szenarien erstellen, müssen Sie eine Reihe von Anfangswerten definieren, auf denen die Szenarien basieren.

Die Schritte zum Einrichten der Anfangswerte für Szenarien sind:

• Definieren Sie die Zellen, die die Eingabewerte enthalten.
• Benennen Sie die Eingabezellen entsprechend.
• Identifizieren Sie die Eingabezellen mit konstanten Werten.
• Geben Sie die Werte für die konstanten Eingaben an.
• Identifizieren Sie die Eingabezellen mit sich ändernden Werten.
• Geben Sie die Anfangswerte für die sich ändernden Eingaben an.
• Definieren Sie die Zellen, die die Ergebnisse enthalten. Die Ergebniszellen enthalten Formeln.
• Benennen Sie die Ergebniszellen entsprechend.
• Platzieren Sie die Formeln in den Ergebniszellen.

Betrachten Sie das vorherige Beispiel eines Darlehens. Gehen Sie nun wie folgt vor:

• Definieren Sie eine Zelle für den Darlehensbetrag.

  • Dieser Eingabewert ist für alle Szenarien konstant.

  • Nennen Sie die Zelle Loan_Amount.

  • Geben Sie den Wert als 5.000.000 an.

• Definieren Sie die Zellen für Zinssatz, Anzahl der Zahlungen und Art (Zahlung am Anfang oder Ende des Monats).

  • Diese Eingabewerte ändern sich je nach Szenario.

  • Nennen Sie die Zellen Interest_Rate, NPER und Type.

  • Geben Sie die Anfangswerte für die Analyse in diesen Zellen als 12%, 360 bzw. 0 an.

• Definieren Sie die Zelle für das EMI.

  • Dies ist der Ergebniswert.

  • Nennen Sie die Zellen-EMI.

  • Platzieren Sie die Formel in dieser Zelle als -

    =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Ihr Arbeitsblatt sieht wie folgt aus -

Wie Sie sehen können, befinden sich die Eingabezellen und die Ergebniszellen in Spalte C mit den in Spalte D angegebenen Namen.

Szenarien erstellen

Nachdem Sie die Anfangswerte für die Szenarien eingerichtet haben, können Sie die Szenarien mit dem Szenario-Manager wie folgt erstellen:

• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEN.
• Klicken Sie in der Gruppe Datentools auf Was-wäre-wenn-Analyse.
• Wählen Sie Szenario-Manager aus der Dropdown-Liste.

Das Dialogfeld Szenariomanager wird angezeigt. Sie können beobachten, dass es eine Nachricht enthält -

“No Scenarios defined. Choose Add to.”

Sie müssen Szenarien für jeden Satz sich ändernder Werte im Szenario-Manager erstellen. Es ist gut, das erste Szenario mit Anfangswerten zu definieren, da Sie jederzeit zu den Anfangswerten zurückkehren können, während Sie verschiedene Szenarien anzeigen.

Erstellen Sie das erste Szenario mit den Anfangswerten wie folgt:

• Drücke den Add Schaltfläche im Dialogfeld "Szenariomanager".

Das Add Scenario Das Dialogfeld wird angezeigt.

• Geben Sie unter Szenarioname Szenario 1 ein.
• Geben Sie unter Zellen ändern die Referenzen für die Zellen ein, dh C3, C4 und C5, und drücken Sie die Strg-Taste.

Der Name des Dialogfelds ändert sich in Szenario bearbeiten.

• Bearbeiten Sie den Text in der Comment as – Initial Values Box.

• Wählen Sie unter Schutz die Option Änderungen verhindern und klicken Sie dann auf OK.

Das Scenario ValuesDas Dialogfeld wird angezeigt. Die von Ihnen definierten Anfangswerte werden in jedem der sich ändernden Zellenfelder angezeigt.

Scenario 1 mit den Anfangswerten wird erstellt.

Erstellen Sie drei weitere Szenarien mit unterschiedlichen Werten in den sich ändernden Zellen wie folgt:

• Drücke den Add Schaltfläche im Dialogfeld "Szenariowerte".

Das Dialogfeld "Szenario hinzufügen" wird angezeigt. Beachten Sie, dass C3, C4, C5 im Feld Zellen ändern angezeigt werden.

• Geben Sie im Feld Szenarioname Szenario 2 ein.

• Bearbeiten Sie den Text in der Comment as - Unterschiedlicher Zinssatz.

• Wählen Sie unter Schutz Änderungen verhindern aus und klicken Sie auf OK.

Das Scenario ValuesDas Dialogfeld wird angezeigt. Die Anfangswerte erscheinen in den sich ändernden Zellen. Ändern Sie den Wert vonInterest_Rate zu 0.13 und klicken Sie auf Add.

Das Add ScenarioDas Dialogfeld wird angezeigt. Beachten Sie, dass C3, C4, C5 im Feld unter Zellenwechsel angezeigt werden.

• Geben Sie im Feld Szenarioname Szenario 3 ein.

• Bearbeiten Sie den Text in der CommentBox als - Unterschiedliche Nr. von Zahlungen.

• Wählen Sie unter Schutz Änderungen verhindern aus und klicken Sie auf OK.

Das Dialogfeld Szenariowerte wird angezeigt. Die Anfangswerte erscheinen in den sich ändernden Zellen. Ändern Sie den Wert von NPER in 300 und klicken Sie aufAdd.

Das Add ScenarioDas Dialogfeld wird angezeigt. Beachten Sie, dass C3, C4, C5 im Feld Zellen ändern angezeigt werden.

• Geben Sie im Feld Szenarioname Szenario 4 ein.

• Bearbeiten Sie den Text in der Comment Feld als - Unterschiedliche Zahlungsart.

• Wählen Sie unter Schutz Änderungen verhindern aus und klicken Sie auf OK.

Das Scenario ValuesDas Dialogfeld wird angezeigt. Die Anfangswerte erscheinen in den sich ändernden Zellen. Ändern Sie den Wert von Typ in 1. Klicken Sie auf OK, da Sie alle Szenarien hinzugefügt haben, die Sie hinzufügen möchten.

Das Scenario ManagerDas Dialogfeld wird angezeigt. Im Feld unter Szenarien finden Sie die Namen aller von Ihnen erstellten Szenarien.

• Klicken Sie auf Szenario 1. Wie Sie wissen, enthält Szenario 1 die Anfangswerte.


Klicken Sie nun auf Summary. Das Dialogfeld Szenariozusammenfassung wird angezeigt.

Szenario-Zusammenfassungsberichte

Excel bietet zwei Arten von Szenario-Zusammenfassungsberichten:

• Zusammenfassung des Szenarios.
• Szenario PivotTable-Bericht.

Im Dialogfeld "Szenarioübersicht" finden Sie diese beiden Berichtstypen.

Wählen Sie unter Berichtstyp die Option Szenarioübersicht aus.

Szenarioübersicht

In dem Result cells Wählen Sie im Feld die Zelle aus C6 (Hier hatten wir das hingelegt PMTFunktion). OK klicken.

Der Szenario-Zusammenfassungsbericht wird in einem neuen Arbeitsblatt angezeigt. Das Arbeitsblatt wird als Szenarioübersicht bezeichnet.

Im Bericht "Szenarioübersicht" können Sie Folgendes beobachten:

• Changing Cells- Listet alle Zellen auf, die als sich ändernde Zellen verwendet werden. Da Sie die Zellen Interest_Rate, NPER und Type benannt haben, scheinen diese den Bericht aussagekräftig zu machen. Andernfalls werden nur Zellreferenzen aufgelistet.

• Result Cells - Zeigt die angegebene Ergebniszelle an, dh EMI.

• Current Values - Es ist die erste Spalte und listet die Werte dieses Szenarios auf, die im Dialogfeld "Szenariomanager" ausgewählt wurden, bevor der Zusammenfassungsbericht erstellt wird.

• Bei allen von Ihnen erstellten Szenarien werden die sich ändernden Zellen grau hervorgehoben.

• In der EMI-Zeile werden die Ergebniswerte für jedes Szenario angezeigt.

Sie können den Bericht aussagekräftiger gestalten, indem Sie die Kommentare anzeigen, die Sie beim Erstellen der Szenarien hinzugefügt haben.

• Klicken Sie auf die Schaltfläche + links neben der Zeile mit den Szenarionamen. Die Kommentare für die Szenarien werden in der Zeile unter den Szenarionamen angezeigt.

Szenarien aus verschiedenen Quellen

Angenommen, Sie beziehen die Szenarien aus drei verschiedenen Quellen und müssen den Szenario-Zusammenfassungsbericht in einer Master-Arbeitsmappe erstellen. Sie können dies tun, indem Sie die Szenarien aus verschiedenen Arbeitsmappen in die Master-Arbeitsmappe zusammenführen. Befolgen Sie die unten angegebenen Schritte -

• Angenommen, die Szenarien befinden sich in den Arbeitsmappen Bank1_Scenarios, Bank2_Scenarios und Bank3_Scenarios. Öffnen Sie die drei Arbeitsmappen.

• Öffnen Sie die Master-Arbeitsmappe, in der Sie die Anfangswerte haben.

• Klicken Sie in der Master-Arbeitsmappe auf DATEN> Was-wäre-wenn-Analyse> Szenario-Manager.

Das Scenario Manager Das Dialogfeld wird angezeigt.

Wie Sie sehen können, gibt es keine Szenarien, da Sie noch keine hinzugefügt haben. KlickenMerge.

Das Dialogfeld Zusammenführungsszenarien wird angezeigt.

Wie Sie sehen können, haben Sie unter Zusammenführungsszenarien von zwei Felder:

• Book
• Sheet

Sie können ein bestimmtes Arbeitsblatt aus einer bestimmten Arbeitsmappe auswählen, die die Szenarien enthält, die Sie zu Ihren Ergebnissen hinzufügen möchten. Klicken Sie auf den Dropdown-Pfeil vonBook um die Arbeitsmappen zu sehen.

Note - Die entsprechenden Arbeitsmappen sollten geöffnet sein, um in dieser Liste angezeigt zu werden.

Wählen Sie das Buch aus - Bank1_Scenarios.

Bank1 Blatt wird angezeigt. Am unteren Rand des Dialogfelds wird die Anzahl der auf dem Quellblatt gefundenen Szenarien angezeigt. OK klicken.

Das Dialogfeld Szenariomanager wird angezeigt. Die beiden Szenarien, die in der Master-Arbeitsmappe zusammengeführt wurden, werden unter Szenarien aufgelistet.

Drücke den MergeTaste. DasMerge ScenariosDas Dialogfeld wird angezeigt. Wählen Sie nunBank2_Scenarios aus der Dropdown-Liste im Feld Buch.

Bank2 Blatt wird angezeigt. Am unteren Rand des Dialogfelds wird die Anzahl der auf dem Quellblatt gefundenen Szenarien angezeigt. OK klicken.

Das Scenario ManagerDas Dialogfeld wird angezeigt. Die vier Szenarien, die in der Master-Arbeitsmappe zusammengeführt wurden, sind unter Szenarien aufgeführt.

Drücke den MergeTaste. DasMerge ScenariosDas Dialogfeld wird angezeigt. Wählen Sie nunBank3_Scenarios aus der Dropdown-Liste im Feld Buch.

Bank3 Blatt wird angezeigt. Am unteren Rand des Dialogfelds wird die Anzahl der auf dem Quellblatt gefundenen Szenarien angezeigt. OK klicken.

Das Dialogfeld Szenariomanager wird angezeigt. Die fünf Szenarien, die in der Master-Arbeitsmappe zusammengeführt wurden, werden unter Szenarien aufgelistet.

Jetzt haben Sie alle erforderlichen Szenarien, um den Szenario-Zusammenfassungsbericht zu erstellen.

Klicken Sie auf die Schaltfläche Zusammenfassung. DasScenario Summary Das Dialogfeld wird angezeigt.

• Wählen Sie Szenarioübersicht.
• Geben Sie im Feld Ergebniszellen C6 ein und klicken Sie auf OK.

Der Szenario-Zusammenfassungsbericht wird in einem neuen Arbeitsblatt in der Master-Arbeitsmappe angezeigt.

Anzeigen von Szenarien

Angenommen, Sie präsentieren Ihre Szenarien und möchten dynamisch von einem Szenario zum anderen wechseln und die Eingabewerte und Ergebniswerte des entsprechenden Szenarios anzeigen.

• Klicken Sie in der Gruppe Datentools auf DATEN> Was-wäre-wenn-Analyse> Szenariomanager. Das Dialogfeld Szenariomanager wird angezeigt. Die Liste der Szenarien wird angezeigt.

• Wählen Sie das Szenario aus, das Sie anzeigen möchten. KlickenShow.

Die Werte im Arbeitsblatt werden auf die des ausgewählten Szenarios aktualisiert. Die Ergebniswerte werden neu berechnet.

Szenario-PivotTable-Bericht

Sie können den Szenariobericht auch in Form einer PivotTable anzeigen.

• Klicken Sie in der auf die Schaltfläche Zusammenfassung Scenario ManagerDialogbox. Das Dialogfeld Szenariozusammenfassung wird angezeigt.

• Wähle aus Scenario PivotTable report unter Berichtstyp.

• Geben Sie C6 in das Feld ein Result cells Box.

Der PivotTable-Bericht für das Szenario wird in einem neuen Arbeitsblatt angezeigt.