013 DAS UMFANGREICHE HANDBUCH Excel · 2014. 7. 3. · professionelle lÖsungen fÜr controlling, projekt- und personalmanagement excel im controlling das umfangreiche handbuch ignatz

PROFESSIONELLE LÖSUNGEN FÜR CONTROLLING, PROJEKT- UND PERSONALMANAGEMENT

Excel im Controlling

DAS UMFANGREICHE HANDBUCH

ignatz SCHELSuwe m. SEIDEL

• PROFESSIONELLE EXCEL-TOOLS ZUR PLANUNG UND STEUERUNG

• LÖSUNGEN FÜR DAS STRATEGISCHE UND OPERATIVE CONTROLLING

• WERKZEUGE FÜR PLANUNG, ANALYSE UND REPORTING

Im Internet: Alle Beispieldateien zum Download

Komplett in

FARBE

DAS UMFANGREICHE HANDBUCH

Für die

Versionen

2010 und

2013

Schels/Seidel

Excel im Controlling

Bleiben Sie auf dem Laufenden!

Unser Computerbuch-Newsletter informiert Sie monatlich über neue Bücher und Termine. Profitieren Sie auch von Gewinnspielen und exklusiven Leseproben. Gleich anmelden unter

www.hanser-fachbuch.de/newsletter

Hanser Update ist der IT-Blog des Hanser Verlags mit Beiträgen und Praxistipps von unseren Autoren rund um die Themen Online Marketing, Webent- wicklung, Programmierung, Softwareentwicklung sowie IT- und Projekt management. Lesen Sie mit und abonnieren Sie unsere News unter

www.hanser-fachbuch.de/update

Ignatz Schels Uwe M. Seidel

Excel im ControllingProfessionelle Lösungen für Controlling, Projekt- und Personalmanagement

Die Autoren:Ignatz Schels, WolnzachProf. Dr. Uwe M. Seidel, Regensburg

Alle in diesem Buch enthaltenen Informationen, Verfahren und Darstellungen wurden nach bestem Wissen zusammengestellt und mit Sorgfalt getestet. Dennoch sind Fehler nicht ganz auszuschließen. Aus diesem Grund sind die im vorliegenden Buch enthaltenen Informationen mit keiner Verpflichtung oder Garantie irgendeiner Art verbunden. Autoren und Verlag übernehmen infolgedessen keine juristische Verantwortung und werden keine daraus folgende oder sonstige Haftung übernehmen, die auf irgendeine Art aus der Benutzung dieser Informationen – oder Teilen davon – entsteht.Ebenso übernehmen Autoren und Verlag keine Gewähr dafür, dass beschriebene Verfahren usw. frei von Schutzrechten Dritter sind. Die Wiedergabe von Gebrauchsnamen, Handelsnamen, Waren be zeich nungen usw. in diesem Buch berechtigt deshalb auch ohne besondere Kennzeichnung nicht zu der Annahme, dass solche Namen im Sinne der Warenzeichen und MarkenschutzGesetzgebung als frei zu betrachten wären und daher von jedermann benutzt werden dürften.

Bibliografische Information der Deutschen Nationalbibliothek:Die Deutsche Nationalbibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über http://dnb.dnb.de abrufbar.

Dieses Werk ist urheberrechtlich geschützt. Alle Rechte, auch die der Übersetzung, des Nachdruckes und der Vervielfältigung des Buches, oder Teilen daraus, vorbehalten. Kein Teil des Werkes darf ohne schriftliche Genehmigung des Verlages in irgendeiner Form (Fotokopie, Mikrofilm oder ein anderes Verfahren) – auch nicht für Zwecke der Unterrichtsgestaltung – reproduziert oder unter Verwendung elektronischer Systeme verarbeitet, vervielfältigt oder verbreitet werden.

© 2014 Carl Hanser Verlag München, www.hanserfachbuch.de Lektorat: Brigitte BauerSchiewek Copy editing: Petra Kienle, Fürstenfeldbruck Herstellung: Irene Weilhart Umschlagdesign: Marc MüllerBremer, www.rebranding.de, München Umschlagrealisation: Stephan Rönigk Gesamtherstellung: Kösel, Krugzell Ausstattung patentrechtlich geschützt. Kösel FD 351, PatentNr. 0748702 Printed in Germany

PrintISBN: 9783446440722EBookISBN: 9783446440944

http://dnb.d-nb.dehttp://www.hanser-fachbuch.dehttp://www.rebranding.de, M�nchen

Tom DeMarcoAls auf der Welt das Licht ausging

ca. 560 Seiten. Hardcover

ca. € 19,99 [D] / € 20,60 [A] / sFr 28,90

ISBN 978-3-446-43960-3

Erscheint im November 2014

Sie möchten mehr über Tom DeMarco und seine Bücher erfahren.Einfach reinklicken unter www.hanser-fachbuch.de/special/demarco

»Der Weltuntergang steht bevor, aber nicht so, wie Sie denken. Dieser Krieg jagt nicht alles in die Luft, sondern schaltet alles ab.«

Im obersten Stock der Cornell University’s Clark Hall stehen der Physiker Homer Layton und seine drei jungen Assistenten vor einem Durchbruch, der es ermöglicht, die Zeit etwas langsamer ablaufen zu lassen. Sie vermuten, dass der sogenannte Layton- Effekt keinen praktischen Nutzen haben wird, rechnen aber damit, dass die von ihnen geplante Abhandlung einem Paukenschlag in der Welt der theoretischen Physik gleichkommen wird. Doch dann bemerkt Loren Martine, jüngstes Mitglied von Homers Team, etwas Seltsames: Wird die Zeit verlangsamt, reicht die in Brenn-stoffen gespeicherte Energie nicht mehr für ein plötzliches Feuer. Dinge können noch immer brennen, wenn auch langsamer, aber nichts kann mehr explodieren. Die Wissenschaftler stellen sich eine Art Layton-Effekt-Taschenlampe vor, die das Abfeuern einer Waffe verhindert. Ihnen wird klar, dass man auch die Explosion einer Bombe oder gar einen ganzen Krieg verhindern könnte.

Sie möchten mehr über Tom DeMarco und seine Bücher erfahren.Einfach Code scannen oder reinklicken unterwww.hanser-fachbuch.de/special/demarco

Hier klicken zur

Leseprobe

Vorwort . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . XI

Die Autoren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . XIII

1 Hinführung zum Thema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.1 Controlling und Controller . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.1.1 Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.1.2 Controlling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21.1.3 Zusammenspiel von Manager und Controller . . . . . . . . . . . . . . . . . . . . . . . . 2

1.2 Controlling und Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.2.1 Versionen und Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.2.2 Versionsvergleich . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41.2.3 Excel in der Cloud . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61.2.4 Excel in Office 365 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71.2.5 Excel im DataWarehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71.2.6 PowerPivot – der nächste Schritt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81.2.7 Excel als ControllingTool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91.2.8 Business Intelligence – OLAP und Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121.2.9 Sharepoint und ExcelServices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

2 Excel-Praxis für Controller . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172.1 Vorlagen, Designs und CIVorschriften . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

2.1.1 Corporate Identity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182.1.2 CIDesign in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192.1.3 Benutzerdefiniertes CIDesign . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202.1.4 Arbeitsmappenvorlage im Startordner erstellen und speichern . . . . . . . . . 22

