Die grafische Darstellung von Soll-Ist-Werten einschließlich ihrer Abweichungen gehört zu den Standardaufgaben in der Praxis von Controlling- und Finanzverantwortlichen. In diesem Beitrag wird die Erstellung eines aussagekräftigen Excel-Diagramms erläutert, welches sich durch eine besonders effiziente Informationsvermittlung auszeichnet und eine hohe Flexibilität hinsichtlich der Darstellungsmöglichkeiten bietet.
Flexible Darstellung von Abweichungen in einem anschaulichen Diagramm
Egal ob Umsätze, Kundenzahlen, Kostenpositionen oder Gewinngrößen wie EBITDA oder EBIT analysiert werden sollen, die grafische Darstellung von Soll-Ist-Werten einschließlich ihrer Abweichungen gehört zu den Standardaufgaben in der Praxis von Controlling- und Finanzverantwortlichen.
Im Folgenden erläutern wir ihnen die Erstellung eines aussagekräftigen Excel-Diagramms (bspw. Umsätze für einen 12-Monatszeitraum), welches nicht nur eine Vielzahl an Informationen effizient vermittelt, sondern sich auch durch eine hohe Flexibilität hinsichtlich der Darstellungsmöglichkeiten auszeichnet.
Allgemeine Grundsätze
Zwei Dinge erleichtern uns in Excel grundsätzlich das Leben. Zum einen die Trennung von Ausgangsdaten, Einstellmöglichkeiten sowie Rohdaten für die Grafik. Zum anderen die Anwendung von etablierten Financial Modeling Standards (vgl. z.B. www.fast-standard.org), wozu bspw. die Verwendung spezieller Zellenformatvorlagen (z.B. für Eingabezellen), aber auch die Nutzung von Gültigkeitsprüfungen, benutzerdefinierten Zahlenformaten und bedingten Formatierungen zählt. Während uns der erste Punkt nicht nur eine besserer Übersicht, eine einfache Aktualisierung und eine schnelle Kopie fertiger Grafiken in andere Dateien ermöglicht, sorgt der zweite Punkt für eine hohe Flexibilität, Nachvollziehbarkeit und einfache Bedienbarkeit, auch durch Dritte.Mehrsprachigkeit
Zur Umsetzung der Mehrsprachigkeit bedarf es zweier Vorbereitungsschritte.- Zunächst fügen wir eine Auswahlliste für alle später zur Verfügung stehenden Sprachen ein und ordnen jeder Sprache eine Nr. 1 bis x zu (vgl. Abb. 2).
- Bei den Einstellungen für das Diagramm fügen wir einen „Sprachwahl-Schalter“ ein. Über die Nutzung von „Daten“ => „Datenüberprüfung“ => Einstellungen => Zulassen: „Liste“ begrenzen wir die Auswahlmöglichkeiten auf unsere oben angelegte Sprachtabelle. Daneben fügen wir eine SVERWEIS-Funktion ein (siehe Formel in Abb. 2), die uns je nach gewählter Sprache die entsprechende Nr. zurückgibt und benennen diese Zelle z.B. mit „language“.
Die eigentliche Übersetzung aller Zellen geschieht mittels der WAHL-Funktion. Dabei ist der Index-Wert unsere Zelle „language“, die Werte 1 bis x dann die jeweiligen Übersetzungen.
Beispiel Grundformel:
Wird z.B. „English“ ausgewählt, ergibt die Zelle „language“ den Wert 2, so dass der zweite Wert der WAHL-Funktion ausgegeben wird. Texte sind jeweils in “ “ zu setzen, es sind aber auch Verkettungen bzw. Formeln möglich wie wir sie bspw. für die Beschriftungen nutzen (vgl. Zelle F31 in Bsp.-Datei). Eine Erweiterung auf zusätzliche Sprachen ist so jederzeit schnell möglich.
Emojis und Schalter
Zur Visualisierung lassen sich auch in Excel beliebige Emojis (od. andere Symbole) nutzen. Besonders komfortabel, da dynamisch, geht dies mittels der UNIZEICHEN-Funktion, die als einziges Argument eine Zahl erwartet. Da jedem Emoji eine bestimmte hexadezimal Zahl zugeordnet ist, könnte bspw. ein „thumbs up“ 
so aussehen:
Eine Liste mit allen Emoji Codes finden Sie bspw. unter http://unicode.org/emoji/charts/full-emoji-list.html.
Schalter sind Zellen, die nur eine „1“ (Ein bzw. Ja) oder eine „0“ (Aus bzw. Nein) enthalten. Mit diesen können wir später bestimmte Funktionen im Diagramm einfach aktivieren bzw. deaktivieren. Per Datenüberprüfung wird auf 0 und 1 beschränkt und über ein benutzerdefiniertes Zahlenformat ("Ja";;"Nein") lässt sich die Darstellung aussagekräftig z.B. auf „Ja“ und „Nein“ einstellen. Da die Zellen auf diese Weise aber tatsächlich Werte und keinen Text enthalten, kann man weiter mit ihnen in Formeln „rechnen“.
Aufbereitung der Rohdaten für die Grafik
Da wir eine dreizeilige x-Achsenbeschriftung mit dem jeweiligen Monat, der Abweichung in % sowie dem Quartal im Diagramm verwenden möchten, starten wir mit der Aufbereitung der dafür nötigen Daten.
Soll der Monat jeweils durch die ersten 3 Buchstaben dargestellt werden, lässt sich dies grundsätzlich über ein benutzerdefiniertes Zahlenformat erreichen. Allerdings gibt es dann ein Problem bei Sprachumschaltung, z.B. im Dez bzw. Dec. Aus diesem Grund gehen wir den Umweg über eine Auswahltabelle, die alle Monatskürzel in beiden Sprachen enthält und diesen jeweils Zahlen zuordnet (1 für Jan, 2 für Feb etc.). In der Rohdatentabelle fügen wir dann entsprechende SVERWEIS-Funktionen ein und zwar innerhalb der WAHL-Funktion (vgl. Bsp-Datei).
Die pos. bzw. neg. Abweichung (in %) lässt sich über eine einfache Formel (Abweichung / Plan) berechnen, sollte aber 0 sein, wenn bei den IST-Werten noch kein Wert eingetragen ist. Auf diese Weise kann die Datei auch unterjährig nach und nach bei jedem Reporting-Termin um weitere IST-Daten ergänzt werden.
Die Ermittlung des jeweiligen Quartals aus einem Datum (z.B. in C7) erreichen wir mit der Formel:
Das benutzerdefinierte Zahlenformat („Q-„0) macht den reinen Zahlenwert aussagekräftiger. Damit das Ganze ab Monat 2 dynamisch ist und nur jeweils einmal pro Quartal ein Wert angezeigt wird, können wir auch hier die WAHL-Funktion nutzen. Index ist der Monat des jeweiligen Datums (z.B. Monat(C8)), das Quartal soll nur in den Monaten 1, 4, 7, und 10 ausgewiesen werden (Formel von oben), ansonsten soll die Zelle leer (““) bleiben.
Die Werte für die IST- und Plan-Daten sowie die Vorjahreswerte werden einfach von oben (Rohdaten) verlinkt. Da wir die VJ-Werte an-/abschaltbar machen wollen, wird, falls der entsprechende Schalter (VJ_on) auf „0“ steht, ein #NV ausgegeben. Beispiel:
Für die Positionierung unserer Emojis wollen wir (neben einem An-/Aus-Schalter) zwei Alternativen vorsehen. Entweder direkt über den Säulen oder alle auf einer gemeinsamen Höhe. Für den ersten Fall nehmen wir einfach für jeden Monat den Maximalwert aus Plan und IST. Für den zweiten Fall den Maximalwert aller Monatswerte aus Plan und IST, den wir ggf. noch um einen einstellbaren %-Wert (zur Feinjustierung in Zelle I24) verändern können. Für den ersten Monat ergibt sich bspw. die Formel:
Last but not least müssen noch je nach Abweichung die gewünschten Emojis für die Beschriftung zugeordnet werden. Das könnte man theoretisch in einer Spalte machen, wir verwenden aber zwei, so dass später in der Grafik unterschiedliche Farben für pos. und neg. Abweichungen verwendet werden können. Die Formel berücksichtigt den An-/Aus-Schalter, prüft, ob die Abweichung pos. od. neg. ist (ansonsten leer) und fügt dann über einen Link zu den Vorgaben das dort eingestellte Emoji ein.
Profi-Tipp
Möchten Sie unterschiedliche Emojis in Abhängigkeit von der Höhe der Abweichung angezeigt bekommen, können statt zwei bspw. 5 Symbole und individuelle Grenzen definiert und die Formeln entsprechend angepasst werden (siehe Formel in C10 bzw. C11 in Abb. 3) Wichtig dabei ist, bei der SVERWEIS-Funktion den Parameter WAHR zu verwenden, nur dann werden die jeweiligen Zwischenwerte auch korrekt zugeordnet.
Grafik – Excel-Diagramm
Das Grundgerüst ist eine simple Balkengrafik (Gruppierte Säulen). Selektieren Sie alle Monatsdaten IST, PLAN und Vorjahr inkl. der Überschriften und drücken einfach „F11“. Schon ist die Grundgrafik fertig (vgl. Abb. 4).
Selektieren Sie im Diagramm die Vorjahreswerte, indem sie eine der (hier grünen) Säulen anklicken, dann „Maus rechts“ und „Datenreihen-Diagrammtyp ändern …“ wählen. Für die Vorjahreswerte wählen Sie dann „Linie mit Datenpunkten aus“. Bei „Datenreihe formatieren“ wird nun bei Markierung unter Markierungsoptionen „Integriert“ gewählt, bei Füllung „Bild od. Texturfüllung“ und bei Rahmen „keine Linie“ gewählt. Unter Linie „keine Linie“ auswählen (vgl. Zwischenstand Abb. 5).
Wir werden nun die Punkte durch eine eigene horizontale Linie ersetzen. Dafür fügen Sie zunächst an irgendeiner Stelle in der Excel Datei über „Einfügen“ => „Formen“ => eine „Linie“ ein und formatieren diese nach ihren Wünschen (Breite, Farbe, Rahmen etc.). Anschließend selektieren Sie die fertige Linie und bringen Sie über [Strg[ + [C] in die Zwischenablage. Jetzt selektieren Sie im Diagramm einen der Punkte und fügen anschließend einfach mit [Strg[ + [V] den Inhalt ihrer Zwischenablage ein.
Selektieren Sie nun eine der beiden Säulen und formatieren unter „Reihenoptionen“ bei Reihenachsenüberlappung auf 100%, damit beide Säulen genau übereinander liegen, bei Abstandsbreite können Sie eine beliebige Breite für die Säulen vorgeben (z.B. ebenfalls 100%).
Jetzt muss bei den Planwerten noch die Farbe deaktiviert (keine Füllung) und ein Rahmen definiert werden (Farbe, Dicke etc.). Die IST-Werte füllen Sie mit einer beliebigen Farbe (ohne Rahmen).
Die Legende können Sie an eine beliebige Stelle verschieben, genau wie den Diagrammtitel und wunschgemäß formatieren (Schriftart, Größe etc.). Um die Bezeichnung des Titels dynamisch anzupassen, selektieren Sie die Box „Diagrammtitel“ und geben in die Bearbeitungsleiste den Link zur entsprechenden Input-Zelle (Überschrift Grafik) ein. Für den Zusatzhinweis auf die kumulierten Abweichungen fügen wir einfach ein Textfeld ein, positionieren und formatieren es wunschgemäß und verlinken ebenfalls in die Zelle, die den Text enthält.
Für die mehrzeilige x-Achsenbeschriftung selektieren wir das Diagramm und wählen nach „Maus rechts“ => „Daten auswählen“. Dort bei „Horizontale Achsenbeschriftung“ auf „Bearbeiten“ klicken (vgl. Abb. 6) und die 3 Spalten der Rohdaten für alle 12 Monate auswählen (mit „OK“ Fenster schließen).
Jetzt fehlen nur noch die Emojis. Dazu fügen wir unter „Daten auswählen“ auf der linken Seite zwei neue Reihen hinzu (pos. u. neg. Abw.). Bei „Reihenwerte“ verwenden wir aber in beiden Fällen die Werte aus der Spalte „Max“ der Rohdaten, da es hier nur um die Positionierung geht.
Anschließend blenden Sie jeweils die Datenbeschriftungen für diese beiden Reihen ein und weisen über Beschriftungsoptionen „Wert aus Zellen“ einmal die pos. Emoji-Spalte und einmal die neg. Emoji-Spalte zu. Führungslinien und Wert deaktivieren und bei „Beschriftungsposition“ => „Über“ anhaken. Anschließend noch grün (pos.) bzw. rot (neg.) färben und die Punkte entfernen („Markierungsoptionen“ => „Keine“).
Falls der Eintrag nun auch in der Legende auftaucht, einfach bei beiden Emoji-Reihen unter „Reihenname“ ein Leerzeichen (in Anführungszeichen) eingeben (=“ “).
SOLL-IST-Analysen für Kostengrößen
Im Beispiel wurde die Analyse für „Umsätze“ durchgeführt. Hier gilt mehr (IST als PLAN) ist besser (= pos. Abw.). Bei Kostenpositionen ist dies allerdings genau umgekehrt. Weniger ist besser. In diesen Fällen müssen Sie bei den aufbereiteten Rohdaten lediglich die Formel in der Spalte „Abweichung“ anpassen bzw. umdrehen, alles andere für die Grafik bleibt gleich (bei den Nebenrechnungen muss das Vorzeichen in Zelle L31 geändert werden).
Fazit
Durch die vielen verschiedenen Schalter und flexiblen Verlinkungen, lassen sich nach Fertigstellung mit wenigen Einstellungen individuelle Diagramme in ihrem Corporate Design und in der gewünschten Sprache generieren. Neue Rohdaten, z.B. für andere Soll-Ist-Analysen sind schnell einkopiert bzw. bestehende schnell aktualisiert.
Die Schalter und auch die Sprachwahl müssen nur einmal definiert werden und können ohne Mehraufwand für beliebige Dateien genutzt bzw. kopiert werden. In dem hier erläuterten Beispiel-Diagramm wurden relativ viele Funktionen eingebaut, so dass es ggf. überladen wirken kann. Der Grund liegt aber allein in der Vermittlung möglichst vieler Techniken. In der Praxis gilt häufig „weniger ist mehr“, aber zum Glück können Sie ja alles mit einem Klick an- und ausschalten.
Wie üblich haben wir auch zu diesem Tutorial eine kostenlose Excel-Download-Datei vorbereitet. Diese erleichtert ihnen das Beispiel nachzuvollziehen und kann als Ausgangsvorlage für eigene Grafiken genutzt werden.
