内部不正を一瞬で見抜く「ベンフォード分析」に母比率推定を加えてEXCELでやる方法
こんにちは、渡部です！
以前のブログでもお話したように、海外進出にはあらゆるトラブルがつきものです。トラブルのなかでも重要なタスクとなる「内部統制の構築」や「内部不正の予防的措置」は、会社の経営環境、ビジネスモデル、ガバナンス状況や内部統制の効き具合等の現在会社がおかれている状況を判断しながら進めていくものです。
しかしながら最近世の中をにぎわしているような内部不正、会計不正の話は、対岸の火事ではないと多くの経営者や経理の方は対策を講じていこうとされているのではないでしょうか？
これら不正の対策ですが、最近ではAIやコンピューティングモデルが利用され、財務数値や取引データ等に基づいて、通常ではないデータの動きを検知し、そこで不正が発生しているかもしれない、といった分析予測を可能にしています。
特にこの分野では統計の様々な手法が用いられ日々進歩を遂げており、不正監査や不正分析をキーワードにすると必ずといって出てくるのが本記事でご紹介する「ベンフォード分析」と呼ばれる手法です。このベンフォード分析は、フォレンジック等の財務諸表監査の一環として一般的に行われている手法であり、異常値を検知する手法の一つとして広く知られています。
実はこの分析は高度なシステムを用いなくても、皆さんがよく利用されているエクセルを用いて実施することができるのです。しかしながらこの関連キーワードでGoogle検索を行うと4百万件を超える検索結果が表示されるのですが、そのコンテンツの特徴としては
「ベンフォード分析」や「ベンフォードの法則」に関する説明
不正監査では使われるという紹介
計算をシステムで利用した結果のぼかし画像表示
等、実際どのような方法で行うものか手元のデータを用いて自分で試してみるための情報が不足しているように感じていました。
そこで、今回はこの分析手法として有効な「ベンフォード分析」をエクセル等のスプレッドシートでお試し利用できるようなモデルとして紹介します。
では、実際に今回このベンフォード分析を行ってみましょう。
「ベンフォード分析」の準備と理解：全体像を眺め、考え方の理解
先ずは、シートの全体像をご覧ください。こんな感じでエクエルやスプレッドシートで簡単にベンフォード分析による異常値検出が簡単にできるようになります。
注意：テンプレートは配布の都合上Googleスプレッドシートでもを用いていますが本説明はエクセルを用いています。
このテンプレートの理解に必要なスキルは4つの関数（left、countif 、sqrt、log）、統計の区間推定「母比率」の信頼区間の考え方になります。実際はすでにテンプレートに入力していますので実際は関数や統計がわからなくても利用できると思います。
手順１：データを準備
このベンフォード分析を経理の担当者であるあなたが実際に行うために必要となるのは、週次や月次の仕訳帳や総勘定元帳です。利用中のシステムから計算に利用するデータを出力しましょう。週次や月次の仕訳日記帳などをシステムからエクセルにダウンロードして以下のシンプルな列項目だけ残します。
テストデータ【日付/伝票番号/勘定科目/相手先/摘要/貸借/金額】
このように並んでいれば十分です。
法則値から外れた数値群が見つかったときに伝票番号や摘要などにさかのぼって確認できることが必要です。今回は仮に1000明細行があると仮定して話を進めます。利用者の方は500行でも3000行でも構いません。統計的にはこの数が多いほど精度高く異常値の検出が可能となります。
手順２：データから一桁目の数字を取り出し、個数をカウント
手順１で作成したテストデータの「金額」の隣の右列に関数「=left(金額セル、1)」を入力して一桁目だけの数字を抜き出します。列名を１件目値としておきます。例：金額5,186,969円であれば最初の「5」が該当します。
次にこの1桁目値列に含まれる数字の個数を確認します。関数「=countif(1桁目値、値)」として値１,2……9までの明細数がそれぞれ何個あるかを数えます。この合計個数は1000個になります。
皆さんのデータでの集計結果はいかがですか？ 作ってみると「1」が一番多く順に減っている結果になっていませんか？ 不思議ですね。これがまさにベンフォードの法則で興味深いところです。
手順３：それぞれの個数とベンフォードの法則の値と比較
手順２で作成した値の個数を、ベンフォードの法則の値と比較して異常値をあぶりだします。テンプレートのそれぞれの値に「貼り付け」してください。このCheckとOKがベンフォード分析の下限値と上限値の間に含まれなかった場合には、Check箇所に数値が表示され、この法則の枠内に95％の確率で含まれる場合にはOK箇所に数値が表示されます。この区分けはエクセルでグラフの色分けを行うためにこのような使い方をします。
今回のサンプル明細数：1000で実行した結果のサンプルは以下のように表示されます。
ここで今回のサンプルデータから次の数字が見えてきます。この例において、「１」と「４」が範囲を超えるいわゆる異常値（赤い線）を示しています。特に95％の確率で収まる範囲の上限値を超えているこの「４」で始まる数字については何らかのエラーがある可能性が高いと考えられます。テストデータに戻って伝票をざっと眺めてみましょう。データの中に同じ伝票や繰り返しで次始まる、勘定科目、見慣れない相手先、摘要の記載が不十分なデータはありませんか？このように数字の法則性から異常値を探すのがこのベンフォード分析です。
チェックする伝票数にもよりますがこの少ない「１」についても法則性から確認対象となる伝票が多くなりますが可能な限りみておいた方がよいとは思います。日ごろから多くの伝票を見慣れている経理の方にとっては270枚なんてへっちゃらですよね！
更にこの数値を深堀りするために同じロジックを用いた上位2桁分析もシステムでは行われています。エクセルの数値を縦方向に10〜99の範囲をつくることで同様の作成が可能ですので試してみてください。
手順４：母比率の推定を応用する
比率に一定幅を持たせたいときの考え方は以下のリンクを参照してください。
今回はこの信頼区間の考えをベンフォードの法則に応用させたブログとなります。
1桁目が「１」の値が出る約30％が95％の確率で会計伝票明細数1000枚に対してどれくらいになるのでしょうか？ シートのオレンジの箇所に入力を行うことで導き出すことが可能です。
その結果は以下の通り、「１」は上限32.9%〜下限27.3％の間に95％の確率で出てくるはずとなります。
また、その時の「１」が含まれる明細枚数は、95％の確率で273個から329個の間で出現するはずと出ます。
もしこの上限よりも大きい、あるいは下限より小さい数字が表れた場合は、異常値としてその伝票を見直してみることで「おかしな」経理伝票の出現を見つけることができます。今回の式ではこの範囲をCheckとOKで分けることですぐに視認できるようにしています。
【まとめ】内部不正を防止するために
いかがでしょうか？ エクセルを使えばチェック手法の一つであるベンフォード分析はできるようになりますね。不正検知によく利用されるベンフォード分析ですが、実際一部の大企業を除きサンプル数が少ない会計数値への応用はこの許容する区間の幅が大きくなってしまうため、あくまでも参考程度にはなるかと思います。（明細が多いのは売上高の明細くらいでしょうか）
とはいえ、不正チェックは予防で行うものです。毎月決算の度に不揃いなデータを海外全ての拠点から集めて分析を都度行うのは大変ですよね。
multibookを使えばいつでも海外からデータを集めて様々な分析をすることができます。しかも、これら不正検知などのチェク機能が標準に実装され利用できるようになるのはマルチブックならでは。
海外拠点の管理って、自分の経験からも本当に不透明になりやすい環境なんですよね。事業の透明性を高め企業のリスクを排除するため、すべての企業が海外拠点でERPを使うことが当たり前になる世界を実現しなくちゃ！
ではでは
この記事が気に入ったらサポートをしてみませんか？