Archiv für den Monat Januar 2014

Excel ist tot – es lebe Excel (Teil 4)

Controller untersuchen und beurteilen traditionell die Effizienz und Effektivität anderer Fachbereiche, müssen aber zunehmend den Nachweis erbringen, selber so zu arbeiten.  Excel ist eines der wichtigsten Standardprogramme im Controlling und Finanz- & Rechnungswesen vieler Unternehmen. Excel wird für die Planung & Budgetierung eingesetzt, für das Reporting, für Simulationen uvm..  In zahlreichen Publikationen zum Thema Planung, Risiko-Management, Treasury-Management u.a. wird festgehalten, dass aus vielen Gründen Excel für solche Aufgaben ungeeignet sei.
Diese Kritik an MS Excel ist so nicht zutreffend, denn die Ursachen sind nicht in den „Beschränkungen“ von MS Excel, sondern in den Kenntnissen der Anwender und der Anwendungsphilosophie in den Unternehmen zu suchen!
Kaum ein Unternehmen besitzt aber Nutzungskonzepte, Definitionen von notwendigen Excel-Kenntnissen für Mitarbeiter, eine Richtlinie zur Modellierung und Nutzung von Excel (CEP).
Aus diesen und weiteren Gründen können auf Excel-Modellen basierende Entscheidungen ein großes Risikopotenzial darstellen. So werden Excel-Modelle meist unabsichtlich fehlerhaft und ineffizient erstellt und Fehler unbemerkt im Informations- und Steuerungsprozess fortgeführt (Garbage in, Garbage Out). Im schlimmsten Fall werden hier existenzgefährdende Risiken für das Unternehmen nicht oder zu spät erkannt.  Ganz abgesehen von den Sanktionen, die sich aus dem Verstoß gegen gesetzliche Regelungen wie die MaRisk, KonTraG und SOX ergeben. Sie sollten also den Einsatz von Excel in Unternehmen genauer unter die Lupe nehmen. Denn nach wie vor ist Excel unverzichtbar!
In dieser Beitragsserie soll eine alternative Anwendungsphilosophie dargestellt mit der der Excel-Workflow im Controlling optimiert werden kann. Es werden keine Excel-Techniken vermittelt.

Problem des Datenimports und der Anzahl der Datensätze

Im Fachbereich Controlling werden in der Regel zahlreiche IT-Systeme verwendet. Dabei dürfte SAP das am meisten eingesetzte ERP-System sein. Aus SAP & Co. werden die ge-wünschten Daten in irgendeinem Datenformat als Datei oder unmittelbar in eine Excel-Tabelle exportiert. Bei diesem Verfahren entsteht jedoch ein erheblicher Aufwand in der Nachbearbeitung der Daten. Die Ursache dafür sind der wenig „Excel-freundliche“ Aufbau und die Struktur der exportierten Standard-Berichte (Beispieldatei 2 im Download-Bereich).
Mit sehr hohem manuellem Aufwand, teilweise mit VBA-Programmierung oder mit Zusatz-Tools werden diese Reports in Excel-Listen verwandelt. Dies ist völlig unnötig!

Alternative 1:

Sie könnten in Ihrem Vorsystem spezielle, für den Export nach Excel vorgesehene Berichte generieren (lassen). Hier stelle ich immer wieder fest, dass in diesem Bereich die Kenntnisse der Controller zur Berichtserstellung (z.B. in SAP) oder auch die Kenntnis über die vorhande-nen Berichte, Tabellen recht gering sind. Damit besteht eine Abhängigkeit von internen oder externen Experten!

Alternative 2:

SAP & Co. werden in Verbindung mit Datenbanksystemen (z.B. Oracle, SQL Server, DB2, etc.) eingesetzt. Mit einfachen Mitteln lässt sich eine Verbindung zwischen Datenbanksystem und Excel aufbauen (ODBC), mittels der Daten nach Excel exportiert werden können. Mit diesem Verfahren entfallen die üblichen Nacharbeiten eines Dateiimports. Die Schnittstelle ODBC wird über die Sprache SQL gesteuert, wofür Microsoft im Rahmen des Office-Paketes ein Tool MS Query zur Verfügung stellt.

