Szenario

Untersuchung von Modellen: Was wäre, wenn ...

Kalkulation	Zahlen-Werkzeug, Profi- Tipps und Tricks
Modell	Das Modell zur Demonstration dieser Beispiele
Daten-Tabelle (1)	Daten-Tabelle mit einem Parameter
Daten-Tabelle (2)	Daten-Tabelle mit 2 Parametern
Szenario	Ein Satz von Parametern unter einem gemeinsamen Namen
Zielwert	Der Schluss vom Ergebnis auf die Ursache

Modell
Als Modell wird eine Kalkulation bezeichnet, die aus einem oder mehreren Vorgaben ein oder mehrere Ergebnisse berechnet. ▶ Prognose: Wenn der Berechnungs-Weg zwischen Vorgaben und Ergebnissen kurz und einfach ist, dann lassen sich die Ergebnisse unterschiedlkicher Vorgaben leicht berechnen.	▶ Diese Seite befasst sich jedoch mit komplexen Fällen, wenn es schwierig oder überhaupt unmöglich ist, exakte mathematische Formeln für den Zusammenhang zwischen Vorgaben und Ergebnissen aufzustellen ▶ Wenn ein derartiges komplexes Modell erstellt ist, kann man die Vorgaben manuell ändern und empirisch untersuchen, wie sich diese Änderungen auf die Ergebnisse auswirken: Die Methode von "Versuch und Irrtum", mehr oder weniger klug angewendet.
Berechnungs-Modell: Es wurde so gewählt, dass es zwar noch einfach zu berechnen ist, der mathematische Zusammenhgang zwischen den beiden Parametern "Stückzahl" und "Rohmaterial-Preis" und dem Ergebnis "Kosten / Stück" jedoch nicht trivial ist.
▶ Vorgaben: Herzustellende Stückzahl in B2 - variabel Kosten des Rohmaterials pro m2 in B3 - variabel Rohmaterial-Bedarf in m2 in B4 - fix ▶ Maschinen: Zur Herstellung stehen verschiedene Maschinen (D3:D6) zur Verfügung. Jede Maschine kann nur eine begrenzte Stückzahl (E3:E6) erzeugen.	▶ Kosten: Materialkosten (aus Stückzahl Rohmaterial-Bedarf und -Kosten) Rüstkosten zur einmaligen Umstellung einer Maschine auf das Produkt., aus der Verweis-Tabelle (F3:F6) Herstellungskosten, unterschiedlich je Maschine (G3:G6) ▶ Ergebnis: Aus der Summe aller Kosten (B8) ergebven sich die Kosten der Herstellung je Stück (B9).
Hier die verwendeten Formeln im Detail: ▶ Die Vorgaben (B2:B4) sind als Zahlenwerte eingegeben. ▶ Die Kostenbestandteile (B6 und B7) werden aus den → Verweis-Tabellen entnommen. ▶ Daraus werden die Gesamtkosten (B8) berechnet. ▶ Zuletzt wird das Ergebnis (B9) berechnet. Die Aufgabe: Analysieren sie die Kosten in Abhängigkeit von Stückzahl (100 bis 10000) und Rohmaterial-Preis (1 bis 2)

