Das Schöne an Excel-Tabellen ist ja, dass man mit Hilfe von Funktionen bestimmte Berechnungen automatisch anstellen kann. Man gibt ein paar Werte ein und die SUMME-Funktion präsentiert sofort das Ergebnis.
Wer jetzt eine Kalkulation mit verschiedenen Werten durchspielen möchte, kann die Werte für die unterschiedlichen Szenarien eintippen und sich die alten Ergebnisse jedes Mal merken.
Oder er verwendet ein cleveres, wenn auch ein relativ unbekanntes Hilfsmittel aus der Excel-Trickkiste: den Szenario-Manager!
Und so geht’s:
Umsatz- und Kostenplanung
Ich habe eine kleine Tabelle vorbereitet, in der fünf verschiedene Produkte enthalten sind. Da ich sowohl die festgelegten Verkaufspreise als auch die Stückkosten eingetragen habe, kann ich den geplanten Umsätzen die zu erwartenden Kosten gegenüberstellen und mir so automatisch das Ergebnis bzw. den Deckungsbeitrag 1 berechnen lassen:
Meine Beispiel-Kalkulation
Alles was ich dazu noch eingeben muss, sind die geplanten Stückzahlen.
Planspiele
Nun möchte der Vertriebsleiter verschiedene Varianten beim Produktmix durchspielen, um zu sehen, wie sich unterschiedliche Stückzahlen auf das Ergebnis auswirken.
Ein angenommener erster Produktmix sieht wie folgt aus:
Veränderbare Werte in Spalte B
Damit ich beim Durchspielen weiterer Szenarien meine ursprünglichen Stückzahlen nicht verliere, greife ich auf den besagten Szenario-Manager zurück. Dieses Werkzeug gehört neben den Datentabellen und der Zielwertsuche zu den Instrumenten der sogenannten „Was-wäre-wenn-Analyse“.
Zu finden ist der Szenario-Manager im Menüband unter „Daten – Was-wäre-wenn-Analyse – Szenario-Manager…“:
Was-wäre-wenn-Analyse
Dadurch kommt ein relativ unscheinbares Fenster zum Vorschein. Erstellen wir also unser erstes Szenario durch einen Klick auf die Schaltfläche „Hinzufügen…“
Der Szenario-Manager
Ein neues Szenario hinzufügen
In diesem Fenster gebe ich zunächst einen möglichst sprechenden Namen für mein Szenario ein. Im zweiten Eingabefeld „Veränderbare Zellen“ trage ich den Zellbereich ein, in dem die Stückzahlen stehen. Dies kann auch durch Markieren der entsprechenden Zellen mit der Maus erfolgen.
Szenario-Name und veränderbare Zellen
Gut zu wissen: In einem Szenario darf es bis zu maximal 32 veränderbare Zellen geben.
Bei Bedarf kann ich im Kommentarfeld noch ein paar Erläuterungen zu diesem Szenario eingeben.
Die beiden Checkboxen „Änderungen verhindern“ und „Schutz“ lassen wir unverändert. Was es damit auf sich hat, erkläre ich später noch.
Mit einem Klick auf „OK“ übernehme ich die Eingabe und Excel zeigt mir nun die momentan für dieses Szenario verwendeten Werte aus den veränderbaren Zellen:
Die Werte in den veränderbaren Zellen
Wenn ich nun auf „OK“ klicke, gelange ich zurück in den Szenario-Manager, wo nun mein erstes Szenario aufgelistet ist:
Das erste Szenario in der Liste
Fügen wir nun ein weiteres Szenario dazu. Da ich in meinem Beispiel mit höheren Verkaufszahlen beim Produkt „Black Mambo“ plane, nenne ich mein Szenario entsprechend. Die veränderbaren Zellen bleiben dieselben wie im ersten Szenario:
Szenario 2 hinzufügen
Im folgenden Fenster werden zunächst noch die Verkaufszahlen aus dem ersten Szenario angezeigt, die ich aber jetzt mit den Stückzahlen des neuen Szenarios überschreibe:
Geänderte Werte eintragen
Danach befinde ich mich wieder im Fenster des Szenario-Managers und erstelle mit „Hinzufügen“ ein drittes Szenario, das bei mir folgende Werte enthält:
Werte für das dritte Szenario
Was hat man nun davon?
Nach wie vor wird immer nur ein Szenario in der Tabelle angezeigt. Nun kann ich aber ganz einfach auf ein beliebiges zuvor erstelltes Szenario umschalten, indem ich dieses aus der Liste auswähle und unten auf die Schaltfläche „Anzeigen“ klicke:
Die verschiedenen Szenarien anzeigen
Nicht schlecht, oder?
Wer jetzt gerne einen direkten Vergleich hätte, dem kann auch geholfen werden.
Der Szenariobericht
Den kompletten Überblick über alle gespeicherten Szenarien kann man sich mit Hilfe des Szenarioberichts verschaffen. Dieses verbirgt sich im Szenario-Manager hinter der Schaltfläche „Zusammenfassen…“:
Die Zusammenfassung
Hier hat man die Auswahl zwischen dem Szenariobericht und dem PivotTable-Szenariobericht:
Szenario-Bericht auswählen
Wir wählen die erste Option und erhalten damit folgende Zusammenfassung:
Der fertige Szenario-Bericht
Über die automatisch erstellten Gliederungssymbole lassen sich dann z.B. auch die Kommentare zu den Szenarien einblenden. Allerdings hat das Ganze einen Schönheitsfehler: Anstelle vernünftiger Bezeichnungen für die veränderbaren Zellen und für die Ergebniszellen werden nur deren Zelladressen angezeigt, was alles andere als übersichtlich ist.
Das lässt sich allerdings leicht beheben. Wir müssen in unserer Ausgangstabelle die Zellen nur mit entsprechenden Namen versehen. Dazu markieren wir zunächst den Bereich mit den veränderbaren Zellen einschließlich der Bezeichnungen, also A2:B6. In der Registerkarte „Formeln“ klicken wir nun in der Gruppe „Definierte Namen“ auf die Schaltfläche „Aus Auswahl erstellen“. Da sich in unserem markierten Bereich die Namen links von den Wertezellen befinden und Excel dies auch korrekt erkennt, bestätigen wir den Vorschlag einfach.
Für die Benennung der Ergebniszellen wählen wir eine Zelle nach der anderen aus und tippen links oben ins Namensfeld den gewünschten Namen ein.
Namen definieren
Das war’s.
Wenn wir nun noch einmal den Szenariobericht erstellen, sieht das Ergebnis schon deutlich besser aus, oder?
Der verbesserte Szenario-Bericht
Schutzfunktionen
Ich bin dir ja noch eine Erklärung schuldig. Du erinnerst dich: Beim Erstellen der Szenarien gab es die beiden Checkboxen „Änderungen verhindern“ und „Ausblenden“:
Schutzfunktionen
Die erste Checkbox, die auch standardmäßig aktiviert ist, verhindert, dass ein Szenario nachträglich verändert werden kann. Damit wird nämlich im Szenario-Manager die Schaltfläche „Bearbeiten“ deaktiviert.
Mit der zweiten Checkbox lässt sich das gerade ausgewählte Szenario ausblenden, so dass es in der Liste im Szenario-Manager nicht mehr angezeigt wird. Dies habe ich im folgenden Screenshot für Szenario 3 umgesetzt:
Blattschutz ist aktiviert
Wichtig:
Beide Checkboxen entfalten ihre Wirkung erst dann, wenn der Blattschutz aktiviert wird!
Das war’s, was ich dir heute über den Szenario-Manager zeigen wollte. Vielleicht findest du ja für dich ein paar sinnvolle Einsatzmöglichkeiten.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.