Vaste getallen in formules opsporen

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.