So entsteht eine dynamische Verbindung zwischen Ihrem Excel-Modell und der Datenbank, die stets die aktuellen Daten in einem genau beschriebenen Umfang „liefert“.

Der Vorteil:

  • Kein Datenfriedhof mit 65.000 oder mehr (Excel 2007 oder jünger) Datensätzen!
  • Die Dateien, die Sie nach der beschriebenen Modellierung erstellen und über MS Query mit den notwendigen Daten befüllen, sind deutlich kleiner als 1 MB! (Beispiel 3 im Downloadbereich).
  • Die Daten lassen sich auf „Knopfdruck“ aktualisieren.

„Datentopf“

Sollten Sie keine Zugriffsberechtigung auf das primäre Datenbanksystem erhalten, können Sie als Alternative den benötigten Datenbestand periodisch aus dem Primärsystem in eine zweite, dezentrale Datenbank schreiben lassen.
Sollte auch das nicht möglich sein, richten Sie sich das dezentrale Datenbanksystem selbst ein und „füttern“ es mit den als Datei exportierten Standardberichten. Das bedeutet einen gewissen Erstellungsaufwand, zahlt sich aber durch den Zeitgewinn aus!

Konzepte, um Daten nach Excel zu importieren

Konzepte, um Daten nach Excel zu importieren

Wird hier fortgesetzt…..

EXCEL-Tipp: Nachgestelltes Minuszeichen nach vorne, Variante 1

Worum geht es?

Nach der Übernahme von Daten aus dem Primärsystem nach Excel ist häufig festzustellen, dass die Datensätze und Zeichen nicht wie gewünscht in der Excel Tabelle angeordnet werden. Hier kann es die vielfältigsten Überraschungen geben:

  • Datensätze werden in eine Zelle geschrieben
  • Datensätze werden auf zahlreiche Zellen verteilt
  • Formate von Zahlen und Datumsangaben sind nicht numerisch
  • Zahlen haben nachgestellte Minuszeichen
  • u.v.m.

Das folgende Beispiel stammt aus unserem Seminar Datenimport & -analyse mit EXCEL:

Problembeschreibung

Gerade aus SAP werden negative Zahlen häufig mit einem nachgestellten Minuszeichen „geliefert“. Dadurch liegt dann in Excel ein Text und leider keine Zahl in der Zelle vor. Text-Funktionen können auch hier wieder helfen:

  1. LÄNGE ermittelt die Anzahl der Zeichen, aus denen eine Zeichenfolge besteht.
  2. RECHTS liefert die Zeichen, die am rechten Rand einer Zeichenfolge zu finden sind.
  3. LINKS liefert die Zeichen, die am linken Rand einer Zeichenfolge zu finden sind.
  4. VERKETTEN verbindet einzelne Zeichen oder Zellinhalte zu einer Zeichenfolge.

Excel-Techniken

Funktion LÄNGE

Funktion RECHTS

Funktion LINKS

Funktion VERKETTEN

Die Lösung sehen Sie in der Beispieldatei.

Excel ist tot – es lebe Excel (Teil3)

