Excel 2016 im Controlling (eBook)

Professionelle Lösungen für Controlling, Projekt- und Personalmanagement

Ignatz Schels, Uwe M. Seidel (Autoren)

eBook Download: PDF | EPUB

2016 | 1. Auflage
626 Seiten
Carl Hanser Fachbuchverlag
978-3-446-44849-0 (ISBN)

Infos

Mit diesem Buch werden Sie zum Experten für Excel im Controlling

Es bietet zahlreiche Lernbeispiele und Lösungen für das Finanzwesen, den Personalbereich und für Projektmanager, professionell aufbereitet und verständlich erklärt, dazu die nützlichsten Techniken, die wichtigsten Funktionen und die besten Tipps und Tricks aus der Praxis.

Von A wie Abschreibung über I wie Investition bis Z wie Zinsberechnung

- Strategische und operative Planung, Kennzahlen, Balanced Scorecard, Deckungsbeitragsrechnung, Human Capital Index sind nur einige von vielen Themen.

- Für Personalcontroller gibt es u.a. Headcount- und Kostenverwaltung, Personalkennzahlen, Zeitkonten und Terminverwaltung.

- Projektschaffende lernen den Aufbau von Projektstrukturplänen, Kapazitätsplanung und Ressourcenmanagement sowie Meilensteintrend- und Earned Value-Analyse und grafische Projektportfolios.

AUS DEM INHALT //

Absatz- und Umsatzplanung // Bilanz, Cashflow-Analyse // Diagramme und Reporting // Kennzahlencockpits // Kostenrechnung // Excel-Praxis für Controller // Planung und Budgetierung // Steuerung und Berichtswesen // Präsentation // VBA-Makroprogrammierung // Tipps und Tricks



Ignatz Schels ist Technik-Informatiker, VBA-Spezialist und zertifizierter Projekt- Fachmann (GPM/IPMA). Er leitet Seminare und Workshops mit Schwerpunkt 'Excel und Access im Controlling und Projektmanagement'. Sein Spezialgebiet ist die Optimierung von Unternehmensprozessen im ERP- und Microsoft Office- Umfeld. Mit über 50 Fachbüchern, darunter einige Bestseller, ist er einer der erfolgreichsten IT-Autoren.

Professor Dr. Uwe M. Seidel lehrt seit 2001 an der Fakultät Betriebswirtschaft der Hochschule Regensburg Rechnungswesen, Controlling und Projektmanagement. Zuvor war er als Manager und Prokurist bei der KPMG Consulting AG (jetzt: BearingPoint GmbH) als Unternehmensberater tätig. Er ist Partner bei der IPM United GmbH und freiberuflicher Trainer der Controller Akademie AG. Zudem leitet Professor Dr. Seidel den AK Süd I des Internationalen Controllervereins (ICV). Am 03. Juni 2009 wurde er als Direktor an das Zentrum für Konfliktkostenforschung der HUMBOLDT-VIADRINA School of Governance berufen.

Inhalt

