Excel 2016 im Controlling

Vorwort ... 29
1. Neuerungen in Excel 2016 ... 35

1.1 ... Neue Funktionen in Pivottabellen ... 35
1.2 ... Neue Diagrammtypen und der Power BI Desktop ... 36
1.3 ... Daten in Dashboards und Karten visualisieren und teilen ... 38
1.4 ... Neue Kalkulationsfunktionen ... 40
1.5 ... Das Beste zum Schluss ... 41
1.6 ... Was ist neu in diesem Buch? ... 42

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 ... 53
2.5 ... Zellbereiche markieren ... 55
2.6 ... Zahlen- und andere Formate schnell zuweisen ... 56
2.7 ... Inhalte löschen ... 58
2.8 ... Diagramme erstellen und bearbeiten ... 59
2.9 ... AutoFilter und Bearbeitung von sichtbaren Zellen ... 60
2.10 ... Weitere nützliche Tastenkombinationen ... 61

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

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

4. Daten importieren und bereinigen ... 69

4.1 ... Textdatei aus einem Warenwirtschaftssystem importieren ... 70
4.2 ... Transaktionsdaten in einer CSV-Datei auswerten ... 81
4.3 ... Daten mit Microsoft Query importieren und SollIstVergleich durchführen ... 94
4.4 ... Daten von einem SQL Server aus Excel abfragen ... 102
4.5 ... Vorhandene Datenverbindungen nutzen ... 103
4.6 ... OLAP-Cubes und Analysis Services ... 105
4.7 ... Importieren von externen Daten mit PowerPivot ... 108
4.8 ... Importieren von Webinhalten ... 110
4.9 ... Importieren und Exportieren von XML-Daten ... 112
4.10 ... Zusammenfassung: Datenimport und -bereinigung ... 114

5. Datenbereinigung mit PowerQuery effizienter gestalten ... 117

5.1 ... Wozu PowerQuery eigentlich gedacht ist ... 117
5.2 ... CSV-Dateien mit PowerQuery importieren ... 118
5.3 ... Einfache Schritte der Datenbereinigung ausführen ... 123
5.4 ... Gruppieren und Spalten berechnen ... 127
5.5 ... Suchtabellen durch Anfügen von Abfragen erzeugen ... 131
5.6 ... Alle Excel-Dateien eines Ordners importieren und bereinigen ... 135

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

6.1 ... Standardsortierung und benutzerdefiniertes Sortieren ... 141
6.2 ... AutoFilter und Datenschnitte ... 146
6.3 ... Vorteile des erweiterten Filters ... 151
6.4 ... Erweiterter Filter mit einem VBA-Makro ... 161
6.5 ... Verwendung von Datenbankfunktionen ... 164
6.6 ... Konsolidierung von Daten ... 180
6.7 ... Zusammenfassung: Basisanalyse ... 195

7. Dynamische Reports erstellen ... 197

7.1 ... Das 5-Minuten-Datenmodell ... 197
7.2 ... Bestandteile eines Datenmodells ... 199
7.3 ... Datenmodell für einen Forecast erstellen ... 206
7.4 ... Datenmodell zur Kalkulation der optimalen Bestellmenge ... 236
7.5 ... Rollierende Berichte ... 243
7.6 ... Zusammenfassung: Datenmodelle ... 249

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

8.1 ... Berechnungen mit Datumsbezug ... 252
8.2 ... Berechnungen mit Zeitangaben ... 268
8.3 ... Arbeiten mit Verweisen und Matrizen ... 271
8.4 ... Funktionen zur Dynamisierung von Tabellen ... 278
8.5 ... Berechnung von Rangfolgen ... 298
8.6 ... Berechnung von Mittelwerten ... 305
8.7 ... Runden von Daten ... 310
8.8 ... Neue Textfunktionen in Excel 2016 ... 313
8.9 ... Fehlerunterdrückung ... 315
8.10 ... Einsatz von logischen Funktionen ... 320

9. Bedingte Kalkulationen in Datenanalysen ... 325

