andreworia/formula-audit-checker

Formel-Audit-Prüfung

Wann verwenden

Nutzen Sie diesen Skill, wenn Sie ein Excel-Modell überprüfen, das von einer anderen Person erstellt wurde, oder wenn Sie Ihr eigenes Modell vor der Weitergabe an einen Kunden oder eine leitende Führungskraft verifizieren möchten. Starten Sie ihn, wenn ein Modell zur Qualitätsprüfung markiert wurde, wenn eine Versionenübergabe stattfindet, oder bevor ein Modell für eine bedeutende finanzielle Entscheidung verwendet wird. Er ist am wertvollsten als Qualitätskontrolle vor der Einreichung: Führen Sie dieses Audit durch, bevor ein Modell einem Gremium, einem Investitionsausschuss oder einem Kunden präsentiert wird.

Was es tut

Bietet einen systematischen, schrittweisen Modell-Auditprozess, der folgende Bereiche abdeckt: die nativen Formel-Audit-Tools von Excel (Trace Precedents, Trace Dependents, Error Checking), Erkennung und Korrektur hartcodierter Werte in Formeln, Identifizierung von Zirkelbezügen, konsistente IFERROR-Umhüllung und Dokumentation der Auditergebnisse in einem Prüfprotokoll. Der Output ist ein abgeschlossenes Prüfprotokoll und eine Liste priorisierter zu behebender Probleme.

Methode

1. Arbeitskopie vor dem Audit speichern. Bevor Sie Änderungen vornehmen, speichern Sie das Modell als neue Datei mit dem Zusatz "_Audit_JJJJ-MM-TT" im Namen. Alle Bearbeitungen während des Audits sollten an dieser Kopie vorgenommen werden. Dies bewahrt den Originalzustand für Vergleiche.