2.2 Navigieren in Arbeitsmappen und Tabellenblättern . . . . . . . . . . . . . . . . . . . . . . . . . 242.2.1 Zoomen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242.2.2 Nützliche Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242.2.3 Neue Mappen und Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252.2.4 Formeln, Funktionen und Zellbezüge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Inhalt

VI Inhalt

2.3 Bedingte Formatierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292.4 Die wichtigsten Funktionen für Controller . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

2.4.1 Summen und Statistiken – Basisfunktionen . . . . . . . . . . . . . . . . . . . . . . . . . 322.4.2 Listen verknüpfen mit SVERWEIS() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342.4.3 WENN() und andere Logikfunktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372.4.4 SUMMEWENN() und ZÄHLENWENN() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382.4.5 Fehlerbehandlung mit ISTFEHLER() und WENNFEHLER() . . . . . . . . . . . . . 392.4.6 Textfunktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

2.5 Rechnen mit Datum und Zeit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412.5.1 ExcelKalender . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412.5.2 Datumsfunktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422.5.3 Kalenderwoche berechnen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422.5.4 Feiertage berechnen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432.5.5 ExcelZeitrechnung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

2.6 Mit Bereichsnamen arbeiten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512.6.1 Lokale und globale Bereichsnamen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512.6.2 Schnelle Zuweisung über das Namensfeld . . . . . . . . . . . . . . . . . . . . . . . . . . . 512.6.3 Namen übernehmen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522.6.4 NamensManager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532.6.5 Konstanten und Formeln in Bereichsnamen . . . . . . . . . . . . . . . . . . . . . . . . . 54

2.7 Analyse und Reporting mit PivotTables und PivotCharts . . . . . . . . . . . . . . . . . . . . . 552.7.1 Grundprinzip . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552.7.2 Voraussetzungen für PivotBerichte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552.7.3 Datenbasis vorbereiten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572.7.4 PivotTableBericht erstellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572.7.5 Elemente filtern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592.7.6 Datenschnitte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602.7.7 PivotTableBericht formatieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612.7.8 Funktionen für den Werte/Datenbereich . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612.7.9 Datumsfelder gruppieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632.7.10 Berechnete Felder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642.7.11 Berechnete Elemente . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652.7.12 Drilldown (Details anzeigen) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652.7.13 PivotBerichte aus externen Daten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662.7.14 PivotCharts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

2.8 Externe Datenquellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692.8.1 ODBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692.8.2 Externe Verbindung aufbauen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 702.8.3 Verbindungseigenschaften . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 722.8.4 Verknüpfungen aufheben . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73

2.9 Tabellen und strukturierte Verweise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 732.9.1 Datenschnitte für Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 752.9.2 Strukturierte Verweise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Inhalt VII

2.10 Mit Formularelementen arbeiten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 782.11 Mit VBAMakros arbeiten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

2.11.1 Makrosicherheit und Makros aktivieren . . . . . . . . . . . . . . . . . . . . . . . . . . . 812.11.2 Spezielle Dateinamen für Makroarbeitsmappen . . . . . . . . . . . . . . . . . . . . . 82

3 Planung und Budgetierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 833.1 Strategische Planung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

3.1.1 Wettbewerberanalyse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 843.1.2 Portfolioanalyse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 903.1.3 SWOTAnalyse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 963.1.4 StärkenSchwächenAnalyse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 973.1.5 Umweltanalyse (ChancenRisikenAnalyse) . . . . . . . . . . . . . . . . . . . . . . . . . . 1033.1.6 Unternehmensstrategien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1063.1.7 Businessplan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1093.1.8 Zielvereinbarung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122

3.2 Operative Planung und Budgetierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1323.2.1 Absatz und Umsatzplanung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1343.2.2 Personalplanung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1573.2.3 Investitionsplanung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1743.2.4 Kostenplanung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1793.2.5 Finanz und Liquiditätsplanung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1843.2.6 Projektplanung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197

4 Steuerung und Berichtswesen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2194.1 Strategische Instrumente . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219

4.1.1 Risikomanagement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2194.1.2 Target Costing/Zielkostenmanagement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2334.1.3 Rating nach Basel II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2414.1.4 ShareholderValue (SHV) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2534.1.5 Economic Value Added (EVA) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2594.1.6 Mitarbeiterzufriedenheitsbefragung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2654.1.7 Human Capital Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2734.1.8 Balanced Scorecard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277

4.2 Operative Instrumente . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2894.2.1 Erlöse und Kosten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2894.2.2 Investition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3254.2.3 Finanzen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3364.2.4 Personal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3894.2.5 Projekt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4104.2.6 Sonstige . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426

5 Berichtswesen (Reporting) und Präsentation . . . . . . . . . . . . . . . . . . . . . 4455.1 Datenaufbereitung für das Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449

5.1.1 Textdaten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449

VIII Inhalt

5.1.2 Datenimport automatisieren mit Access UNIONAbfragen . . . . . . . . . . . . . . 4515.1.3 SAPBerichte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455

5.2 PowerPivot – Business Intelligence für Controller . . . . . . . . . . . . . . . . . . . . . . . . . . . 4615.2.1 Voraussetzungen für Hardware und Software . . . . . . . . . . . . . . . . . . . . . . . . 4625.2.2 PowerPivot und SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4625.2.3 PowerPivot installieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4625.2.4 PowerPivot starten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4635.2.5 PowerPivotFenster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4645.2.6 DAXFunktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4655.2.7 Tutorial: PowerPivot für Controller . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465

5.3 Visualisierung von Berichtsinhalten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4725.3.1 Praxiseinsatz von Diagrammen in Printmedien . . . . . . . . . . . . . . . . . . . . . . 4735.3.2 Historische Hintergründe von Geschäftsgrafiken . . . . . . . . . . . . . . . . . . . . . 4735.3.3 Wissenschaftlicher Hintergrund . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4755.3.4 Methoden zur Visualisierung und Präsentation . . . . . . . . . . . . . . . . . . . . . . 479

5.4 Standarddiagramme professionell gestalten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4975.4.1 ExcelPraxis: Funktion Datenreihe() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4975.4.2 ExcelPraxis: Farbmarkierungen nutzen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4985.4.3 ExcelPraxis: Kamera . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4995.4.4 ExcelPraxis: Flexible Legende . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5005.4.5 ExcelPraxis: Grafikobjekte auf Datenreihen . . . . . . . . . . . . . . . . . . . . . . . . . 5015.4.6 ExcelPraxis: Linienabfall auf null verhindern . . . . . . . . . . . . . . . . . . . . . . . . 5025.4.7 ExcelPraxis: Balkendiagramm mit Funktion . . . . . . . . . . . . . . . . . . . . . . . . . 503

5.5 Spezialdiagramme . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5045.5.1 ExcelPraxis: BenchmarkDiagramm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5045.5.2 ExcelPraxis: Tachometerdiagramm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5055.5.3 ExcelPraxis: Wasserfalldiagramm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5085.5.4 ExcelPraxis: Umsatzbericht mit Sparklines . . . . . . . . . . . . . . . . . . . . . . . . . 5085.5.5 ExcelPraxis: Ergebnisrechnung aus GuV . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5135.5.6 ExcelPraxis: Projektportfolio mit ProjektMap . . . . . . . . . . . . . . . . . . . . . . . 5185.5.7 ExcelPraxis: QualitätsmanagementCockpit . . . . . . . . . . . . . . . . . . . . . . . . . 528