9.1 ... Kalkulationen ohne Bedingungen ... 326
9.2 ... Kalkulationen mit einer Bedingung ... 329
9.3 ... Bereichsnamen -- der schnelle Zugriff auf Datenbereiche ... 332
9.4 ... Fehlervermeidung bei der Eingabe von Bedingungen -- die Datenüberprüfung ... 338
9.5 ... Bedingte Kalkulationen mit mehr als einer Bedingung ... 343
9.6 ... Vorteile von SUMMENPRODUKT() gegenüber anderen Funktionen zur bedingten Kalkulation ... 351
9.7 ... Multiplikation von Textwerten mit SUMMENPRODUKT() ... 352
9.8 ... Bedingte Kalkulation mit ODER im Tabellenblatt Report_III ... 353
9.9 ... Ausschluss von Datensätzen bei bedingten Kalkulationen ... 354
9.10 ... Häufigkeiten schnell berechnen ... 355
9.11 ... Mittelwerte ohne Nullwerte berechnen ... 358
9.12 ... Mittelwert bei #DIV/0! ... 359
9.13 ... Fallbeispiel zur bedingten Kalkulation ... 361
9.14 ... Zusammenfassung: Bedingte Kalkulationen ... 366

10. Pivottabellen und -diagramme ... 369

10.1 ... Vorbereitung der Basisdaten für eine Pivottabelle ... 370
10.2 ... Pivottabellen mit Excel 2016 erstellen ... 374
10.3 ... Pivotcache und Speicherbedarf ... 393
10.4 ... Visuelle interaktive Analyse von Daten ... 395
10.5 ... Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen ... 402
10.6 ... Filtern von Daten in einer Pivottabelle ... 404
10.7 ... Gruppierungen in Pivottabellen ... 405
10.8 ... Weiterverarbeitung von Daten aus Pivottabellen ... 428
10.9 ... Personaldaten mit Hilfe von Pivottabellen konsolidieren ... 436
10.10 ... Grundlegendes zu PivotCharts ... 446
10.11 ... Zusammenfassung: Pivottabellen und PivotCharts ... 457

11. Business Intelligence mit PowerPivot ... 461

11.1 ... Arbeiten auf der Self-BI-Baustelle ... 462
11.2 ... Inhaltliches und Organisatorisches zu den Beispielen ... 463
11.3 ... Die PowerPivot-Oberfläche im Überblick ... 464
11.4 ... Logische Beziehungen statt SVERWEIS() und Co. ... 470
11.5 ... Berechnete Spalten und berechnete Felder unterscheiden ... 473
11.6 ... Eine berechnete Spalte erstellen ... 474
11.7 ... Eine PowerPivot-Tabelle in Excel erstellen ... 476
11.8 ... Mehr Übersichtlichkeit herstellen ... 478
11.9 ... Referenztabellen einbinden ... 480
11.10 ... Einbinden von Daten aus anderen Datenquellen ... 482
11.11 ... Typische Erkennungszeichen für fehlende logische Beziehungen in Datenmodellen ... 485
11.12 ... Tabellen der Arbeitsmappe in das Datenmodell einbinden ... 487
11.13 ... Tabellen des Datenmodells ausblenden ... 491
11.14 ... Berechnete Felder in PowerPivot-Tabellen verwenden ... 493
11.15 ... Bearbeiten von berechneten Feldern ... 499
11.16 ... Bedingte Kalkulationen mit CALCULATE() ... 500
11.17 ... Datenschnitte und Zeitachsen ... 505
11.18 ... Wie DAX-Funktionen arbeiten ... 510
11.19 ... Bedingte Formatierungen und Diagramme in PowerPivot-Reports ... 518

12. Excel als Planungswerkzeug ... 521

12.1 ... Wettbewerberanalyse ... 521
12.2 ... Potenzialanalyse ... 529
12.3 ... Portfolioanalyse ... 534
12.4 ... Stärken-Schwächen-Analyse ... 540
12.5 ... Absatzplanung ... 545
12.6 ... Prognosen erstellen ... 564
12.7 ... Personalplanung ... 572
12.8 ... Liquiditätsplanung ... 589
12.9 ... Marktanalyse und Absatzplanung ... 597

13. Operatives Controlling mit Excel ... 605