Inhalt 6
Vorwort 11
Die Autoren 13
1 Hinführung zum Thema 14
1.1 Controlling und Controller 14
1.1.1 Management 14
1.1.2 Controlling 15
1.1.3 Zusammenspiel von Manager und Controller 15
1.2 Controlling und Excel 17
1.2.1 Versionen und Updates 17
1.2.2 Versionsvergleich 17
1.2.3 Excel in der Cloud 20
1.2.4 Excel in Office 365 20
1.2.5 Excel im Data-Warehouse 21
1.2.6 Excel als Controlling-Tool 22
1.2.7 Business Intelligence 25
1.2.8 SharePoint- und Excel-Services 28
1.3 Business Intelligence mit Excel 29
1.3.1 Excel – ein BI-Tool? 29
1.3.2 BI-Werkzeuge in Excel 29
1.3.3 Visualisierung perfekt 31
2 Excel-Praxis für Controller 34
2.1 Vorlagen, Designs und CI-Vorschriften 34
2.1.1 Corporate Identity 35
2.1.2 CI-Design in Excel 36
2.1.3 Benutzerdefiniertes CI-Design 37
2.1.4 Arbeitsmappenvorlage im Startordner erstellen und speichern 39
2.2 Navigieren in Arbeitsmappen und Tabellenblättern 41
2.2.1 Zoomen 41
2.2.2 Nützliche Shortcuts 41
2.2.3 Neue Mappen und Tabellen 42
2.2.4 Formeln, Funktionen und Zellbezüge 43
2.3 Bedingte Formatierung 47
2.4 Die wichtigsten Funktionen für Controller 49
2.4.1 Summen und Statistiken – Basisfunktionen 50
2.4.2 Listen verknüpfen mit SVERWEIS() 51
2.4.3 WENN() und andere Logikfunktionen 55
2.4.4 SUMMEWENN() und ZÄHLENWENN() 56
2.4.5 Fehlerbehandlung mit ISTFEHLER() und WENNFEHLER() 57
2.4.6 Textfunktionen 58
2.4.7 Statistik-Funktionen 59
2.5 Rechnen mit Datum und Zeit 60
2.5.1 Excel-Kalender 60
2.5.2 Datumsfunktionen 61
2.5.3 Kalenderwoche berechnen 61
2.5.4 Feiertage berechnen 62
2.5.5 Excel-Zeitrechnung 66
2.6 Arbeiten mit Bereichsnamen 70
2.6.1 Lokale und globale Bereichsnamen 70
2.6.2 Schnelle Zuweisung über das Namensfeld 71
2.6.3 Namen übernehmen 71
2.6.4 Namens-Manager 72
2.6.5 Konstanten und Formeln in Bereichsnamen 73
2.7 Analyse und Reporting mit PivotTables und PivotCharts 74
2.7.1 Grundprinzip 74
2.7.2 Voraussetzungen für Pivot-Berichte 74
2.7.3 Datenbasis vorbereiten 76
2.7.4 PivotTable-Bericht erstellen 77
2.7.5 Elemente filtern 78
2.7.6 Datenschnitte 79
2.7.7 PivotTable-Bericht formatieren 80
2.7.8 Funktionen für den Werte-/Datenbereich 80
2.7.9 Datumsfelder gruppieren 82
2.7.10 Berechnete Felder 83
2.7.11 Berechnete Elemente 84
2.7.12 Drilldown (Details anzeigen) 85
2.7.13 Pivot-Berichte aus externen Daten 85
2.7.14 PivotCharts 87
2.8 Externe Datenquellen 89
2.8.1 ODBC 89
2.8.2 Externe Verbindung aufbauen 90
2.8.3 Verbindungseigenschaften 92
2.8.4 Verknüpfungen aufheben 93
2.9 PowerQuery 93
2.9.1 Eine neue Abfrage 94
2.9.2 Der Editor 95
2.9.3 Die Abfragetools 96
2.10 Tabellen und strukturierte Verweise 96
2.10.1 Datenschnitte für Tabellen 98
2.10.2 Strukturierte Verweise 98
2.11 Arbeiten mit Formularelementen 101
2.12 Arbeiten mit VBA-Makros 103
2.12.1 Makrosicherheit und Makros aktivieren 104
2.10.2 Spezielle Dateinamen für Makroarbeitsmappen 105
3 Planung und Budgetierung 106
3.1 Strategische Planung 106
3.1.1 Wettbewerberanalyse 107
3.1.2 Portfolioanalyse 113
3.1.3 SWOT-Analyse 119
3.1.4 Stärken-Schwächen-Analyse 120
3.1.5 Umweltanalyse (Chancen-Risiken-Analyse) 126
3.1.6 Unternehmensstrategien 129
3.1.7 Businessplan 132
3.1.8 Zielvereinbarung 145
3.2 Operative Planung und Budgetierung 155
3.2.1 Absatz und Umsatz 157
3.2.2 Personal 180
3.2.3 Investitionen 197
3.2.4 Kosten 203
3.2.5 Finanzen und Liquidität 208
3.2.6 Projekte 221
4 Steuerung und Berichtswesen 242
4.1 Strategische Instrumente 242
4.1.1 Risikomanagement 242
4.1.2 Target Costing/Zielkostenmanagement 256
4.1.3 Rating nach Basel II 264
4.1.4 Shareholder-Value (SHV) 276
4.1.5 Economic Value Added (EVA) 282
4.1.6 Mitarbeiterzufriedenheitsbefragung 288
4.1.7 Human Capital Index 296
4.1.8 Balanced Scorecard 300
4.2 Operative Instrumente 312
4.2.1 Erlöse und Kosten 312
4.2.2 Investitionen 348
4.2.3 Finanzen und Liquidität 359
4.2.4 Personal 412
4.2.5 Projekte 433
4.2.6 Sonstige 449
5 Berichtswesen (Reporting) und Präsentation 468
5.1 Datenaufbereitung für das Reporting 472
5.1.1 Textdaten 472
5.1.2 Datenimport automatisieren mit Access UNION-Abfragen 474
5.1.3 SAP-Berichte 478
5.1.4 ODBC und SQL 484
5.1.5 Abfragen modellieren mit PowerQuery 489
5.1.6 PowerPivot 493
5.1.7 Tutorial: PowerPivot für Controller 497
5.1.8 Trichterdiagramm 503
5.2 Visualisierung von Berichtsinhalten 504
5.2.1 Praxiseinsatz von Diagrammen in Printmedien 504
5.2.2 Historische Hintergründe von Geschäftsgrafiken 505
5.2.3 Wissenschaftlicher Hintergrund 506
5.2.4 Methoden zur Visualisierung und Präsentation 510
5.3 Standarddiagramme professionell gestalten 529
5.3.1 Excel-Praxis: Funktion Datenreihe() 529
5.3.2 Excel-Praxis: Farbmarkierungen nutzen 530
5.3.3 Excel-Praxis: Kamera 531
5.3.4 Excel-Praxis: Flexible Legende 532
5.3.5 Excel-Praxis: Grafikobjekte auf Datenreihen 533
5.3.6 Excel-Praxis: Linienabfall auf null verhindern 534
5.3.7 Excel-Praxis: Balkendiagramm mit Funktion 535
5.4 Spezialdiagramme 536
5.4.1 Excel-Praxis: Benchmark-Diagramm 536
5.4.2 Excel-Praxis: Tachometerdiagramm 537
5.4.3 Excel-Praxis: Wasserfalldiagramm (manuell) 540
5.4.4 Excel-Praxis: Umsatzbericht mit Sparklines 540
5.4.5 Excel-Praxis: Ergebnisrechnung aus GuV 545
5.4.6 Excel-Praxis: Projektportfolio mit Projekt-Map 550
5.4.7 Excel-Praxis: Qualitätsmanagement-Cockpit 560
5.5 Neue Diagrammtypen in Excel 2016 570
5.5.1 Wasserfalldiagramm 570
5.5.2 Treemap 571
5.5.3 Sunburst 572
5.5.4 Histogramm 573
5.5.5 Pareto 574
5.5.6 Kastengrafik (Boxplot) 574
5.6 Präsentieren mit PowerPoint 576
5.6.1 CI-Vorlage vorbereiten 576
5.6.2 Von Excel zu PowerPoint 577
6 VBA-Makroprogrammierung 584
6.1 Controller – Programmierer? 584
6.1.1 Training und Selbststudium 585
6.1.2 Entwicklertools 585
6.1.3 Excel-Praxis: Projektbericht 585
6.2 Makrorecorder 586
6.2.1 Funktionsweise 586
6.2.2 Excel-Praxis: Projektbericht erstellen und aufzeichnen 587
6.3 Visual Basic-Editor 589
6.3.1 VBA-Editor aktivieren 589
6.3.2 Oberfläche 589
6.3.3 Codiertechniken 590
6.3.4 Fehler und Entwurfsmodus 590
6.4 Makro starten 590
6.4.1 Makros aktivieren 590
6.4.2 Excel-Praxis: Makroaufrufschaltfläche für Projektberichtsmakro 590
6.5 Makros editieren 591
6.5.1 Makrocode überprüfen 591
6.5.2 Excel-Praxis: Aufgezeichnetes Makro nachbearbeiten 591
6.5.3 Makromappe speichern 594
6.6 UserForms für mehr Dialog 594
6.6.1 Eingabemasken gestalten 594
6.6.2 Excel-Praxis: Projektleiterauswahl 595
6.7 Dateien versenden über Outlook 598
6.7.1 Mailversand automatisieren 598
6.7.2 Excel-Praxis: Projektleiterbericht versenden 598
7 Tipps und Tricks 600
Index 622

