書籍に入れようと考えていた内容ですが、ページの都合上除いた内容です。
今回の記事もページの都合上除いた内容です。
入るとしたらP74の後あたりです。
こちらからデータをダウンロードしてください。
経理の仕事がサクサク進むExcel「超」活用術 2013/2010/2007対応 ダウンロード
利用するデータは「chapter3_1.xlsx」のシート「損益計算書2」と「損益計算書5」です。
部門別損益計算書を見れば、各部門の数値を見ることができます。
ただ損益計算書だと、数値だけで判断してしまうことになるので、不十分な場合があります。
例えば部門ごとのこのような表があります。
売上高 | 利益 | 売上高利益率 | |
A部門 | 200 | 15 | 7.5% |
B部門 | 100 | 10 | 10% |
A部門の方が売上高も多いですし、利益も稼いでいますが、売上高利益率を見るとB部門の方が高いです。
ただ、これから説明する内容はExcel売上高利益率を計算するものではありません。
取引総額に対して各科目の取引金額の比率を確認するものです。
どういったものかは、実際の手順の中で説明していきたいと思います。
列にデータを追加する
「損益計算書2」のフィールドのT列に「比率」と追加します。
※自動的にT列がテーブルに加えられます。
計算式を設定する
セルT2に次の計算式を入力します
=IF(O2="01売上",S2,S2*-1)
※直接セルO2,S2を選択すると、以下の計算式で表示されます。
「=IF([@勘定分類]="01売上",[@試算用金額],[@試算用金額]*-1)」
ピボットテーブルを更新する
シート「損益計算書5」を選択します。
適当なセルを選択した状態で、アプリケーションキーを押して「更新」をします。
値の集計方法を変える
値ボックスに「比率」のフィールドを追加します。
試算用金額とは、収益項目を「プラス」の値に、費用項目を「マイナス」の値に変換しました。
比率を見るためには、収益項目も費用項目も「プラス」の値に変換します。
そのためにステップで「=IF(O2="01売上",S2,S2*-1)」という計算式を入力しました。
この式は費用項目の試算用金額に「マイナス」をかけて、費用項目を「プラス」の値にします。
値のフィールド設定をクリックします。
「計算の種類」のタブを選択します
計算の種類から「列集計に対する比率」を選択します。
月度ごとの各科目の比率がわかる損益計算書が作成されました。
「列集計に対する比率」とは、列合計に対して科目金額を計算してくれるものです。
費目 | 数値 | 率 | 計算式 |
売上高 | 100 | 62.5% | (100/160) |
経費 | 60 | 37.5% | (60/160) |
取引総額 | 160 | 100% | (160/160) |
売上高のパーセンテージが多いほど、利益率が高いということになります。
ちなみに損益計算書はこんな感じ。
費目 | 数値 | 率 | 計算式 |
売上高 | 100 | ||
費用 | -60 | ||
利益 | 40 | 40% | (40/100) |
比較する対象を変更する
列ラベルボックスを「月度」から「部門名」に変更します。
これによって部門間での各科目の比率を見ることができます。
部門間での人件費を比較する
行ラベルボックスから「営業利益」と「売上総利益」を除き、「勘定分類」と「勘定科目名」の間に「人件費集計」を追加します。
「人件費 集計」が追加されました。
B部門は人材派遣を行っている部門なので「労務費」の分、他より高くなっています。
給料手当で見るとA部門が一番低いことがわります。
フィールドを折りたたんでコンパクトにしてみましょう。
コンパクトになりました。
部門間での売上高に対する費用の効率性を比較して、利益率が低い(売上高の比率が低い)部門に対して、何が問題で低いのかを明らかにすることができます。