Daten-Tabelle für einen Parameter
Zur Lösung der gestellten Aufgabe können sie u.a. "Schnitte" durch das Modell legen: ▶ Sie nehmen einen der Parameter (z.B. den Rohmaterial-Preis) fix an und analysieren die Abhängigkeit des Ergebnisses von der Stückzahl. ▶ Rechts die manuelle Lösung: so berechnen sie einzelne Punkte des Modells.	Manuelle Lösung: ▶ Legen sie eine Analyse-Tabelle mit 2 Spalten an: Stückzahl und Kosten / Stück. ▶ Tragen sie links verschiedene Stückzahlen ein. ▶ Tragen sie jede Stückzahl einzeln in Zelle B2 des Modells ein und kopieren sie den Zahlenwert des berechneten Ergebnisses in die rechte Spalte (Befehl Bearbeiten | Inhalte einfügen | Werte)
Lösung mit Datentabelle: ▶ Legen sie eine Analyse-Tabelle mit verschiedenen Parameter-Werten an: Hier z.B. die Stückzahlen 100, 200, ... ▶ Tragen sie in die Zelle rechts oberhalb der Parameter-Spalte eine Formel ein, welche das gewünschte Ergebnis gibt: Hier einfach =B9 (siehe Modell) ▶ Markieren sie die gesamte Parameter-Spalte und die Formel und die Ergebnis-Spalte (hier gelb unterlegt) ▶ Menübefehl Daten | Tabelle bringt das Dialogfenster der Datentabelle (rechts).	Tragen sie im Dialogfenster in "Werte aus Spalte" jene Zelle ein, deren Zahlenwert durch die Werte der Parameter-Spalte ersetzt werden soll - hier die Stückzahl in Zelle B2. Das Feld "Werte aus Zeile" bleibt frei. ▶ Die Funktion berechnet die Ergebnisse für alle Parameter und trägt sie in die Ergebnis-Spalte ein. ▶ Jede der Ergebnis-Zellen enthält die gleiche Formel: {=MEHRFACHOPERATION(;B2)} Die {} um die Formel bezeichnen eine Matrix-Formel, d.h. Löschen oder Verändern einzelner Ergebnis-Zellen ist nicht möglich. Sie können jedoch die Formel aus allen Ergebnis-Zellen löschen oder sie dort neu eingeben. ▶ Da die Ergebnis-Spalte keine Zahlenwerte sondern eine Formel enthält, können sie die Parameter-Werte (Stückzahlen) beliebig ändern, das Ergebnis wird sofort berechnet und angezeigt.
So siehr das Ergebnis der Analyse in grafischer Darstellung aus: Wenn die Analyse nur wenige Punkte umfasst, dann kann sie noch punktweise manuell ausgeführt werden. Bei mehreren 100 Punkten ist das jedoch aussichtslos: Hier wird die Methode der Datentabelle erfolgreich eingesetzt. Hinweis: Der Hersteller hat die Methode aus unerfindlichen Gründen umbenannt: Sie war in älteren Versionen unter der Bezeichnung "Mehrfach-Operationen" bekannt.
Dieses Verfahren lässt sich zur Analyse jeweils eines isolierten Parameters heranziehen. ▶ Für Modelle mit mehreren Parametern setzen sie deren Zahlenwerte fest und analysieren der Reihe nach je einen davon mit einer Daten-Tabelle.	▶ Bei Festlegung anderer Werte für die übrigen Parameter kann die Analyse allerdings andere Ergebnisse liefern ! ▶ In diesem Fall verwenden sie z.B. Daten-Tabellen mit 2 Parametern (s.u.)
Sensitivität: Diese Methode können sie verwenden, um die Sensitivität eines Modells gegen Änderung eines Parameters zu berechnen: Sensitivität ist die partielle Ableitung des Ergebnisses gegen einen Parameter, oder anders die Steigung der Diagramm-Linie, oder anders das Ausmaß, in dem sich das Ergebnis ändert, wenn sich einer der Parameter ändert.	In den meisten Fällen ist das nicht eine Konstante, sondern eine (komplexe) Funktion: Schon dieses einfache Beispiel zeigt, dass die Steigung unterschiedliche Werte annehmen kann. Mit dieser Methode können sie u.a. untersuchen, welche Parameter ein Modell am stärksten beeinflussen (starke Steigung), und welche Parameter sich nur wenig auf das Ergebnis auswirken (geringe Steigung).

Daten-Tabelle für 2 Parameter
Das Modell enthält 2 wählbare Parameter lässt sich daher als 3dimensionale Fläche darstellen. Zur Berechnung wird die Methode der Datentabelle herangezogen, diesmal mit 2 variablen Parametern.	Die Tabelle wird so aufgebaut: ▶ Erstellen sie eine Matrix: In die Spalte ganz links tragen sie die Zahlenwerte eines Parameters ein (hier: Stückzahl). ▶ In die oberste Zeile tragen sie die Zahlenwerte des anderen Parameters ein (hier: Rohmaterial-Preis). Die links gezeigte Tabelle stellt nur einen Ausschnitt (link oben) der verwendeten Tabelle dar. ▶ Tragen sie in die Zelle links oben eine Formel ein, welche das Ergebnis berechnet (hier: =B9, siehe Modell). ▶ Markieren sie den gesamten Bereich, inkl. Formel, Parameter-Spalte und Parameter-Zeile. ▶ Menübefehl Daten | Tabelle öffnet das Dialogfenster (links)
In das Dialogfenster tragen sie ein: ▶ Werte aus Zeile = Jene Zelle, in welche die Zahlenwerte der Parameter-Zeile eingetragen werden sollen. ▶ Werte aus Spalte = Jene Zelle, in welche die Zahlenwerte der Parameter-Spalte eingetragen werden sollen. ▶ Anschließend wird die komplette Ergebnis-Matrix berechnet (Zahlen in blauer Schrift). ▶ Zur 3D-Darstellung können sie z.B. ein Oberflächen-Diagramm verwenden. Dieser Diagrammtyp ist nicht gerade die Stärke von Excel, die meisten Programme für Geschäftsgrafik bieten bessere Lösungen an.