5.6 Präsentieren mit PowerPoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5395.6.1 CIVorlage vorbereiten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5395.6.2 Von Excel zu PowerPoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 540

6 VBA-Makro programmierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5476.1 Controller – Programmierer? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

6.1.1 Training und Selbststudium . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5486.1.2 Entwicklertools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5486.1.3 ExcelPraxis: Projektbericht . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548

6.2 Makrorecorder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5496.2.1 Funktionsweise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5496.2.2 ExcelPraxis: Projektbericht erstellen und aufzeichnen . . . . . . . . . . . . . . . . 550

Inhalt IX

6.3 Visual BasicEditor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5526.3.1 VBAEditor aktivieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5526.3.2 Oberfläche . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5526.3.3 Codiertechniken . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5536.3.4 Fehler und Entwurfsmodus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553

6.4 Makro starten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5536.4.1 Makros aktivieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5536.4.2 ExcelPraxis: Makroaufrufschaltfläche für Projektberichtsmakro . . . . . . . . 553

6.5 Makros editieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5546.5.1 Makrocode überprüfen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5546.5.2 ExcelPraxis: Aufgezeichnetes Makro nachbearbeiten . . . . . . . . . . . . . . . . . 5546.5.3 Makromappe speichern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557

6.6 UserForms für mehr Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5576.6.1 Eingabemasken gestalten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5576.6.2 ExcelPraxis: Projektleiterauswahl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558

6.7 Dateien versenden über Outlook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5616.7.1 Mailversand automatisieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5616.7.2 ExcelPraxis: Projektleiterbericht versenden . . . . . . . . . . . . . . . . . . . . . . . . . 561

7 Tipps und Tricks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585

Vorwort

Liebe Leserin, lieber Leser,

wir freuen uns, Ihnen unser erfolgreiches Excel-Handbuch für Controller als Neuauflage im HanserVerlag für die ExcelVersionen 2010 und 2013 präsentieren zu dürfen. In dieser Auflage haben wir unsere bewährten Tools überarbeitet, neue Themen aufbereitet und umfangreiche Praxisbeispiele zum Thema Reporting hinzugefügt. Aufgrund des durchwegs positiven Feedbacks haben wir auch weiterhin auf die bewährte Verbindung von ControllingThemen und ExcelUmsetzung gesetzt. Unser Buch ist damit mehr als nur eine Anleitung für ExcelAnwender.Das Berufsbild des Controllers hat sich besonders in den letzten Jahren stark verändert. Obwohl die meisten Unternehmen ERPSysteme und ControllingSoftware aller Art einsetzen, werden viele Aufgaben in der Praxis mit Excel gelöst. Ob als Client für BI oder ERPSoftware, als FrontEnd relationaler oder multidimensionaler Datenbanken oder für un ternehmensspezifische Tabellenmodelle – ohne Excel ist ein effektives und effizientes Finanzwesen und Controlling undenkbar.Mit diesem Buch werden Sie Ihre Arbeit mit Excel im Finanzwesen und Controlling optimieren. Wir beschreiben alle wichtigen ControllingInstrumente und zeigen, wie diese praxisbezogen mit Excel umgesetzt werden. Zu jedem Thema findet der Leser neben fachlicher Beschreibung eine klar strukturierte und sofort einsetzbare Lösung. Auch die Anbindung an ERPSysteme (z. B. SAP) und externe Datenquellen wird ausführlich beschrieben, viele Lösungen aus den Bereichen Personalwesen und Projektmanagement machen das Buch besonders interessant für Controller aus diesen Bereichen.Die ControllingInstrumente sind nach der gebräuchlichen Differenzierung von strategischer und operativer Planung und Steuerung gegliedert. Ein eigenes Kapitel widmet sich dem Berichtswesen (Reporting), für das standardisierte Methoden und Werkzeuge vorgestellt werden. Wir haben natürlich Wert darauf gelegt, dass alle vorgestellten Themen fachlich und methodisch state of the art sind, beachten Sie aber, dass es vor allem für Kennzahlenberechnungen keine absolute Richtigkeit gibt.Für die Lösungen verwenden wir ExcelWerkzeuge und Techniken, die wir für den professionellen Einsatz besonders geeignet finden, zum Beispiel Matrixfunktionen, PivotTableBerichte, dynamische Bereiche, Gültigkeitslisten, bedingte Formatierungen und Formularelemente, Tabellen und strukturierte Verweise. Einen großen Schwerpunkt bilden die Integration externer Daten mit ODBCVerknüpfungen und die Integration in die Office

XII Vorwort

Umgebung mit Access, Outlook und PowerPoint. VBAMakros kommen zum Einsatz, wenn es die Aufgabe erfordert, die meisten Tabellenmodelle sind aber ausschließlich über Kalkulationen und Verknüpfungen konstruiert. Kapitel 6 enthält eine Einweisung in die Grundlagen der VBAProgrammierung, zu vielen Themen stellen wir nützliche Makros vor.

Tipps und Tricks . . .. . . sind das Salz in der Suppe und unentbehrlich für den professionellen Einsatz von Excel im Controlling. Die besten ExcelTipps finden Sie in diesem Buch. Da viele aber zu mehreren Themen passen, haben wir sie in Kapitel 7 ausgelagert. Ein Symbol im Text verweist auf den Tipp, hier zum Beispiel der Verweis auf Tipp Nr. 01 – 02:

Tipp 01-02: Vorlage für neue Tabellenblätter

FeedbackWir freuen uns über Ihr Feedback. Bitte haben Sie Verständnis dafür, dass wir keinen Support zu den vorgestellten Beispiellösungen oder Makros leisten können. Und wenn Sie einen guten Tipp oder eine interessante ExcelLösung für Controller haben, lassen Sie es uns wissen. Unter www.excellent-controlling.de finden Sie alle Informationen zu unseren Büchern, ExcelTipps und Tools und natürlich die Termine für die besten Seminare zum Thema Excel im Controlling.

Beispiele und Tools zum DownloadAlle Beispiele und Tools, die in diesem Buch vorgestellt werden, finden Sie auf den Internetseiten des Verlags zum Download:

http://downloads.hanser.de

Viel Spaß mit dem großen ExcelHandbuch für Controller und viel Erfolg wünschen Ihnen Ihre AutorenIgnatz Schels und Dr. Uwe Seidel

http://www.excellent-controlling.dehttp://downloads.hanser.de

Ignatz Schels ist TechnikInformatiker, Spezialist für Microsoft OfficeProgramme, VBAProgrammierer und zertifizierter ProjektFachmann (GPM/IPMA). Er leitet Seminare und Workshops mit Schwerpunkt Excel/Access im Controlling, Personal und Projektmanagement. Sein Spezialgebiet ist die Optimierung von Unternehmensprozessen im ERP und MicrosoftOfficeUmfeld. Mit über 50 Fachbüchern, darunter einige Bestseller, ist er einer der erfolgreichsten ITAutoren.www.xing.com/profile/Ignatz_Schelswww.schels.dewww.excellent-controlling.de

