Excel im Controlling

Vorwort ... 29
1. Neuerungen in Excel 2019 ... 33
2. Tipps, Tricks und Tastenkürzel -- zeitsparende Techniken für Controller ... 43

2.1 ... Daten effizient eingeben ... 43
2.2 ... Kopieren, Ausschneiden und Einfügen von Daten ... 48
2.3 ... Formelzusammenhänge erkennen ... 50
2.4 ... Cursorsteuerung und Bewegen in Tabellen ... 52
2.5 ... Zellbereiche markieren ... 54
2.6 ... Zahlen- und andere Formate schnell zuweisen ... 55
2.7 ... Inhalte löschen ... 57
2.8 ... Diagramme erstellen und bearbeiten ... 57
2.9 ... AutoFilter und Bearbeitung von sichtbaren Zellen ... 58
2.10 ... Weitere nützliche Tastenkombinationen ... 59

3. xlSMILE -- Excel-Lösungen mit System ... 61

3.1 ... Simplify -- Big Data nutzen und Datenmüll entfernen ... 61
3.2 ... Model -- systematisch arbeiten und Reports automatisieren ... 62
3.3 ... Integrate -- Layouts entwickeln und Tabellen und Diagramme anwenden ... 63
3.4 ... Lead -- Benutzer führen und Fehleingaben verhindern ... 64
3.5 ... Explain -- informieren und zusammenfassen ... 65

4. Daten importieren und bereinigen ... 67

4.1 ... Textdatei aus einem Warenwirtschaftssystem importieren ... 69
4.2 ... Transaktionsdaten in einer CSV-Datei auswerten ... 79
4.3 ... Daten mit Microsoft Query importieren und Soll-Ist-Vergleich durchführen ... 90
4.4 ... Daten von einem SQL Server aus Excel abfragen ... 99
4.5 ... Vorhandene Datenverbindungen nutzen ... 100
4.6 ... OLAP-Cubes und Analysis Services ... 102
4.7 ... Importieren von externen Daten mit Power Pivot ... 105
4.8 ... Importieren von Webinhalten ... 107
4.9 ... Importieren und Exportieren von XML-Daten ... 109
4.10 ... Zusammenfassung: Datenimport und -bereinigung ... 111

5. Datenbereinigung mit Power Query effizienter gestalten ... 113

5.1 ... Wozu ist Power Query eigentlich gedacht? ... 114
5.2 ... CSV-Dateien mit Power Query importieren ... 114
5.3 ... Einfache Schritte der Datenbereinigung ausführen ... 120
5.4 ... Gruppieren und Spalten berechnen ... 125
5.5 ... Bedingte Berechnungen in Spalten ... 129
5.6 ... Power Query als Ersatz für Textfunktionen in Excel ... 131
5.7 ... Weitere Optionen beim Erstellen von Spalten und Gruppierungsmerkmalen ... 136
5.8 ... Suchtabellen durch Anfügen von Abfragen erzeugen ... 139
5.9 ... Alle Excel-Dateien eines Ordners importieren und bereinigen ... 145
5.10 ... Alltäglicher Datensalat -- Power-Query-Lösungen für den Alltag ... 149

6. Unternehmensdaten prüfen und analysieren ... 165

6.1 ... Standardsortierung und benutzerdefiniertes Sortieren ... 166
6.2 ... AutoFilter und Datenschnitte ... 170
6.3 ... Vorteile des erweiterten Filters ... 174
6.4 ... Erweiterter Filter mit einem VBA-Makro ... 183
6.5 ... Verwendung von Datenbankfunktionen ... 186
6.6 ... Konsolidierung von Daten ... 200
6.7 ... Zusammenfassung: Basisanalyse ... 214

7. Dynamische Reports erstellen ... 217

7.1 ... Das 5-Minuten-Datenmodell ... 217
7.2 ... Bestandteile eines Datenmodells ... 219
7.3 ... Datenmodell für einen Forecast erstellen ... 226
7.4 ... Datenmodell zur Kalkulation der optimalen Bestellmenge ... 253
7.5 ... Rollierende Berichte ... 259
7.6 ... Zusammenfassung: Datenmodelle ... 264

8. Wichtige Kalkulationsfunktionen für Controller ... 267

8.1 ... Berechnungen mit Datumsbezug ... 268
8.2 ... Berechnungen mit Zeitangaben ... 283
8.3 ... Arbeiten mit Verweisen und Matrizen ... 286
8.4 ... Funktionen zur Dynamisierung von Tabellen ... 292
8.5 ... Berechnung von Rangfolgen ... 308
8.6 ... Berechnung von Mittelwerten ... 315
8.7 ... Runden von Daten ... 320
8.8 ... Neue Textfunktionen ab Excel 2016 (Office 365) ... 323
8.9 ... Fehlerunterdrückung ... 324
8.10 ... Einsatz von logischen Funktionen ... 329