Textauszug

2	Excel-Praxis für Controller

Kennen Sie Excel? Die Frage lässt sich nicht einfach beantworten aufgrund der Vielzahl der Möglichkeiten, die diese Standardsoftware bietet. Neben zahlreichen Werkzeugen für die Formatierung, Gestaltung und Drucklegung von Tabellen wollen Filter und Sortierwerkzeuge, Zielwertsuche, Solver und Teilergebnisse beherrscht sein. 450 Rechenfunktionen von A wie Abrunden bis ZZR wie Zinszeitraum stehen zur Auswahl. Wer professionelle Kalkulationen erstellen will, sollte vor allem die Matrix- und Logikfunktionen beherrschen. Wer externe Daten bezieht, braucht fundierte Kenntnisse über Dateiformate und ODBC-Abfragen und Sicherheit im Umgang mit Textfunktionen. Zu den wichtigsten Werkzeugen des Controllers gehört die Pivot-Tabelle, ein Analysetool, das als „Programm im Programm“ mehrere Dutzend Funktionen aufweist.

In diesem Kapitel stellen wir die wichtigsten Werkzeuge in kompakter Form vor, zeigen, wie diese zur Anwendung kommen, und sparen nicht mit Tipps und Tricks aus der Praxis.

2.1	Vorlagen, Designs und CI-Vorschriften

