複数の利益体系の実現

売上総利益と貢献利益を同時に算定する

財務会計では、原価計算により売上原価を計算し売上総利益を算定する一方、管理会計では費用を変動費と固定費に分割し貢献利益を算定する企業も多いかと思います。

一般的には、財務会計は経理システムにより自動計算させ、管理会計用のエクセルファイルなどを準備し貢献利益を計算する場合が一般的と思われます。それでもよいのですが、本項では、エクセルデータベースとピボットテーブルを用いて、一つの財務データから、売上総利益⇒営業利益、及び、貢献利益⇒営業利益を同時に算定する方法を記します。

貢献利益とは、売上高から変動費を差し引いたものですが、一般に、各々の勘定科目は売上によって金額が変動する変動費的な要素と、売上に影響されない固定費的な要素を併せ持っています。しかし、ここでは分析の簡便さを優先し、勘定科目を変動費、或いは固定費と分類できると仮定します。

エクセルデータベースとＶＬＯＯＫＵＰ関数を使えば簡単に実現

データベースによるアプローチでは、下表のように、各勘定科目に、「売上原価」か「販管費」か、「変動費」か「固定費」かの属性を加えた表を、エクセルのワークシートの中に準備しておきます。

コード	勘定科目	利益体系Ａ	利益体系Ｂ
4111	売上高	１．売上高	１．売上高
5421	給与（原）	２．売上原価	２．固定費
5423	賞与（原）	２．売上原価	４．変動費
5425	法定福利費（原）	２．売上原価	２．固定費
5429	福利厚生費（原）	２．売上原価	４．変動費

次に、エクセルのＶＬＯＯＫＵＰ関数を用いてこの属性を従前に作成したデータベースに追加することで、一つのデータベースから異なる二つの帳票を同時に作成できます。

具体的な実施方法

このファイルをご自身のＰＣの適当なフォルダにダウンロードしてください。ファイル名はご自由に変えていただいて構いません。

上記でダウンロードしたファイルを開きます。なお、このファイルにはマクロが含まれていますので、「マクロが無効にされました」といった警告が出る場合があります。その場合は、警告の横にある「コンテンツの有効化」ボタンを押下してください。

このファイルの「計画値」というワークシートに予測財務数値（＝予算）を記入します。
なお、このワークシートの1行目には、データ名称として”予算”という名称を記入しています。

次に「計画値」ワークシートのいずれかのセルを選択した後、エクセルのメニュー⇒表示⇒マクロにて「データベース作成」を実行します。
勘定科目の示すセル範囲として、セルアドレス：A7からB60を、年月などを示すセル範囲として、セルアドレス：C1からN2を選択します。
このマクロによって「output」というワークシートが作成されます。

次に、このファイルの「科目体系マスタ」ワークシートにて、各勘定科目を、科目体系Aと科目体系Bの二つの観点から属性付けをします。
なお、このファイルでは、科目体系Aが売上総利益を、科目体系Bが貢献利益を算定する体系となっています。

VLOOKUP関数によりエクセルデータベースへ属性を追加

「データベース作成」マクロにより自動的に作成された「output」ワークシートのF列、及びG列に、VLOOKUP関数を用いて、「科目体系マスタ」に設定した勘定科目の属性を追加します。

まず、「output」ワークシートの１行目に、”コード”、”科目名称”、”データ名称”、 ”年月”、”金額”、”利益体系Ａ” 、”利益体系Ｂ”と記入します。

次に、「output」ワークシートのアドレスＦ２のセルに

=VLOOKUP(A2,科目体系マスタ!A:D,3,FALSE)

また、Ｇ２のセルに

=VLOOKUP(A2,科目体系マスタ!A:D,4,FALSE)

と入力します。この、Ｆ２セル、Ｇ２セルをコピーし、「output」ワークシートでデータが入っている行（４１４行）のＦ列、Ｇ列に貼り付けます。

なお、作成した「output」データベースでは、もともとのデータに含まれる　売上原価、売上総利益、営業利益、経常利益に関して、利益体系A及び利益体系Bの項目が＃N/Aとなっており、VLOOKUP関数が機能していませんが、そのままにしておきます。
これは、「科目体系マスタ」にこれらの項目を設定していないことに起因します。これらの勘定科目は他の科目から計算により導出できるので、今後の過程において、ピボットテーブル内で計算により求めることとします。

