スプレッドシートでPLを作る方法 - 予算作成編
いざ事業計画をPL（損益計算書）に落とし込もうと思ったときに、Excelやスプレッドシートでうまくまとめられないことはないでしょうか。もしくは、まとめることはできるものの人によってフォーマットがバラバラで見にくい、といったことはないでしょうか。
この記事では、とある会社の事業責任者が自分でPLを作る場面を想定して、作り方をお伝えしようと思います。
今回作るPLを先にお見せします。
※手入力の数字は紫色、計算式が入っている数字は黒色で表示しています
上記のPLの作成手順をステップバイステップでお伝えしていきます。
スプレッドシートで見やすいサマリシートを作る8つのポイントで作成したフォーマットをもとにご説明していくので、もし必要な場合は先にこちらの記事をご参照ください。
1.まず主要な項目を入力する
分かりやすいように、まずはPL（損益計算書）の一番大枠となる項目を入力します。
売上、売上原価、粗利（売上 - 売上原価）、販管費、営業利益（粗利 - 販管費）、経常利益、税引前当期純利益、当期純利益。今回は一事業責任者がPLを作ることを想定し、簡略化して営業利益までに絞ってご説明していきます。
2.売上計画を立てる
ここから先は、事業内容やどういう切り口で計画を立てるかによって作り方が変わります。一例として下記の想定で作成してみます。
- 商品A、商品B、商品CをECサイトと店舗で販売している
- ECサイトは自社ECサイトのみで、「ユーザー数」「CVR（コンバージョン率）」「平均単価」の掛け合わせで数字を算出しつつ、商品別の売上計画も立てたい
- 店舗は店舗A、店舗Bがあり、店舗別に客数や客単価などのKPIの掛け合わせで数字を作ると同時に、商品別の売上計画も立てたい
1.自社ECサイトの売上
自社ECサイトを「ユーザー数」「CVR（コンバージョン率）」「平均単価」に分解し、想定のユーザー数とCVRを入力します。平均単価は商品別売上点数の数量から計算をすることにします。なお、商品単価の情報も必要になるので、別途入力しておきましょう。
2.自社ECサイトの商品別売上点数
商品A,B,Cの売上点数をそれぞれ3,500個、2,500個、1,800個とすると、平均単価を計算して9,400円となるため、自社ECサイトの売上は1,694,000円になります。
3.自社店舗の売上
次に自社店舗を客数×平均単価とし、客数は1日あたり客数×営業日数とします。平均単価は自社ECサイトで計算したやり方と同じです。それぞれ想定の数字を入力すると、自社店舗の売上が計算できます。
これで売上が25,194,000円となりました。補足として、ここでは1月の数字しか入れていませんが、2月以降も同様に作り込むことで年間の計画を立てることができます。
3.売上原価を計算する
売上原価の計算は、商品ごとの売上数量と商品原価（単価）が分かれば計算できます。商品原価（単価）の情報を用意した上で、ここではシンプルに「自社ECサイト」と「自社店舗」に分けて計算しましょう。
4.粗利・粗利率を計算する
- 粗利 = 売上 - 売上原価
- 粗利率 = 粗利 / 売上
で計算することができます。
自社ECサイトと自社店舗で粗利率が若干異なりますが、これは商品の売上構成比によるもので、粗利率が高い商品が売れれば売れるほど、全体の粗利率が上がります。
5.販管費を入力する
例えば販管費が「人件費」「物流費」「減価償却費」「地代家賃」の4つとし、人件費を単価×人数だと想定すると下記のようになります。
実際には、売上や売上点数などによって変動する費用があったりするため、もう少し細かな計算ロジックが必要になる可能性があります。ただしまずは簡単な形で作り方を習得しておけば、あとは少し応用を効かせれば作れると思います。
6.営業利益を計算する
そして最後に「粗利 - 販管費」で営業利益を計算します。売上から営業利益を全て並べて見てみると、下記のような形になります。
7.月別の数字を作り込んでいく
毎月同じ数字となる項目もあれば、毎月異なる数字となる項目もあります。季節要因で変動する箇所もあるでしょう。それらの要因を全て盛り込んだ上で年間のPL予算を作ります。作り方は2〜6の流れに沿って進めれば良いです。
最後に
PLの形は事業内容やKPIなどによって変わりますが、大枠はこの記事でご説明した内容になります。ぜひ参考にしてみてください。
また、貴社のダッシュボード構築のご相談については、下記からお問い合わせください。