9. Neue dynamische Matrixfunktionen in Excel für Office 365 ... 335

9.1 ... Das Control-Shift-Enter-Beben ... 336
9.2 ... Neue Optionen für die Erstellung dynamischer Datenmodelle ... 363

10. Bedingte Kalkulationen in Datenanalysen ... 371

10.1 ... Kalkulationen ohne Bedingungen ... 372
10.2 ... Kalkulationen mit einer Bedingung ... 374
10.3 ... Bereichsnamen -- der schnelle Zugriff auf Datenbereiche ... 377
10.4 ... Fehlervermeidung bei der Eingabe von Bedingungen -- die Datenüberprüfung ... 383
10.5 ... Bedingte Kalkulationen mit mehr als einer Bedingung ... 388
10.6 ... Vorteile von SUMMENPRODUKT() gegenüber anderen Funktionen zur bedingten Kalkulation ... 396
10.7 ... Multiplikation von Textwerten mit SUMMENPRODUKT() ... 396
10.8 ... Bedingte Kalkulation mit ODER im Tabellenblatt »Report_III« ... 397
10.9 ... Ausschluss von Datensätzen bei bedingten Kalkulationen ... 398
10.10 ... Häufigkeiten schnell berechnen ... 399
10.11 ... Mittelwerte ohne Nullwerte berechnen ... 402
10.12 ... Mittelwert bei #DIV/0! ... 403
10.13 ... Fallbeispiel zur bedingten Kalkulation ... 404
10.14 ... Zusammenfassung: Bedingte Kalkulationen ... 409

11. Pivottabellen und -diagramme ... 413

11.1 ... Vorbereitung der Basisdaten für eine Pivottabelle ... 414
11.2 ... Pivottabellen erstellen ... 418
11.3 ... Pivotcache und Speicherbedarf ... 437
11.4 ... Visuelle interaktive Analyse von Daten ... 438
11.5 ... Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen ... 445
11.6 ... Filtern von Daten in einer Pivottabelle ... 448
11.7 ... Gruppierungen in Pivottabellen ... 448
11.8 ... Weiterverarbeitung von Daten aus Pivottabellen ... 473
11.9 ... Personaldaten mithilfe von Pivottabellen konsolidieren ... 480
11.10 ... Grundlegendes zu PivotCharts ... 488
11.11 ... Zusammenfassung: Pivottabellen und PivotCharts ... 498

12. Business Intelligence mit Power Pivot ... 501

12.1 ... Arbeiten auf der Self-BI-Baustelle ... 502
12.2 ... Inhaltliches und Organisatorisches zu den Beispielen ... 503
12.3 ... Die Power-Pivot-Oberfläche im Überblick ... 504
12.4 ... Logische Beziehungen statt SVERWEIS() und Co. ... 509
12.5 ... Berechnete Spalten und berechnete Felder unterscheiden ... 513
12.6 ... Eine berechnete Spalte erstellen ... 514
12.7 ... Eine Power-Pivot-Tabelle in Excel erstellen ... 515
12.8 ... Mehr Übersichtlichkeit herstellen ... 517
12.9 ... Referenztabellen einbinden ... 519
12.10 ... Einbinden von Daten aus anderen Datenquellen ... 521
12.11 ... Typische Erkennungszeichen für fehlende logische Beziehungen in Datenmodellen ... 523
12.12 ... Tabellen der Arbeitsmappe in das Datenmodell einbinden ... 526
12.13 ... Tabellen des Datenmodells ausblenden ... 529
12.14 ... Berechnete Felder in Power-Pivot-Tabellen verwenden ... 531
12.15 ... Bearbeiten von berechneten Feldern ... 536
12.16 ... Bedingte Kalkulationen mit CALCULATE() ... 537
12.17 ... Datenschnitte und Zeitachsen ... 542
12.18 ... Wie DAX-Funktionen arbeiten ... 546
12.19 ... Bedingte Formatierungen und Diagramme in Power-Pivot-Reports ... 553

13. Excel als Planungswerkzeug ... 557

13.1 ... Wettbewerberanalyse ... 557
13.2 ... Potenzialanalyse ... 564
13.3 ... Portfolioanalyse ... 569
13.4 ... Stärken-Schwächen-Analyse ... 574
13.5 ... Absatzplanung ... 579
13.6 ... Prognosen erstellen ... 597
13.7 ... Personalplanung ... 604
13.8 ... Liquiditätsplanung ... 621
13.9 ... Marktanalyse und Absatzplanung ... 628

