Feste (hardcoded) Zahlen in Formeln sollten vermieden werden, da oft unklar ist, was sie darstellen. Wenn sich Variablen im Modell ändern, werden festgelegte Zahlen außerdem häufig übersehen. Ein klassisches Beispiel dafür sind Mehrwertsteuerprozentsätze. PerfectXL hilft Ihnen, risikobehaftete feste Zahlen in Ihrem Modell zu identifizieren, indem gängige Konstanten wie 10, 100 und 1000 absichtlich ausgeschlossen werden.
- Lade die Beispieldatei herunter, um die Schritte zu verfolgen
- Arbeiten Sie bereits mit PerfectXL? Dann springen Sie direkt zur Lösung mit PerfectXL.
Schritt-für-Schritt ohne PerfectXL
Ihr Modell enthält keine festen Zahlen… oder doch?
Es reicht nicht, beim Erstellen des Modells auf feste Zahlen in Formeln zu verzichten. Was, wenn Ihr Modell bereits solche Zahlen enthält, da Sie ein altes überarbeiten? Daher ist es entscheidend, sich über alle festen Zahlen im Modell im Klaren zu sein. Doch wie können Sie sicherstellen, dass Sie alle gefunden haben? Ein Excel-Modell enthält viele Formeln, und Fehler passieren leicht. Mit anderen Worten: Diese Aufgabe manuell zu erledigen ist nicht ideal, aber wir versuchen es dennoch.
Schritte 1: Formeln anzeigen
Wir beginnen damit, alle Formeln in unserer Beispieldatei sichtbar zu machen. Wählen Sie dazu die Option „Show Formulas“ unter „Formulas“ in der Hauptnavigationsleiste von Excel.
Schritt 2: Jede Arbeitsmappe überprüfen
Auf der ersten Registerkarte („Summary“) sehen wir verschiedene Verweise auf andere Registerkarten und einige Subtraktionen in Spalte D und in Zeile 6, jedoch keine fest codierten Zahlen.
Auf der zweiten Registerkarte („Income“) sehen wir hauptsächlich direkt eingegebene Werte, einige Subtraktionen in Spalte D und einige Additionen in Zeile 8. Auch hier keine fest codierten Zahlen.
Auf der dritten Registerkarte sehen wir wieder einige Verweise auf andere Registerkarten, einige Subtraktionen und einige Additionen. Bisher sind immer noch keine fest codierten Zahlen zu finden.
Auf der vierten Registerkarte („Personnel“) sehen wir dasselbe Bild wie auf der zweiten Registerkarte: einige direkt eingegebene Werte, einige Subtraktionen und einige Additionen. Wird es langsam langweilig?
Nun, endlich ist die Situation auf der fünften (und letzten) Registerkarte („Operating“) anders. Hier sehen wir eine Formel in Spalte C mit der fest codierten Zahl 0,18 und in Zelle E23 eine Addition von +30.
Schritt 3: Feste Zahl isolieren und einen Namen definieren
Zunächst kümmern wir uns um die Zahl 0,18 in Spalte C. Der Spaltenname deutet darauf hin, dass es sich bei 0,18 um einen Mehrwertsteuersatz handelt. Falls sich dieser Prozentsatz jemals ändert, könnte dies leicht übersehen werden, da die Formel normalerweise verborgen ist. Tatsächlich müsste man den Prozentsatz in jeder Zelle der Spalte anpassen.
Um das zu lösen, erstellen wir eine neue Registerkarte: „Variables“, auf der wir den Mehrwertsteuersatz mit einer klaren Bezeichnung notieren.
Dann definieren wir einen Namen für den Prozentsatz über Formulas -> Define Name. Wir nennen den Prozentsatz „VAT_PERC“.
Schritt 4: Den definierten Namen anwenden
Gehen Sie zurück zur fünften Registerkarte („Operating“), auf der der fest codierte Mehrwertsteuersatz gefunden wurde. Ersetzen Sie die fest codierte Zahl (0,18) in der Formel durch den Namen „VAT_PERC“.
Diese Methode hat einen entscheidenden Vorteil: Wenn sich der Mehrwertsteuersatz ändert, zum Beispiel von 18 % auf 21 %, müssen wir ihn nur an einer Stelle (auf der Registerkarte „Variables“) anpassen. An allen Stellen, an denen der Name „VAT_PERC“ verwendet wird, wird ab diesem Moment mit 21 % gerechnet, wodurch das Risiko von Flüchtigkeitsfehlern erheblich verringert wird. Am Ergebnis der Formel ändert sich nichts, aber die Formel ist nun wesentlich transparenter und leichter zu aktualisieren.
Schritt 5: Feste Zahl in eine passendere Spalte verschieben
Als Nächstes gibt es die Zahl +30 in Zelle E23. „30“ ist eine fest codierte Zahl, deren Ursprung unklar ist. Beim Interpretieren der Tabelle sehen wir, dass 30 zur Differenz zwischen „Estimated“ und „Actual“ addiert wird, was zu zusätzlichen Kosten von 30 führt. Diese zusätzlichen Kosten sind derzeit als unerwartete Addition innerhalb eines Formelfeldes „versteckt“. Es ist viel transparenter, diese Kosten in Spalte D („Actual“) in einer neuen Zeile „Other“ zu dokumentieren und die Formel in Spalte E zu erweitern, um Konsistenz in der gesamten Tabelle zu gewährleisten.
Schritt 6: Anpassung überprüfen
Mit dieser Anpassung sehen wir, dass die Gesamtdifferenz (in Zelle E25) unverändert bleibt, aber die Summe in Spalte D („Actual“) im Vergleich zur Ausgangssituation um 30 gestiegen ist.
Dies wirkt sich auf die Gesamtsumme in Zelle C6 der dritten Registerkarte („Expenses“) aus:
Somit ändern sich auch die Ergebnisse auf unserem Dashboard in der ersten Registerkarte („Summary“).
Zusammenfassung
Wir haben das Risiko des festen Mehrwertsteuersatzes durch Ersetzen der festen Zahl mit einem Namen verringert und einen direkten Fehler im Ergebnis des Modells entdeckt und behoben (weil sich die Differenz zwischen tatsächlichen und geschätzten Kosten von 480 auf 510 erhöht hat).
Fazit
Unsere Beispielarbeitsmappe ist nun wesentlich zuverlässiger, aber der Prozess hat viel Zeit in Anspruch genommen. Bei einem größeren Modell ist dieser Ansatz extrem zeitaufwändig und unpraktisch.
Schritt-für-Schritt mit PerfectXL
Schritt 1: Überprüfen Sie auf fest codierte Zahlen mit dem PerfectXL Risk Finder
Das Auffinden fest codierter Zahlen in Formeln ist mit dem PerfectXL Risk Finder unglaublich einfach. Öffnen Sie die Datei, die Sie analysieren möchten, in Excel und navigieren Sie zum PerfectXL-Menüband. Klicken Sie auf „Detect Problems“ und wählen Sie „Hardcoded Numbers“ aus.
StaSchritt 2: Analysieren Sie die Datei im PerfectXL Risk Finder
Der PerfectXL Risk Finder analysiert die Datei und führt Sie direkt zur Übersicht „Hardcoded Number in Formula“. Dort erhalten Sie eine Liste aller Fälle bzw. Positionen, an denen fest codierte Zahlen gefunden wurden.
Wählen wir den ersten Fall aus, um loszulegen.
Schritt 3: Details zur fest codierten Zahl
Nach dem Klicken auf den Fall erhalten wir mehrere Details zur spezifischen fest codierten Zahl, wie etwa einen Screenshot unseres tatsächlichen Excel-Modells. Unter dem Screenshot finden wir die Formel, die die fest codierte Zahl enthält. In diesem Fall sehen wir, dass die Formel in Operating!C4 die fest codierte Zahl 0,18 enthält. Der Screenshot zeigt, dass sich über dem Problem in der Spaltenüberschrift „VAT“ befindet, was es naheliegend macht, dass die 0,18 den Mehrwertsteuersatz darstellt.
Klicken wir auf den Screenshot, um uns das tatsächliche Modell in Excel anzusehen.
Schritt 4: Bewerten und beheben Sie das Problem
Hier sehen wir, dass die Mehrwertsteuer-Spalte der Tabelle durch diese erweiterte Formel gefüllt wird. Glücklicherweise ist die Spalte beschriftet, und die Zahl wird konsequent in allen relevanten Zellen verwendet. Es ist jedoch viel besser, diese Zahl an einem anderen Ort zu speichern, etwa als definierten Namen. Sie können sie „VAT“ oder „MWST“ nennen, sodass bei einer zukünftigen Änderung des Mehrwertsteuersatzes alle Verweise auf den Prozentsatz in einem Schritt aktualisiert werden können (siehe Schritt 3 der manuellen Methode).
Kehren Sie nun zum PerfectXL Risk Finder zurück, um die anderen fest codierten Zahlen in der Liste zu beheben.
Fazit
Verwenden Sie die Pfeiltasten oder den Pfeil rechts („< 1/2 >“), um schnell zur nächsten Instanz von „Hardcoded Number in Formula“ zu navigieren. Wiederholen Sie dies für alle gefundenen Instanzen in der Datei, und Ihr Modell wird im Handumdrehen verbessert!