Professor Dr. Uwe M. Seidel lehrt seit 2001 an der Fakultät Be triebswirtschaft der OTH Regensburg Rechnungswesen, Controlling und Projektmanagement. Er ist als Unternehmensberater auf diesen Gebieten und als freiberuflicher Trainer für die Controller Akademie AG und andere Fortbildungsinstitute tätig. Zudem leitet Dr. Seidel den AK Süd I des Internationalen Controllervereins (ICV). Er veröffentlichte mehrere Bücher und Beiträge zu Controlling, Rechnungswesen und RisikomanagementThemen.https://www.xing.com/profile/UweM_Seidelwww.oth-regensburg.dewww.ipm-united.comwww.seidelmc.com

Die Autoren

http://www.xing.com/profile/Ignatz_Schelshttp://www.schels.dehttp://www.excellent-controlling.dehttps://www.xing.com/profile/UweM_Seidelhttp://www.oth-regensburg.dehttp://www.ipm-united.comhttp://www.seidelmc.com

1 Hinführung zum Thema■■ 1.1■Controlling und Controller

Unternehmen müssen gemanagt und controlled werden. Die beiden verwendeten Begriffe zählen mittlerweile bereits zum gängigen Wortschatz der Betriebswirtschaftslehre. Eine exakte Übersetzung der beiden Begriffe in die deutsche Sprache bereitet Schwierigkeiten. Die deutschen Begriffe geführt und gesteuert kommen den angelsächsischen Begriffen wohl am nächsten. Das gilt auch für die Termini „Management“ und „Controlling“ wie auch „Manager“ und „Controller“.

1.1.1■Management

Management ist eine Funktion, die neben der fachlichen Tätigkeit (z. B. bestellen, buchen, konstruieren, verkaufen) in den unterschiedlichen Unternehmensbereichen (z. B. F & E, Beschaffung, Produktion, Vertrieb, IT, Rechnungswesen) ausgeübt werden muss, und hat eine personenbezogene und eine sachbezogene Komponente.

Tabelle 1.1■Personen- und sachbezogene Komponente des Managements

Personenbezogene Komponente Sachbezogene KomponenteAufgaben und Kompetenzen Festlegung von ZielenBeurteilung von Eignung und Leistung Konkretisierung der Ziele durch die Erstellung von

PlänenFörderung Steuerung auf der Basis von Soll-Ist-Vergleichen

TIPP: Aufgaben und Kompetenzen spiegeln sich im Eignungsprofil eines Mitar-beiters wider (Istprofil). Gleichzeitig ergeben sich Anforderungen aus einer wahr-zunehmenden Funktion (z. B. Leitungsfunktion im Unternehmen) oder aus einem Projekt (z. B. Suche eines Mitarbeiters, der eigenverantwortlich ein Risiko-managementsystem einführt). Es wird hier vom sog. Sollprofil gesprochen. Im nächsten Schritt sind Sollprofil und Istprofil gegeneinander abzugleichen

2 1 Hinführung zum Thema

(Beurteilung der Eignung und Leistung). Daraus wird ein Förderungsbedarf abge-leitet (z. B. besitzt der Mitarbeiter zu geringe Kenntnisse auf dem Gebiet des Risikomanagements) und Förderungsmaßnahmen werden definiert (z. B. Besuch einer Schulung zum Risikomanagement). Der Manager nimmt hier die Rolle eines Trainers im betrieblichen Lernprozess wahr.

1.1.2■Controlling

To control (engl.) bedeutet steuern, regeln, aber auch kontrollieren. Am anschaulichsten lässt sich die Bedeutung von Controlling am Beispiel eines ControllingProzesses in der Technik verdeutlichen:Mithilfe einer Temperaturvorwahl wird für einen Raum festgelegt, dass die Raumtemperatur einen bestimmten Zielwert erreichen bzw. halten soll. Die Raumtemperatur wird von einem Thermostat überwacht. Sinkt die Temperatur unter den vorgegebenen Zielwert, weil beispielsweise die Fenster undicht sind, signalisiert der Thermostat der Heizanlage, dass sie mehr heizen soll – so lange, bis die Zieltemperatur wieder erreicht ist.Aus betriebswirtschaftlicher Sicht steht Controlling für Folgendes: Vereinbarung von Zielen, Aufstellung von Plänen zur Erreichung der Ziele und auf der Basis von zeitlich fixierten – z. B. monatlichen – SollIstVergleichen (Kontrolle, Überwachung),

Entscheidung (Steuerung) über korrektive Maßnahmen, um wieder auf Plankurs zu kommen oder Abweichungen vom Ziel zum Ende der Planperiode anzukündigen.

Dies entspricht der sachbezogenen Komponente der Managementfunktion.Daraus ergibt sich, dass „Controlling zu betreiben“ Sache des Managers ist. Nicht der Controller betreibt also das Controlling, sondern der Controller leistet hierfür den betriebswirtschaftlichen Service in Form der Bereitstellung standardisierter Werkzeuge (z. B. Risikoerfassungsblätter, berichte), Beratung der Manager (z. B. beim Ausfüllen der Risikoerfassungsblätter, berichte) und „Ermahnung“, dass das Controlling auch tatsächlich vom Manager betrieben wird.

1.1.3■Zusammenspiel von Manager und Controller

Das Zusammenspiel von Manager – hier in der Form desjenigen, der für ein Ergebnis verantwortlich ist (z. B. Vertriebsleiter, der für Deckungsbeiträge, Umsatzerlöse etc. verantwortlich ist) – und Controller – also derjenige, der den Manager mit standardisierten ControllingWerkzeugen unterstützt (z. B. Vertriebscontroller) – kann durch das Schnittmengenbild aus der Mengenlehre verdeutlicht werden (vgl. Deyhle, „Controllerpraxis“, Band II, S. 177).

1.1 Controlling und Controller 3

Bild 1.1■ Controlling als Schnittmenge

Der Manager ist verantwortlich für das Ergebnis (z. B. Ausschussquote in der Produktion, Deckungsbeiträge, Umsatzerlöse im Vertrieb), wobei die tatsächliche Durchführung der Planung und Steuerung bei den operativen Einheiten liegt. Der Manager definiert die Entscheidungssituation (z. B. Ausschussoptimierung im Produktionsbereich), und die operativen Einheiten (z. B. die einzelnen Produktionsabteilungen) definieren die Maßnahmen zur Er reichung dieses Ziels (z. B. Einführung eines Qualitätsmanagementsystems, Schulung der Produktionsmitarbeiter).Der Controller ist verantwortlich für die Ergebnistransparenz. Der Controller unterstützt den Manager und die operativen Einheiten durch die Vorgabe standardisierter Werkzeuge (z. B. Berichtsformulare, einheitliche Berechnungsmethoden für Kennzahlen, Planungsformulare) und einheitlicher Verfahrensweisen (z. B. einheitliches Vorgehen bei der Erstellung der Unternehmensplanung, einheitliche Anwendung von Verfahren zur Investitionsrechnung). Dass der Controller kein Kontrolleur ist, wird dadurch deutlich, dass er keine Kontrollen im Nachhinein ausübt, sondern die Manager zur Steuerung im Vorhinein anleitet.Von der International Group of Controlling (IGC), einer 1995 gegründeten internationalen Interessengemeinschaft von Institutionen und Unternehmen, die Controlling in der praktischen Anwendung und Weiterentwicklung fördern wollen, wurde ein Controller-Leitbild erarbeitet, das das Berufs und Rollenbild des Controllers spezifiziert. Mit dem Leitbild wird auch das breite Aufgabenspektrum des Controllers deutlich.„Controller leisten als Partner des Managements einen wesentlichen Beitrag zum nachhaltigen Erfolg der Organisation.Controller . . .1. . . . gestalten und begleiten den ManagementProzess der Zielfindung,

