So erstellen Sie Drop-Down-Szenarien in Ihrem Finanzmodell

Die am häufigsten verwendete Methode zum Erstellen von Szenarien ist die Verwendung einer Kombination aus Formeln und Dropdown-Feldern. Im Finanzmodell erstellen Sie eine Tabelle mit möglichen Szenarien und deren Eingaben und verknüpfen die Szenarionamen mit einer Dropdown-Box für die Eingabezelle. Die Eingaben des Modells sind mit der Szenariotabelle verknüpft. Wenn das Modell ordnungsgemäß erstellt wurde und alle Eingaben zu den Ausgaben fließen, ändern sich die Ergebnisse des Modells, wenn der Benutzer verschiedene Optionen aus dem Dropdown-Feld auswählt.

Dropdown-Felder für die Datenvalidierung werden für eine Reihe von verschiedenen Zwecken in der Finanzmodellierung verwendet, einschließlich der Szenarioanalyse.

Nutzung von Datenvalidierungen zur Modellierung von Rentabilitätsszenarien

Datei 0801.xlsx herunterladen . Öffnen Sie es und wählen Sie die Registerkarte mit der Bezeichnung 8-1-Start.

So wie dies modelliert wurde, sind die Eingaben in Spalte B aufgereiht. Sie könnten eine Sensitivitätsanalyse durchführen, indem Sie einfach eine der Eingaben ändern – ändern Sie beispielsweise die Kunden pro Anruf-Operator in Zelle B3 von 40 auf 45, und Sie werden Sehen Sie, wie sich alle abhängigen Zahlen ändern. Dies wäre eine Sensitivitätsanalyse, da Sie nur eine Variable ändern. Stattdessen werden Sie in dieser vollständigen Szenarioanalyse-Übung mehrere Variablen gleichzeitig ändern, sodass Sie mehr tun müssen, als nur einige Zahlen manuell zu optimieren.

Gehen Sie folgendermaßen vor, um eine Szenarioanalyse mithilfe von Dropdown-Feldern zur Datenvalidierung durchzuführen:

Nehmen Sie das heruntergeladene Modell und schneiden Sie die Beschreibungen von Spalte C bis Spalte F aus und fügen Sie sie ein. Sie können dies tun, indem Sie die Zellen C6:C8 markieren, Strg+X drücken, Zelle F6 auswählen und die Eingabetaste drücken.

Die Eingaben in den Zellen B3 bis B8 sind der aktive Bereich, der das Modell antreibt und dies auch bleiben wird. Sie müssen jedoch zu Formeln werden, die sich je nach dem Dropdown-Feld ändern, das Sie erstellen.

Kopieren Sie den Bereich in Spalte B in die Spalten C, D und E.

Sie können dies tun, indem Sie B3:B8 markieren, Strg+C drücken, die Zellen C3:E3 auswählen und die Eingabetaste drücken. Diese Beträge sind für jedes Szenario gleich, bis Sie sie ändern.

In Zeile 2 Geben Sie den Titel Best Case , Basisfall , und Worst Case.

Einrichten des Modells für die Szenarioanalyse.

Beachten Sie, dass die Formeln immer noch mit den Eingaben in Spalte B verknüpft sind, wie Sie sehen können, indem Sie Zelle C12 auswählen und die Tastenkombination F2 drücken.

Bearbeiten Sie die Eingaben unter jedem Szenario.

Sie können alles eingeben, was Sie für wahrscheinlich halten, aber um die Zahlen mit denen in diesem Beispiel abzugleichen, geben Sie die Werte ein. Ignorieren Sie Spalte B vorerst.

Eingaben für die Szenarioanalyse.

Jetzt müssen Sie das Dropdown-Feld oben hinzufügen, das Ihre Szenarien steuert. Es spielt keine Rolle, wo genau Sie das Dropdown-Feld platzieren, aber es sollte sich an einer leicht zu findenden Stelle befinden, normalerweise oben auf der Seite.

Geben Sie in Zelle E1 den Titel Szenario ein .

Wählen Sie Zelle F1 aus und ändern Sie die Formatierung in Eingabe, damit der Benutzer sehen kann, dass diese Zelle bearbeitet werden kann.

Der einfachste Weg, dies zu tun, ist, diesen Schritten zu folgen:

Klicken Sie auf eine der Zellen, die bereits als Eingabe formatiert sind, z. B. Zelle E3.

Klicken Sie auf das Format Painter-Symbol im Abschnitt Zwischenablage auf der linken Seite der Registerkarte Start. Ihr Cursor verwandelt sich in einen Pinsel.

Wählen Sie Zelle F1 aus, um die Formatierung einzufügen.

Format Painter ist normalerweise für den einmaligen Gebrauch bestimmt. Nachdem Sie die Zelle ausgewählt haben, verschwindet der Pinsel vom Cursor. Wenn Sie möchten, dass der Format Painter „klebrig“ wird und auf mehrere Zellen angewendet wird, doppelklicken Sie auf das Symbol, wenn Sie es auf der Registerkarte Start auswählen.