Wir kennen Sie alle, die kunstvoll gestalteten Excel-Tabellen mit farbigen Hintergrundmustern, verziert mit Linien in allen Strichstärken unter Ausnutzung aller abenteuerlichen Schriftarten und Schriftgrößen. Und die Präsentationen, in denen versucht wird, uns mit den individuellen Gestaltungsvorlieben des Erstellers zu beeindrucken.

Excel bietet leider genügend Möglichkeiten, Diagramme so weit mit Farben, Formaten und Gestaltungselementen auszustatten, dass die Optik über die Aussage siegt. Wenn Ihre Controlling-Berichte noch solche Diagramme enthalten, sollten Sie dringend über ein CI-Konzept nachdenken:

Bild 2.1 Negativbeispiel für Diagramme im Controlling-Bericht

2.1.1

Corporate Identity

Gibt es im Unternehmen CI-Vorschriften für einheitliche Gestaltung, gelten diese meist für Verkaufsbroschüren und Visitenkarten. Wer Zahlen mit Excel visualisiert, unterwirft sich in der Praxis selten irgendwelchen Regeln, was der Lesbarkeit und dem Verständnis der präsentierten Information meist nicht gerade zugutekommt. Einheitlich gestaltete Berichte sind auch mit Excel machbar. Voraussetzung ist, dass die Regeln zuvor aufgestellt und für alle verbindlich gemacht werden.

CI (Corporate Identity) ist für jedes Unternehmen ein wichtiges Merkmal für die Präsentation nach außen. Ein einheitliches und professionelles Auftreten gegenüber Kunden und Geschäftspartnern ist Pflicht, auch für interne Geschäftsprozesse sind Regeln und Vorschriften für Präsentation und Kommunikation unabdingbar.