Planung und Steuerung, sodass jeder Entscheidungsträger zielorientiert handelt.2. . . . sorgen für die bewusste Beschäftigung mit der Zukunft und ermöglichen dadurch,

Chancen wahrzunehmen und mit Risiken umzugehen.3. . . . integrieren die Ziele und Pläne aller Beteiligten zu einem abgestimmten Ganzen.4. . . . entwickeln und pflegen die ControllingSysteme. Sie sichern die Datenqualität und

sorgen für entscheidungsrelevante Informationen.


5. . . . sind als betriebswirtschaftliches Gewissen dem Wohl der Organisation als Ganzes verpflichtet.“

(Quelle: International Group of Controlling, Bukarest, 08. 06. 2013)Manager und Controller müssen im Dialog miteinander stehen. Diesen ControllingDialog – also das Miteinander im Controlling – symbolisiert die Schnittmenge in der vorhergehenden Abbildung. Nur so kann vermieden werden, dass der Manager gleichzeitig die Datenflut und den Informationsmangel beklagt.

■■ 1.2■Controlling und Excel

Microsoft Excel gehört zur Kategorie der Standardsoftware für Personalcomputer. Excel war zunächst kein eigenständiges Programm, sondern Teil des integrierten Softwarepakets Microsoft Office. Dieses Paket, das neben Excel auch das Schreibprogramm Word, die Präsentationssoftware PowerPoint, einen Mailclient namens Outlook und – je nach Version – die Datenbanksoftware Access (und weitere kleinere Programme) enthält, gehört auf den meisten Arbeitsplatzcomputern ebenso zum Standard wie das Betriebssystem Windows.

1.2.1■Versionen und Updates

Excel wurde ursprünglich (1985) als Bürosoftware für das grafische System Apple Macintosh entwickelt und zwei Jahre später auf das Betriebssystem Windows portiert. Mit regelmäßigen Updates im ZweiJahresRhythmus wurde das Programm erweitert und verbessert, neue Funktionen, Assistenz und Zusatzprogramme kamen hinzu. Mit Office 95 konnten erstmals mehrere Tabellen in einer Arbeitsmappe gespeichert werden und Excel 97 wurde mit dem für Controller wichtigsten Analysewerkzeug, der PivotTabelle, ausgeliefert.Geschichte und Versionsübersicht bei Wikipedia:

http://de.wikipedia.org/wiki/Microsoft_Excel

1.2.2■Versionsvergleich

Standardsoftware orientiert sich – wie der Name bereits vermuten lässt – an einem bestimmten Standard, und Microsoft Office ist führend im Bereich der funktionsübergreifenden Bürosoftware. Um den wachsenden Anforderungen der Anwender gerecht zu werden, werden Excel, Word, PowerPoint, Outlook und Co. alle drei Jahre neu aufgelegt. Diese Updates sind nicht immer beliebt und verursachen hohe Kosten bei den Unternehmen.

Zwischen den OfficeVersionen 97 und 2003 hielt sich die Anzahl der Neuerungen bei Excel in Grenzen, was für viele Unternehmen ein Argument war, die Kosten für SoftwareUpdates zu sparen. Erst mit der Version 2007

wurde Excel grundlegend erneuert. Die Tabellengröße wuchs von 65 536 Zeilen × 256 Spalten

http://de.wikipedia.org/wiki/Microsoft_Excel

1.2 Controlling und Excel 5

auf 1 048 576 Zeilen × 16 384 Spalten, angesichts der Datenmengen, die aus externen Quellen wie SAP abgerufen werden, eine entscheidende Neuerung. Die traditionelle Oberfläche mit Menüs und Symbolleisten musste dem „Ribbon“ weichen, einer Multifunktionsleiste mit Reitern, Symbolgruppen und Symbolen.Office 2010 kam im Juni 2010 auf den Markt, erwartungsgemäß mit wenigen, aber wichtigen Neuerungen. Aus dem OfficeMenü wurde das »Backstage« mit einer Registerkarte, die wieder Datei heißt. Die Multifunktionsleiste wurde in Menüband umbenannt, sie ließ sich wieder benutzerspezifisch anpassen. Das Sicherheitskonzept wurde runderneuert und mit einer Versionsverwaltung für Dateien versehen. Neue Funktionen gab es dagegen wenige, einige wurden umbenannt und optimiert. Für Umsteiger von den ExcelVersionen 97 – 2003 bot die Funktionspalette aber über 150 neue Funktionen, die zuvor im AddIn Analyse-Funk-tionen versteckt waren.

Mit der Version 2013 schaltete Microsoft den 3DLook ab, verpasste dem Menüband und den Symbolen eine schlichtere, ruhigere Optik und einige wenige, unspektakuläre Neuerungen:

Die Schnellanalyse bietet die Möglichkeit, aus einer Liste mit wenigen Klicks eine Tabelle oder ein Diagramm zu erstellen. Sie wird in einem Symbol rechts an der Liste angeboten.

Mit der Blitzvorschau versucht Excel, Eingabemuster zu erkennen und umzusetzen. Wird zum Beispiel in einer Liste, die Namen und Vornamen in einer Zelle enthält, die Information in einer einzelnen Zelle getrennt, erkennt die Blitzvorschau das Muster auch für die restlichen Zeilen der Liste.

Empfehlungen liefert Excel 2013 für Diagrammtypen und PivotTableLayouts, auch hier genügt ein Klick auf ein Symbol, um die Visualisierung oder PicotAnalyse auszuführen.

Datenschnitte können in Excel 2013 auch für Tabellen (in Excel 2010 nur für PivotTables) erstellt werden.

In den Kategorien Mathematik/Trigonometrie, Statistik, Technik, Datum und Zeit, Matrix, Logik und Text gibt es neue Funktionen. Neu sind auch Webdienstfunktionen zum Verweisen auf vorhandene Webdienste.

Mit Excel 2013 wird die alte FensterinFensterTechnik abgeschaltet. Jede neue Arbeitsmappe erhält ein eigenes Fenster, bis zur Vorgängerversion wurden alle Mappen in einem Fenster (task) gehalten.

Excel 2013 bietet den direkten Zugriff auf OneDrive (SkyDrive), dem CloudDienst von Microsoft. Dateien können direkt aus der Cloud geladen und in OneDrive gespeichert werden.

Das AddIn PowerPivot für die Integration großer Datenmengen ist Bestandteil von Excel 2013 und muss nicht mehr separat installiert werden.


Bild 1.2■Excel 2013 im neuen Look

In diesem Buch finden Sie die Beschreibungen zu den ExcelTechniken und Funktionen für die Versionen 2010 und 2013. Wenn es für eine beschriebene Aktion oder Schrittfolge abweichende Menübefehle gibt oder der Bildschirm abweicht, weist ein Symbol auf die jeweilige Version hin. Hier ein Beispiel:

Beschreibung

