1.1 So sollten Sie Ihr Excel

1.1 So sollten Sie Ihr Excel-Modell aufbauen!
Die Möglichkeiten von Excel in Bezug auf den Aufbau von Modell für das
Controlling / Finanzwesen sind nahezu unbegrenzt. Die Grenzen werden allein
von der Leistungsfähigkeit Ihres PC gesetzt. Damit ist gemeint, daß Sie Daten in
einer begrenzten Menge berechnen, gestalten und darstellen können. Damit ist
auch gemeint, daß Sie in Excel prinzipiell alles tun können, aber auch die Frage
nach der Aufwands-/Nutzen-Relation zu stellen ist. Nur je komplexer ein ExcelModell bereits ist, desto größer ist der zeitliche Aufwand für die notwendigen
Veränderungen. Dies hat dazu geführt, dass Excel einerseits das am meisten
unterschätzte, weil am meisten falsch eingesetzte IT-Tool ist.
Im Folgenden möchten wir Ihnen einen Vorschlag für den Aufbau von ExcelModellen für den längerfristigen Einsatz vorstellen, der sich in unserer Praxis
sehr bewährt hat und die genannten Nachteile vermeidet. Generell verwenden
wir ein "hierarchisch" aufgebautes Modell. Dazu gehört u.a.:
1. Trennung von so genannten "Veränderbaren Zellen" (Zellen ohne Formeln,
Funktionen, Verknüpfungen = Eingabezellen) und "Formelzellen" auf
verschiedenen Blättern.
2. Einsatz von Bereichsnamen und Verknüpfungen
3. Soll das Modell sehr dynamisch sein, dann empfiehlt es sich mit MatrixFunktionen
(SVERWEIS.
INDEX.VERGLEICH.
BEREICH.VERSCHIEBEN)
4. Datenbank-ähnlicher Aufbau
So gibt es generell ein Tabellenblatt, das wir Basisdaten oder general dates
nennen. 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 notwendigsten Daten.
Basisdaten/general
dates
Auf diesem Tabellenblatt werden die für Ihr 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..
Werte/values
Auf diesen Blättern erfolgen Ihre Berechnungen, indem Sie Basisdaten und
Basisvariablen miteinander per Verknüpfungen berechnen. Natürlich werden Sie
nicht mit einem Berechnungsblatt auskommen, daher erzeugen wir
Verknüpfungen immer nach dem Prinzip der Einbahnstraße. D.h. die
Verknüpfungen verlaufen immer von Blatt zu Blatt in eine Richtung, niemals
zurück.
Berechnungen /
calculations
Auf diesem Blatt erfolgt die Zusammenstellung der für Sie wichtigsten Zahlen,
quasi als Oberfläche eines Informationssystems. Die Benutzeroberfläche/das
Frontend kann aus mehreren Tabellenblättern bestehen, die wiederum
Diagramme, eine Pivot-Tabelle, Datenselektion per Schaltflächen enthalten.
Benutzeroberfläche
Frontend
Basisdaten
Werte
Berechnungen
Frontend
Frontend
Abbildung: Das PRT-Modell
Es empfiehlt sich bei Einsatz von Bereichsnamen ein separates Tabellenblatt
einzufügen, auf dem Sie die bereits festgelegten Namen einfügen. Damit haben
Sie stets eine aktuelle Übersicht über bereits verwendete Namen. In diesem
Zusammenhang sollten Sie die Namensvergabe sorgfältig planen, denn nicht
immer ist es vorteilhaft mit Namen zu arbeiten. Am besten eignen sich Namen
für Zellen, die auf einem zentralen Blatt Werte bereitstellen, eben dem Werte /
value-Blatt
Diese Modellierung hat den Vorteil, dass nachdem Sie die Richtigkeit Ihrer
Formeln und Funktionen überprüft haben, diese Zellen/Blätter schützen können.
Danach müssen Sie nur noch die Basisdaten und die Basisvariablen pflegen.
Namenliste
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.
Die einzelnen Hierarchien lassen sich durch entsprechende Registerfarben
darstellen.
Sie können zuletzt mit Hilfe von Makros oder Hyperlinks eine Benutzerführung
einbauen, die den Anwender gezielt von Zelle zu Zelle führt, von Blatt zu Blatt.
Tabellenblätter, auf die der Anwender keinen Zugriff erhalten soll, werden
ausgeblendet.
Unsere Erfahrungen zeigen, daß Sie so einen Standard-Modell für Ihr
Unternehmen entwickeln können. Jeder Mitarbeiter findet sich sofort darin
zurecht, da es einen Standard darstellt. Das Modell kann wachsen und Sie
kommen prinzipiell mit zehn Funktionen aus. Letztlich wiederholen Sie
permanent ein bestimmtes Schema, daß aber hoch flexibel ist!
So importieren Sie flexibel Daten in Ihr Modell!
Sie möchten flexibel, schnell und effizient Daten aus Ihrem Haupt-IT-System
nach Excel importieren. Dafür lässt sich für alle Systeme ein Grundprinzip
einheitlich beschreiben:
Sie arbeiten mit einem Softwaresystem für Ihre Rechnungswesen/Controlling
(SAP FI/CO, DATEV, NAVISION, BAAN, KKH, etc.). In diesem System gibt
es standardisierte Abläufe zur Verdichtung von Daten zu Reports.
- Sie können in der Regel aus Ihrem Vorsystem heraus die gewünschten Daten
in irgendeinem Datenformat als Datei exportieren und diese Datei in Excel
öffnen. Bei diesem Weg bleibt jedoch meist ein erheblicher Aufwand in der
Nachbearbeitung der Daten. Diese Nacharbeit wird manuell mit
Textfunktionen, dem Text-Asssitenten, per VBA-Programmierung oder mit
Zusatz-Tools vollzogen.
- Ihr Primärsystem arbeitet mit Daten aus einem Datenbanksystem (Oracle,
SQL Server, Sybase, DB2, etc.), verabeitet diese Daten in definierten
Prozessen und schreibt die Ergebnisse zurück. Tatsächlich läßt sich mit
einfachen Mitteln eine physische Verbindung zwischen serverbasierten
Datenbanksystemen und Excel aufbauen, über die dann über verschiedene
Konzepte Daten nach Excel zu exportiert werden können. Dazu muß das
Datenbanksystem über eine standardisierte Schnittstelle mit dem Namen
ODBC verfügen. Bei diesem Weg entfallen die üblichen Nacharbeiten nach
einem Dateiimport. Die Schnittstelle ODBC wird über die Sprach SQL
gesteuert, wofür Microsoft im Rahmen des Office-Paktes ein Tool MS
Query zur Verfügung stellt.
Wenn Sie mit Query und Excel arbeiten möchten, sollten Query, das QueryAdd-In und die notwendigen ODBC-Treiber installiert sein. Normalerweise wird
die Installation automatisch mit vorgenommen bei der Excel-StandardInstallation. Wenn diese Komponenten nicht vorhanden sind, müssen sie
nachinstalliert werden.
Abbildung: Datenimport-Übersicht
So könnte ein Planungsmodell aussehen!
Um Ihnen ein Gefühl dafür zu geben, was Sie diese Techniken im Alltag nutzen
können, hier ein kleines Beispiel aus einem unserer Projekte:
Bei einem Unternehmen wurden monatlich von 22 Vertriebs-Niederlassungen
weltweit jeweils eine Excel-Datei per mail "eingesammelt". Diese Dateien enthalten in einem vorgegeben Formular Planungsdaten zur Einschätzung des
Vertriebspotenzials, die in der Unternehmenszentrale durch das VertriebsControlling verdichtet werden. Jede Datei enthält 30 relevante Zahlen für die
Potenzialanalyse. Diese Zahlen wurden bisher manuell kopiert und in einer
weiteren Datei zusammengefasst, damit für die Vertriebsleitung diverse
Auswertungen erstellt werden konnten.
Das Unternehmen befindet sich seit ca. 2 Jahren in einem permanenten Umbruch,
die Strukturen verändern sich mit. Daher können diese Strukturen auch nicht in
SAP - dem Primärsystem - abgebildet werden, da zu aufwendig. Dennoch werden
die aktuellen Informationen benötigt. Erschwerend kommt hinzu, dass die
Vertriebsgebiete sich selbst permanent verändern.
Manuelles Zusammenfassen der 22*30 Werte zu einer Liste (Copy & Pace),
danach manuelle Überarbeitung von 22 Berichten und Überarbeitung der
Potenziale. Die Berichte enthalten Tabellen, Kennzahlen und Diagramme. Dauer
des Prozesses waren bisher drei Arbeitstage. Aufgrund der Sicherung der
Vergangenheitsdaten war die Grenze von 65.000 Datensätzen in "Sichtweite".
Aufgabenstellung
Verbesserung des bisherigen Prozesses und Aufbau eines ganz einfachen Modells,
da diese wahrscheinlich maximal ein Jahr genutzt werden würde.
Im Rahmen eines Vormittages wurden die 22*30 Einzelwerte zu einer Liste
(einfache Makroaufzeichnung) zusammengefaßt, in eine Datenbank verknüpft,
mit Hilfe der Pivot-Tabelle verdichtet und analysiert, sowie an diverse Berichte
und Diagramme übergeben. Im Rahmen des Nachmittages wurden die
verschiedenen Berichte "automatisch" gestaltet.
Dauer des Prozesses jetzt: 5 M I N U T E N!
Dauer der Erstellung: 1 Manntag!
Planung und Vorbereitung : 1 Manntag!
Beschreibung
Problem
Zustand vorher
Lösung