Controlling mit Excel: Tipps für einen effizienten Einsatz
Einleitung Controlling mit Excel
Controlling mit Excel bietet zahlreiche Möglichkeiten, dynamische Modelle zu erstellen, die Szenarien abbilden und Anpassungen ermöglichen. Formeln, Pivot-Tabellen und Diagramme helfen dabei, komplexe Datenmengen verständlich aufzubereiten. Controller nutzen Excel, um grosse Datenmengen zu analysieren, indem sie Funktionen wie SVERWEIS, SUMMEWENN und andere einsetzen. Die Integration von Daten aus unterschiedlichen Quellen ist ein weiterer Anwendungsfall. Diagramme und Grafiken in Excel unterstützen dabei, Zahlen zu visualisieren und Entscheidungsträgern klare Handlungsanweisungen zu geben.
Dabei bleibt es jedem Anwender selbst überlassen, mit welchen Excel-Techniken er Aufgaben löst. Das führt nicht nur zu einer Vielfalt von Techniken, die alle zum gleichen Ziel führen, sondern auch dazu, dass nicht immer die effizientesten und effektivsten Excel-Techniken angewendet werden. Hinzu kommt ein Anwendungsprinzip in Excel, das Fehler leichter ermöglicht. Damit sind Fehler gemeint, die zu falschen Ergebnissen führen, aber auch Fehler, bei denen mit falschen Methoden potenzielle Fehler für die unternehmerische Entscheidung entstehen können.
HINWEIS
Ein unsachgemässer Umgang kann zu Sicherheitsvorfällen führen, die nicht nur den Betrieb gefährden, sondern auch rechtliche Folgen nach sich ziehen. Diese potenziellen Fehler sollen aufgezählt werden, und es soll ein Anwendungskonzept vorgestellt werden, das solche Fehler vermeidet.
Datenkonsistenz und -integrität
Im Finanzbereich sind Unternehmen strengen gesetzlichen Vorschriften unterworfen, insbesondere in Bezug auf die Genauigkeit von Finanzberichten und den Schutz sensibler Daten. Fehlfunktionen oder menschliche Fehler in Excel-Dateien können schwerwiegende Folgen haben. Jede Änderung an einer Excel-Datei sollte dokumentiert und nachvollziehbar sein. Dies erfordert, dass Excel-Modelle klare Versionierungskonzepte und eine lückenlose Protokollierung von Änderungen aufweisen.
TIPP
Dies kann in der Datei selbst in Form einer Tabelle geschehen, besser aber in einer begleitenden Dokumentation.
Finanzdaten müssen häufig überprüft und validiert werden, um Fehler zu vermeiden. Excel bietet rudimentäre Mittel zur Datenvalidierung, diese sollten jedoch systematisch eingesetzt werden, um Fehler in Formeln oder Eingaben zu minimieren. Vor allem sollte das Prinzip der Datenintegrität und Datenkonsistenz beachtet werden. Viele gebräuchliche Excel-Techniken verletzen diese Prinzipien.
Datenintegrität bezeichnet die Korrektheit, Konsistenz und Vollständigkeit von Daten über ihren gesamten Lebenszyklus hinweg. Sie stellt sicher, dass Daten unverändert und zuverlässig bleiben, wenn sie erstellt, gespeichert, übertragen oder bearbeitet werden. Verletzungen der Datenintegrität, wie z. B. durch unbefugte Änderungen, Datenverluste oder Übertragungsfehler, können zu ungenauen Informationen und fehlerhaften Analysen führen.
Datenkonsistenz bedeutet, dass Daten in allen Systemen, Anwendungen und Speicherorten übereinstimmen und korrekt sind. Sie stellt sicher, dass verschiedene Datenbanken oder Dateien keine widersprüchlichen Informationen enthalten, sodass jede Änderung oder Aktualisierung einheitlich und zuverlässig ist. Wenn Daten inkonsistent sind, können Verwirrung und Fehler in der Analyse oder Entscheidungsfindung entstehen.
Folgt man diesen für Datenbanken geltenden Prinzipien, dann müsste man für Excel- Modelle Integritätsregeln aufstellen und dafür sorgen, dass diese nicht verletzt werden. Geschieht das dennoch, müssten die letzten Transaktionen rückgängig gemacht werden können. Das ist ohne VBA unmöglich. Nun gibt es einige bestechend einfach anzuwendende Excel-Techniken, die schnell zu einem Erfolg führen, langfristig gesehen aber eher Probleme verursachen (können). Bei folgenden Excel-Techniken sehe ich Gefahren für die Datenkonsistenz:
Kopieren/Ausschneiden und Einfügen von Daten
Nehmen Sie an, Sie hätten aus (z. B. SAP) einen Report als Datei exportiert und kopieren oder schneiden bestimmte Daten aus diesem Report aus und fügen diese Daten in einer Excel-Datei wieder ein. Die Herkunft der Daten ist nicht mehr überprüfbar. Vor allem dann, wenn die nächste Technik angewendet wird:
Sortieren von Daten
Beim Sortieren von Daten in Datenbanken werden Datensätze sortiert. Bei falscher Markierung in Excel wird unter Umständen nur ein Teil der Daten sortiert. Wie auch immer, die Sortierung lässt sich Monate später nicht mehr zum Ursprungszustand zurückverfolgen.
Filtern von Daten
Beim Filtern von Daten, so schön das auch sein kann, wird ein Teil der Daten ausgeblendet. Es wird leider oft übersehen, dass es sich in diesem Zustand um gefiltert Daten handelt, und die Teilmenge wurde als Gesamtheit kopiert und an dritter Stelle wieder eingefügt. Das Filtern lässt sich wieder rückgängig machen, nicht aber wenn es mit dem …
Ausblenden von Zellen
verbunden wird. Ähnlich, wie beim Filtern, kann man ausgeblendete Zellen an der blauen Schriftfarbe in den Zeilen- und Spaltenköpfen erkennen, wird aber dennoch gerne übersehen. Der «Super-GAU» ist allerdings die Funktionalität.
Teilergebnis
im Menü Daten • Gliederung, die Zwischenergebniszeilen in eine Datenliste einfügt, die zuvor aber sortiert werden muss (nicht gemeint ist die Funktion TEILERGEBNIS() aus der Kategorie «Math.&Trigon.»). Diese Aufgabe erledigt man am besten mit der Pivot-Tabelle.
Fehler
Im Controlling mit Excel gibt beliebte Techniken, die schnell zu Fehlern führen können oder per se fehleranfällig sind. Wenn man auf diese Techniken verzichtet, verringert man potenziell Fehler:
Vermeidung von «harten» Eingaben in Formeln
Werte sollten nie direkt in Formeln eingetragen werden. Stattdessen sollten alle Eingabewerte in separaten Zellen erfasst werden, um die Transparenz und Nachvollziehbarkeit zu erhöhen.
Vermeidung von komplexen verschachtelten Formeln
Komplexe Formeln erhöhen die Fehleranfälligkeit und erschweren die Nachvollziehbarkeit. Einfachere, modular aufgebaute Berechnungen sind in der Regel besser überprüfbar. Kombiniert mit bestimmten Prinzipien sind solche Funktionen oft nicht notwendig.
Vermeidung von externen Verknüpfungen
Externe Verknüpfungen können Dateien instabil und langsamer machen, da sie von der Erreichbarkeit und dem Zustand der externen Quellen abhängen. Wenn die externen Quellen geändert oder gelöscht werden, können Fehler in den Verknüpfungen auftreten, was die Datenintegrität gefährdet. Externe Verknüpfungen erschweren die Nachverfolgung und Verwaltung der Datenquellen, insbesondere bei der Zusammenarbeit in Teams oder dem Weitergeben der Datei an andere Personen.
Vermeidung von Kopieren und Einfügen
Das Kopieren von Daten und Formeln führt oft zu Fehlern. Stattdessen sollten Formeln dynamisch aufgebaut werden, um manuelle Eingriffe zu minimieren.
Vermeidung von versteckten Zellen
Versteckte Zellen (Zellen weit ausserhalb des sichtbaren Bereichs auf einem Tabellenblatt) können dazu führen, dass wichtige Informationen übersehen werden. Ein klar strukturierter Aufbau (PRT-Modell) erhöht die Transparenz und erleichtert das Auditing.
Vermeidung von verbundenen Zellen
Verbundene Zellen erschweren das Sortieren und Filtern von Daten, da das bei verbundenen Zellen oft nicht korrekt funktioniert. Ausserdem können sie bei der Verwendung von Formeln und Bezügen zu unerwarteten Ergebnissen oder Fehlern führen, da Excel Schwierigkeiten hat, auf verbundene Bereiche zuzugreifen. Besonders Power Query kann verbundene Zellen nicht eindeutig erkennen.
Formelüberwachung
Es empfiehlt sich, regelmässig die verwendeten Formeln zu überprüfen und sicherzustellen, dass sie korrekt und aktuell sind.
Prüfung von Excel-Modellen auf potenzielle Fehleranfälligkeit
Zur Überprüfung von vorhandenen Excel-Modellen sollten systematisch Prüfungsschritte durchgeführt werden, um Fehler zu identifizieren und Risiken zu minimieren. Im ersten Schritt geht es darum, das Modell als Ganzes zu verstehen: Was ist das Ziel des Modells, und welche Techniken wurden verwendet, um Daten strukturiert darzustellen? Hierbei sollten die verwendeten Formeln und Funktionen sowie eventuelle Sicherheitseinstellungen hinterfragt werden.
Passende Produkt-Empfehlungen
Folgende Prüfungsschritte sollten im Controlling mit Excel durchgeführt werden:
Modellaufbau analysieren
Ist die Struktur sinnvoll, und wurden die richtigen Funktionen verwendet? Sind Makros dokumentiert und Zellen oder Tabellenblätter versteckt?
Datenquellen identifizieren
Welche Daten wurden manuell erfasst oder aus anderen Quellen importiert? Externe Verknüpfungen zu Excel-Dateien oder Datenbanken sollten ebenfalls geprüft werden. Gibt es ein Power-Query-Modell oder ein Datenmodell (Power Pivot), welche die Daten importieren?
Formeln und Funktionen überprüfen
Es ist wichtig, die Berechnungen auf Plausibilität zu prüfen. Enthalten die Formeln Konstanten, und liefern sie stets korrekte Ergebnisse?
Datenausgabe bewerten
Sind die Diagramme und Tabellen korrekt aufgebaut, und entsprechen sie dem Corporate Design? Wurde dabei die Informationspsychologie berücksichtigt?
Makros nachvollziehen
Selbst ohne VBA-Kenntnisse sollte der Code analysiert und auf Automatismen oder manuelle Eingriffe überprüft werden. Hierbei kann ChatGPT helfen. Risikopotenziale ermitteln und in einer Risk Map darstellen, um Ineffizienzen und Ressourcenbindung zu identifizieren. Durch diese strukturierten Prüfungen lassen sich Fehlerquellen minimieren, und das Risiko für die Organisation wird reduziert.
Techniken zur Vermeidung von Sicherheitsrisiken
Finanz- und Personaldaten sollten eigentlich niemals in Excel-Dateien gespeichert werden, die unverschlüsselt sind. Falls eine Verschlüsselung nicht möglich ist, sollten alternative Methoden in Betracht gezogen werden, denn der Passwortschutz für Excel-Dateien ist ein Hindernis, aber kein sicheres System.
Über den SharePoint oder File-Server können Berechtigungen vergeben werden:
- Über Microsoft Teams kann der Zugriff auf Excel-Dateien durch die Zuweisung von Rollen und Berechtigungen in Teams oder SharePoint gesteuert werden. Nur autorisierte Nutzer können die Dateien ansehen, bearbeiten oder freigeben.
- Teams speichert automatisch Versionen von Dateien, sodass bei Fehlern oder unerwünschten Änderungen frühere Versionen der Excel-Datei wiederhergestellt werden können.
- Auf Servern können Administratoren Benutzerrechte verwalten, um den Zugriff auf Excel-Dateien nur auf berechtigte Personen zu beschränken. Diese Rechte können Lese-, Schreib- oder Administratorrechte umfassen, wodurch unbefugte Änderungen vermieden werden.
- Server ermöglichen automatische und regelmässige Back-ups von Excel-Dateien, sodass im Falle von Datenverlust oder Dateibeschädigung eine Wiederherstellung von vorherigen Versionen der Datei möglich ist.
Durch den Einsatz von Firewalls, Verschlüsselung und Virenschutzprogrammen auf Servern kann der Schutz von Excel-Dateien vor externen Bedrohungen wie Hackerangriffen, Malware oder Datenlecks gewährleistet werden. Diese Massnahmen sorgen für Sicherheit und Datenintegrität und minimieren das Risiko von unbefugtem Zugriff oder Datenverlust.
Excel-Anwendungskonzept (Corporate Excel Policy)
Wie sollte so ein Anwendungskonzept, ein Leitfaden für Excel-Dateien aussehen? Im Prinzip Ausschluss von dem, was vorher angesprochen wurde. Das lässt sich in einem Leitfaden beschreiben, in einer Corporate Excel Policy, die auch das Anwendungskonzept definiert.
Wie könnte insgesamt ein Konzept aussehen? Auf jeden Fall sollten die zuvor beschriebenen Techniken und Risiken vermieden werden, die damit einhergehen.
Grundsätzlich die Verwendung eines Dateiaufbaus oder einer Modellierung, wie in Ausgabe September 2021 beschrieben, als Standard. Dieses Konzept sieht in einer Excel-Datei die Trennung der
- Eingabe/Import der notwendigen Daten, von
- der Verarbeitung und
- der Ausgabe auf verschiedenen Ebenen und damit Tabellenblättern vor.
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. Damit ist jedes Excel-Modell, beliebig erweiterungsfähig. Sollte Excel als Tool an seine Grenzen kommen, haben Sie mit diesem Modell bereits eine Art von Leistungsbeschreibung für die Beschaffung oder Entwicklung einer Software erstellt.
- Solch eine Standardmodellierung ermöglicht es allen beteiligten Mitarbeitenden, sich in kurzer Zeit im Modell zurechtzufinden.
- Sind die Modelle durch Aufbau und verwendete Excel-Techniken «standardisiert», ist eine grosse Anwendungssicherheit erzielt. Mit der permanenten Anwendung der gleichen Techniken erzielen Sie bei allen Beteiligten eine grosse Zeitersparnis bei der Erstellung neuer Modelle.
- Die Weiterbildung kann erheblich effizienter und zielgerichteter betrieben werden, da die für die Standardmodellierung notwendiget über Excel-Techniken zum Inhalt gemacht werden.
- Das Modell kann mit den Anforderungen wachsen und benötigt für die Steuerung in der Regel maximal zehn Funktionalitäten. Ausserdem ist es sehr klein (max. 2 MB, wenn Power Query eingesetzt wird!).
- Einmal erstellt, wird das Modell automatisch mit aktuellen Daten «gefüttert», berechnet und zeigt auf dem Frontend die aktuellen Ergebnisse. Nachbearbeitungen sind nicht mehr notwendig, Programmierung auch nicht.
Für solch eine Modellierung haben sich obige Excel-Techniken bewährt (siehe Tabelle 1).
Excel ist ein äusserst mächtiges Werkzeug im Controlling und Finanzwesen, birgt jedoch auch Risiken in Bezug auf Sicherheit und Fehleranfälligkeit. Durch die Implementierung geeigneter Sicherheitsmassnahmen, die Vermeidung von risikobehafteten-Techniken und die Nutzung von Automatisierungstools kann Excel jedoch effizient und sicher eingesetzt werden. Entscheidend ist, dass Controller ein Bewusstsein für die potenziellen Risiken entwickeln und proaktiv Techniken zur Fehlervermeidung und Compliance einführen. Nur so lässt sich Excel langfristig als zuverlässiges Werkzeug im Finanzwesen nutzen.
