Excelのピボットテーブル Excel書籍の補足

Excelで損益計算書の科目比率を算出し、収益性を確認する方法

更新日:

書籍に入れようと考えていた内容ですが、ページの都合上除いた内容です。
今回の記事もページの都合上除いた内容です。
入るとしたらP74の後あたりです。

こちらからデータをダウンロードしてください。
経理の仕事がサクサク進むExcel「超」活用術 2013/2010/2007対応 ダウンロード

利用するデータは「chapter3_1.xlsx」のシート「損益計算書2」と「損益計算書5」です。

部門別損益計算書を見れば、各部門の数値を見ることができます。
ただ損益計算書だと、数値だけで判断してしまうことになるので、不十分な場合があります。

例えば部門ごとのこのような表があります。

売上高 利益 売上高利益率
A部門 200 15 7.5%
B部門 100 10 10%

A部門の方が売上高も多いですし、利益も稼いでいますが、売上高利益率を見るとB部門の方が高いです。

ただ、これから説明する内容はExcel売上高利益率を計算するものではありません。
取引総額に対して各科目の取引金額の比率を確認するものです。
どういったものかは、実際の手順の中で説明していきたいと思います。

列にデータを追加する

「損益計算書2」のフィールドのT列に「比率」と追加します。
image003
※自動的にT列がテーブルに加えられます。

計算式を設定する

セルT2に次の計算式を入力します
=IF(O2="01売上",S2,S2*-1)
image008

※直接セルO2,S2を選択すると、以下の計算式で表示されます。
「=IF([@勘定分類]="01売上",[@試算用金額],[@試算用金額]*-1)」

ピボットテーブルを更新する

シート「損益計算書5」を選択します。
image009

適当なセルを選択した状態で、アプリケーションキーを押して「更新」をします。
image014

値の集計方法を変える

値ボックスに「比率」のフィールドを追加します。
image021

試算用金額とは、収益項目を「プラス」の値に、費用項目を「マイナス」の値に変換しました。

比率を見るためには、収益項目も費用項目も「プラス」の値に変換します。
そのためにステップで「=IF(O2="01売上",S2,S2*-1)」という計算式を入力しました。
この式は費用項目の試算用金額に「マイナス」をかけて、費用項目を「プラス」の値にします。

値のフィールド設定をクリックします。
image024

「計算の種類」のタブを選択します
image028

計算の種類から「列集計に対する比率」を選択します。
image033

月度ごとの各科目の比率がわかる損益計算書が作成されました。

「列集計に対する比率」とは、列合計に対して科目金額を計算してくれるものです。

費目 数値 計算式
売上高 100 62.5% (100/160)
経費 60 37.5% (60/160)
取引総額 160 100% (160/160)

売上高のパーセンテージが多いほど、利益率が高いということになります。

ちなみに損益計算書はこんな感じ。

費目 数値 計算式
売上高 100
費用 -60
利益 40 40% (40/100)

比較する対象を変更する

列ラベルボックスを「月度」から「部門名」に変更します。
image038

image034

これによって部門間での各科目の比率を見ることができます。

部門間での人件費を比較する

行ラベルボックスから「営業利益」と「売上総利益」を除き、「勘定分類」と「勘定科目名」の間に「人件費集計」を追加します。
image037

「人件費 集計」が追加されました。image041
B部門は人材派遣を行っている部門なので「労務費」の分、他より高くなっています。
給料手当で見るとA部門が一番低いことがわります。

フィールドを折りたたんでコンパクトにしてみましょう。
image046

コンパクトになりました。image047

部門間での売上高に対する費用の効率性を比較して、利益率が低い(売上高の比率が低い)部門に対して、何が問題で低いのかを明らかにすることができます。

03投稿記事下_関連コンテンツ

-Excelのピボットテーブル, Excel書籍の補足
-, ,

Copyright© 経理と総務の効率化 , 2024 All Rights Reserved.