14. Operatives Controlling mit Excel ... 635

14.1 ... Betriebsabrechnungsbogen ... 635
14.2 ... Divisionskalkulation ... 646
14.3 ... Zuschlagskalkulation ... 648
14.4 ... Äquivalenzziffernrechnung ... 651
14.5 ... Prozesskostenrechnung ... 653
14.6 ... Deckungsbeitragsrechnung ... 657
14.7 ... Dynamische Break-even-Analyse ... 658
14.8 ... Mehrstufige Deckungsbeitragsrechnung ... 667
14.9 ... Planen von Kosten und Erlösen mithilfe von Szenarien ... 668
14.10 ... Produktkalkulation mit Deckungsbeitragsrechnung ... 673
14.11 ... Eigenfertigung oder Fremdbezug (make or buy) ... 686
14.12 ... Zinsen, Tilgung, Annuitäten für Darlehen berechnen ... 689
14.13 ... Abschreibungen ... 694
14.14 ... Methoden der Investitionsrechnung ... 697
14.15 ... Customer Lifetime Value ... 711
14.16 ... Kundenscoring ... 726
14.17 ... Personalstrukturanalyse ... 728
14.18 ... Arbeitszeitanalyse ... 733
14.19 ... Reisekostenabrechnung ... 737
14.20 ... Lieferantenbewertung ... 740

15. Unternehmenssteuerung und Kennzahlen ... 755

15.1 ... Zielkostenmanagement (Target Costing) ... 756
15.2 ... Cashflow ... 765
15.3 ... Free Cashflow ... 769
15.4 ... Discounted Cashflow ... 770
15.5 ... Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern ... 771
15.6 ... Shareholder-Value ... 772
15.7 ... Economic Value Added -- EVA® ... 777
15.8 ... Market Value Added -- MVA ... 779
15.9 ... Bilanzkennzahlen ... 781
15.10 ... GuV-Gliederung ... 789
15.11 ... Beispieldatei GuV -- Bilanz -- Kapitalfluss ... 795
15.12 ... Return on Investment und DuPont-Schema ... 798
15.13 ... Messung der Mitarbeiterzufriedenheit ... 802
15.14 ... Selbstbewertung nach EFQM ... 818

16. Reporting mit Diagrammen und Tabellen ... 835

16.1 ... Grundlagen ... 835
16.2 ... Das Standarddiagramm in Excel ... 837
16.3 ... Wichtige Gestaltungsregeln ... 842
16.4 ... Umgang mit Farben ... 846
16.5 ... Auswahl des richtigen Diagrammtyps ... 847
16.6 ... Die neuen Diagrammtypen in Excel 2016 und 2019 ... 865
16.7 ... Allgemeine Formatierungsregeln ... 879
16.8 ... Elemente und Gestaltungsregeln für Dashboards ... 880
16.9 ... Infografiken seit Excel 2016 ... 882
16.10 ... Power View ... 883
16.11 ... 3D-Karten ... 887
16.12 ... Kombinationen aus Tabellen und Diagramm erstellen ... 888
16.13 ... Dynamische Diagramme ... 890
16.14 ... Spezielle Diagrammtypen ... 895
16.15 ... Spezielle Formatierungen im Diagramm ... 907
16.16 ... Diagramme in Tabellenblättern ... 916
16.17 ... Dashboards erstellen ... 929
16.18 ... Übernahme in PowerPoint ... 939
16.19 ... Übernahme in Word ... 947

17. Automatisierung mit Makros -- VBA für Controller ... 949

17.1 ... Wie alles anfängt: die Aufzeichnung eines Makros ... 950
17.2 ... Makros über Schaltflächen aufrufen ... 959
17.3 ... Quellcode im Editor bereinigen ... 963
17.4 ... Bereiche adressieren ... 967
17.5 ... Arbeiten mit Variablen ... 971
17.6 ... Umgang mit Programmfehlern ... 976
17.7 ... Kopieren, Verschieben und Filtern von Daten ... 979
17.8 ... Zugriff auf Dateien über VBA-Makros ... 991
17.9 ... Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast ... 999
17.10 ... Flusskontrolle mit »If ... Then ... Else« ... 1001
17.11 ... Programmierung von Schleifen in VBA ... 1007
17.12 ... Formeln und Funktionen in VBA-Makros ... 1019
17.13 ... Gestaltung von Dialogen in VBA ... 1023
17.14 ... Benutzerdefinierte Funktionen ... 1033
17.15 ... Die Beispiele aus dem Buch zum Herunterladen ... 1041

Index ... 1043