Controller untersuchen und beurteilen traditionell die Effizienz und Effektivität anderer Fachbereiche, müssen aber zunehmend den Nachweis erbringen, selber so zu arbeiten. Excel ist eines der wichtigsten Standardprogramme im Controlling und Finanz- & Rechnungswesen vieler Unternehmen. Excel wird für die Planung & Budgetierung eingesetzt, für das Reporting, für Simulationen uvm.. In zahlreichen Publikationen zum Thema Planung, Risiko-Management, Treasury-Management u.a. wird festgehalten, dass aus vielen Gründen Excel für solche Aufgaben ungeeignet sei. Diese Kritik an MS Excel ist so nicht zutreffend, denn die Ursachen sind nicht in den „Beschränkungen“ von MS Excel, sondern in den Kenntnissen der Anwender und der Anwendungsphilosophie in den Unternehmen zu suchen! Kaum ein Unternehmen besitzt aber Nutzungskonzepte, Definitionen von notwendigen Excel-Kenntnissen für Mitarbeiter, eine Richtlinie zur Modellierung und Nutzung von Excel (CEP). Aus diesen und weiteren Gründen können auf Excel-Modellen basierende Entscheidungen ein großes Risikopotenzial darstellen. So werden Excel-Modelle meist unabsichtlich fehlerhaft und ineffizient erstellt und Fehler unbemerkt im Informations- und Steuerungsprozess fortgeführt (Garbage in, Garbage Out). Im schlimmsten Fall werden hier existenzgefährdende Risiken für das Unternehmen nicht oder zu spät erkannt. Ganz abgesehen von den Sanktionen, die sich aus dem Verstoß gegen gesetzliche Regelungen wie die MaRisk, KonTraG und SOX ergeben. Sie sollten also den Einsatz von Excel in Unternehmen genauer unter die Lupe nehmen. Denn nach wie vor ist Excel unverzichtbar! In dieser Beitragsserie soll eine alternative Anwendungsphilosophie dargestellt mit der der Excel-Workflow im Controlling optimiert werden kann. Es werden keine Excel-Techniken vermittelt.

Problem des Entwurfs und der Dokumentation

Excel lädt sehr stark dazu ein, spontan zu arbeiten. Das führt dazu, dass Tabellen zunächst einmal „irgendwie“ erstellt, im Laufe der Zeit erweitert und anpasst werden, um irgendwann völlig unübersichtlich zu sein, Unmengen an Datensätzen zu enthalten. und nicht mehr beherrschbar zu sein.Selbst der Schöpfer dieses Modells findet sich nur schwer darin zurecht, wie soll das Kolleginnen/Kollegen gelingen, die ebenfalls mit diesem Modell arbeiten oder es weiterentwickeln sollen? Denn natürlich hat niemand dieses Modell dokumentiert!

Meine Empfehlung:

  • Investieren Sie Zeit für die Planung des Modells.
  • Überlegen Sie sich, welche Aufgabe das Modell erfüllen soll und welche Funktionalitäten Sie dazu benötigen. Wenn Sie z.B. viel mit SVERWEIS arbeiten, dann erfordert der Einsatz dieser Funktion einen bestimmten Tabellenaufbau!
  • Dokumentieren Sie Ihr Modell, damit Sie selbst und andere dieses Modell langfristig durchschauen!

Ein Excel-Modell zu dokumentieren ist sehr sinnvoll, leider nimmt kaum jemand diesen Aufwand auf sich. Diese Problematik könnte gemildert werden, in dem bestimmte Vorgehensweisen zum verbindlichen Standard erklärt werden.

  • Angefangen bei einer standardisierten Modellierung,
  • über Standards in der Anwendung bestimmter Techniken / Funktionalitäten,
  • das Verbot der Anwendung bestimmter Techniken / Funktionalitäten (z.B. SVERWEIS).

Wie könnte solch eine standardisierte Modellierung aussehen?

Das PRT-Modell

Das PRT-Modell

Basisdaten

So gibt es generell ein Tabellenblatt, das Basisdaten genannt wird. Auf dieses Tabellenblatt werden Daten aus anderen Systemen importiert oder per Hand eingegeben. Hier gibt es keinerlei Berechnungen. Damit Excel nicht als zusätzliche Datenbank verwendet wird, umfasst der Datenbestand auf diesem Tabellenblatt nur die für die Aufgabe notwendigen Daten.

Zentrale Variablen

Auf einem Tabellenblatt mit dem Namen „Werte“ werden die für das Modell relevanten Variablen zentral untergebracht. Auch hier erfolgt keine Berechnung. Dies können Werte sein wie Zielrenditen, der EURO-Referenzkurs, Zuschlagssätze für die Kalkulation, Kostenstelleninformationen, usw..

Berechnungen

