ベンフォード分析をExcelで行う方法

公認会計士が行う会計監査では経営者不正への対応するための手続きとして仕訳テストを行うことが求められています。その仕訳テストの一つ、ベンフォード分析についてExcelで行う方法についてご紹介していきます。

関連:

仕訳テストとは

仕訳テストは経理処理された仕訳に異常を示すような兆候がないか確かめるための手続です。

どうやって異常と判断するのかは会社によって違いますが、違和感や不自然さがある次にあげるようなケースだと検証を進めていくことになっています。

  • 普段使われない科目の利用
  • 摘要の入力が不十分
  • 仕訳の入力時間や日付が不自然
  • 金額が変(ゾロ目、キリがいい数字)
  • 仕訳の入力が(自動・手入力など)想定する方法と違う

仕訳テストとして行うベンフォード分析

そしてその仕訳テストの一環としてベンフォードの法則を活用して行うベンフォード分析があります。

ベンフォードの法則をざっくり説明すると自然界における多くの数字は最初の桁の数字の分布に偏りがあって、最初の桁が1となる割合は約30%、最初の桁が9となる確率は4%台と低くなっているというもの。

ベンフォード分析はそのベンフォードの法則の理論値と実際の会社の仕訳データをすべて集計した結果に、大きなズレがないか確かめていく手続です。

ベンフォード分析で行うこと

知らない人からするとベンフォードの法則は本当にそうなるの?と、疑ってしまうような統計学の法則なのですが、仕訳データを全般的に検証する際に実際に利用されることがあります。

ベンフォード分析を行う際の流れとしては

  1. 仕訳データを用意
  2. 各仕訳から金額欄の最初の桁の数字を抽出
  3. 抽出後最初の数字ごとに個数を数えて、割合を算定
  4. ベンフォードの法則の理論値と比較して妥当なのか検討する

というもの。

今回は100個の仕訳のサンプルデータを用意し、こちらについてベンフォード分析を実施します。

LEFT関数で最初の桁を抽出する

Excelで仕訳データをベンフォード分析を活用するためには、まず仕訳数値の最初の桁の数字を抽出することが必要です。

例えば 売掛金/売上 1,234.567円の取引仕訳だと、1,234…の1の数字を拾うのですがLEFT関数を活用してデータを抽出していきます。

LEFT関数の関数式は=LEFT(抽出元のセル.左から拾う桁数)で構成。

今回用意したデータでは、まずD3のセルの最初の桁の数字を抽出する関数式を任意のセル(ここではF3のセルを利用)に

=LEFT(D3.1)

と、入力しています。

 

そのF3のセルをコピーし、F4~F102まで貼り付ければ集計対象となる仕訳金額の最初の桁の数字を抽出することが出来ました。

今度は仕訳金額の最初の桁を表示しているF3~F102までについて、COUNTIF関数で各数字ごとデータの個数を集計していきます。

抽出した最初の桁の数字ごとに個数を数え、割合を算定

次に仕訳データから抽出した仕訳金額の最初の桁の数字ごとに個数を数えていきます。

任意の空いているセル(ここではH3~H11)に1~9までの数字を並べ、COUNTIF関数でI3~I11に仕訳金額の最初の桁の数字ごとに個数を数えます。

COUNTIF関数は(検索範囲,検索対象)で構成されているので、

=COUNTIF(F3:F102,H3)

と関数を組み、F3~F102にある仕訳金額の最初の数字から探す対象となる数字(H3に表示された1)を指定する関数を作成。

さらにF3:F102の間にカーソルを移動してクリックした後、F4を一度押すと関数式が=COUNTIF($F$3:$F$102,H3)と変化。

 

この関数を別のセルにコピー&ペーストするとF3:F102を参照先を固定した絶対参照のまま貼り付けることができます。

 

数字の分布割合を計算する

1~9のそれぞれの個数の構成割合を算定する際も同様に絶対参照を活用します。

まずI12に個数の合計をSUM関数で計算、

 

J3のセルに構成割合を表示させるため=I3/I12と入力し、I12の位置でF4を押し=I3/$I$12とします。

 

ベンフォードの法則の割合と比較、形式を整えればベンフォード分析資料として使える形になります。

 

なお、追加でグラフ化もしておけば視覚的に分かるため資料としての説得力も増すかと思います。

あとがき

今回はベンフォード分析をExcelで行う操作方法について解説しました。

ただ実際のところ、こういったテクニックは単なる入り口に過ぎず、理論値と実際のデータの割合がズレてしまう原因が妥当なのか検討し、説明していくことが監査人のお仕事です。

監査というと一般的には帳簿から元資料と数字が合っているのか確かめるイメージが強いですし、やっている方も仕事をしている感があるのですが、むしろこういった仕訳データ自体が違和感がないか理論立てて確かめられる思考力を持つことが大切なのではないのかと思っています。

関連:

|||||||||||||||||||||||||||||||||||||||||||||||||||||||

【編集後記】
執筆案件の初稿を終えた開放感から、欅坂の平手さんが主演映画『響 -HIBIKI-』を観てくるなど
「ここで終わるのっ?」という圧倒的に次回作が気になるED

次回作の公開予定はいつでしょうか、とても気になります…^^;

【最近読んだ本】

お知らせ
東京都の杉並区荻窪で守屋冬樹税理士事務所を営んでいます。
お客様の担当は必ず守屋冬樹自身、監査法人での経験を駆使した決算申告のみ対応が強みです。

■サービス一覧
内容と料金は下記のリンク先にて明示。ご入用の際はぜひクリックしてご覧下さい。
小規模会社個人事業主の税務顧問
・創業から2年内の会社様限定メニュー 創業顧問創業借入
・小規模会社向け顧問契約外で行う決算・申告のみ業務
・期限後申告・無申告 個人向け会社向け
出版、講演、記事執筆(監査実務、各種税金など)
個別相談
■ブログ投稿リクエストフォーム
moriya-blogで書いて欲しい内容について募集中です。是非こちらからリクエストのご連絡を下さい。

■ブログランキングに参加中
にほんブログ村 士業ブログ 公認会計士へ
にほんブログ村

■スポンサードリンク

ABOUTこの記事をかいた人

守屋冬樹(もりや ふゆき)

公認会計士、税理士。高校時代の出会いがきっかけで公認会計士を目指すと決意。2007年(22歳)高卒での公認会計士試験合格を実現。準大手監査法人に勤務しつつ2011年(25歳)公認会計士登録。2012年(26歳)税理士となり守屋冬樹税理士事務所を創設。 さらに詳細なプロフィール/お仕事のご依頼