Zuletzt verwendete Arbeitsmappe aktivieren

Datei/Zuletzt verwendet Datei/Öffnen/Zuletzt verwendete Arbeitsmappen

1.2.3■Excel in der Cloud

Excel steht als Teil des OfficePakets sowohl lokal auf PC oder Notebook als auch als CloudVersion zur Verfügung. Technisch heißt der Begriff Cloud-Computing und damit ist die Verlagerung von Datenspeichern, Netzwerk und Softwarediensten in das Internet gemeint. Der Anwender speichert seine Daten nicht mehr auf lokalen Computern, sondern mietet die Infrastruktur von einem Anbieter und legt seine Daten auf dessen OnlineSpeichern ab. Software wird nicht mehr lokal installiert, sondern direkt über das Internet genutzt.Die Basis aller CloudDienste von Microsoft ist OneDrive (früherer Name: SkyDrive), eine virtuelle Festplatte, die dem Benutzer 25 GB OnlineSpeicherplatz zur Verfügung stellt. OneDrive ist kostenlos und erfordert nur eine Anmeldung über ein – ebenfalls kostenloses –


MicrosoftKonto (www.onedrive.com). OneDrive bietet die Möglichkeit, eine Ordnerstruktur für die OnlineDaten anzulegen, Dateien hochzuladen oder aus Ordnern zu exportieren.Arbeitsmappen anzulegen, ohne das Programm installiert zu haben, diese Möglichkeit bietet die ExcelWebApp in OneDrive. Die App ist Teil der OfficeWebApps, neben ExcelDateien können auch WordDokumente, PowerPointPräsentationen und OneNoteNotizen produziert werden.

1.2.4■Excel in Office 365

Mit Office 365 bietet Microsoft eine cloudbasierte Version der Bürosoftware. Excel & Co müssen nicht mehr lokal oder im Netz installiert werden, die Programme werden direkt im Browser gestartet. Die Vorteile liegen auf der Hand: geringe CloudGebühren an Stelle von Installations und Wartungsaufwand, automatische Updates, EMail und Konferenzdienste und Sicherheit vor Schadsoftware. Und vor allem: unbegrenzte Verfügbarkeit von Programmen und Daten an jedem beliebigen Ort der Welt.

Bild 1.3■Office 365: Daten und Programme in der Cloud

1.2.5■Excel im Data-Warehouse

Von allen OfficeProgrammen ist Excel das mächtigste und umfangreichste, und das nicht nur in Bezug auf die große Anzahl von Menüs und Symbolen. Excel hat sich vom Tabellenkalkulationswerkzeug zum Planungs und Analysetool mit Präsentations und ReportingFunktionen entwickelt. Mit den integrierten Werkzeugen und nicht zuletzt über die Makroprogrammiersprache VBA (Visual Basic for Applications) lässt sich jede Aufgabe im Bereich Controlling und Reporting lösen.Excel ist trotz der enormen Größe seiner Tabellenblätter kein Datenbankprogramm und nicht für die Verwaltung großer Datenmengen geeignet. Dazu fehlt dem Programm die

http://www.onedrive.com


Fähigkeit, relationale oder multidimensionale Verknüpfungen zwischen Datenpools herzustellen. Excel kann aber Daten aus Vorsystemen integrieren und analysieren.Welche Rolle das Werkzeug Excel im Unternehmen spielt, hängt von mehreren Faktoren ab: Die meisten mittelständischen Firmen und Großunternehmen setzen heute ERPSysteme ein (ERP = enterprise resource planing). Zu den bekanntesten gehören SAP, Microsoft Dynamics NAV und Sage, es gibt aber Hunderte von Standardlösungen und zahlreiche individuell erstellte Applikationen. Diese Systeme basieren meist auf relationalen Datenbanken (Oracle) oder multidimensionalen Datenbanken mit OLAPCubes (Cognos, Hyperion, SQL Server Analysis).Der Controller bezieht seine Daten aus diesen Systemen, verarbeitet und verknüpft diese und erstellt seine Berichte, Analysen und Präsentationen. Als Client dient dazu meist Excel, der Datentransfer geschieht über einfache Speicherung der ERPDaten im XLSFormat, per Copy & Paste oder über ODBCVerbindungen. Data Warehouses und BusinessIntelligenceLösungen nutzen Excel bereits direkt als Client, integrieren das OfficeProgramm als Ausgabeschnittstelle und stellen makrogesteuerte oder über Abfragen (Querys) dynamisch verknüpfte Berichte bereit.

Bild 1.4■Excel als Client für Vorsysteme

1.2.6■PowerPivot – der nächste Schritt

Mit Excel 2010 hat Hersteller Microsoft einen längst fälligen nächsten Schritt getan. Das kostenlose AddIn PowerPivot bietet die Möglichkeit, sehr große Datenmengen in Excel zu integrieren und über PivotTables und PivotCharts zu verwalten. Daten aus Datenbanken (Access, SQLServer u. a.), aus SharePoint und natürlich auch aus ExcelTabellen werden in Sekundenschnelle eingelesen, Verknüpfungen können automatisch übernommen oder spezifisch erstellt werden. Die integrierte Formelsprache DAX bietet zahlreiche Möglichkeiten zur Auswertung und Analyse der Datenmengen. PowerPivot ist eine ideale Ergänzung und für viele Anwender sogar ein Ersatz für BusinessIntelligenceSysteme (BI).


Das Add-In wird auf der Webseite www.powerpivot.com zum Download angeboten. Installieren Sie es und aktivieren Sie es anschließend über Entwicklertools/Add-Ins/COM-Add-Ins.

PowerPivot ist bereits installiert, Sie können das Add-In über Entwicklertools/Add-Ins/COM-Add-Ins aktivieren.

In Kapitel 5 finden Sie das Tutorial PowerPivot für Controller für die ExcelVersion 2010. Dieses Tutorial können Sie auch vom Verlagsserver downloaden (PowerPivot_Tutorial.wmv) oder online auf YouTube ansehen. Geben Sie unter www.youtube.de als Suchbegriff Excel-lentControlling ein.

http://www.youtube.com/watch?v=h4o8bzbKZfQhttp://www.youtube.com/watch?v=Ryf1ld1ESOI

1.2.7■Excel als Controlling-Tool

In vielen Unternehmen wird Excel noch als eigenständiges ControllingTool für Planung, Steuerung und Berichtswesen eingesetzt. Da Excel nicht über die Möglichkeiten von Datenbanken verfügt, Daten und Anwendungslogik zu trennen, sind für solche Anwendungen sehr viele umständliche Formelverknüpfungen nötig. Um die in Datenbanken benötigten relationalen Verknüpfungen zu simulieren, werden Verweise neue Tabellenblätter immer mehr mittels SVERWEIS() miteinander verknüpft. Das Ganze resultiert nicht selten in riesengroßen, unüberschaubaren Arbeitsmappen, in denen falsche Verknüpfungen und fehlerhafte Daten nicht mehr kontrollierbar sind.Mit der Programmiersprache VBA lassen sich zwar Makros erstellen, die Prozesse automatisieren und Benutzereingaben im Dialog ermöglichen, in der Praxis fehlen dem Controller aber die dazu zwingend erforderlichen Kenntnisse in Programmierung und Programmorganisation, und die größtenteils per Recorder aufgezeichneten VBAMakroprozeduren machen die ExcelLösungen noch kritischer, weil sie sehr wartungsintensiv, intransparent und fehlerträchtig sind.Diese berüchtigten „ExcelBasteleien“ schaffen meist nur kurzfristig Lösungen, schaden aber mehr, als dass sie nutzen.

