Excelで変動損益計算書を作ってみる

変動損益計算書と聞くと難しく感じますが、そんなことはありません。

あわせて読みたい

自社を把握するための経理が大切 経理をしようと思うと、普段聞き慣れない言葉がたくさん出てきます。一生懸命学ぼうと思えば思うほど、目的がブレてしまうことも...。 重要なのは、自社を把握するため...

先日の記事でも固定費と変動費に分けて考えてみるというお話をしました。
分けて考えるとことで、将来の数字をイメージしやすくなるからです。

ですが、会計ソフトから出力した表を見ても分かりづらいことが多いです。
そういうときはExcelで作ってしまいましょう。

今回は「Excelで変動損益計算書を作ってみる」というお話です。

固定費と変動費に分けてみる

まずは必要となるのが、固定費と変動費を分けることです。
細かく考えすぎると難しくなり過ぎます。

まず、変動費をどうするかを考えてみましょう。
売上が「0」だと発生しない費用を考えてみると…

• 仕入
• 材料費
• 外注費

がメインになってくるのかなと。
特に中小企業の場合は、これら3つがほとんどです。

「いやいや、水道光熱費も増減するよね！？」と考えられる方もいらっしゃるでしょう。
ですが、水道光熱費は、売上が無くても発生するもので、毎月大きく変動するわけではありません。
その他にも増減はあるものの大きく変わらないものがほとんどかと。

まずは、上記の3つを変動費と割り切って振り分けをしてみると良いでしょう。
もし、もっと細かく調べたい場合は、Excel関数の「correl」を使ってみるのも面白いかと。
相関係数を調べることができるので、売上との相関係数が高いものを変動費とする方法もあります。

Excelで作成してみる

Excelで作成する場合は、xlookup関数を上手く活用しましょう。
今回は、freeeの推移表から作成しています。

freeeの推移表の画面に行くと右端に「エクスポート」というボタンがあるのでクリックしましょう。
「CSV・PDFエクスポート」を選択します。

1. 「エクスポート形式の選択」で「CSV」を選択
2. 「CSVの出力形式を選択してください」で「項目を整列する」にチェック
3. 「文字コードの設定」ではWindowsの方は「Shift_JIS」、Macの方は「UTF-8」を選択
4. 「出力する帳票の選択」は「損益計算書」にだけチェック
5. 「出力開始」→「ダウンロード」

こういった形式の推移表が出力されます。
これを自分好みに変更していきましょう。

表ができたら、貼付用のシートを作成しておきましょう。

この表は、後からExcel関数を入力して、貼付用シートに貼り付けたデータを引っ張ってくる形になります。

貼付用のシートに、先程出力したfreeeの推移表をもう1度ダウンロードして貼付をします。

そして、月次推移のシートに戻り、上の画像のように計算式を入力していきます。
最初は時間が掛かりますが、1度作成してしまえば、
後からの処理が格段に早くなります。

vlookup関数を使っても良いのですが、freeeの推移表は空白の行があります。

上の画像でいうと、黄色で塗りつぶされている箇所です。
vlookup関数の性質上、上から下に検索値を探すので、空白行の「売上高」の数値を拾ってしまい値がゼロになってしまうのです。
xlookup関数を使えば、下から上に検索値を探すように指定ができるので、こちらを使用しました。

これで完成です。

注意点として「計」の欄、「期間累計」の欄、「利益」の欄にはsum関数を入れいています。
xlookup関数で全ての値を引っ張ると、間違いがあったときに発見ができません。
sum関数を入力していれば、最終値が違っていれば、どこかが間違っていると気づくことができます。

まとめ

今回は「Excelで変動損益計算書を作ってみる」というお話でした。

Excelで作成するとアレンジが自由です。
データバーを付け加えても良いですし、グラフを作ることもできます。
固定費の分け方も自由ですから、自分が見やすい表を作り上げると良いかと。

---

【ログ帳】
昨日は、朝にブログを更新。
その後は、外出を。
帰宅後に税理士業をしました。