Vermeintliche Fehleranfälligkeit von Excel-Modellen
Kompetenzbereiche zur Erstellung von Finanzmodellen
Grundsätzlich erfordert die Erstellung professioneller Modelle 1. betriebswirtschaftliches Grundlagenwissen, 2. praktisches Umsetzungs-Know-how in Excel und 3. Kenntnisse über Grundsätze und Standards zur ordnungsgemäßen Erstellung von Excel-basierten Finanzmodellen. Gerade der letzte Punkt ist vielen Anwendern weitgehend unbekannt, bietet aber ein enormes Potential.
Dabei handelt es sich nicht um Excel-Know-how i.e.S. oder spezielle finanzmathematische Funktionen, sondern um eine „Planungsphilosophie“, die Struktur, Format und Methodik beschreibt, um die zentralen Ziele Transparenz, Nachvollziehbarkeit, Qualität und Fehlerfreiheit eines Planungsmodells sicherzustellen.
Diese Grundsätze wurden von Experten mit jahrzehntelanger Erfahrung entwickelt und haben sich auch international als Standard etabliert (vgl. z.B. https://www.fast-standard.org). Allein der Umfang dieser frei herunterladbaren Empfehlungen zeigt, dass mit diesem Thema ein ganzer Tagesworkshop gefüllt werden kann.
An dieser Stelle wollen wir lediglich ausgewählte Grundsätze ansprechen, welche die Fehlerfreiheit bspw. von Finanzplanungen erhöhen. Dabei soll jeweils auch die praktische Umsetzung in Excel gezeigt werden. Die hier erläuterten Beispiele (und viele weitere) finden Sie auch in der kostenlosen Download-Datei am Ende dieses Beitrages.
Ziel 1: Hohe Transparenz und Nachvollziehbarkeit erzielen
Hier helfen gleich mehrere Prinzipien, um die Wahrscheinlichkeit von Fehlern zu verringern.
- Trennung von Annahmen, Berechnungen und Ergebnissen (jeweils auf eigenen Blättern).
- Jede Unterplanung ebenfalls auf einem separaten Tabellenblatt (Umsatz-, Kosten-, Personal-, Investitionsplanung etc.)
- Zeitachse auf jedem Blatt identisch und nur auf einem Blatt „Timing“ einmalig, flexibel ermitteln (Erster Planmonat bspw. immer in Spalte J, zweiter in Spalte K etc.)
- Einheiten (nicht nur) bei Eingabezellen klar kenntlich gemacht (Beschriftung in eigener Spalte)
- In jeder Zeile nur eine Formel, die durchkopiert werden kann. Damit verbunden die Empfehlung monatlich zu planen. Ergebnisse können später einfach mittels SUMMEWENN-Funktion zu Quartalen, Halbjahren etc. aggregiert werden.
- Nutzung von Zellformatvorlagen (wg. Transparenz und Zeitersparnis)
Die von vielen Nutzern sträflich vernachlässigten Zellformatvorlagen in Excel, sind ein mächtiges Werkzeug, das ihnen auch bei komplexen Formatierungen sehr viel Zeit und Arbeit ersparen und für einheitliche, professionell aussehende Finanzmodelle sorgen kann.
Die wesentlichen Vorteile bei Verwendung von Zellenformatvorlagen sind:
- Erheblicher Zeitgewinn, da auch komplexe Formatierungen mit einem „Klick“ erstellt werden können.
- Professionelles und stringentes Layout (hohe Transparenz).
- Schnelle und einfache (auch nachträgliche) Anpassungen für die gesamte Arbeitsmappe möglich.
Es wird empfohlen, sich im Rahmen der Erstellung von Excel-Modellen die durchgehende Benutzung von Zellformatvorlagen anzugewöhnen. D.h. nach jeder Eingabe sollte direkt die Zuweisung einer entsprechenden Zellenformatvorlage erfolgen.
Um eine entsprechende Vorlage anzuwenden markieren Sie eine Zelle oder einen Zellbereich den Sie formatieren wollen. Wählen Sie im Register „Start“ => Gruppe „Formatvorlagen“ => Zellenformatvorlagen eine aus (vgl. Abb. 1). Sobald Sie mit der Maus auf eine der Zellformatvorlagen klicken wird diese zugewiesen
Eigene Zellformatvorlagen erstellen können Sie, indem Sie eine beliebige Zelle vollständig nach ihren Vorstellungen formatieren und anschließend die Übersicht mit den Formatvorlagen ausklappen und dort auf „Neue Zellenformatvorlage …“ klicken. Geben Sie noch einen kurzen aber aussagefähigen Namen ein und haken anschließend noch die gewünschten Optionen an, die später bei der Zuweisung dieser Formatvorlage übertragen werden sollen. Die Download-Datei zu diesem Beitrag enthält ein vollständiges Zellformatvorlagen-Set (ca. 40 verschiedene), mit dem alle möglichen – auch englischsprachige – Finanzmodelle erstellt werden können.
Wichtig: Bedingte Formatierungen und Gültigkeiten (= Datenüberprüfung) lassen sich nicht in Zellformatvorlagen hinterlegen. Derartige Zellen können aber einfach kopiert werden (siehe weiter unten).
Ziel 2: Fehlerhafte Eingaben von Nutzern vermeiden
Um Eingaben von Anwendern zu verhindern, die nicht sinnvoll verarbeitet werden können, bietet die Datenüberprüfung zahlreiche Möglichkeiten.
Die häufigsten Anwendungsfälle dabei sind:
- Es sollen nur bestimmte Werte eingegeben werden dürfen (z.B. nur ganze Zahlen, nur positive Werte, Datumswerte in einem bestimmten Format, vorgegebene Optionen aus einer Liste, Ja/Nein, An/Aus)
- Werte sollen innerhalb eines bestimmten Bereiches liegen (z.B. Modellzeitraum, Prozentwerte zw. 0 und 100%, max. Laufzeiten, Vielfache eines Wertes)
Einrichten lässt sich eine Datenprüfung indem Sie eine Zelle auswählen, im Register „Daten“ => „Datenüberprüfung“ => „Datenüberprüfung“ auswählen. Im Reiter „Einstellungen“ können unter „Zulassen“ verschiedene Optionen gewählt werden (Ganze Zahl, Dezimal, Liste etc.)
| Ziel / Bemerkung | Datenüberprüfung: Eingabe im Feld Formel |
| In A1 eingegebenes Datum soll immer der erste Tag eines Monats sein (Abb. 2) | =TAG(A1)=1 |
| In A1 dürfen nur Vielfache von 100 eingegeben werden | =REST(A1;100)=0 |
| In A1 dürfen nur Vielfache von 100 und zwar nur zw. 500 und 1.000 eingegeben werden | =UND(REST(A1;100)=0;A1>=500;A1<=1000) |
| Alternative bei wenigen Werten | Liste wählen und bei Quelle eingeben: 500;600;700;800;900;1000 |
| Flexibelste Lösung: Zusätzlich Untergrenze in B1 und Obergrenze in C1 definieren | =UND(REST(A1;100)=0;B1>=500;C1<=1000) |
| Nutzer darf nur entweder Eingabezelle A1 oder B1 ausfüllen | In A1: =B1=““ In B1: =A1=““ |
Ziel 3: Versehentliches Löschen von Formeln verhindern
Grundsätzlich sollte in einer fertig aufgesetzten Finanzplanung für alle Blätter der Blattschutz aktiviert werden. Dies verhindert ein versehentliches Löschen von Formeln oder Texten. Die Inputzellen bleiben dabei weiterhin bearbeitbar (sofern in der zugehörigen Zellformatvorlage so definiert). Außerdem können alle Formeln (auch die der geschützten Zellen) weiterhin eingesehen und nachvollzogen (aber nicht verändert oder gelöscht) werden.
Darüber hinaus kann durch die Verwendung von Zeilensummen bspw. kontrolliert werden, ob bspw. horizontale und vertikale Summen identisch sind. Derartige Aggregationskontrollen sind vor allem wichtig für Übersichtsblätter, um sicherzustellen, dass die Summe aller Monatsdaten identisch mit der Summe bspw. aller Quartale oder Jahre ist. Wird dann nur eine einzige Formel bzw. Zelle in einer solchen aggregierten Übersicht (versehentlich) gelöscht, schlägt umgehend die entsprechende Kontrollzelle an.
Ziel 4: Betriebswirtschaftlich unsinnige Eingaben anzeigen
Hier sind Fehlerquellen gemeint, die sich nicht über Datenüberprüfungen abfangen lassen. Häufige Beispiele sind Tilgungen, die in Summe größer sind als das zugehörige Darlehen oder Abschreibungen, deren Gesamtbetrag die Anschaffungs- bzw. Herstellungskosten des Vermögensgegenstandes übersteigt.
Hier helfen bspw. entsprechende Bilanzkonten (vgl. Abb. 3), die, sobald eine negative Schlussbilanz vorliegt, einen Fehler ausgeben. Weitere klassische Kontrollzellen in einer Finanzplanung sollten sein: Bilanzidentität (Aktiva = Passiva) oder auch Mittelverwendung gleich Mittelherkunft.
Auffällige Werte bzw. Fehler lassen sich gut über bedingte Formatierungen in Excel visualisieren. In Kombination mit einem benutzerdefinierten Zahlenformat erreicht man ein neues Professionalitätslevel (vgl. Abb. 4).
Eine Kontrollzelle enthält eine „0“ wenn alles in Ordnung ist, eine „1“, wenn eine bestimmte Bedingung nicht erfüllt ist. Dies realisieren Sie später über beliebige WENN-Funktionen wie bspw.
Damit die Zelle optisch gut sichtbar wird, definieren Sie eine auffällige bedingte Formatierung für den Fall, dass der Zellwert ungleich „0“ ist (vgl. Abb. 5: Start => Bedingte Formatierung => Neue Regel => Nur Zellen formatieren, die enthalten)
Zu guter Letzt nutzen Sie noch ein benutzerdefiniertes Zahlenformat (vgl. Abb. 4), damit nicht 0 oder 1, sondern aussagekräftige Informationen angezeigt werden (z.B. „OK“ und „Fehler“ oder „An“ und „Aus“).
Dazu gehen Sie in das Menu „Zellen formatieren“ (STRG+1), wählen im Reiter „Zahlen“ unter Kategorie „Benutzerdefiniert“ und tragen bei „Typ“ z.B. ein. "Fehler";"Fehler";"OK" (in Anführungszeichen, da Text!). Ein Zahlenformat-Code kann aus bis zu vier Abschnitten bestehen, zwischen denen jeweils ein Semikolon steht. Die einzelnen Abschnitte definieren jeweils das Format für positive Zahlen, negative Zahlen, Nullwerte und Text.
Der große Vorteil bei Nutzung von benutzerdefinierten Zahlenformaten ist, dass Sie anschließend mit den Zellen/Werten weiter rechnen z.B. multiplizieren können (siehe Fehlerkontrollsystem). Dies ginge nicht ohne Weiteres, wenn dort Text wie „Fehler“ enthalten wäre.
Ziel 5: Effizientes Fehlerkontroll- und -meldesystem in Excel
Hat man zahlreiche Kontrollzellen in sein Finanzmodell eingebaut ist es wichtig, dass
- jegliche Fehlermeldung jederzeit direkt und unübersehbar für den Anwender sichtbar wird,
- sich einzelne Fehlermeldungen ggf. gezielt deaktivieren lassen bzw. mit einer Toleranzschwelle versehen werden können und,
- dass die eigentliche Fehlerursache auch in umfangreichen Modellen schnell und gezielt gefunden bzw. angesprungen werden kann.
Damit nicht alle Kontroll- und Prüfzellen innerhalb einer Finanzplanung einzeln geprüft werden müssen, ob diese möglicherweise eine Fehlermeldung ausweisen, macht es Sinn, sämtliche Kontrollzellen in einer einzigen Übersicht zusammen zu führen (vgl. Abb. 6 unterer Teil).
Damit jederzeit sichergestellt ist, dass man bei Änderungen im Modell eine ggf. resultierende Fehlermeldung direkt bemerkt, wird die Master-Fehlerzelle (= SUMME aller Einzelkontrollen darüber) einfach in die Kopfzeile eines jeden Tabellenblatts verlinkt. Sobald auch nur eine einzige der zahlreichen Kontrollen einen Fehler ausweist, wird dies auf jedem Blatt direkt oben in der Kopfzeile sichtbar.
Bei Bedarf kann für einzelne Kontrollzeilen ein Toleranzwert vorgegeben werden, so dass die Fehlermeldung nicht oder erst ab einem bestimmten Wert anschlägt (z.B. Kontokorrentüberziehung > best. Wert). Zur schnellen und einfachen Auffindbarkeit der Fehlerursache, kann die jeweilige Kontrollzelle über einen Hyperlink direkt angesprungen werden.
Fazit
Es lassen sich zahlreiche Fehlerquellen in Excel-basierten Finanzmodellen vermeiden, wenn man die hier genannten Planungsgrundsätze befolgt. Die vorgestellten Lösungen mittels individueller Datenüberprüfungen und bedingter Formatierungen kosten nur scheinbar im Rahmen der Modellentwicklung zunächst viel mehr Zeit. Greift man auf ein (einmal erstelltes) Set an Zellformatvorlagen zurück, geht die Erstellung sogar noch schneller als auf klassische Weise und darüber hinaus lässt sich viel Zeit sparen, die andernfalls zur Fehlersuche aufgewendet werden müsste. In welchem Umfang man die einzelnen Maßnahmen und Möglichkeiten anwendet hängt einerseits vom jeweiligen Finanzmodell ab, andererseits vom persönlichen Geschmack des Modellerstellers.
- Tipp: Video-Kurs „Erstellung einer integrierten Finanzplanung mit Excel“
In diesem Video-Training werden u.a. auch die Grundsätze für die ordnungsgemäße Erstellung von Excel-basierten Planungsmodellen (nach dem FAST-Standard) ausführlich dargestellt und erläutert. Auch die Integration einer modellübergreifenden „Integritäts- und Fehlerkontrolle“ wird Schritt-für-Schritt erklärt.
