ADMINISTRATIE
Cijferanalyse met behulp van Benford’s Law (2)
H ET
LIJKT
INGEWIKKELDER DAN HET IS In het voorgaande artikel werd aangegeven hoe de vaste verdeling van cijfers in getallen, zoals deze voortvloeit uit Benford’s Law, kan worden benut voor de uitvoering van cijferanalyses. In dit noodzakelijkerwijs wat ‘technischer’ vervolg wordt uiteengezet hoe met eenvoudige hulpmiddelen een test op de eerste twee cijfers kan worden uitgevoerd. DOOR
MR.DRS.
MARC
DE
GUNST, SBV, FORENSIC BUSINESS EXPERTS & INVESTIGATORS
Voor een incidentele uitwerking van een cijferanalyse voldoet een spreadsheet-programma.
39
TIJDSCHRIFT
ADMINISTRATIE oktober 2002 - nr. 10
De verdeling van cijfers in een getallenverzameling is volgens Benford’s Law een vaste logaritmische verdeling.Voor de eerste k significante cijfers van getallen kan deze verdeling met de volgende formule worden weergegeven:
Deze formule lijkt ingewikkelder dan die is. Eenvoudig gezegd: de kans op een getal met de begincijfers 106 in een getallenverzameling die voldoet aan Benford’s Law is log10 (1+(1/106)) ≈ 0,0040779. In beginsel is dat alles wat we moeten weten voor het analyseren van een bestand. Nu de praktische uitwerking nog. Voor het analyseren van databestanden op eventuele afwijkingen van Benford’s Law maken accountants en fraudespecialisten gebruik van specifieke software, die voor incidenteel gebruik echter veel te kostbaar is.Daarom zullen we in het navolgende voorbeeld gebruikmaken van een spreadsheet-programma.De genoemde formules zijn bedoeld voor Microsoft Excel,maar kunnen eenvoudig worden aangepast voor gebruik in andere pakketten.Wie erg handig is met spreadsheets zal ongetwijfeld een toepassing kunnen ontwikkelen die ‘eleganter’werkt dan het in volgende voorbeeld.Dit is echter bewust eenvoudig gehouden, om de stappen beter te kunnen toelichten.
Een aldus vervaardigd bestand zal in elk geval de crediteurnaam en het te betalen bedrag bevatten. In beginsel zijn voor het testen van de getallen van een bestand op Benford’s Law uitsluitend getallen nodig.Voor het analyseren van geconstateerde afwijkingen dient nader onderzoek te worden uitgevoerd. Dan is noodzakelijk dat men weet welke betalingen bij welke crediteuren horen. Na correct inlezen van het saldibestand beschikken we over een Excelbestand met in ieder geval twee kolommen:‘Crediteurnaam’ en ‘Bedrag’.Voor het voorbeeld gaan we ervan uit dat deze gegevens in respectievelijk kolom A en kolom B staan.
Bepalen van de eerste twee cijfers Voor het testen van de getallen in de kolom ‘Bedrag’ dienen we de eerste twee cijfers te bepalen. En daarbij stuiten we direct op een praktisch probleem: we moeten alle cijfers rechts van de eerste twee cijfers ‘weghakken’. Het ligt voor de hand om hierbij gebruik te maken van de diverse functies die Excel biedt voor het bewerken van tekst-strings, maar dan kunnen we in de problemen komen met de plaats van de komma die per bedrag kan verschillen. Daarom berekenen we liever de zogenoemde ‘collapsed value’ waarbij elk getal een evengroot aantal cijfers voor de komma krijgt (in ons geval kiezen we voor twee).We doen dat met de volgende berekening:
Samenvatting Hoewel de in bijgaand artikel beschreven Excel-toepassing niet erg geschikt is voor de frequente analyse van zeer grote gegevensverzamelingen, blijkt het toch mogelijk op relatief eenvoudige wijze bestanden te toetsen aan Benford’s Law. Daardoor kan ook zonder kostbare hulpmiddelen inzicht worden verkregen in de opbouw van bestanden en komt voor de administrateur/controller een nieuwe norm voor toepassing bij cijferanalyses beschikbaar.
Door van deze waarde uitsluitend het deel vóór de komma te gebruiken (eenvoudig te bepalen met de integer-functie), beschikken we direct over de eerste twee cijfers. Dit resultaat willen we weergeven in kolom C,waarvoor we nu de volgende formule kunnen gebruiken (regel 7 als voorbeeld):
Analyse van het bestand Inlezen van de data
40
TIJDSCHRIFT
Stel dat we maandelijks een betaaldiskette vervaardigen voor het betalen van crediteuren. Door het grote aantal betalingen en de elementen waaruit het bestand is samengesteld (meerdere facturen per crediteur, meerdere orderregels per factuur enzovoort) verwachten we dat het bestand in beginsel moet voldoen aan Benford’s Law. Om deze verwachting te testen, dienen we de bedragen in te lezen in een onderzoekstoepassing, voor ons voorbeeld is dat Excel. Dit kan door de betaaldiskette zelf in te lezen, al zullen we in dat geval wel aardig wat overbodige informatie moeten verwijderen (rekeningnummers, scheidingstekens, toelichtende opmerkingen zoals factuurnummers enzovoort). In de meeste gevallen is het eenvoudiger om vanuit het financieel systeem te betalen crediteurensaldi te exporteren naar een bestand. Sommige pakketten beschikken al over de mogelijkheid om te exporteren naar een Excel-bestand, maar ook een tab-gescheiden, komma-gescheiden, dbf- of ASCIIbestand kunnen door Excel probleemloos worden ingelezen. ADMINISTRATIE oktober 2002 - nr. 10
De resultaten in kolom C dienen nu geanalyseerd te worden door deze te vergelijken met de frequentie zoals we die op grond van Benford’s Law zouden verwachten.We geven het werkblad met de betalingen de naam ‘Bedragen’ en gaan verder op een nieuw werkblad in hetzelfde bestand.We kunnen dit bijvoorbeeld ‘Analyse’ noemen. In kolom A van het werkblad ‘Analyse’ maken we een reeks van alle mogelijke combinaties van eerste twee cijfers: de getallen 10 tot en met 99. In kolom B kunnen we nu voor elk van deze combinaties een telling weergeven. Stel dat we een bestand met 500 betalingen analyseren en dat de eerste twee cijfers van deze bedragen op het werkblad ‘Bedragen’ zijn berekend in de cellen C3 tot en met C502.Voor de telling van het aantal getallen dat begint met 19 hanteren we in kolom B de volgende formule (stel dat deze cijfercombinatie is weergegeven in regel 12):
We hebben nu het aantal malen dat de verschillende cijfercombinaties in het betalingenbestand voorkomen op een rijtje.Voor analyse is het noodzakelijk dat de relatieve frequentie wordt berekend.We voegen daarom in kolom B onder de cijfercombinatie 99 een totaal toe (bijvoorbeeld in B94), waarin we de som berekenen van alle tellingen (=SUM(B3:B92)). Dit totaal dient uiteraard overeen te komen met het totaal aantal bedragen in het te analyseren bestand, in dit voorbeeld 500. In kolom C berekenen we nu voor elke cijfercombinatie de relatieve frequentie, voor regel 9 bijvoorbeeld:
De frequentie die we verwachten op grond van Benford’s Law, berekenen we in kolom D.Voor dezelfde cijfercombinatie wordt dit dan:
In elk geval moeten we beschikken over de naam van de crediteur en het bedrag. We bepalen de eerste twee cijfers.
Grafische presentatie Het rekenwerk is achter de rug. Het beoordelen van de analyse op de eerste twee cijfers in het betalingenbestand gaat het eenvoudigst met een grafiek.Voor een prettige weergave van de resultaten selecteren we de kolommen C en D en formatteren deze als ‘Percentage’, waarbij we drie cijfers achter de komma weergeven. Vervolgens vervaardigen we met de chart-wizard een eenvoudige lijngrafiek. Figuur 1 laat de resultaten van het rekenwerk zien. Voldeed het bestand met factuurbedragen uit het voorgaande artikel heel aardig aan de verdeling zoals we die op grond van Benford’s Law zouden verwachten (de duidelijke afwijking bij 49 even buiten beschouwing latend), het bestand met betalingen voldoet er duidelijk niet aan. Het lijkt er eerder op dat er sprake is van meer of minder grote afwijkingen van de 1 procent-lijn. Dit is ook niet vreemd: voor deze analyse is een bestand gebruikt waarbij 500 betalingen met behulp van een random-generator zijn vervaardigd: daarbij is voor elke cijfercombinatie de waarschijnlijkheid even groot. Zou op deze wijze een zeer groot bestand worden vervaardigd, dan zou een vrijwel rechte lijn het resultaat zijn. Op een nieuw werkblad gaan we verder met de analyse. Analyse op eerste twee cijfers
Figuur 1. Relatieve frequenties van de eerste twee cijfers van de be-
42
TIJDSCHRIFT
talingen
ADMINISTRATIE oktober 2002 - nr. 10
Gebruik in de praktijk Hoewel op voorgaande wijze vrij eenvoudig een bestand op Benford’s Law kan worden getoetst, is het vrij omslachtig om voor elk bestand opnieuw een analyseblad aan te maken. Maar dat is ook niet nodig.Het is mogelijk om het werkblad ‘Analyse’te kopiëren naar een ander Excel-bestand dat geanalyseerd dient te worden.Wel dienen voor een snelle analyse enkele aanpassingen te worden gemaakt. In de eerste plaats dienen ook bestanden met meer of minder bedragen geanalyseerd te kunnen worden. Dit kan eenvoudig worden gerealiseerd door de range voor de COUNTIF()-functie te vergroten: lege cellen worden eenvoudigweg niet meegeteld. De volgende formule maakt gebruik van de maximale range die in Excel kan worden gehanteerd (voor bestanden met meer dan 65.536 bedragen kan Excel niet worden toegepast):
In het voorgaande is steeds gebruikgemaakt van een analyse op de eerste twee cijfers. Deze wordt in de praktijk ook het meest toegepast, omdat met relatief weinig rekenwerk veel informatie kan worden verkregen, die grafisch kan worden geanalyseerd.Voor analyse op uitsluitend het eerste cijfer, wat uiteraard minder informatie oplevert, kan op het werkblad ‘Bedragen’ gebruik worden gemaakt van de formule:
Voor de eerste drie cijfers, waarbij de analyse veelal niet meer grafisch maar door beoordeling van tabellen geschiedt, wordt dit:
Wel dient dan nog de range van cijfercombinaties in kolom A van het werkblad ‘Analyse’ te worden beperkt (1 tot en met 9) respectievelijk uitgebreid (100 tot en met 999).
Verder dient te worden bedacht dat in het hier gebruikte voorbeeldbestand met gegenereerde getallen geen bedragen voorkomen die kleiner zijn dan nul of gelijk aan nul. In een betaalbestand zal dat ook niet het geval zijn, maar voor andere bestanden kan dit anders liggen. De hiervoor gebruikte formule voor het bepalen van de eerste twee cijfers leidt dan tot een foutmelding. Om bepaalde bedragen uit te zonderen, kan een aangepaste formule worden gehanteerd, bijvoorbeeld:
Let er ten slotte op dat op het werkblad ‘Analyse’ wordt verwezen naar het werkblad ‘Bedragen’. Indien dit werkblad wordt gekopieerd naar een ander te analyseren bestand, dan dient de koppeling opnieuw te worden gemaakt door aanpassing van de desbetreffende formules. Evenzo dient de formule voor het bepalen van de eerste twee cijfers steeds op alle te analyseren bedragen te worden toegepast, ofwel door deze in een kolom te plaatsen op het werkblad met de te analyseren bedragen ofwel door een separaat werkblad met deze formule te vullen en voor elke analyse de koppeling naar het werkblad met de bedragen aan te passen.
advertentie
43
TIJDSCHRIFT
ADMINISTRATIE oktober 2002 - nr. 10
44
TIJDSCHRIFT
ADMINISTRATIE oktober 2002 - nr. 10