Auf den sogenannten Berechnungs-Blättern erfolgen die notwendigen Berechnungen, indem Basisdaten und Basisvariablen per Verknüpfungen verbunden werden. Natürlich reicht in der Regel ein Berechnungsblatt nicht aus. Daher werden die zahlreichen Berechnungsblätter per „Verknüpfungen“ nach dem Prinzip der Einbahnstraße verbunden. D.h. die Verknüpfungen verlaufen immer von Blatt zu Blatt in eine Richtung, niemals zurück (Zirkelbezüge!). Auf dieser Ebene kann auch eine Pivot-Tabelle positioniert werden, um Daten in einer bestimmten Form zu verdichten oder zu analysieren.

Frontend

Auf diesem Blatt erfolgt die Zusammenstellung der wichtigsten Zahlen, quasi als Oberfläche eines Informationssystems. Die Benutzeroberfläche/das Frontend kann aus mehreren Tabellenblättern bestehen, die Diagramme, Tabellen, Kennzahlenbäume enthalten Die Datenselektion erfolgt dynamisch per Schaltflächen, die Benutzerführung per Hyperlinks.

Die „Verknüpfungen“ in solch einem Modell erfolgen über benannte Zellen und Bereiche. Damit kann die Fachsprache des Controllings für „sprechende Formeln“ verwendet werden, das Bilden von „typischen“ Kennzahlen erfolgt intuitiver. Es empfiehlt sich bei Einsatz von Bereichsnamen eine Dokumentation der festgelegten Namen.

Der Vorteil dieser seit 1997 bewährten Modellierung:

  • Das Grundprinzip aller IT-Systeme, nämlich Trennung der Eingabe von Verarbeitung und Ausgabe (EVA-Prinzip), ist erfolgreich umgesetzt.
  • Trennung von so genannten „Veränderbaren Zellen“ (Zellen ohne Formeln, Funktionen, Verknüpfungen = Eingabezellen) und „Formelzellen“ auf verschiedenen Blättern.
  • Einsatz von Namen für Zellen und Zellbereiche (Kommunikation mit dem Anwender!) sowie „Verknüpfungen“
  • Soll das Modell sehr dynamisch sein, dann empfiehlt es sich mit Matrix-Funktionen (MTRANS, INDEX, VERGLEICH, BEREICH.VERSCHIEBEN) zu arbeiten.
  • Datenbank-ähnlicher Aufbau
  • Jedes Excel-Modell, das nach diesem Prinzip entwickelt wurde, ist beliebig erweiterungsfähig.
  • Die einzelnen Hierarchien lassen sich durch entsprechende Registerfarben darstellen. Damit nehmen Sie eine einfache Form der Dokumentation vor (z.B. Basisdaten + Werte = schwarz, Berechnungen = blau, Frontend = grün).
  • Wenn Sie mit Szenario-Manager und dem Solver arbeiten wollen, so können Sie dies auf diesen Blättern tun. Veränderungen hier wirken sich durch den Modellaufbau auf das gesamte Modell aus.
  • Tabellenblätter, auf die der Anwender keinen Zugriff erhalten soll, werden ausgeblendet.
  • Sind alle Formeln und Funktionen überprüft, können für die Berechnungsblätter und das Frontend die Schutzfunktionen von Excel verwendet werden. Tritt ein unerwarteter Wert oder ein Fehlermeldung auf, kann die Ursache schnell bei den „Basisdaten“ und den „Werten“ gesucht werden. Zeitersparnis beim Fehler-Management!

So könnte eine sehr flexible Standard-Modellierung in einem Unternehmen aussehen. Jeder Mitarbeiter findet sich in so einem Modell über die Registerfarben sofort zurecht. Das Modell kann mit den Anforderungen wachsen und benötigt für die Steuerung maximal zehn Funktionen.

Wird hier fortgesetzt…..

EXCEL-Tipp: Anglomerikanisches Datumsformat umwandeln

Worum geht es?