Wählen Sie nun in Zelle F1 im Abschnitt Datentools der Registerkarte Daten die Option Datenvalidierung aus.

Das Dialogfeld Datenvalidierung wird angezeigt.

Ändern Sie auf der Registerkarte Einstellungen die Dropdown-Liste Zulassen in Liste, verwenden Sie die Maus, um den Bereich =$C$2:$E$2 auszuwählen, und klicken Sie auf OK.

Erstellen der Dropdown-Szenarien für die Datenvalidierung.

Klicken Sie auf das Dropdown-Feld, das jetzt neben Zelle F1 angezeigt wird, und wählen Sie eines der Szenarien aus (z. B. Basisfall).

Anwenden von Formeln auf Szenarien

Die Zellen in Spalte B steuern immer noch das Modell und müssen durch Formeln ersetzt werden. Bevor Sie die Formeln hinzufügen, sollten Sie jedoch die Formatierung der Zellen im Bereich ändern, um anzuzeigen, dass sie Formeln anstelle von hartcodierten Zahlen enthalten. Folge diesen Schritten:

Wählen Sie die Zellen B3: B8 aus, und wählen Sie die Füllfarbe aus der Gruppe Schriftart auf der Registerkarte Start aus.

Ändern Sie die Füllfarbe in einen weißen Hintergrund.

Es ist sehr wichtig, zwischen Formeln und Eingabezellen in einem Modell zu unterscheiden. Sie müssen jedem Benutzer, der das Modell öffnet, klarmachen, dass die Zellen in diesem Bereich Formeln enthalten und nicht überschrieben werden sollten.

Jetzt müssen Sie die hartcodierten Werte in Spalte B durch Formeln ersetzen, die sich ändern, wenn sich das Dropdown-Feld ändert. Sie können dies mit einer Reihe verschiedener Funktionen tun; ein HLOOKUP, eine verschachtelte IF-Anweisung, ein IFS und ein SUMIF werden alle den Zweck erfüllen. Fügen Sie die Formeln hinzu, indem Sie die folgenden Schritte ausführen:

Wählen Sie Zelle B3 aus, und fügen Sie eine Formel hinzu, die den Wert abhängig von dem, was sich in Zelle F1 befindet, ändert.

So sieht die Formel unter den verschiedenen Optionen aus:

• =HVERWEIS($F$1,$C$2:$E$8,2,0)

  Beachten Sie, dass Sie bei dieser Lösung die Zeilenindexnummer von 2 auf 3 usw. ändern müssen, wenn Sie die Formel nach unten kopieren. Stattdessen könnten Sie im dritten Feld eine ROW-Funktion wie folgt verwenden: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)

• =IF($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3))
• =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3)
• =SUMME($C$2:$E$2,$F$1,C3:E3)

  Wie immer stehen verschiedene Optionen zur Auswahl und die beste Lösung ist die, die am einfachsten und am leichtesten zu verstehen ist. Jede dieser Funktionen führt zu genau dem gleichen Ergebnis, aber das Ändern der Zeilenindexnummer in HLOOKUP ist nicht robust, und das Hinzufügen der ROW kann für einen Benutzer verwirrend sein. Die verschachtelte IF-Anweisung ist schwierig zu erstellen und zu befolgen, und obwohl die neue IFS-Funktion eine verschachtelte IF-Funktion vereinfachen soll, ist sie immer noch ziemlich unhandlich. Das SUMIF ist recht einfach zu erstellen und zu befolgen, und es ist einfach zu erweitern, wenn Sie in Zukunft zusätzliche Szenarien hinzufügen müssen.

  Beachten Sie, dass IFS eine neue Funktion ist, die nur verfügbar ist, wenn Office 365 und Excel 2016 oder höher installiert sind. Wenn Sie diese Funktion verwenden und jemand dieses Modell in einer früheren Excel-Version öffnet, kann er die Formel anzeigen, aber nicht bearbeiten.

Kopieren Sie die Formel in Zelle B3 in die Spalte.

Die abgeschlossene Szenarioanalyse.

Wenn Sie ein normales Kopieren und Einfügen verwenden, verlieren Sie Ihre gesamte Formatierung. Es ist wichtig, die Formatierung des Modells beizubehalten, damit Sie auf einen Blick erkennen können, welche Eingaben in Dollarwerten, Prozentsätzen oder Kundenzahlen erfolgen. Verwenden Sie Formeln einfügen, um die Formatierung beizubehalten. Sie können darauf zugreifen, indem Sie die Zelle in die Zwischenablage kopieren, den Zielbereich markieren, mit der rechten Maustaste klicken und das Symbol Formeln einfügen auswählen, um nur Formeln einzufügen und die Formatierung intakt zu lassen.

Jetzt zum lustigen Teil! Es ist an der Zeit, die Szenariofunktionalität im Modell zu testen.

Klicken Sie auf Zelle F1, ändern Sie das Dropdown-Feld und beobachten Sie, wie sich die Modellausgaben ändern, während Sie zwischen den verschiedenen Szenarien wechseln.