さらに、「output」データベースのＤ列（年月）の表示フォーマットを”２０１７年７月”などの日付形式としておきます。

一つのデータベースから二つのピボットテーブルを作成

この「output」ワークシートからピボットテーブルを作成します。
メニュー⇒挿入⇒ピボットテーブルと選択しますと、データ範囲が自動で設定されます。また、ピボットテーブルを配置する場所として、新規ワークシートを選択してOKを押します。

すると新しいシートに「ピボットテーブル１」という表示が出ますので、右側のピボットテーブルのフィールドの選択画面において、「フィルター」という場所にデータ名称、「行」に利益体系A、科目名、「列」という場所に年月、「値」という場所に金額をドラッグします。

作成されたピボットテーブルには、利益に関する勘定科目は　利益体系が#N/Aの下に配置されています。3.でも記しましたが、利益に関しては別途計算しますので、ここでは、ここではピボットテーブル内の行ラベルから、利益体系Ａを選択し、#N/Aのチェックを外しておきます。

次にピボットテーブルの　集計アイテムの挿入　という機能により、利益項目を追加します。
ピボットテーブル内のA列の「１．売上高」を選択し、メニュー⇒ピボットテーブルツール⇒分析⇒フィールド／アイテム／セットから集計アイテムの挿入を選択します。
「“利益体系A”への集計アイテムの挿入」というウィンドウが開きますので、

名前：　3.売上総利益
数式：= 利益体系Ａ[‘1.売上高’]- 利益体系Ａ[‘2.売上原価’]

と、記入しOKを押します。
この操作により、「3.売上総利益」がピボットテーブルに追加されます。
この際、「3.売上総利益」の下に科目名が多数発生しますが、個々の数値は必要ないため、「3.売上総利益」の左の―（マイナス）ボタンを押して閉じてしまいます。

もし、ピボットテーブルの並び順が、1.売上高⇒2.売上原価…となっていないようであれば、その項目をドラッグして手動で任意の場所に動かすことができます。

次に、再び、ピボットテーブル内のA列の「１．売上高」を選択し、メニュー⇒ピボットテーブルツール⇒分析⇒フィールド／アイテム／セットから集計アイテムの挿入を選択します。

「“利益体系A”への集計アイテムの挿入」というウィンドウが開きますので、

名前：　5.営業利益
数式：= 利益体系Ａ[‘3.売上総利益’]- 利益体系Ａ[‘4.一般管理費’]

と、記入しOKを押します。
この作業により、「5.営業利益」がピボットテーブルに追加されます。

最後に、作成したピボットテーブルが含まれているワークシート名を「利益体系A」と変更しておきます。

次に、貢献利益ベースの表を作成します。
上で作成したこの「利益体系A」ワークシートタブを右クリックして「移動またはコピー」を選び、「コピーを作成する」により、コピーしてください。

「利益体系A」と全く同じピボットテーブルが作成されていますが、画面右側にある”ピボットテーブルのフィールド”というエリアの「行」にある　利益体系Aを外し、利益体系Bを代わりに選択してください。

その後、利益体系Aと同じように以下の集計アイテムを挿入します。

名前：3.貢献利益
数式：=利益体系Ｂ[ '1.売上高']- 利益体系Ｂ[ '2.変動費']

名前：5.営業利益
数式：=利益体系Ｂ[ '3.貢献利益']- 利益体系Ｂ[ '4.固定費']

このワークシートの名称を、「利益体系B」とします。

以上の作業により、「output」ワークシートのデータベースに対して、売上総利益を算定する「利益体系A」ワークシート、並びに貢献利益を算定する「利益体系B」ワークシートの二つの帳票が作成できました。
もし、計画が変更となった場合は、、「output」ワークシートに変更されたデータを追加し、「データ名称」を選択しなおしたうえで、ピボットテーブル上で右クリックにより「更新」を実行するだけで、営業総利益や貢献利益が計算されます。