Nach der Übernahme von Daten aus dem Primärsystem nach Excel ist häufig festzustellen, dass die Datensätze und Zeichen nicht wie gewünscht in der Excel Tabelle angeordnet werden. Hier kann es die vielfältigsten Überraschungen geben:

  • Datensätze werden in eine Zelle geschrieben
  • Datensätze werden auf zahlreiche Zellen verteilt
  • Formate von Zahlen und Datumsangaben sind nicht numerisch
  • Zahlen haben nachgestellte Minuszeichen
  • u.v.m.

Das folgende Beispiel stammt aus unserem Seminar Datenimport & -analyse mit EXCEL:

Problembeschreibung

Im vorliegenden Beispiel soll ein auf drei Spalten verteiltes Datum in angloamerikanischer Schreibweise in einer Zelle vereinigt werden mit einer kontinentaleuropäischen Darstellung.

Das kann leicht mit der Funktion DATUM aus der Kategorie DATUM & ZEIT geschehen.

Excel-Techniken

Funktion DATUM

Die Lösung sehen Sie in der Beispieldatei.

Was ist denn eigentlich dieses PowerPivot? => Mehr Power für die Pivot-Tabelle?

In meinen Seminaren werde ich häufig auf PowerPivot angesprochen. Typische Frage:

  • Was kann die Pivot Tabelle denn nun mehr!
  • Was gibt es Neues durch PowerPivot?
  • Brauche ich PowerPivot wirklich?
  • Wie bekomme ich meine IT-Abteilung dazu, dass sie mir das installiert?
  • Gibt es PowerPivot auch für mein Excel 2003?

Hier die Antworten auf die diese Fragen:

Weiterlesen

Excel ist tot – es lebe Excel (Teil2)

Controller untersuchen und beurteilen traditionell die Effizienz und Effektivität anderer Fachbereiche, müssen aber zunehmend den Nachweis erbringen, selber so zu arbeiten. Excel ist eines der wichtigsten Standardprogramme im Controlling und Finanz- & Rechnungswesen vieler Unternehmen. Excel wird für die Planung & Budgetierung eingesetzt, für das Reporting, für Simulationen uvm.. In zahlreichen Publikationen zum Thema Planung, Risiko-Management, Treasury-Management u.a. wird festgehalten, dass aus vielen Gründen Excel für solche Aufgaben ungeeignet sei. Diese Kritik an MS Excel ist so nicht zutreffend, denn die Ursachen sind nicht in den „Beschränkungen“ von MS Excel, sondern in den Kenntnissen der Anwender und der Anwendungsphilosophie in den Unternehmen zu suchen! Kaum ein Unternehmen besitzt aber Nutzungskonzepte, Definitionen von notwendigen Excel-Kenntnissen für Mitarbeiter, eine Richtlinie zur Modellierung und Nutzung von Excel (CEP). Aus diesen und weiteren Gründen können auf Excel-Modellen basierende Entscheidungen ein großes Risikopotenzial darstellen. So werden Excel-Modelle meist unabsichtlich fehlerhaft und ineffizient erstellt und Fehler unbemerkt im Informations- und Steuerungsprozess fortgeführt (Garbage in, Garbage Out). Im schlimmsten Fall werden hier existenzgefährdende Risiken für das Unternehmen nicht oder zu spät erkannt. Ganz abgesehen von den Sanktionen, die sich aus dem Verstoß gegen gesetzliche Regelungen wie die MaRisk, KonTraG und SOX ergeben. Sie sollten also den Einsatz von Excel in Unternehmen genauer unter die Lupe nehmen. Denn nach wie vor ist Excel unverzichtbar!In dieser Beitragsserie soll eine alternative Anwendungsphilosophie dargestellt mit der der Excel-Workflow im Controlling optimiert werden kann. Es werden keine Excel-Techniken vermittelt.

Keep it simple!

