Snelle scan op risico’s en blunders
Als er haast geboden is bij de oplevering of controle van een model heb je geen tijd om alle data en berekeningen handmatig na te lopen, maar je hebt wel behoefte aan enige houvast. Met PerfectXL controleer je snel op grote risico’s, zoals onderbroken formulereeksen, incomplete verwijzingen en incorrect gebruik van externe bronnen. Je kunt de scan uitbreiden en beperken op basis van het belang van het model.
- Download het voorbeeldbestand om alle stappen te volgen.
- Werk je al met PerfectXL? Ga dan direct naar de oplossing mèt PerfectXL.
Stappenplan zonder PerfectXL
Stap 1a: Identificeer de risico’s die je wilt nalopen
Als je twijfels hebt over de berekeningen in een model, of als je in korte tijd meerdere modellen moet beoordelen, dan is het fijn als je wat houvast hebt.Zonder PerfectXL is het enorm lastig om snel voldoende grip te krijgen op een Excel model om de kernrisico’s eruit te pikken. Heel concreet, zul je in ieder geval, voor zover aanwezig, handmatig willen controleren op:
- Approximate lookup
- Calculation Set to Manual
- Circular Reference
- Excel error
- Data Validation Rule not Followed
- Double Count
- Double Operator
- Excel Error
- Formula Interrupted by Data
- Formula Interrupted by Other Formula
- Iterative Calculation Enabled
- Longer Cell Reference Expected
- Numeric Text Calculation
- Reference to Empty Cell
- Unnecessary Space
Stap 1b: “Show all formulas”
Begin met de button “Show Formulas” onder “Formulas” in het Excel hoofdmenu.
Stap 1c: Zichtbare formules
Als je op deze button klikt tonen de cellen die normaalgesproken de uitkomst van een formule bevatten nu in plaats van een waarde de formule zelf.
Stap 2: Scan op onregelmatigheden
Zodra de formules zichtbaar zijn kun je met het blote oog scannen of je onregelmatigheden ziet en deze acties herhaal je voor ieder tabblad opnieuw.
Ons voorbeeldmodel heeft vijf tabbladen, waarin we de cellen met formules voor het gemak geel hebben gemarkeerd.
Op tabblad “Summary” zien we 9 formules. Vier daarvan zijn referenties naar cellen van andere tabbladen en de overige vijf zijn eenvoudige aftreksommen. We zien hier niets dat directe aandacht vereist.
Op het tweede tabblad “Income” zien we in cel C4 een referentie naar een ander Excel-bestand, vier simpele extracties in kolom D en drie optellingen in regel 9. Met het blote oog kunnen we zien dat er sprake is van een regelmatig formulebereik in zowel kolom D als in regel 9.
Ook op de volgende twee tabbladen zien we geen onregelmatigheden.
Op het laatste tabblad “Operating” zien we met het blote oog wel iets dat aandacht vereist. In kolom D lijkt sprake te zijn van een doorgetrokken formule, aangezien de aftrekking =C[ ]-B[ ] in alle cellen voorkomt, behalve in cel D8: daar staat een ‘0’. Het lijkt erop dat we de doorgetrokken formule hier hebben onderbroken door een vast getal (een hardcoded number) in te voeren. Dit is vast een fout!
Vervolgens valt er nog iets op aan kolom D. In cel D23 zien we een ander type onderbreking van de doorgetrokken formule. Waar alle andere cellen in de kolom bestaan uit het patroon “=C[ ]-B[ ]” bevat cel D23 de formule “=C23-B23+30”. Er is dus opnieuw sprake van een hardcoded number, maar dit keer is het als waarde aan de formule toegevoegd.
Stap 3: Evalueer en verbeter bevindingen
Je moet als gebruiker zelf beoordelen of dit getal ‘+30’ op die plek hoort of niet. In ons geval betreft het getal ‘+30’ onvoorziene kosten, maar het is erg onduidelijk om deze kosten op deze manier te presenteren. Het is beter om een nieuwe post voor de onvoorziene kosten toe te voegen en om de formule vervolgens verder door te trekken.
Nu er een regel aan de tabel is toegevoegd is het belangrijk om ook in de laatste regel “Total operating expenses” te controleren of het celbereik van de sommen nog correct is (dus of de nieuwe regel wordt meegenomen in de optelling). In ons voorbeeld is dit gelukkig goed gegaan.
Conclusie
Ons voorbeeld betreft slechts een klein en zeer overzichtelijk model, maar je kunt je voorstellen dat een dergelijk onderzoek in omvangrijkere modellen enorm tijdrovend is. Het duurt dagen om een Excel-bestand sheet voor sheet en cel voor cel door te nemen, en die tijd heb je vaak niet. Bovendien zijn er diverse problemen die je niet zo eenvoudig zichtbaar kunt maken, zoals de locatie van een kringverwijzing, de locatie van gebroken externe links, verborgen elementen of onbedoelde witruimte.
Stappenplan met PerfectXL
Stap 1: Open het model in PerfectXL Explore
Open het model in PerfectXL Explore en controleer daarin
- Of er verbindingen bestaan tussen sheets waarvan je overtuigd bent dat ze nodig zijn
- Of de juiste externe bronnen worden aangeroepen
- Of alle tabbladen in de visualisatie relevant zijn
In de visualisatie van ons beknopte voorbeeldmodel zien we dat de tabbladen Personnel en Operating naar tabblad Expenses verwijzen en dat zowel Expenses als Income naar Summary verwijzen.
The visualization of our concise exemplary model shows us that both the “Personnel” and “Operating” sheets refer to “Expenses” and that both “Expenses” and “Income” refer to “Summary.”
Bovendien zie we dat externe bron Invoices.xlsx wordt aangeroepen door tabblad Income. Dat ziet er allemaal prima uit.
Maar, we zien ook een tabblad dat we met de handmatige methode niet gevonden hebben: Content. De grijze kleur geeft aan dat het om een verborgen tabblad gaat en het feit dat er geen pijltjes van of naar dit tabblad wijzen betekent dat hoogst waarschijnlijk geen relevante informatie voor ons model bevat. Daarom is het nuttig om het tabblad te inspecteren.
Door op het externe link icoontje in de rechterbovenhoek te klikken kan Explore ons direct naar de juiste locatie brengen, maar er verschijnt eerst een pop-up met de vraag “Unhide worksheet ‘Content’?”
Zodra je op OK hebt geklikt brengt de link je direct naar de relevante locatie in je model.
Het verborgen tabblad blijkt een inhoudsopgave van het model te zijn en kan dus prima blijven staan, zolang het openbaar is (dus verberg de worksheet niet opnieuw).
Stap 2: Open het model in PerfectXL Risk Finder
Open het model in PerfectXL Risk Finder. Filter linksonder op Risk level: “Risk” en loop de risico’s een voor een na.
De meeste risico’s die je treft kunnen rechtstreeks negatieve invloed hebben op de uitkomst van het model. Je weet het echter nooit zeker, soms is iets wel een risico maar niet direct fout.
Waarschijnlijk wil je nu controleren op direct potentiële fouten. Dan zijn bijvoorbeeld “Excel errors”, Longer Cell Reference” en “Formula interrupted by data” zeer relevant.
Zoals je ziet is er in ons voorbeeldmodel maar één “Formula interrupted by data” gevonden, in cel D8 op tabblad Operating. Als we meer over dit geval willen weten klikken we simpelweg op de gevonden ‘case’ om meer details te zien:
In het scherm verschijnt nu een fragment van het relevante worksheet met daarin cel D8 rood gemarkeerd. Onder het fragment zien we de beschrijving van het probleem: “The formula range D4:D22 is interrupted by a value at D8”.
Dankzij het visuele fragment en de beschrijving is het eenvoudig om te bepalen dat hier een belangrijk risico schuilt; de formule moet doorgetrokken worden!
Als je nu op het fragment klikt dan spring je direct vanuit PerfectXL Risk Finder naar de relevante locatie in je spreadsheet: cel D8 in tabblad Operating, zodat je het ter plekke kunt aanpassen.
Zaken als “Hard Coded Number in Formula” of “Many operations” zijn ook belangrijk, maar als je echt weinig tijd hebt, kun je die zelf negeren. Ze zijn bepalend voor de kwaliteit van een model, maar niet voor de concrete uitkomst van een concrete formule.
In de PerfectXL Risk Finder vind je de toelichtingen hoe met ieder gevonden risico om te gaan. Ook is er een handleiding beschikbaar (link) naar een uitgebreide beschrijving van ieder element.