Kostenanalyse mit dynamischem Wasserfalldiagramm

Zusammenfassung

Überblick

Bei der Analyse von Kosten- oder Erlösanteilen kommen oft Varianten der Diagrammtypen Säule oder Kreis zum Einsatz. Für eine geringe Anzahl von Datenpunkten ist dies meistens eine gute Wahl. Wenn Sie aber 8 oder mehr Teile eines Ganzen übersichtlich präsentieren wollen, dann sind die beiden erwähnten Diagrammtypen schlicht ungeeignet.

Die Lösung dieses Problems ist ein sogenanntes dynamisches Wasserfalldiagramm, das eine benutzerdefinierte Abwandlung des Standard-Diagrammtyps Gestapelte Säulen darstellt.

Aber entscheiden Sie doch selbst, welche der Darstellungen in Abb. 1 am übersichtlichsten die absoluten und relativen Anteile von 10 Kostenarten an den Gesamtkosten visualisiert.

Im hier vorgestellten Praxisbeispiel lautete die Vorgabe, eine Verteilung von 10 Kostenarten in 8 unterschiedlichen Kostenstellen darzustellen. Diesem Anspruch wurde mit einer dynamisierten Präsentationsfassung genügt (Konten statisch, Kostenstellen variabel), deren Anfertigung keine Programmierkenntnisse erfordert.

Anhand der Excel-Musterdatei Kostenanalyse können Sie die in unserem Praxisbeitrag vorgestellten Arbeitsschritte nachvollziehen.

[1]

1 Versionsabhängigkeit, Funktionalität und Struktur

Abb. 1: Was ist die beste Übersicht für die Kostenverteilung? Das Wasserfalldiagramm liefert alles auf einen Blick.

Die Musterdatei zu diesem Beitrag enthält eine aus 4 Arbeitsblättern aufgebaute Berichtslösung. Zur Anfertigung eines vergleichbaren Modells sind fortgeschrittene Anwenderkenntnisse nützlich. Die Konstruktion ist unter den Excel-Versionen 2010 und 2007 gleichartig. Unter Excel 2003 gelingt sie ebenso, auch wenn auch dort etliche Inhalte und Vorgehensweisen von den hier vorgestellten abweichen. Das in Abbildung 2 komplett dargestellte Diagramm befindet sich im Arbeitsblatt Focus 1 der Beispieldatei. Wenn Sie dort (oben rechts) das Steuerelement Bildlaufleiste benutzen, blättern Sie hin oder her durch die Kostenstellen – die Diagrammüberschrift passt sich jeweils an – und bringen so 9 verschiedene, kostenstellenspezifische Datenverteilungen zur Ansicht (Kostenstellen gesamt und 8 Einzelkostenstellen).

Abb. 2: Auf einen Blick: Das Ganze und seine Teile – dargestellt als Wasserfalldiagramm

Ganz links sehen Sie jeweils die Säule der Gesamtkosten, daneben in absteigender Versetzung die Anteile der 10 Kostenarten als "schwebende Säulensegmente". Jedes Segment schwebt in seiner eigenen Spalte und ist zusätzlich in einer 3-zeiligen Rubrikachsenbeschriftung mit seinem Kontennamen, dem Euro-Betrag und dem relativen Anteil an den Gesamtkosten bezeichnet. Ober- und unterhalb des jeweils größten Segments erscheinen zur schnellen Identifikation hervorhebende Signalfarben.

Mit dem kleinen Drehfeld unten rechts im Diagramm kehren Sie die horizontale Reihenfolge der Segmentanzeige um; Sie wählen also entweder die Ansicht Kosten 01 bis Kosten 10 oder aber Kosten 10 bis Kosten 01.

Die Erstellung und Bearbeitung eines solchen Diagramms ist weit weniger schwierig, als in mancherlei Quellen behauptet wird. Und irgendwelche Tools zu seiner Anfertigung brauchen Sie ganz gewiss nicht, da reichen die "Bordmittel" von Excel vollständig aus.

Im Folgenden erhalten Sie zunächst nur eine pauschale Übersicht. In Kapitel 2 folgen dann detaillierte Erläuterungen zur Konstruktion des Modells.

Die funktionalen Teile der Arbeitsmappe bestehen aus 4 Arbeitsblättern. Im Arbeitsblatt Namensliste finden Sie die kleine Liste jener 6 Namen, die für einige Zellbereiche vergeben wurden und die, wie Sie sehen werden, in etlichen Formeln zum Einsatz kommen. Die Gültigkeit der Namen würde für die gesamte Arbeitsmappe festgelegt. Die kleine Liste dient lediglich der Dokumentation und zu Prüfzwecken.

Das Arbeitsblatt Daten 1 beinhaltet in einer Konten-Kostenstellen-Matrix neben den Kontenbezeichnungen die zu präsentierenden Werte. Wenn Sie hier Texte der Kontenbezeichnungen ändern oder andere Euro-Werte eingeben, wirkt sich das direkt auf die Inhalte der anderen Arbeitsblätter aus. Im Bereich I9:P18 sind Konstanten hinterlegt, die in Spalte H und in Zeile 8 summiert werden.

In jeder Datenzeile der Tabelle werden der jeweils höchste und der jeweils niedrigste Wert mit Farbsignalen angezeigt. Dies ist das Ergebnis einer bedingten Formatierung. Deren Resultat ist eine Zweifachinformation:

• Vertikale Betrachtung: Welche Kosten sind in dieser Kostenstelle die höchsten, welche die niedrigsten?
• Horizontale Betrachtung: Welche Kostenstelle zeigt welche kostenspezifischen Maxima oder Minima in Relation zu den anderen Kostenstellen?

Das Arbeitsblatt Basis 1 ist Grundlage des Diagramms. Es besteht fast vollständig aus Formeln und ist geschützt, jedoch ohne Kennwort. Die Struktur des Arbeitsblatts mag zunächst befremdlich und merkwürdig erscheinen, wird sich aber aus den Beschreibungen in Kapitel 3 erschließen.

Die blau formatierten Zahlen im Bereich F13:P23 enthalten jene 11 Euro-Werte, die im Diagramm als Säulen bzw. Säulensegmente zu sehen sind. Die Formelergebnisse im Bereich F10:P12 werden in die 3-zeilige Rubrikachsenbeschriftung übernommen. ...