Die Möglichkeiten von Excel für das Controlling sind nahezu unbegrenzt. Die Grenzen werden allein von der Leistungsfähigkeit der Hardware gesetzt. Als Excel-Anwender versuchen wir immer die „Eier-legende-Wollmilchsau“ zu erstellen. Damit ist z.B. eine Tabelle gemeint, in die Zahlen eingegeben, berechnet und für das Reporting „schön“ formatiert werden. Dafür gehen wir in der Regel zahlreiche excel-technische Kompromisse ein und „missbrauchen“ Excel.

  • Eingabezellen versteckt in einem nicht wahrgenommenen Zellbereich.
  • Ausgeblendete (Hilfs-)Spalten/Zeilen.

Am schlimmsten sind die Modelle, in denen zahllose Excel-Dateien miteinander verknüpft sind. Dadurch entstehen zum Teil Bezugszeichenfolgen von mehr als 1024 Zeichen in den Zellen. Diese werden von Excel abgeschnitten und sind letztlich unbrauchbar!

Wie aber sieht ein Excel-Workflow“ im Controlling für „typische“ Aufgabenstellungen (z.B. Reporting) aus?

In einem ersten Schritt werden Daten aus einem ERP-System

  1. nach Excel übernommen.
  2. Diese übernommenen Daten müssen in der Regel nachbearbeitet, verdichtet und ergänzt werden, damit die für die jeweilige Aufgabenstellung notwendige Datenbasis entsteht.
  3. Nicht notwendigerweise im nächsten Schritt, aber sehr häufig müssen Daten analysiert werden.
  4. Excel ist nach wie vor das am meisten eingesetzte Tool, wenn es um das Thema Reporting geht. Also werden für das Management geeignete Tabellen und Diagramme erstellt.
  5. Das Thema Planung / Simulation mit der Frage „Was-wäre-wenn“ wird gerne mit Excel-Modellen gelöst und ergibt sich meistens aus der Suche nach den Ursachen für Abweichungen.
  6. Gerne wird versucht, Teile dieses Workflows oder alles per Makro-Aufzeichung oder VBA-Programmierung zu automatisieren.
Typischer Workflow im Controlling

Typischer Workflow im Controlling

(Enterprise Resource Planning -System ist eine komplexe Anwendungssoftware zur Unterstützung der Ressourcensteuerung eines gesamten Unternehmens, wie z.B. SAP, Oracle E-Business Suite, Navision u.a.)

Vordenken. Nicht nachdenken!

Jeder dieser Teilschritte wird mit einem erheblichen Zeitaufwand erbracht. Mit ein paar wenigen Änderungen im Gesamtkonzept kann hier erheblich Zeit eingespart werden! Dies beginnt bereits mit dem Aufbau eines Excel-Modells.

Wird hier fortgesetzt…..

EXCEL-Tipp: Zellinhalte zu einer Zeichenfolge verbinden

Worum geht es?

Nach der Übernahme von Daten aus dem Primärsystem nach Excel ist häufig festzustellen, dass die Datensätze und Zeichen nicht wie gewünscht in der Excel Tabelle angeordnet werden. Hier kann es die vielfältigsten Überraschungen geben:

  • Datensätze werden in eine Zelle geschrieben
  • Datensätze werden auf zahlreiche Zellen verteilt
  • Formate von Zahlen und Datumsangaben sind nicht numerisch
  • Zahlen haben nachgestellte Minuszeichen
  • u.v.m.

Das folgende Beispiel stammt aus unserem Seminar Datenimport & -analyse mit EXCEL:

Problembeschreibung

Im vorliegenden Beispiel soll eine auf fünf Spalten verteilte Rechnungsnummer in einer Zelle vereinigt werden. Das kann leicht mit der Funktion VERKETTEN aus der Kategorie TEXT geschehen. Das Ergebnis einer Text-Funktion ist allerdings immer ein Text. Im zweiten Schritt muß also das Textformat in ein Zahlenformat verwandelt werden:

  1. Verbindung der fünf Zeichen zu einer Zeichenfolge.
  2. Umwandlung der Zeichenfolge in eine (berechenbare) Zahl.

1.2       Excel-Techniken

Funktion VERKETTEN

Funktion WERT

&-Zeichen

Die Lösung sehen Sie in der Beispieldatei.