2. Das Fehlerüberprüfungs-Tool ausführen. Gehen Sie zu Formeln > Fehlerüberprüfung. Excel durchläuft alle Zellen, die Fehler enthalten (#NV, #BEZUG!, #DIV/0!, #WERT!, #NAME?) und zeigt den Fehler und die Formel an. Für jeden Fehler: Diagnose der Ursache (fehlender Suchschlüssel, gelöschte Quellzelle, falscher Datentyp, fehlender definierter Name), Behebung und Dokumentation im Prüfprotokoll mit: Zellbezug, Fehlertyp, Ursache, angewandte Korrektur, Schweregrad (Kritisch, Schwerwiegend, Geringfügig).

3. Zirkelbezüge finden und beheben. Gehen Sie zu Formeln > Fehlerüberprüfung > Zirkelbezüge. Excel listet alle Zellen mit zirkulären Abhängigkeiten auf. Zirkelbezüge sind in einem Finanzmodell fast immer ein Fehler (außer bei iterativen Berechnungsszenarien, die selten und ausdrücklich beabsichtigt sein müssen). Für jeden Zirkelbezug: Verfolgen Sie die Abhängigkeitskette, identifizieren Sie die Schleife und brechen Sie sie durch Umstrukturierung der Formellogik. Dokumentieren Sie jeden gefundenen Zirkelbezug.

4. Verwenden Sie Trace Precedents zur Überprüfung der Formellogik. Klicken Sie für jede wichtige Berechnungszeile (Umsatz, EBITDA, FCF, Eigenkapitalwert, IRR) auf die Zelle und drücken Sie Strg+[ oder gehen Sie zu Formeln > Trace Precedents. Dies zeichnet Pfeile, die anzeigen, welche Zellen diese Formel speisen. Überprüfen Sie: (a) die Formel bezieht sich auf die richtigen Registerkarten (Registerkarte Eingaben für Annahmen, Registerkarte Daten für Verweise, richtige Registerkarte Berechnung für Zwischenergebnisse), (b) es werden keine unerwarteten Zellen referenziert, (c) die Formel referenziert keine Zellen, die sich auf einer anderen Registerkarte befinden sollten.

5. Verwenden Sie Trace Dependents, um zu überprüfen, was eine Zelle speist. Verwenden Sie für wichtige Ausgabezellen auf Berechnungs-Registerkarten Formeln > Trace Dependents, um zu sehen, welche nachgelagerten Zellen diese Ausgabe referenzieren. Dies bestätigt: (a) dass Ausgabezellen korrekt von der Registerkarte Ausgaben übernommen werden, (b) dass keine unerwarteten nachgelagerten Formeln eine Zelle referenzieren, die ein lokaler Zwischenwert sein sollte.

6. Alle hartcodierten Zahlen in Formelzellen finden. Dies ist der wichtigste Audit-Schritt für die Modellintegrität. Alle hartcodierten Zahlen (außer auf der Registerkarte Eingaben) sind Modellfehler, da sie ohne Öffnen jeder Formel nicht einfach geändert werden können.

   Schritt: Drücken Sie Strg+H (Suchen und Ersetzen). Klicken Sie auf Optionen. Setzen Sie "Suchen in" auf Formeln. Suchen Sie nach bestimmten Zahlen zur Überprüfung (versuchen Sie "0,2", "0,3", "0,15", "1000", "12"). Jede Formel, die diese Zahlen außerhalb der Registerkarte Eingaben enthält, sollte untersucht werden.

   Besserer Ansatz: Verwenden Sie die Gehe-zu-Spezial-Funktion von Excel (Strg+G > Spezial > Konstanten > Zahlen). Dies wählt alle Zellen aus, die hartcodierte Zahlen enthalten. Überprüfen: Jede solche Zelle auf einer Berechnungs-Registerkarte, die nicht auf der Registerkarte Eingaben liegt, sollte markiert werden.

   Für jede hartcodierte Zahl, die in einer Berechnungsformel gefunden wurde: Verschieben Sie die Annahme auf die Registerkarte Eingaben, geben Sie ihr eine Bezeichnung, wenden Sie eine blaue Füllung an und ersetzen Sie den Hartcode durch einen Zellbezug auf die Registerkarte Eingaben.

7. Audit auf IFERROR-Konsistenz. Überprüfen Sie alle Suchformeln (SVERWEIS, INDEX-VERGLEICH, XVERWEIS, VERGLEICH), um sicherzustellen, dass sie in IFERROR eingewickelt sind. Eine nicht umwickelte Suchformel, die #NV zurückgibt, kann sich stillschweigend durch nachgelagerte Formeln verbreiten und falsche Ergebnisse produzieren.

   So finden Sie ungeschützte Suchformeln: Strg+F > suchen Sie nach "=SVERWEIS", "=INDEX(", "=XVERWEIS" ohne das Präfix "IFERROR(". Für jede ungeschützte Suchformel: Wickeln Sie sie in IFERROR(Formel, "Prüfung: [Beschreibung] nicht gefunden") oder IFERROR(Formel, 0) ein, je nach Kontext. Dokumentieren Sie jede Suchformel, die umwickelt wurde.

8. Integrität definierter Namen prüfen. Gehen Sie zu Formeln > Namensmanager. Für jeden definierten Namen: Überprüfen Sie, dass er auf die richtige Zelle oder den richtigen Bereich verweist (kein #BEZUG! in der Spalte "Bezieht sich auf"), überprüfen Sie, dass der Name noch im Modell verwendet wird (suchen Sie mit Strg+F nach dem Namen in Formeln), und löschen Sie alle verwaisten definierten Namen (Namen, die keinen gültigen Bezug mehr haben oder nicht mehr verwendet werden).

9. Vorzeichenkonvention überprüfen. Ein häufiger Modellfehler ist eine inkonsistente Vorzeichenkonvention – einige Posten positiv, einige negativ, ohne festgelegte Regel. Audit: Alle GuV-Posten (Umsatz, Bruttogewinn, EBITDA) sollten positive Zahlen sein. Kosten können positiv (mit Subtraktion in der Formel) oder negativ (mit Addition) sein. Wählen Sie eine Konvention und wenden Sie sie konsequent an. Markieren Sie jede Zeile, in der das Vorzeichen inkonsistent mit den umgebenden Zeilen erscheint.

10. Sensitivitätstabellen auf korrekte Eingabezellbezüge prüfen. In jeder Excel-Datentabelle müssen die Zeileneingabezelle und die Spalteneingabezelle auf die genauen Zellen auf der Registerkarte Eingaben verweisen, die variiert werden. Ein häufiger Fehler: Die Eingabezellen verweisen auf eine andere Zelle als die, die tatsächlich in den Modellberechnungen verwendet wird. Zur Überprüfung: Ändern Sie manuell den Wert der Zeileneingabezelle und prüfen Sie, ob sich die Modellausgabe wie erwartet ändert. Führen Sie dann die Datentabelle aus und überprüfen Sie, ob sich die Ergebnisse entsprechend ändern.

11. Formelkomplexität prüfen. Lange verschachtelte WENN-Formeln (mehr als 2 Ebenen) sind ein Audit-Risiko, da sie schwer zu lesen, schwer zu debuggen und anfällig für Logikfehler sind. Markieren Sie jede Formel, die länger als 150 Zeichen ist oder mehr als 2 Verschachtelungsebenen enthält. Für jede markierte Formel: Brechen Sie sie in Zwischenberechnungszeilen auf, benennen Sie jeden Zwischenschritt klar und ersetzen Sie die verschachtelte Formel durch eine sauberere Schritt-für-Schritt-Sequenz.

12. Das Prüfprotokoll vervollständigen. Dokumentieren Sie alle Ergebnisse in einer strukturierten Prüfprotokolltabelle auf der Registerkarte Dokumentation oder in einer separaten Registerkarte Prüfprotokoll:

    Ergebnis-ID | Registerkarte | Zelle | Problemtyp | Schweregrad | Beschreibung | Status (Offen/Behoben) | Behoben von | Datum der Behebung.

    Problemtypen: Hartcodierter Wert, Zirkelbezug, Formelfehler, IFERROR fehlt, Vorzeicheninkonsistenz, Definierten Namen defekt, Fehler in Sensitivitätstabelle, Formelkomplexität.

    Schweregrad: Kritisch (wird im aktuellen Zustand falsche Ergebnisse produzieren), Schwerwiegend (wird unter bestimmten Bedingungen falsche Ergebnisse produzieren), Geringfügig (beeinflusst die Ergebnisse nicht, verstößt aber gegen Konventionen).

    Fassen Sie am Anfang des Protokolls zusammen: Gesamtergebnisse, Anzahl kritischer, Anzahl schwerwiegender, Anzahl geringfügiger und Gesamtauditstatus (Bestanden bei null Kritischen, Bedingt bestanden bei nur Geringfügigen, Nicht bestanden bei ungelösten Kritischen oder Schwerwiegenden).

Eingaben

• Das zu prüfende Excel-Modell (Registerkartennamen, Beschreibung des Zwecks und wo sich die wichtigsten Ausgaben befinden)
• Alle bekannten Probleme oder Bedenken, die vom Modellersteller gemeldet wurden
• Die Zielgruppe des Modells (bestimmt, wie gründlich das Audit sein muss)
• Die Frist für den Abschluss des Audits

Ausgabeformat

Ein vollständiges Audit-Spezifikation mit:

1. Prüfliste: Alle 12 Audit-Schritte als überprüfbare Checkliste mit dem Excel-Navigationspfad für jeden.
2. Anleitung zur Erkennung von Hartcodes: Schritt-für-Schritt-Ansatz mit Strg+H und Gehe zu Spezial.
3. IFERROR-Prüfanleitung: Die Suchzeichenfolge zum Auffinden ungeschützter Suchformeln.