Excel im Controlling

Vorwort ... 29 1. Neuerungen in Excel ... 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 ... 51 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 ... 57 2.8 ... Diagramme erstellen und bearbeiten ... 57 2.9 ... AutoFilter und Bearbeitung von sichtbaren Zellen ... 58 2.10 ... Erweiterte Filterfunktionen in Microsoft 365 ... 59 2.11 ... Weitere nützliche Tastenkombinationen ... 61 2.12 ... Tabellenansichten in der Webversion von Excel nutzen ... 62 2.13 ... Kommentare, Notizen und Chat in Excel ... 63 3. xlSMILE -- Excel-Lösungen mit System ... 67 3.1 ... Simplify -- Big Data nutzen und Datenmüll entfernen ... 67 3.2 ... Model -- systematisch arbeiten und Reports automatisieren ... 68 3.3 ... Integrate -- Layouts entwickeln und Tabellen und Diagramme anwenden ... 69 3.4 ... Lead -- Benutzer führen und Fehleingaben verhindern ... 70 3.5 ... Explain -- informieren und zusammenfassen ... 71 4. Daten importieren und bereinigen ... 73 4.1 ... Textkonvertierungs-Assistent ... 75 4.2 ... Transaktionsdaten in einer CSV-Datei auswerten ... 84 4.3 ... Importieren von externen Daten mit Power Pivot ... 96 5. Datenbereinigung mit Power Query effizienter gestalten ... 99 5.1 ... Wozu ist Power Query eigentlich gedacht? ... 99 5.2 ... CSV-Dateien mit Power Query importieren ... 100 5.3 ... Einfache Schritte der Datenbereinigung ausführen ... 105 5.4 ... Verbindungen zu anderen Datenquellen ... 110 5.5 ... Gruppieren und Spalten berechnen ... 122 5.6 ... Bedingte Berechnungen in Spalten ... 126 5.7 ... Power Query als Ersatz für Textfunktionen in Excel ... 128 5.8 ... Weitere Optionen beim Erstellen von Spalten und Gruppierungsmerkmalen ... 133 5.9 ... Suchtabellen durch Anfügen von Abfragen erzeugen ... 138 5.10 ... Alle Excel-Dateien eines Ordners importieren und bereinigen ... 144 5.11 ... Alltäglicher Datensalat -- Power-Query-Lösungen für den Alltag ... 148 5.12 ... Individuelle Datentypen in Power Query erstellen ... 163 6. Unternehmensdaten prüfen und analysieren ... 181 6.1 ... Standardsortierung und benutzerdefiniertes Sortieren ... 181 6.2 ... AutoFilter und Datenschnitte ... 186 6.3 ... Vorteile des erweiterten Filters ... 191 6.4 ... Erweiterter Filter mit einem VBA-Makro ... 200 6.5 ... Verwendung von Datenbankfunktionen ... 203 6.6 ... Konsolidierung von Daten ... 216 7. Dynamische Reports erstellen ... 231 7.1 ... Das 5-Minuten-Datenmodell ... 231 7.2 ... Bestandteile eines Datenmodells ... 233 7.3 ... Datenmodell für einen Forecast erstellen ... 239 7.4 ... Datenmodell zur Kalkulation der optimalen Bestellmenge ... 266 7.5 ... Rollierende Berichte ... 271 8. Wichtige Kalkulationsfunktionen für Controller ... 277 8.1 ... Berechnungen mit Datumsbezug ... 278 8.2 ... Berechnungen mit Zeitangaben ... 293 8.3 ... Arbeiten mit Verweisen und Matrizen ... 296 8.4 ... Funktionen zur Dynamisierung von Tabellen ... 314 8.5 ... Berechnung von Rangfolgen ... 330 8.6 ... Berechnung von Mittelwerten ... 337 8.7 ... Runden von Daten ... 341 8.8 ... Textfunktionen zur Bereinigung und Strukturierung von Daten ... 345 8.9 ... Fehlerunterdrückung ... 351 8.10 ... Einsatz von logischen Funktionen ... 357 8.11 ... Berechnungen mit »LET()« erstellen -- Funktionsweise und Nutzen ... 361 8.12 ... Wie viel VBA benötigen Controller nach der Einführung von »LAMBDA()« noch? ... 372 9. Neue dynamische Matrixfunktionen in Excel für Microsoft 365 ... 387 9.1 ... Das Control-Shift-Enter-Beben ... 388 9.2 ... Neue Optionen für die Erstellung dynamischer Datenmodelle ... 414 10. Bedingte Kalkulationen in Datenanalysen ... 423 10.1 ... Kalkulationen ohne Bedingungen ... 424 10.2 ... Kalkulationen mit einer Bedingung ... 426 10.3 ... Bereichsnamen -- der schnelle Zugriff auf Datenbereiche ... 429 10.4 ... Fehlervermeidung bei der Eingabe von Bedingungen -- die Datenüberprüfung ... 435 10.5 ... Bedingte Kalkulationen mit mehr als einer Bedingung ... 439 10.6 ... Vorteile von »SUMMENPRODUKT()« gegenüber anderen Funktionen zur bedingten Kalkulation ... 447 10.7 ... Multiplikation von Textwerten mit »SUMMENPRODUKT()« ... 448 10.8 ... Bedingte Kalkulation mit ODER im Tabellenblatt »Report_III« ... 449 10.9 ... Ausschluss von Datensätzen bei bedingten Kalkulationen ... 449 10.10 ... Häufigkeiten schnell berechnen ... 451 10.11 ... Mittelwerte ohne Nullwerte berechnen ... 453 10.12 ... Mittelwert bei #DIV/0! ... 454 10.13 ... Fallbeispiel zur bedingten Kalkulation ... 456 11. Pivottabellen und -diagramme ... 461 11.1 ... Vorbereitung der Basisdaten für eine Pivottabelle ... 462 11.2 ... Pivottabellen erstellen ... 466 11.3 ... Pivotcache und Speicherbedarf ... 484 11.4 ... Visuelle interaktive Analyse von Daten ... 485 11.5 ... Zeitbezogene Auswertungen von Pivottabellen mit Zeitachsen ... 493 11.6 ... Filtern von Daten in einer Pivottabelle ... 495 11.7 ... Gruppierungen in Pivottabellen ... 496 11.8 ... Weiterverarbeitung von Daten aus Pivottabellen ... 520 11.9 ... Personaldaten mithilfe von Pivottabellen konsolidieren ... 527 11.10 ... Grundlegendes zu PivotCharts ... 535 11.11 ... Zusammenfassung: Pivottabellen und PivotCharts ... 546 12. Business Intelligence mit Power Pivot ... 549 12.1 ... Arbeiten auf der Self-BI-Baustelle ... 550 12.2 ... Inhaltliches und Organisatorisches zu den Beispielen ... 551 12.3 ... Die Power-Pivot-Oberfläche im Überblick ... 552 12.4 ... Logische Beziehungen statt »SVERWEIS()« und Co. ... 557 12.5 ... Berechnete Spalten und berechnete Felder unterscheiden ... 560 12.6 ... Eine berechnete Spalte erstellen ... 561 12.7 ... Eine Power-Pivot-Tabelle in Excel erstellen ... 563 12.8 ... Mehr Übersichtlichkeit herstellen ... 565 12.9 ... Referenztabellen einbinden ... 567 12.10 ... Einbinden von Daten aus anderen Datenquellen ... 570 12.11 ... Typische Erkennungszeichen für fehlende logische Beziehungen in Datenmodellen ... 572 12.12 ... Tabellen der Arbeitsmappe in das Datenmodell einbinden ... 575 12.13 ... Tabellen des Datenmodells ausblenden ... 578 12.14 ... Berechnete Felder in Power-Pivot-Tabellen verwenden ... 580 12.15 ... Bearbeiten von Measures ... 585 12.16 ... Bedingte Kalkulationen mit »CALCULATE()« ... 587 12.17 ... Datenschnitte und Zeitachsen ... 591 12.18 ... Wie DAX-Funktionen arbeiten ... 596 12.19 ... Verwendung von Zeitintelligenz-Funktionen in einem Datenmodell ... 603 12.20 ... Vorjahresvergleiche mit »SAMEPERIODLASTYEAR()« erstellen ... 606 12.21 ... Individuelle Zeitintervalle mit »DATEADD()« berechnen ... 607 12.22 ... Bedingte Formatierungen und Diagramme in Power-Pivot-Reports ... 609 13. Excel als Planungswerkzeug ... 613 13.1 ... Wettbewerberanalyse ... 613 13.2 ... Potenzialanalyse ... 620 13.3 ... Portfolioanalyse ... 625 13.4 ... Stärken-Schwächen-Analyse ... 630 13.5 ... Absatzplanung ... 636 13.6 ... Prognosen erstellen ... 654 13.7 ... Personalplanung ... 661 13.8 ... Liquiditätsplanung ... 678 13.9 ... Marktanalyse und Absatzplanung ... 685 14. Operatives Controlling mit Excel ... 693 14.1 ... Betriebsabrechnungsbogen ... 693 14.2 ... Divisionskalkulation ... 703 14.3 ... Zuschlagskalkulation ... 706 14.4 ... Äquivalenzziffernrechnung ... 709 14.5 ... Prozesskostenrechnung ... 711 14.6 ... Deckungsbeitragsrechnung ... 715 14.7 ... Dynamische Break-even-Analyse ... 716 14.8 ... Mehrstufige Deckungsbeitragsrechnung ... 725 14.9 ... Planen von Kosten und Erlösen mithilfe von Szenarien ... 726 14.10 ... Produktkalkulation mit Deckungsbeitragsrechnung ... 731 14.11 ... Eigenfertigung oder Fremdbezug (make or buy) ... 745 14.12 ... Zinsen, Tilgung, Annuitäten für Darlehen berechnen ... 748 14.13 ... Abschreibungen ... 753 14.14 ... Methoden der Investitionsrechnung ... 756 14.15 ... Customer Lifetime Value ... 770 14.16 ... Kundenscoring ... 784 14.17 ... Personalstrukturanalyse ... 786 14.18 ... Arbeitszeitanalyse ... 792 14.19 ... Reisekostenabrechnung ... 795 14.20 ... Lieferantenbewertung ... 799 15. Unternehmenssteuerung und Kennzahlen ... 813 15.1 ... Zielkostenmanagement (Target Costing) ... 814 15.2 ... Cashflow ... 824 15.3 ... Free Cashflow ... 827 15.4 ... Discounted Cashflow ... 828 15.5 ... Gewichtete durchschnittliche Gesamtkapitalkosten nach Steuern ... 829 15.6 ... Shareholder-Value ... 830 15.7 ... Economic Value Added -- EVA® ... 835 15.8 ... Market Value Added -- MVA ... 837 15.9 ... Bilanzkennzahlen ... 838 15.10 ... GuV-Gliederung ... 847 15.11 ... Beispieldatei GuV -- Bilanz -- Kapitalfluss ... 852 15.12 ... Return on Investment und DuPont-Schema ... 856 15.13 ... Messung der Mitarbeiterzufriedenheit ... 859 15.14 ... Selbstbewertung nach EFQM ... 875 16. Reporting mit Diagrammen und Tabellen ... 891 16.1 ... Grundlagen ... 891 16.2 ... Das Standarddiagramm in Excel ... 893 16.3 ... Wichtige Gestaltungsregeln ... 898 16.4 ... Umgang mit Farben ... 902 16.5 ... Auswahl des richtigen Diagrammtyps ... 903 16.6 ... Die neuen Diagrammtypen seit Excel 2016 ... 921 16.7 ... Allgemeine Formatierungsregeln ... 935 16.8 ... Elemente und Gestaltungsregeln für Dashboards ... 936 16.9 ... Infografiken seit Excel 2016 ... 938 16.10 ... Piktogramme und Fotos ... 939 16.11 ... 3D-Karten ... 943 16.12 ... Power View ... 944 16.13 ... Kombinationen aus Tabellen und Diagramm erstellen ... 947 16.14 ... Dynamische Diagramme ... 949 16.15 ... Spezielle Diagrammtypen ... 954 16.16 ... Spezielle Formatierungen im Diagramm ... 966 16.17 ... Diagramme in Tabellenblättern ... 974 16.18 ... Dashboards erstellen ... 988 16.19 ... Übernahme in PowerPoint ... 996 16.20 ... Übernahme in Word ... 1004 17. Automatisierung mit Makros -- VBA für Controller ... 1005 17.1 ... Wie alles anfängt -- die Aufzeichnung eines Makros ... 1007 17.2 ... Makros über Schaltflächen aufrufen ... 1015 17.3 ... Quellcode im Editor bereinigen ... 1019 17.4 ... Bereiche adressieren ... 1023 17.5 ... Arbeiten mit Variablen ... 1027 17.6 ... Umgang mit Programmfehlern ... 1031 17.7 ... Kopieren, Verschieben und Filtern von Daten ... 1034 17.8 ... Zugriff auf Dateien über VBA-Makros ... 1047 17.9 ... Fallbeispiel: CSV-Import und Datenaktualisierung für einen Forecast ... 1054 17.10 ... Flusskontrolle mit »If ... Then ... Else« ... 1057 17.11 ... Programmierung von Schleifen in VBA ... 1063 17.12 ... Formeln und Funktionen in VBA-Makros ... 1075 17.13 ... Gestaltung von Dialogen in VBA ... 1079 17.14 ... Benutzerdefinierte Funktionen ... 1088 17.15 ... Die Beispiele aus dem Buch zum Herunterladen ... 1096 Index ... 1097