1.2.7.1■Nutzen von Microsoft ExcelHier einige Tipps aus der Praxis, wie Excel gewinnbringend im Controlling eingesetzt wird: Trennen Sie Datenhaltung und Datenanalyse strikt. Nutzen Sie vorhandene Datenbanken oder ERPSysteme oder erstellen Sie relationale Datenbanken mit Verknüpfungen auf diese. Excel sollte selbst niemals zur Erfassung oder Speicherung von Daten verwendet werden.

Automatisieren Sie den Datentransfer zwischen Ihren Vorsystemen und Excel. Lassen Sie SAPBerichte erstellen, die weniger Kalkulationsaufwand erfordern, richten Sie BW oder BIAbfragen ein, die Daten automatisch aus der Datenbank ziehen (BW = Business

http://www.powerpivot.comhttp://www.powerpivot.comhttp://www.youtube.dehttp://www.youtube.com/watch?v=h4o8bzbKZfQhttp://www.youtube.com/watch?v=Ryf1ld1ESOI


Warehouse, BI = Business Intelligence). Nutzen Sie PowerPivot, wenn es um sehr große Datenmengen geht.

Versuchen Sie, Ihre Datenanalysen mit den „Bordmitteln“ von Excel zu lösen, vermeiden Sie Makros, solange es geht. Tabellenkalkulation ist erlernbar und erfordert nur Fleiß und die Fähigkeit, logisch zu denken. Makros sind eine weitere Benutzerebene, die fundiertes Wissen über Programmierung, Algorithmen und Ablaufsteuerung erfordert.

Können statt Kennen: Das Wissensniveau der ExcelAnwender ist grundsätzlich zu schwach für die Bewältigung der Standardaufgaben im Controlling. Die ParetoRegel gilt besonders im ExcelUmfeld: 80 % der Anwender nutzen 20 % der Möglichkeiten, und für 20 % Effizienz werden 80 % der Ressourcen verbraucht. Wer nur wenige Funktionen des Programms kennt, wird umständlich und zeitaufwendig arbeiten und schlechte, ineffiziente Lösungen produzieren. Lassen Sie sich und Ihr Team schulen, veranstalten Sie SpezialWorkshops, stellen Sie gute Literatur bereit.

Makroprogrammierung ist nur dann eine nützliche Komponente bei der Erstellung von ControllingLösungen, wenn sie von Anfang an von programmiererfahrenen Anwendern für die Automatisierung von Prozessen und für den Benutzerdialog verwendet wird. Auf keinen Fall sollte versucht werden, bestehende ExcelLösungen mit Makro zu „retten“ oder aufzuwerten. Eine Neustrukturierung, beginnend mit Datenflussplänen und Programmablaufplänen, ist in jedem Fall vorzuziehen.

1.2.7.2■Nutzen relationaler DatenbankenFür die Datenhaltung und verwaltung im MicrosoftOfficePaket ist das Datenbanksystem Access zuständig. Das relationale Datenbankprogramm wird nicht in den Basisversionen (Small Office, Studentenversion) mitgeliefert, kann aber nachgeordert werden. Excel und Access sind ein ideales Gespann, Access verfügt über direkte Schnittstellen zum Import und Export von ExcelDaten, umgekehrt bietet Excel die Möglichkeit, AccessDaten dynamisch verknüpft in Tabellenblätter und Diagramme zu integrieren.

Vorteile einer Excel-Access-KooperationEine ControllingLösung auf der Basis dieser beiden Applikationen aufzubauen, ist eine gute Entscheidung, erfordert aber eine straffe Planung und gute Kenntnisse in beiden Systemen. AccessDatenbanken (Version 2007/2010/2013) können mehrere GByte groß sein. Falls das nicht ausreicht, lassen sich mehrere Datenbankdateien miteinander verknüpfen. Wenn das Datenvolumen der geplanten Lösung bei Excel an Grenzen stößt, ist die Kombination ExcelAccess die erste Wahl.

Die Datenerfassung mit Access ist wesentlich sicherer als mit Excel. Im Unterschied zum ExcelTabellenblatt fordert die AccessTabelle eine klare Struktur mit Felddatentypen, Feldgrößen und Gültigkeitsregeln. Erfassungsformulare regeln den Dialog mit dem Anwender, Fehleingaben lassen sich ohne großen Aufwand ausschließen.

Access bietet einen sicheren Zugriffsschutz mit Berechtigungen für Benutzer und Benutzergruppen. Tabellen, Formulare und Berichte können von vielen Anwendern gleichzeitig bearbeitet werden, lassen sich aber bis auf Satzebene sperren, wenn es nötig ist.


Access verknüpft Tabellen relational über Schlüssel und Indizes, was Datenredundanz, Mehrfacherfassung und Erfassungsfehler auf allen Ebenen verhindert. Durch die referenzielle Integrität werden Aktualisierungen und Löschungen auch an verknüpfte Datenpools weitergegeben.

Daten aus ERPSystemen oder anderen Datenbanken und Datenquellen lassen sich bequem integrieren und verknüpfen (SAPBerichte im XLSFormat, SAPBIQuerys, Textdateien, CSV).

An die Grenzen stößt Access natürlich bei Planungs und Berichtsaufgaben für viele Unternehmensbereiche, bei Konsolidierungen über mehrere Geschäftszweige. Hier bieten große Systeme wie SAP oder multidimensionale Datenbanken einen klaren Vorteil, schlagen aber auch mit wesentlich höheren Anschaffungs und Lizenzkosten zu Buche und erfordern wesentlich mehr Personalressourcen.Die Makroprogrammiersprache VBA ist im OfficePaket für alle Applikationen einheitlich, VBA ist die meistgenutzte Sprache für die Erstellung von Businesslösungen. Für eine individuelle ControllingLösung mit gutem KostenNutzenVerhältnis gibt es keine bessere Alternative als Excel, Access und VBA.

TIPP: Ein Tipp aus der Praxis: Ein guter VBA-Programmierer wird immer zusätz-lich zu Excel ein Datenbanksystem kennen, er kann Sie bei der Umsetzung Ihrer Lösung beraten und den Aufwand an Access-Datenmodellierung und VBA- Optimierung einschätzen. Lassen Sie sich beraten, reservieren Sie ausreichend Zeit und Geld für eine Lösung und stellen Sie für die Umsetzung einen Mitar-beiter oder ein Mitarbeiterteam ab, der bzw. das als Key-User aus gebildet wird und die fertige Lösung pflegen und weiterentwickeln kann.

Bild 1.5■Controlling-Datenbank mit Microsoft Access 2013


1.2.8■Business Intelligence – OLAP und Excel