Ein wichtiger Teil des Konzepts ist das Corporate Design, hierunter fällt neben dem Firmenlogo und dem Briefbogen auch die Gestaltung von Präsentationen und Berichten. Was früher nur für PowerPoint galt, wird zunehmend auch für Excel zur Vorschrift: Schriftart und Schriftgrößen in Tabellen müssen den CI-Richtlinien entsprechen, das Layout von Tabellenblättern sollte intern genormt sein und Größe und Inhalt von Kopf- und Fußzeilen dürfen nicht individuell von jedem Anwender frei definierbar sein. Diese Richtlinien werden im CI-Handbuch festgeschrieben.

Das Textverarbeitungsprogramm Word bietet für solche Zwecke Dokumentvorlagen (Templates) an, NORMAL.DOTX ist die Standardvorlage und für ein einheitliches Design von Dokumenten genügt es, diese (oder andere spezifische wie Faxvorlagen etc.) für jeden Anwender schon bei der Installation zur Verfügung zu stellen. Auch PowerPoint kann Standardvorgaben für Präsentationen aus einer globalen Vorlage beziehen. Excel-Anwender haben keine Möglichkeit, auf Vorlagen zurückzugreifen, da Excel keine Standardvorlage anbietet, aber auch das lässt sich mit einem Trick realisieren.

2.1.2

CI-Design in Excel

Der erste Schritt zu einheitlichen Berichten und Präsentationen ist die Umsetzung der CI-Vorschriften für Excel-Anwender. Ein CI-Handbuch beschreibt:

• wie Daten gespeichert werden (Dateitypen, Ordnerstruktur im Netz, Dateiinformationen),

• welche Sicherheitsrichtlinien zu beachten sind,

• wie Tabellen auf dem Bildschirm und gedruckt aussehen sollten (Verwendung von Farben, Mustern, Linien, Kopf- und Fußzeilen, Hochformat/Querformat etc.),

• welche Diagrammtypen erlaubt und vorgeschrieben sind,

• wie Diagramme in Berichten zu gestalten sind (Farben, Hintergrund, Gestaltungselemente).

Das CI-Konzept für Excel-Anwender enthält dann z.?B. folgende Vorgaben (auszugsweise):

Tabelle 2.1 CI-Konzept für Excel

Bereich

Vorgaben

Tabellenlayout

Standardformat: Hochformat A4 Seitenrand: links und rechts 2?cm, oben und unten 2?cm Keine Hintergrundfarben oder -bilder Kopfzeile links: Dateiname und Speicherpfad Fußzeile links: Name und Abteilung des Erstellers Fußzeile rechts: Seitenzahl und Anzahl Seiten Keine Gitternetze im Ausdruck

Diagramme

RGB-Farben (Rot/Gelb/Grün) für Diagramme: Reihe: 0/135/116 Reihe: 0/77/146 Reihe: 155/187/89

Um für alle Mitarbeiter ein einheitliches Design von Excel-Tabellen und Diagrammen nach den Gestaltungsrichtlinien der Firma zu erstellen, brauchen Sie zunächst ein eigenes Design. Dieses kann über die Werkzeuge im Seitenlayout zusammengestellt und abgespeichert werden. Anschließend gestalten Sie eine Arbeitsmappe und ein Tabellenblatt nach den internen Richtlinien und speichern diese zusammen mit dem Design als Standardvorlage für neue Arbeitsmappen und Tabellenblätter.

Bild 2.2 CI-Design in Excel ? der Weg

2.1.3

Benutzerdefiniertes CI-Design

Mit Office 2010 hat Microsoft einen wichtigen Schritt in Richtung CI getan: Die Einführung von Designs für alle Office-Produkte bietet die Möglichkeit, CI-Richtlinien für Word-Dokumente, Excel-Tabellen und Diagramme und PowerPoint-Präsentationen einheitlich umzusetzen. Ein Design besteht aus je einem Satz Designfarben und Designschriftarten sowie vordefinierten Effekten wie Linien und Fülleffekten. In Excel finden Sie eine Auswahl vordefinierter Designs unter Seitenlayout/Designs. Das Standarddesign heißt Larissa, damit arbeiten alle Excel-Anwender, für die kein firmenspezifisches Design installiert wurde.

