Risikocontrolling mit dem Gesetz von Benford / 10 Tests in der Excel-Datei

Beitrag unter anderem enthalten im Controlling Office

Arbeitsblatt „B-Test1”

Sowohl der Anpassungstest als auch das Modell des Distorsion-Faktors erschließen sich dem Tagesgeschäft erst dann, wenn sie in einer praktikablen Form vorliegen, sodass in diesem Abschnitt der Aufbau des Arbeitsblattes „B-Test1” der Excel-Dateien auf Haufe Controlling Office erläutert wird (s. Abb. 5)

Abb. 5: B-Test1

Wie man aus der Struktur der Tabelle erkennen kann, setzt sich das Blatt aus vier Teilen zusammen:

Statistische Parameter
DF-Modell nach Nigrini
Analyse der ersten Ziffer
Chi²-Test für die ersten beiden Ziffern.

Statistische Parameter

Die statistischen Parameter zeigen einige wichtige Kennzahlen der Zahlenmenge an und beziehen sich auf bereits vorgestellte Konzepte. Die jeweiligen statistischen Kennzahlen werden durch entsprechende Excel-Funktionen realisiert und berechnen sich konkret:

Anzahl N:	= ANZAHL(Zwischenrechnung!A:A)
Mittelwert:	= MITTELWERT(Zwischenrechnung!A:A)
Standardabweichung:	= STABW(Zwischenrechnung!A:A)
Schiefe:	= SCHIEFE(Zwischenrechnung!A:A)

Distorsion-Faktor

Das DF-Modell nach Nigrini realisiert die Berechnung des Distorsion-Faktors, wobei auch hier wieder auf bekannte Excel-Funktionen zurückgegriffen wird und wobei die bereits vorgestellten Ideen zum DF-Modell hier nur umgesetzt werden:

Anzahl N:	= ANZAHL(Zwischenrechnung!B:B)
AM (actual mean):	= MITTELWERT(Zwischenrechnung!B:B)
EM (expected mean):	= 90/(N*(10^(1/N)-1))
DF (Distorsion-Faktor):	= (AM-EM)/EM
95 % Konfidenzintervall oben:	=0+KONFIDENZ(0,05;0,63825342;N)
95 % Konfidenzintervall unten:	= 0-KONFIDENZ(0,05;0,63825342;N)

Aus Verständnisgründen wurden in den unteren vier Formeln die Adressen der Zellen innerhalb des Excel-Blatts durch die sie beschreibenden mathematischen Größen ersetzt. Die genauen Formeln können in den Excel-Dateien nachgelesen werden. Erwähnenswert hierbei ist nur die Verwendung der Excel-Funktion KONFIDENZ, die ausgehend von einer vorgegebenen Irrtumswahrscheinlichkeit von 5 % (entspricht 95 % Sicherheit), der bekannten Standardabweichung für die durchschnittlichen Abweichungen vom theoretischen Mittelwert einer Benford-Verteilung, die sich nach Nigrini auf 0,63825342 beläuft, sowie der Anzahl der Elemente in der untersuchten Zahlenmenge die halbe Länge des Konfidenzintervalls berechnet. Da sich für eine Benford-Menge ein Erwartungswert für DF von 0 ergibt, kommt man somit auf die Formeln für die untere und die obere Grenzen des 95-%-Konfidenzintervalls.

Konfidenzintervall

Neben der Bestimmung des Konfidenzintervalls für den Distorsion-Faktor ist wie bereits weiter oben erwähnt auch die Bestimmung der Konfidenzintervalle für die relativen Häufigkeiten der Anfangsziffer, d.h. für die so genannten Anteilswerte, sinnvoll, da diese Anteilswerte im Falle einer Benford-Menge eine Näherung für die Auftretenswahrscheinlichkeiten der Ziffern angeben und in denen dann die Anteilswerte mit einer festen statistischen Sicherheit zu finden sein sollten. Besitzt die zu untersuchende Zahlenmenge mindestens rund 200 Zahlen, dann kann die eigentlich zur Berechnung der Intervallgrenzen notwendige Binomialverteilung durch eine Normalverteilung ersetzt werden und man gelangt dann zu folgenden Ober- und Untergrenzen für die Konfidenzintervalle mit 95 % statistischer Sicherheit:^{^[1]}

Obergrenze:	=	1/(N+1,96^2) x (N x p + 1,96^2/2+1,96 x WURZEL (N x p x (1-p)+1,96^2/4))
Untergrenze:	=	1/(N+1,96^2) x (N x p + 1,96^2/2 - 1,96 x WURZEL (N x p x (1-p)+1,96^2/4))

Dabei ist N wie üblich die Anzahl der Zahlen in der Zahlenmenge und p die Benford-Wahrscheinlichkeit für das Auftreten der jeweils betrachteten Ziffer. Innerhalb der Tabelle B-Test1 zeigt sich somit, dass alle Ziffern innerhalb ihrer Konfidenzintervalle liegen, sodass für alle Anfangsziffern einzeln betrachtet eine Übereinstimmung mit dem Benford-Profil festgestellt werden kann.

Chi²-Anpassungstest

Der bereits vorgestellte Chi²-Anpassungstest für die ersten Ziffern kann mit Hilfe von MS-Excel sehr viel einfacher durchgeführt werden, als dies theoretisch erklärt wurde, denn der Vergleich von Prüfgrößen mit einem Vergleichswert, der sich aus der statistischen Sicherheit und der Größe der Zahlenmenge bestimmt, entfällt hier. Die Excel-Funktion CHITEST liefert direkt aus den beobachteten und den theoretischen absoluten Häufigkeiten die statistische Sicherheit für die Übereinstimmung der empirischen mit der Benford-Verteilung, d.h.:

Sicherheit für 1. Ziffer	=	CHITEST(B13:J13;B12:J12)
Sicherheit für 1. u. 2. Ziffer	=	CHITEST(Histo_12Z!B2:B91; Histo_12Z!C2:C91)

In der Excel-Datei zu den Tagesumsätzen findet man somit folgende Ergebnisse:

Mit mehr als 99,99 % Sicherheit stimmt die Verteilung der ersten Ziffern in der Menge der Tagesumsätze mit der Benford-Verteilung überein.
Mit fast 100 % Sicherheit stimmt die Verteilung der ersten beiden Ziffern in der Menge der Tagesumsätze mit der Benford-Menge überein.