Der Softwaremarkt bietet als Alternative zu reinen ExcelLösungen BusinessIntelligence(BI)Software mit multidimensionalen Datenbanken an, die Excel größtenteils als Frontend unterstützen. Die Integration erfolgt über AddIns, die mit der Installation der BISoftware in die ExcelOberfläche eingebaut werden. Der Vorteil: Der Controller kann sein vertrautes Excel weiter für die Analyse, das Reporting und für Präsentationen nutzen, die Daten bezieht er aber aus einem sicheren BackendSystem. Die meisten Systeme unterstützen sogar die Administration der Datenbanken, sodass der Controller neue oder geänderte Daten auch wieder ins System einspeisen kann.Spezielle ExcelAddIns von Drittanbietern ermöglichen auch den Zugriff auf mehrere Datenbanken aus einer ExcelArbeitsmappe. Hier ein Auszug aus dem Angebot der in Deutschland erhältlichen multidimensionalen Datenbanken:

Tabelle 1.2■Business-Intelligence-(BI-)Software für Controller (Auszug; Quelle: ControllerMagazin, Software-Kompendium 2013/14)

Hersteller Produktname Produktschwerpunkt

ExcelAddIn

Homepage

BOARD Deutschland GmbH

BOARD Manage-ment Intelligence Toolkit

BI & Corporate Performance Management

Ja www.board.de

CoPlanner Software & Consulting GmbH

CoPlanner (unterschiedliche Editionen)

Analyse, Planung, Reporting, Konso-lidierung und Da-tenmanagement

Excel-Client

www.coplanner.com

CP Corporate Planning AG

CP-Suite Finanzplanung, Controlling, Konsolidierung, Unternehmens-steuerung

k. A. www.corporate-planning.com

Cubeware GmbH

Cubeware Cock-pit V6 Pro, Cube-ware Importer

Analyse, Planung, Reporting

Ja www.cubeware.de

DENZHORN Geschäfts- führungs-Sys-teme GmbH

BPS-ONE, BPS_KONS, BPS-PM, BPS-BI

Planung, Repor-ting, Konsolidie-rung, Business Intelligence

k. A. www.denzhorn.de

Diamant Soft-ware GmbH & Co. KG

Diamant/3 IQ Rechnungswesen und Controlling

k. A. www.diamant-software.de

IBM Deutsch-land GmbH

Cognos TM1 Unternehmens-planung, Business Intelligence

Ja www.ibm.com

Jedox AG Jedox Premium, Jedox Palo

Planung, Forecas-ting, Reporting, Analyse

Ja www.jedox.com

http://www.board.dehttp://www.coplanner.comhttp://www.corporate-planning.comhttp://www.cubeware.dehttp://www.denzhorn.dehttp://www.diamant-software.dehttp://www.ibm.comhttp://www.jedox.com


Hersteller Produktname Produktschwerpunkt

ExcelAddIn

Homepage

LucaNet AG LucaNet.Planner, LucaNet.Financial Consolidation, LucaNet.Importer

Planung, Konso lidierung, Reporting und Analyse

k. A. www.lucanet.com

Microsoft Deutschland GmbH

MS SQL Server BI und Daten-management

Ja www.microsoft.com

MIK GmbH MIK.starship, MIK.starlight etc.

Analyse, Planung, Reporting

Ja www.mik.de

Prevero AG prevero profes-sional planner, prevero enter-prise p7

Planung, Controlling

Ja www.prevero.com

Oracle Deutschland B. V. & Co. KG

Oracle Essbase, Oracle BI Suite

Analyse, Reporting

Ja www.oracle.com

SAP AG SAP Netweaver BI (SAP BW)

Data Warehousing, BI, Analysen

Ja www.sap.de

Moderne, durchgängige Lösungen für das Controlling haben zum Ziel, objektive Grundlagen für die ganzheitliche, strategische und operative Führung eines Unternehmens zu schaffen. Es geht um die zukunftsgerichtete Fähigkeit, das Spannungsfeld zwischen kurzfristigen, finanziellen Ergebnissen und Investitionen in künftige Erfolge unternehmensindividuell und zielgerichtet zu lösen. Hier unterstützen moderne BusinessIntelligence(BI) bzw. BusinessPerformanceManagement(BPM)Systeme.Stellvertretend für derartige Systeme fassen die Produkte der prevero AG die für eine integrierte Unternehmenssteuerung notwendigen Prozesse in einer Software zusammen. Der Ansatz von prevero sieht den Planungs und Budgetierungsprozess als zentralen Bestandteil innerhalb der Unternehmenssteuerung bzw. des Corporate Performance Management (CPM). Von ihm ausgehend werden die weiteren unternehmensübergreifenden Komponenten etabliert, wie z. B. das Risikomanagement oder die Balanced Scorecard. So werden inkonsistente Daten und uneinheitliche Metriken vermieden und es erfolgt Schritt für Schritt eine nachvollziehbare Modellierung der Unternehmensstrategien sowie der Maßnahmen zur Steuerung und Überwachung derselben.Die meisten ITTools für das Controlling sind getrieben von einem Gedanken, der mit der Standardsoftware auch sämtliche ExcelAktivitäten aus der ITLandschaft verbannt oder verbannen will. Die Frage ist daher, warum hier nicht das Beste aus beiden Welten zusammengenommen werden kann: Ein Ansatz sollte sein, die Umgebung des Excelaffinen Nutzers eigenständig zu belassen und gleichzeitig eine einheitliche Datenbasis im Sinne einer BILösung zu schaffen.Die Professional Services für Microsoft Excel von prevero gewährleisten die einfache und elegante Integration der preveroProdukte, wie professional planner oder professional

http://www.lucanet.comhttp://www.microsoft.comhttp://www.mik.dehttp://www.prevero.comhttp://www.oracle.comhttp://www.sap.de


treasury, in die MSExcelUmgebung. Sie ermöglichen die zentrale bzw. dezentrale Erfassung von Daten über Excel und sind zugleich Frontend für Reporting, Analyse und Dashboards.

Bild 1.6■Excel-Client für Reporting und Analysis von prevero

1.2.9■Sharepoint- und Excel-Services

Viele Unternehmen nutzen für das Informations und Datenmanagement bereits die SharepointServices, eine Serverapplikation von Microsoft. Auf dem SharepointServer stehen Listen und Bibliotheken für die teaminterne Nutzung von Daten bereit, Webseiten und Formulare vereinheitlichen die Erfassung und Pflege und die Versionsverwaltung stellt sicher, dass keine Redundanzen auftreten. Mit dem Microsoft Office Sharepoint Server (MOSS) lässt sich eine komplette Dokumenten und ContentVerwaltung über alle OfficeProdukte sicherstellen. Die ExcelServices sind eine SharepointApplikation zur teamorientierten Verwaltung von ExcelArbeitsmappen (ab Excel 2007).ExcelMappen werden in eine Dokumentenbibliothek auf dem SharepointServer publiziert. Externe Verbindungen werden unterstützt, die Daten können aus ERPSystemen bezogen werden.Alle Benutzer erstellen je nach Berechtigung Berichte, Dashboards und Webseiten, die auf diesen ExcelMappen basieren. Der gemeinsame Zugriff erfolgt über den Webbrowser.Einführung in ExcelServices und Excel Web Access:

http://office.microsoft.com/de-de/sharepointserver/HA101054761031.aspx
http://office.microsoft.com/de-de/sharepointserver/HA101054761031.aspx

Documents

013 DAS UMFANGREICHE HANDBUCH Excel · 2014. 7. 3. · professionelle lÖsungen fÜr controlling, projekt- und personalmanagement excel im controlling das umfangreiche handbuch ignatz