Bild 2.3 Integrierte Designs im Seitenlayout

2.1.3.1

Designfarben definieren

Für ein benutzerdefiniertes Design stellen Sie im ersten Schritt die Farben für Texte und Diagrammelemente ein. Die Akzentfarben 1 bis 6 sind für die Formatierung der Datenreihen in Diagrammen zuständig, sie werden auch auf SmartArt-Grafiken und WordArt-Schrifteffekte angewandt. Über die Formenbibliothek gezeichnete Grafikobjekte erhalten automatisch die Farbe der ersten Akzentfarbe.

In mehrreihigen Diagrammtypen (Säulen, Balken, Linien, Flächen) erhalten die Datenreihen die Akzentfarben nach der Anordnung der Reihen (erste Reihe Akzentfarbe 1, zweite Reihe Akzentfarbe 2 usw.). Das Kreisdiagramm verwendet die Akzentfarben für die Segmente, die Zuweisung erfolgt im Uhrzeigersinn ausgehend vom Winkel 0?°.

1. Schalten Sie um auf das Register Seitenlayout.

2. Klicken Sie auf Designs/Farben/Neue Designfarben erstellen.

3. Die ersten Designfarben legen die Kombination aus Textfarben und Hintergrund fest (schwarzer Text auf weißem Hintergrund und weißer Text auf blauem Hintergrund).

4. Öffnen Sie die Farbpalette der ersten Akzentfarbe und stellen Sie die Farbe ein. Um die RGB-Werte der CI-Farben einzutragen, wählen Sie Weitere Farben.

5. Tragen Sie die RGB-Werte der ersten Akzentfarbe ein und bestätigen Sie mit OK.

6. Ändern Sie auch die übrigen Akzentfarben, tragen Sie für jede Farbe die RGB-Werte aus dem CI-Konzept ein.

7. Geben Sie in das Namensfeld CI-Farben ein und speichern Sie die neue Farbgebung.

2.1.3.2

Designschriftarten und Effekte definieren

Ein Design enthält je eine Schriftartendefinition für Überschriften und für Text. Überschriften spielen in Excel keine Rolle, die Schriftart für Text wird automatisch für alle Zellen im Tabellenblatt verwendet. Schreibt das CI-Konzept eine firmeneigene Schrift vor, muss diese unter Windows installiert sein, damit sie in Excel verwendet werden kann. In der Systemsteuerung finden Sie alle Schriftarten, geben Sie in das Suchfeld im Startmenü einfach Schriftarten ein und die Liste aller installierten Schriftarten wird angezeigt.

1. Unter Designs/Schriftarten stehen mehrere Kombinationen zur Auswahl.

2. licken Sie auf Neue Designschriftarten erstellen, wenn Sie keine passende Kombination finden.

3. Stellen Sie die im CI-Konzept vorgeschriebene Schriftart für Textkörper ein.

4. Tragen Sie einen Namen für die Schriftartenkombination Ihres Designs ein und klicken Sie auf Speichern.

Excel bietet in den Optionen noch eine weitere Möglichkeit, die Standardschriftart einzustellen, diese Einstellung hat Vorrang vor der Schriftdefinition im...

Produkt-Details

• Erscheint lt. Verlag: 11.4.2016
• Verlagsort: München
• Sprache: deutsch
• Themenwelt: Informatik ► Office Programme ► Excel
• Schlagworte: Controller • Controlling • Excel • Finanzwesen • Personalbereich • Personalcontroller • Projektmanager
• ISBN-10: 3-446-44849-7 / 3446448497
• ISBN-13: 978-3-446-44849-0 / 9783446448490