エクセルで行う金額単位サンプリングのやり方

監査法人の若手、J1のスタッフがよく悩む金額単位サンプリングのExcel操作法を投稿します。

確認状の相手先の選定などで使うので3月決算の前に押さえておきましょう。

必要な情報

金額単位サンプリングを行う際に用意しておくのは次のふたつ。

・抽出対象のデータ(売掛金や買掛金の科目明細)

・抽出基準となる金額の把握

サンプル抽出を行うように指示を受けたら、いつの対象データを使うのか、その入手先や時期など確認しておきましょう。

なお、抽出基準となる金額をどうするのかは監査計画で決めています。

抽出データの加工

抽出対象のデータ(売掛金や買掛金の科目明細)を入手したら、サンプル抽出しやすいように加工をしていきます。

残高を降順に並び替える

まず行うのは残高を降順(金額の大きい順)にならび替え。

エクセルの操作としては残高数値が入力されているB列、B2のセルを選択

2016 02 03 13 15 14

【ホーム】タブの【編集】から【並び替えとフィルター】を選択【降順】をクリック

2016 02 03 13 16 33

取引先明細の残高が大きい順に並び替えられることが出来ます。

2016 02 03 13 16 51

累計金額を追加

次に累計金額を追加する計算式を加えていきます。

C3のセルはB2の数値とし、C4からC12までは一つ上C3と左横C4の数値を足し算。
金額の大きい項目から順次足して累計金額を計算します。

2016 02 03 13 21 11

C5以降のセルはC4のセルをコピーしてしまいましょう。

2016 02 03 13 26 12

INT関数を使い累計金額を抽出基準で割る

抽出基準となる金額(仮定で3,000,000)で累計金額を割っていきますが、その際のポイントとしてはINT関数を利用すること。

2016 02 03 13 54 21

INTは括弧の中に入れた数値を整数にする関数。

D3のセルでは7,866,876÷3,000,000=2と割り切れないものが整数表示、小数点以下は自動的に切り捨てられています。

2016 02 03 13 56 13

D5以降のセルはD4のセルをコピーしてしまいましょう。

2016 02 03 13 59 21

サンプル抽出対象の決定

ここまで来ると後は簡単。サンプルの抽出対象は一番残高の大きかったDの欄、『累計/抽出基準』の項目がそれぞれ一つ上のセルの数字より1以上大きい取引先となります。

抽出先を分かりやすくするためにデータを加工する場合、E3のセルはD3の数値を表示

2016 02 03 14 09 40

E4以降のセルは左のD4の値と、そのひとつ上のセルD3の値を引き算

2016 02 03 14 10 12

E5以降のセルはE4のセルをコピー。『抽出先決定』Eの行が1以上となった相手先が抽出対象となります。

2016 02 03 14 23 12

見にくいと感じた際は条件付き書式で0より大きい値は強調するように設定してみると分かりやすいです。
2016 02 03 14 27 10

 

参考までに活用して頂けたら幸いです。

関連エントリーはこちら

決算数字の増減比較、大きな増減に目が留まる強調表示ルールが便利

Excelに『今日の日付を入力出来るショートカット』があることを知っていましたか?

監査法人J1の方は要注意。棚卸立会の遅刻はダメ絶対!!

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

【編集後記】

金額単位サンプリングのやり方の記事がヒットせずあえての投稿。
データの加工作業を文字にするのに時間が掛かってしまいました(^^;)

【昨日の一日一新】

黒いコアラのマーチを食べる

一日一新のきっかけはこちら→一日一新

■ブログ投稿リクエストフォーム
moriya-blogで書いて欲しい内容について募集中です。是非こちらからリクエストのご連絡を下さい。

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

■スポンサードリンク