Szenario
Ein ganzer Satz von Parametern lässt sich unter einem gemeinsamen Namen zusammenfassen und bei Bedarf in ein Modell einsetzen. Beispiele: ▶ Das Szenarion "best case" wird durch einen günstigen Preis für das Rohmaterial dargestellt (in der Praxis kann das eine Kombination aus vielen Parametern sein), das Szenarion "worst case" durch einen hohen Preis für das Rohmaterial. ▶ Sie können nun die Daten-Tabelle für einen Parameter analysieren, so wie oben gezeigt. Daneben bringen sie je eine Taste für die beiden Szenarien an. ▶ Bei Klick auf "best case" wird das Diagramm für günstige Bedingungen angezeigt, bei Klick auf "worst case" für ungünstige.	Szenario: best case worst case
Programmierung: ▶ Organisieren sie alle Vorgabe-Daten in einem zusammenhängenden Bereich (hier z.B. B2:B3) ▶ Vergeben sie sprechende Namen für die Zellen (hier z.B. stueckzahl für B2 und rm_preis für B3). ▶ Menübefehl Extras | Szenarien Das Dialogfenster Szenario-Manager bietet alle Möglichkeiten der Einstellung: ▶ Neues Szenario: Taste "Hinzufügen". Vergeben sie einen Namen und geben sie den Eingabebereich an (z.B. "test" und "$B$2:$B$3)	▶ Das Dialogfenster "Szenariowerte" wird geöffnet: Dort tragen sie die Werte (Zahlen, Texte) sämtlicher Parameter ein. ▶ Szenario anzeigen: Mit Klick auf diese Taste werden sämtliche Daten des Szenarios in ihr Modell eingesetzt. ▶ Wenn sie die Szenarien eleganter auswählen wollen (so wie im Beispiel gezeigt), dann erstellen sie → Tasten und verknüpfen diese mit entsprechenden VBA-Subs.

Zielwertsuche
Diese Funktion bietet eine Umkehrung von Ursache und Wirkung: In einem Modell wird die Frage gestellt: Welcher Parameter-Wert bewirkt ein vorgegebenes Ergebnis ? Das angewendete Verfahren ist eine Iteration, d.h. Excel versucht, den bezeichneten Parameter so lange schrittweise zu variieren, bis das gewünschte Ergebnis erreicht ist.	Die Iteration führt in seltenen Fällen zu einem mathematisch exakten Ergebnis, in den meisten Fällen zu einer guten Näherung. In einigen Fällen divergiert die Iteration, d.h. die Schrittweite verringert sich nicht bis zum Ergebnis, sie vergrößert sich - ein Ergebnis wird dann nicht erreicht.
Ausführung: ▶ Markieren sie die Ergebnis-Zelle (in diesem Modell B9) ▶ Menübefehl Extras | Zielwertsuche ▶ Geben sie den Zielwert ein (Das gesuchte Resultat) ▶ Geben sie die Veränderbare Zelle ein - jenen Parameter, der verändert werden soll - diese Zelle muss Daten enthalten, keine Formel.
Wenn ein Ergebnis gefunden wurde, dann können sie es per Mausklick in die Parameter-Zelle einsetzen.	Wie oben erwähnt, gibt es keine Garantie für ein korrektes Ergebnis - überprüfen sie es daher immer !

>   Anwendung   >   Kalkulation   >   Profi   >   Szenario