13.1 ... Betriebsabrechnungsbogen ... 606
13.2 ... Divisionskalkulation ... 617
13.3 ... Zuschlagskalkulation ... 620
13.4 ... Äquivalenzziffernrechnung ... 623
13.5 ... Prozesskostenrechnung ... 626
13.6 ... Deckungsbeitragsrechnung ... 630
13.7 ... Dynamische Break-even-Analyse ... 632
13.8 ... Mehrstufige Deckungsbeitragsrechnung ... 642
13.9 ... Planen von Kosten und Erlösen mit Hilfe von Szenarien ... 643
13.10 ... Produktkalkulation mit Deckungsbeitragsrechnung ... 648
13.11 ... Eigenfertigung oder Fremdbezug (make or buy) ... 662
13.12 ... Zinsen, Tilgung, Annuitäten für Darlehen berechnen ... 666
13.13 ... Abschreibungen ... 672
13.14 ... Methoden der Investitionsrechnung ... 674
13.15 ... Customer Lifetime Value ... 690
13.16 ... Kundenscoring ... 706
13.17 ... Personalstrukturanalyse ... 709
13.18 ... Arbeitszeitanalyse ... 715
13.19 ... Reisekostenabrechnung ... 719
13.20 ... Lieferantenbewertung ... 722

14. Unternehmenssteuerung und Kennzahlen ... 739

14.1 ... Zielkostenmanagement (Target Costing) ... 740
14.2 ... Cashflow ... 750
14.3 ... Free Cashflow ... 754
14.4 ... Discounted Cashflow ... 755
14.5 ... Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern ... 756
14.6 ... Shareholder-Value ... 758
14.7 ... Economic Value Added -- EVA® ... 763
14.8 ... Market Value Added -- MVA ... 766
14.9 ... Bilanzkennzahlen ... 767
14.10 ... GuV-Gliederung ... 776
14.11 ... Beispieldatei GuV -- Bilanz -- Kapitalfluss ... 781
14.12 ... Return on Investment und DuPont-Schema ... 786
14.13 ... Messung der Mitarbeiterzufriedenheit ... 789
14.14 ... Selbstbewertung nach EFQM ... 808

15. Reporting mit Diagrammen und Tabellen ... 825

15.1 ... Grundlagen ... 825
15.2 ... Das Standarddiagramm in Excel ... 827
15.3 ... Wichtige Gestaltungsregeln ... 833
15.4 ... Umgang mit Farben ... 836
15.5 ... Auswahl des richtigen Diagrammtyps ... 838
15.6 ... Die neuen Diagrammtypen in Excel 2016 ... 858
15.7 ... Allgemeine Formatierungsregeln ... 872
15.8 ... Elemente und Gestaltungsregeln für Dashboards ... 874
15.9 ... Infografiken in Excel 2016 ... 876
15.10 ... Power View ... 878
15.11 ... 3D-Karten ... 881
15.12 ... Kombinationen aus Tabellen und Diagramm erstellen ... 882
15.13 ... Dynamische Diagramme ... 885
15.14 ... Spezielle Diagrammtypen ... 890
15.15 ... Spezielle Formatierungen im Diagramm ... 903
15.16 ... Diagramme in Tabellenblättern ... 913
15.17 ... Dashboards erstellen ... 928
15.18 ... Übernahme in PowerPoint ... 934
15.19 ... Übernahme in Word ... 942

16. Automatisierung mit Makros -- VBA für Controller ... 943

16.1 ... Wie alles anfängt: die Aufzeichnung eines Makros ... 944
16.2 ... Makros über Schaltflächen aufrufen ... 954
16.3 ... Quellcode im Editor bereinigen ... 958
16.4 ... Bereiche adressieren ... 963
16.5 ... Arbeiten mit Variablen ... 968
16.6 ... Umgang mit Programmfehlern ... 973
16.7 ... Kopieren, Verschieben und Filtern von Daten ... 976
16.8 ... Zugriff auf Dateien über VBA-Makros ... 990
16.9 ... Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast ... 999
16.10 ... Flusskontrolle mit »If ... Then ... Else« ... 1002
16.11 ... Programmierung von Schleifen in VBA ... 1009
16.12 ... Formeln und Funktionen in VBA-Makros ... 1022
16.13 ... Gestaltung von Dialogen in VBA ... 1027
16.14 ... Benutzerdefinierte Funktionen ... 1038
16.15 ... Die Beispiele aus dem Buch zum Herunterladen ... 1047

Stichwortverzeichnis ... 1049