Vaste getallen in formules opsporen
Vaste (of hardcoded) getallen in formules wil je te allen tijde voorkomen, omdat het zelden duidelijk is waar ze voor staan. Als ze moeten worden aangepast, zie je er makkelijk een aantal over het hoofd. BTW-percentages zijn een berucht voorbeeld. PerfectXL helpt je om alle risicovolle vaste getallen in je model terug te vinden, maar laat enkele veelvoorkomende constanten (zoals 10, 100 en 1000) bewust buiten beschouwing.
- Download het voorbeeldbestand om de stappen te volgen.
- Werk je al met PerfectXL? Ga dan direct naar de oplossing met PerfectXL.
Stappenplan zonder PerfectXL
De formules in jouw model bevatten geen vaste getallen…of toch wel?
Vaste getallen vermijden tijdens het bouwen is vaak niet genoeg. Wat als je model al vaste getallen bevat omdat je doorbouwt op een oud model? Daarom is het opsporen van vaste getallen belangrijk; maar hoe kun je er zeker van zijn dat je ze allemaal hebt gevonden? Een Excelmodel bevat immers veel formules en fouten liggen altijd op de loer. Kortom, deze taak handmarig uitvoeren is niet ideaal, maar laten we het toch proberen.
Stap 1: “Show Formulas”
We beginnen met het zichtbaar maken van alle formules in ons voorbeeldbestand. Selecteer hiervoor de optie “Show Formulas” onder “Formulas” in de hoofdnavigatiebalk van Excel.
Stap 2: Controleer alle werkbladen
Op het eerste tabblad (“Summary”) zien we verschillende verwijzingen naar andere tabbladen en enkele aftrekkingen in kolom D en op regel 6, maar er zijn geen vaste getallen zichtbaar.
Op het tweede tabblad (“Income”) zien we voornamelijk direct ingevoerde waarden, enkele aftrekkingen in kolom D en enkele optellingen op regel 8. Maar opnieuw merken we geen vaste getallen in formules op.
Op het derde tabblad zien we opnieuw enkele verwijzingen naar andere tabbladen, enkele aftrekkingen en enkele optellingen. Maar tot nu toe nog steeds geen vaste getallen in formules gevonden.
Op het vierde tabblad (“Personnel”) zien we hetzelfde beeld als op het tweede tabblad: enkele rechtstreeks ingevoerde waarden, enkele aftrekkingen en enkele optellingen. Begin je je al te vervelen?
De situatie op het vijfde (en laatste) tabblad (“Operating”) is anders: hier is wel sprake van vaste getallen in formules. In kolom C zien we een formule met het hardcoded nummer 0,18 en in cel E23 zien we een toevoeging van +30.
Stap 3: Isoleer het vaste getal en definieer een naam
Laten we eerst het nummer 0,18 in kolom C aanpakken. De naam van de kolom suggereert dat 0,18 een BTW-percentage is. Als dit percentage ooit verandert, loop je het risico dat je het snel over het hoofd ziet omdat de formule normaal gesproken verborgen is. En in dat geval zou je het voor elke cel in de kolom aan moeten passen.
Om dit op te lossen, maken we een nieuw tabblad: “Variables” waar we het BTW-percentage noteren met een duidelijk label.
Daarna definieren we een naam voor deze variabele, via Formulas -> Define Name. We noemen het BTW-percentage “VAT_PERC.”
Stap 4: Pas de gedefinieerde naam toe
Ga terug naar het vijfde tabblad (“Operating”), waar we het vaste getal vonden. Vervang het getal (0,18) in de formule door de naam “VAT_PERC”.
Deze methode heeft een groot voordeel. Als het BTW-percentage verandert, bijvoorbeeld van 18% naar 21%, dan hoeven we het slechts op één plaats aan te passen (op het tabblad “Variables”). Op alle plaatsen waar de naam “VAT_PERC” wordt gebruikt, wordt voortaan gerekend met 21%, waardoor de kans op slordige fouten aanzienlijk wordt verkleind. De uitkomst van de formule verandert niet, maar het is nu veel transparanter en veel eenvoudiger bij te werken.
Stap 5: Verplaats het vaste getal naar een kolom die beter geschikt is
Vervolgens moeten we nog iets met het getal +30 in cel E23. ’30’ is een vast getal waarvan de oorsprong onduidelijk is. Bij het interpreteren van de tabel zien we dat 30 wordt toegevoegd aan het verschil tussen Estimated en Actual, wat resulteert in een verhoging van de kosten met 30. Deze extra kosten zijn momenteel ‘verborgen’ als een onverwachte toevoeging binnen een formulebereik. Het is veel transparanter om deze kosten te registreren in kolom D (“Actual”) op een nieuwe ‘Overige’ regel en om de formule verder door te trekken vanaf kolom E, zodat de consistentie van de tabel gewaarborgd wordt.
Stap 6: Controleer de aanpassing
Met deze aanpassing zien we dat het totale verschil (in cel E25) onveranderd blijft, maar dat het totaal in kolom D (“Actual”) met 30 is toegenomen ten opzichte van de oorspronkelijke situatie.
Dit heeft ook invloed op het totaal in cel 6 van het derde tabblad (“Expenses”):
En daarom veranderen de resultaten op het “Summary” dashboard (het eerste tabblad) ook:
Samengevat
We hebben het risico van het hardgecodeerde BTW-percentage verlaagd door het vaste getal te vervangen door een naam, en we hebben een directe fout in het resultaat van het model ontdekt en gecorrigeerd (omdat het verschil tussen de Estimated en Actual is verhoogd van 480 naar 510).
Conclusie
Ons voorbeeldwerkblad is nu een stuk betrouwbaarder geworden, maar het proces heeft veel tijd in beslag genomen. Je kunt je voorstellen dat deze aanpak onpraktisch en extreem tijdrovend is in het geval van een groter bestand.
Stappenplan met PerfectXL
Stap 1: Op vaste getallen controleren met PerfectXL Risk Finder
Het traceren van vaste getallenin formules is ongelooflijk eenvoudig met de PerfectXL Risk Finder. Open het bestand dat je wilt analyseren in Excel en navigeer naar de PerfectXL-ribbon via het hoofdmenu . Klik op “Detect Problems” en selecteer “Hardcoded Numbers”.
Stap 2: Analyseer het bestand
PerfectXL Risk Finder analyseert het bestand en brengt je direct naar het overzicht “Hardcoded Number in Formula.” Je krijgt een lijst te zien van alle locaties waar vaste getallen gevonden zijn.
Laten we beginnen met het eerste geval.
Stap 3: Details van het vaste getal
Er worden nu verschillende details gepresenteerd van het vaste getal waarop we geklikt hebben, zoals een screenshot van de locatie in het echte Excel-model en, daaronder, de formule die het nummer bevat. In dit geval kunnen we zien dat de formule in Operating!C4 een hardgecodeerde “0,18” bevat. Op het screenshot kunnen we zien dat het de kolom “VAT” betreft; het is dus logisch om aan te nemen dat 0,18 in dit geval een BTW-percentage betreft.
We klikken op het screenshot om naar het werkelijke model in Excel te springen.
Stap 4: Beoordeling en oplossing
Hier zien we dat de kolom “VAT” wordt gevuld met een doorgetrokken formule. Gelukkig is de kolom gelabeld en wordt het nummer consequent in alle cellen gebruikt. Het is echter veel beter om dit nummer ergens anders op te slaan, zoals in een gedefinieerde naam. Je kunt het bijvoorbeeld “VAT” noemen en als de BTW in de toekomst verandert, kunnen alle verwijzingen naar het percentage in één keer worden bijgewerkt (zie stap 3 van handmatige methode).
Keer terug naar de PerfectXL Risk Finder om de andere vaste getallen in de lijst na te lopen.
Conclusie
Gebruik de pijltjestoetsen of de pijl naar rechts (< 1/2 >) om snel naar het volgende vaste getal in het overzicht van ‘Hardcoded Number in Formula’ te navigeren. Herhaal dit proces voor alle vaste getallen in het bestand en je model zal in een mum van tijd verbeterd zijn!