Embed Size (px)
DESCRIPTION
5 Geheimtipps für ein exzellentes Reporting in MS Excel“ Praxistipps und Empfehlungen für den Aufbau von qualitätsorientierten und praxistaug-lichen Anwendungen für das Reporting mit Microsoft Excel.Mehr Informationen unter:www.solutiontogo.de
Citation preview
„5 Geheimtipps für ein exzellentes Reporting in MS Excel“
Praxistipps und Empfehlungen für den Aufbau von qualitätsorientierten und praxistaug-
lichen Anwendungen für das Reporting mit Microsoft Excel
Ihr heutiger Referent – Sebastian Zang
Meilensteine der beruflichen Entwicklung
2006 – 2010 Betriebswirtschaftlicher Berater (zuletzt: Prokurist)
Wirtschaftsprüfungs- & Beratungsunternehmen KPMG AG
Konzeptentwicklung & Implementierung von Better Practice
Prozessen sowie Tools für Finanzorganisationen:
Business Intelligence, Management Informations Systeme,
Controllingtools, Planungssysteme, Simulationswerkzeuge
2011 - heute Geschäftsführer Categis GmbH
Rundum-Service für MS Excel und weitere Office-Anwendungen für
Finanz- und Vertriebsorganisationen
• On-Demand Service (Coaching) zur Fehlerbehebung oder zur
technischen Beratung
• Automatisierung bestehender Anwendungen
(mithilfe VBA Programmierung)
• Entwicklung maßgeschneiderter Tools und Funktionsmodule
Sebastian Zang
Warum MS Excel?
Flexibilität
Verbreitungsgrad
Investitionssicherheit
3
Excel Reports einfach
webfähig mit Sharepoint
4
Grundstruktur von Reports in MS Excel
Rohdaten(import) Datenaufbereitung Reports
Tabellendesign
Diagrammdesign
Zugriff auf Quelldaten
im Bereich
„Datenaufbereitung“
Kennzahlenermittlung
Datenselektion für
Reports auf Basis von
Filtereinstellungen (z.B.
Periode)
+ Einfacher &
automatisierungsfähiger
Datenimport
+ Konsistente Kennzahlenberechnung
Einfache Implementierung von Filtermöglichkeiten
(Dynamische Reports)
5
Rohdaten(import)
Rohdaten(import) Reports
Grundsätzlich: Datenhaltung in Datenbanken, aber
Konsolidierung in Excel möglich, ebenso kleinere
Mengen in MS Excel
Erforderlich: Struktur der Datenhaltung, die ebenfalls
strukturelle Veränderungen der Daten zulässt (z.B.
Veränderungen des Kontenplans, neue Abteilungen)
Datenaufbereitung
6
Rohdaten(import) – Konsistente Datenstruktur
Prim
ärs
ch
lüsse
l (z.B
. Ko
nto
Nr)
Rohdaten
Datentyp (Ist, Forecast, Planszenarien, …)
Zeitreihe (Jahr, Quartal, Monat, Tag, …)
Da
ten
qu
elle
(op
tion
al)
Be
ze
ich
nu
ng
en
Ein
ze
lpo
sitio
ne
n
Schematisierte ideale Struktur zur Abbildung von Rohdaten
Datensatz
7
Rohdaten(import) – Intelligenter Datenimport
Optionen für
Datenimport
Performance
Testergebnis
Vorteile
Nachteile
ADODB Verbindung SQL-Import in Pivot SQL-Import in Tabelle
Herstellung einer Verbindung
zur Datenbank mit dem
OLEDB Connection provider
Datenimport aus einer SQL
Abfrage in eine Pivot-Tabelle
Langsamere Datenabfrage
(da immer Live-Abfrage
aus Datenbank)
Höherer Aufwand bei
Einrichtung / Anpassung
Sehr leichtes Excel-File
(i.d.R. kleiner 1 MB)
Write Back Option
(z.B. für Kommentare)
Keine Write Back Option
Excel-File sehr groß
Aktualisierung Gesamt-
daten sehr zeitaufwändig
(>> 5 min; i.d.R. Batchlauf
ein Mal pro Tag/Nacht)
Schnelle Datenabfrage bei
gutem Pivot-Design
Mittlerer Aufwand bei
Einrichtung / Anpassung
Aktualisierung von 30
Werten; Access-Tabelle mit
2,6 Mio. Einzel-Datensätzen;
Abfragezeit: 3 s
Dashboard Aktualisierung:
Zugriff auf Pivot-Tabelle über
Verformelung
Aktualisierung ohne relevante
Verzögerung
Dashboard Aktualisierung:
Zugriff über Verformelung auf
verdichtete Rohdaten
Aktualisierung ohne relevante
Verzögerung
Keine Write Back Option
Excel-File vergleichsweise
klein
Aktualisierung Gesamt-
daten zeitaufwändig (>> 5
min; i.d.R. Batchlauf ein
Mal pro Tag/Nacht)
Schnelle Datenabfrage
Mittlerer Aufwand bei
Einrichtung / Anpassung
Datenimport aus einer SQL-
Abfrage in Tabellenform
Datenquelle
8
Rohdaten(import) – Intelligenter Datenimport
Datenimport über SQL-Abfrage
erlaubt punktuellen Datenimport
nach Excel
Minimierung der Dateigröße in
Excel [um bis zu Faktor 400 im
Vergleich zu Pivotdatenimport]
Dynamische Abfragen lassen sich
u.a. per Makro gestalten
9
Datenaufbereitung für Reports in MS Excel
Datenaufbereitung Reports
Kennzahlenermittlung auf Basis der Reportdaten
(z.B. Working Capital, Cash Flow, Net Debt)
Aufbereitung von Daten für die Diagrammerstellung
Soweit erforderlich/zulässig: Manuelle Umgliederung
von Daten, Manuelle „Datenmanipulation“, etc.
„Dynamisierung“ des Reports durch „dynamischen“
Zugriff auf den „Rohdaten“-Bereich
Rohdaten
10
Datenaufbereitung – Technische Umsetzung - Beispiel
=INDIREKT(C$6&ZELLE("row";A8))/$A$2
11
Datenaufbereitung – Technische Umsetzung – Beispiel
'2001a!P'
Tabellenblatt Spalte
Beispiel für die Umsetzung einer Datenablage über mehrere Tabellenblätter und die systematische Ansteuerung per INDIREKT-Formel im Bereich „Datenaufbereitung“
12
Exkurs: Datenvalidierung
Rohdaten(import) Datenaufbereitung Reports
Planperiode FY ACT Q1 PLAN Q2 PLAN Q3 PLAN Q4 PLAN FY PLAN H1 PLAN H2 PLAN FY PLAN FY PLAN FY PLAN FY PLAN
Finanzjahr 2012 2013 2013 2013 2013 2013 2014 2014 2014 2015 2016 2017
Periodenende 31.12.12 31.3.13 30.6.13 30.9.13 31.12.13 31.12.13 30.6.14 31.12.14 31.12.14 31.12.15 31.12.16 31.12.17
Anzahl Tage 2.557 90 91 92 92 365 181 184 365 365 366 365
Validierungsprüfung
Konzerninternes Clearing
Konzernintern gezahlte / erhaltene Zinsen OK OK OK OK OK OK OK OK OK OK OK
Dividenden
Gezahlte Dividenden = Erhaltene Dividenden OK OK OK OK OK OK OK OK OK OK OK
Konzern
Deckungsbeitrag Finanz- / Vertriebsplanung OK OK OK OK OK OK OK OK OK OK OK OK
Betriebsergebnis Finanz- / Vertriebsplanung OK OK OK OK OK OK OK OK OK OK OK OK
EBIT Finanz- / Vertriebsplanung OK OK OK OK OK OK OK OK OK OK OK OK
Aktiva = Passiva OK OK OK OK OK OK OK OK OK OK OK OK
Vortragsbuchung Eigenkapital OK OK OK OK OK OK OK OK OK OK OK
Cash Flow Bilanz = Cash Flow Finanzplanung OK OK OK OK OK OK OK OK OK OK OK
Validierung
Zentrales Validierungssheet
Prüfkalkulationen, die jeweils
„0“ ergeben müssen
Bedingte Formatierung nach
Ampel-Farben
13
Reporting
Reporting Datenaufbereitung Rohdaten
Aufbereitung der steuerungsrelevanten Informationen:
Was sind die kritischen Erfolgsfaktoren eines
Geschäftsmodells? Was sind die strategischen
Projekte eines Unternehmens?
Informationsaufbereitung nach den Prinzipien:
Vereinfachung - Verdichtung - Verknüpfung
Reporting - Leitlinien zur Gestaltung effizienter Berichte
Übersichtliche Strukturen je
Berichtseite für Daten / Graphiken
Einsatz einfacher Diagramme
(beispielsweise ohne Pseudo-3D)
Vereinheitlichung der Notation, um
Orientierung zu erleichtern
„Lean“ Design, so dass Inhalte
nicht durch dominante gliedernde
Elemente erdrückt werden
„ Stil ist die Fähigkeit, kompliziertere Dinge einfach zu sagen – nicht umgekehrt.“
Jean Cocteau
Vereinfachung Verdichtung Verknüpfung
Reduktion der Berichtselemente
auf informationstragende
Elemente
Einsatz von Microcharts und Ta-
bellen, um Informationsgehalt je
Berichtsseite zu erhöhen
Vermeidung von Redundanz in
der Aufbereitung von Daten
Keine unnötig großen Stellenzah-
len: Platz für wertvolle Information
statt irrelevante „Erbsenzählerei“
Gliederung der Berichtsdaten nach
dem Ursache-Wirkung-Prinzip, so
dass Entwicklung von Kennzahlen
durch sinnvolle Zusammenstellung
von Daten erläutert wird
Einsatz von Diagrammen, die Zu-
sammenhänge zwischen verschie-
denen Kennzahlen in eine Darstel-
lung bringen
15
„Man kriegt ja regelmäßig den Risikobericht, da kann man
nachgucken. Und da hat man so eine kleine Ampel drin. Und
wenn es grün ist, und der überwiegende Teil war eben im grünen
Bereich (…) na, dann scheint es so zu gehen.“
Ronald Weckesser, Verwaltungsrat Sachsen LB,
in den Tagesthemen, 27. August 2007
… zur weitverbreiteten Nutzung von Ampelsystemen …
16
Apr 09
EURm PY Budget Actual
Sales 241 260 274 33 Δ 14 Δ
EBITDA 41 43 46 5 Δ 3 Δ
in % of sales 17,0% 16,5% 16,7% -0,3% Δ 0,2% Δ
Net Income 25 26 27 3 Δ 2 Δ
Sales 36 45 46 10 Δ 1 Δ
EBITDA 7 9 9 2 Δ 0 Δ
in % of sales 20,0% 20,0% 20,0% 0,0% Δ 0,0% Δ
Sales 83 91 91 8 Δ 0 Δ
EBITDA 9 9 9 0 Δ 0 Δ
in % of sales 11,3% 10,0% 10,0% -1,3% Δ 0,0% Δ
Sales 122 124 137 15 Δ 13 Δ
EBITDA 24 25 27 3 Δ 3 Δ
in % of sales 20,0% 20,0% 20,0% 0,0% Δ 0,0% Δ
Lic
ht
Ab
ga
sÖ
lfil
ter
Ko
nze
rn
Δ Act/Bud
Actual & Budget YTD
Δ Act/PY
Ist Budget
274
260
250
255
260
265
270
275
Ist Vorjahr
47,4
37,1
0
5
10
15
20
25
30
35
40
45
50
Ist Vorjahr
160,3
142,7
130
135
140
145
150
155
160
165
Ölfilter
Ist Vorjahr
41,7
37,4
35
36
37
38
39
40
41
42
Abgase Licht
Ca
sh
Flo
w
Sales YTD April
91
137
46
Ölfilter
Abgasanlagen
Lichtsystem
91
137
46
Ölfilter
Abgasanlagen
Lichtsystem
Keine Farben ohne
Bedeutung!
Keine 3D-Effekte ++
++
Schritt für Schritt - Umsetzung der Gestaltungsrichtlinien (1 von 3)
Keine 3D-Effekte
++
17
160,3
142,7
130
135
140
145
150
155
160
165
Ist Vorjahr
41,7
37,4
35
36
37
38
39
40
41
42
43
Ist Vorjahr
47,4
37,1
0
5
10
15
20
25
30
35
40
45
50
Ist Vorjahr
Ölfilter Abgase Licht
Ca
sh
Flo
w
91
137
46
Ölfilter
Abgasanlagen
Lichtsystem
274
260
250
255
260
265
270
275
280
Ist Budget
Sales YTD April
91
137
46
Ölfilter
Abgasanlagen
Lichtsystem
Apr 09
EURm PY Budget Actual
Sales 241 260 274 33 Δ 14 Δ
EBITDA 41 43 46 5 Δ 3 Δ
in % of sales 17,0% 16,5% 16,7% -0,3% Δ 0,2% Δ
Net Income 25 26 27 3 Δ 2 Δ
Sales 36 45 46 10 Δ 1 Δ
EBITDA 7 9 9 2 Δ 0 Δ
in % of sales 20,0% 20,0% 20,0% 0,0% Δ 0,0% Δ
Sales 83 91 91 8 Δ 0 Δ
EBITDA 9 9 9 0 Δ 0 Δ
in % of sales 11,3% 10,0% 10,0% -1,3% Δ 0,0% Δ
Sales 122 124 137 15 Δ 13 Δ
EBITDA 24 25 27 3 Δ 3 Δ
in % of sales 20,0% 20,0% 20,0% 0,0% Δ 0,0% Δ
Lic
ht
Ab
ga
sÖ
lfilte
rK
on
ze
rn
Δ Act/Bud
Actual & Budget YTD
Δ Act/PY
Sehr geringe
Informationsdichte!
Vermeiden von
Kuchendiagrammen!
Unterschiedliche Skalierung der
Graphiken verzerrt konzernweiten
Cash-Beitrag der einzelnen
Geschäftsbereiche!
Zuordnung von
goldgelb für IST-
Werte zur
Orientierung im
Bericht
++++
++
Schritt für Schritt - Umsetzung der Gestaltungsrichtlinien (2 von 3)
18
-5
0
5
10
15
20
25
Jan Feb Mrz Apr Mai Jun Jul Aug Sep Okt Nov Dez
-5
0
5
10
15
20
25
Jan Feb Mrz Apr Mai Jun Jul Aug Sep Okt Nov Dez
-5
0
5
10
15
20
25
Jan Feb Mrz Apr Mai Jun Jul Aug Sep Okt Nov Dez
Kommentar
1
2
3
Kommentar 1
Kommentar 3
Kommentar 2
Ölfilter Abgase Licht
Apr 09
EURm LTM PY Budget Actual PY Budget Actual YE FC
Sales 65 71 73 8 Δ 2 Δ 241 260 274 33 Δ 14 Δ 782 -1 Δ
EBITDA 12 11 12 0 Δ 1 Δ 41 43 46 5 Δ 3 Δ 132 -4 Δ
in % of sales 18,3% 16,1% 16,6% -1,7% Δ 0,5% Δ 17,0% 16,5% 16,7% -0,3% Δ 0,2% Δ 16,9% -1,0% Δ
Net Income 7 8 7 0 Δ -1 Δ 25 26 27 3 Δ 2 Δ 79 -2 Δ0
Sales 10 14 13 3 Δ -1 Δ 36 45 46 10 Δ 1 Δ 135 0 Δ
EBITDA 2 3 3 1 Δ 0 Δ 7 9 9 2 Δ 0 Δ 27 0 Δ
in % of sales 20,0% 20,0% 20,0% 0,0% Δ 0,0% Δ 20,0% 20,0% 20,0% 0,0% Δ 0,0% Δ 20,0% -1,3% Δ
Sales 22 28 25 3 Δ -3 Δ 83 91 91 8 Δ 0 Δ 252 22 Δ
EBITDA 3 3 3 -1 Δ 0 Δ 9 9 9 0 Δ 0 Δ 26 1 Δ
in % of sales 15,0% 10,0% 10,0% -5,0% Δ 0,0% Δ 11,3% 10,0% 10,0% -1,3% Δ 0,0% Δ 10,4% -0,6% Δ0
Sales 33 30 35 2 Δ 5 Δ 122 124 137 15 Δ 13 Δ 395 -23 Δ
EBITDA 7 6 7 0 Δ 1 Δ 24 25 27 3 Δ 3 Δ 79 -5 Δ
in % of sales 20,0% -1,5% 20,0% 0,0% Δ 21,5% Δ 20,0% 20,0% 20,0% 0,0% Δ 0,0% Δ 20,0% -0,5% Δ
Lic
ht
Ab
ga
sÖ
lfilte
rK
on
ze
rn
Δ Act/BudΔ Act/PY
Actual & Budget MTD
Δ Bud/FC Δ Act/Bud
Actual & Budget YTD YearEnd
Δ Act/PY
1
2
3
Ca
sh
Flo
w
-5
0
5
10
15
20
25
Jan
Feb
Mrz
Apr
Mai
Jun
Jul
Aug S
ep Okt
Nov
Dez
Budget Cash Flow Operating Budget Cash Flow Investing Budget Cash Flow Financing
Actual Cash Flow Operating Actual Cash Flow Investing Actual Cash Flow Financing
Mio. Eur
Integration eines Kommen-
tarfelds für Datenanalyse
mit Referenzierungslogik
auf Tabellenzeilen.
Erhöhung der Datendichte
durch Microcharts:
Erhöhung der Datendichte durch:
Differenzierung des CashFlows in
Operating/Investing/Financing, 12-
Monatssicht inkl. Forecast
Einheitliche Skalierung zur
Abbildung des anteiligen Beitrags
der Geschäftsbereiche zum
Konzernergebnis
Schritt für Schritt - Umsetzung der Gestaltungsrichtlinien (3 von 3)
[LTM]
Gestaltungsoptionen MS Excel Reports - Beispiele (1 von 2)
Keine unnötig großen
Stellenzahlen
Nur informationstragende
Elemente im Design
Hoher Informationsgehalt
durch Tabellen
Lean Design: Keine dominan-
ten gliedernden Elemente
19
Gestaltungsoptionen MS Excel Reports - Beispiele (2 von 2)
Einfache Diagramme
(ohne Pseudo-3D)
Kommentarfeld
(für Analyseergebnisse)
Übersichtliche Struktur:
Head – Daten - Filter
Lean Design: Keine dominan-
ten gliedernden Elemente
20
ON-DEMAND Support: Experten-Knowhow, wenn Sie es brauchen!
Webkonferenz
Telefon
Fehleranalyse
Fehlende Erfahrung bei
Umsetzung einer
Anforderung
Zeitaufwändige Umsetzung
von Arbeitsprozessen
Ihre Herausforderung Unser Lösungsangebot
Expertenrat bei allen Fragen
rund um MS Excel
Programmierung kleiner
Makros für einen effizienten
Arbeitsprozess
Warum Solutiontogo?
Exzellentes Preis-Leistungs-Verhältnis: ab 35 EUR / h
Hochwertige und qualitätsgeprüfte Anwendungen von
langjährigen IT-Profis in Office-Anwendungen
Ihr Ansprechpartner mit mehrjähriger Expertise in
Finanzen, Vertrieb und Administration
Solutiontogo
„Das Ergebnis jedes Leistungsprozesses
wird begrenzt durch das am knappsten
vorhandene Hilfsmittel: Die Zeit.“
Peter F. Drucker
Smarte Arbeitsprozesse
Categis GmbH
Am Hofgrund 21
97769 Bad Brückenau
www.solutiontogo.de
