Excelで作る経営計画

売上高の策定(損益分岐点売上高・ 目標達成売上高・経営安全点売上高・資金分岐点売上高・資金安全点売上高)

経営計画に熟知し、Excelで経営計画ソフトを作成している田村博税理士時事務所 ( http://www.tamcon.co.jp )に、Excelで経営計画を作る方法(考え方)を聞いた

Excelで経営計画 ユーザー定義関数を作る(損益分岐点売上高、目標達成売上高)

bizvba_160

ユーザー定義関数を作る(損益分岐点売上高、目標達成売上高)

Excelには、関数が存在するが、「ユーザー定義関数」として自分で関数を作ることができる
今回は、損益分岐点売上高と、目標達成売上高 を求めるユーザー定義関数を作ってみる。

ユーザー定義関数の作成には、VBAが必要ですが、とても簡単です。
VBE というプログラミング用のエディターを起動(Excel で Alt + F11)して、標準モジュールに次のコードを入力する。(以下をコピペしても大丈夫)


Function 損益分岐点売上高(ByVal 固定費 As Long, ByVal 限界利益率 As Double)

    損益分岐点売上高 = Int(固定費 / 限界利益率)
    
End Function


Function 目標達成売上高(ByVal 目標利益 As Long, ByVal 固定費 As Long, ByVal 限界利益率 As Double)
    
    目標達成売上高 = Int((固定費 + 目標利益) / 限界利益率)
    
End Function

 ↓ こんな感じです。

vbe


あとは、Excelに戻って、入力したいセルを選択してから、数式バーの 「fx」マークを押して「関数の挿入」ダイアログボックスを表示して、関数の分類を「ユーザー定義関数」にする。(下図)

dialog1

すると、関数名に、損益分岐点売上高と、目標達成売上高が表示されます。

まずは、損益分岐点売上高を選択してみます。
「関数の引数」ダイアログボックスが表示されるので、固定費に「13,600」を、限界利益率に「0.12」と入力してみます。結果は、113,333となります。(下図)

dialog2

セルには、「=損益分岐点売上高(13600,0.12)」と入力されます。
ダイアログボックスを使わずに、直接セルに入力しても大丈夫です。



次は、目標達成売上高を選択した場合です。
「関数の引数」ダイアログボックスが表示されるので、目標利益に「10,000」、固定費に「13,600」を、限界利益率に「0.18」と入力してみます。結果は、131,111となります。(下図)

dialog3

セルには、「=目標達成売上高(10000,13600,0.18)」と入力されます。
ダイアログボックスを使わずに、直接セルに入力しても大丈夫です。


ユーザー定義関数を作ることで簡単に計算できるようになり、また、セルに入力する算式も短くなるメリットがあります。VBAで、その他いろいろな関数を作ってみてください。





Excelで経営計画 損益分岐点売上高のグラフを作る

bizvba_160

損益分岐点売上高グラフをExcelで作成

損益分岐点売上高のグラフをExcelで作成してみます。
下の図のように、表データをもとに、損益分岐点売上高の折れ線グラフを作成できます。
「固定費+変動費」と「売上高」の交点が、損益分岐点ですね。

1

上の表全体(青いセル見出しから、緑色のセル見出し全て6行3列)を選択状態にして、Excelのリボンの「挿入」タブを選択します。(注意)データ表の上部、緑色の売上高は、横軸のメモリを表示する為に必要です。
グラフグループの「おすすめグラフ」を選択すると、「グラフ挿入」ダイアログボックスが表示されます。その中の「すべてのグラフ」タブを選択して、「折れ線」グラフを選択します。(下図)

sonekibunki


折れ線グラフが、このように表示されない場合は、まず、表の選択範囲を、「目標達成売上高」だけにしてグラフを表示させてから、表の選択エリアを「固定費+変動費」まで広げてみてください。

もし、次図のように、グラフの横軸のゼロと、縦軸のゼロが一致しない場合は・・・
sonekibunki2


Excelグラフ 横軸のゼロ点と縦軸のゼロ点を一致させる(X軸の0点とY軸の0点を交差させる)Excel2007,2010
をご覧ください。





Excelで経営計画 目標達成売上高の求め方

bizvba_160

目標達成売上高の求め方(算式)

目標達成売上高とは、経常利益が目標額(目標経常利益)になった時の売上高です。

いくらの利益が必要で、その為の売上高はいくらになるのか!
ストラック図をみながらお話します。

3

注意)以下、固定費に営業外損益を含めて説明していますので、利益については、営業利益ではなく経常利益と書きます。

ストラック図を見ると分かるように、限界利益-固定費 がプラスになれば経常利益が出ます。

目標達成売上高の時、限界利益=固定費+目標経常利益 の状態となります。

損益分岐点売上高を求める式は、損益分岐点売上高=固定費/限界利益率 でした。
(前回の、損益分岐点売上高の求め方(算式)より)

目標達成売上高を求める式では、固定費の部分が、固定費+目標経常利益 になりますので、
目標達成売上高=(固定費+目標経常利益)/限界利益率 になります。


次は、損益分岐点売上高、目標達成売上高をグラフにしてみます





Excelで経営計画 損益分岐点売上高の求め方(算式)

bizvba_160

損益分岐点売上高の求め方(算式)

損益分岐点売上高については、前回書きました。


次のストラック図は、損益分岐点の状態です。

4

売上高に占める限界利益の割合を、限界利益率といいます。

限界利益率=限界利益/売上高


損益分岐点は、限界利益と固定費が等い状態

限界利益=固定費


ですから、限界利益を固定費と置き換えることができます。

限界利益率=固定費/売上高

また、損益分岐点での売上高なので、売上高を損益分岐点売上高と書き直します。

限界利益率=固定費/損益分岐点売上高

よって、損益分岐点売上高を求める式は次になります。

損益分岐点売上高=固定費/限界利益率

次は、目標達成売上高の求め方(算式)です。





Excelで経営計画 損益分岐点売上高とは

bizvba_160

損益分岐点売上高とは

損益分岐点売上高とは、売上高と費用が等しい(売上高=費用)時の売上高で、損益がゼロの状態の売上高です。 

費用には、変動費と固定費があります。
費用は、変動費+固定費

よって、売上高=変動費+固定費 の状態が、損益分岐点売上高になります。

図で説明した方が理解しやすいと思いますので、以下、ストラック図を使って解説します。

次の図は、売上高と費用が等しい時の図です。この時の売上高が、損益分岐点売上高です。 
売上高=変動費+固定費

1

注意)以下、固定費に営業外損益を含めて説明していますので、利益については、営業利益ではなく経常利益と書きます。


売上高が費用より大きくなれば経常利益が生じます。
売上高>(変動費+固定費)

2


売上高より費用が大きくなれば経常損失が生じます。
売上高<(変動費+固定費)

3

売上高から変動費を引いた部分を、限界利益と呼びます。

ストラック図を書き換えてみます。

売上高が損益分岐点売上高の時、限界利益と固定費は等しいです。
限界利益=固定費

4

限界利益が固定費より大きければ経常利益が生じます。
限界利益>固定費

5

限界利益より固定費が大きくなれば経常損失が生じます。
限界利益<固定費

6


売上高が、損益分岐点を超えた時に利益が生じ、超えなかった時には損失が生じます。
損益分岐点を求めることは、売上高の策定に重要な数値になります。


次は、損益分岐点売上高の求め方(算式)です。





記事検索
ExcelVBAマクロ作成代行


様々な企業、会計事務所、病院からマクロ作成代行を請け負っております。マクロ作成でお役に立てたら嬉しいです。まずはお気軽にご相談ください。
筆者・Excel 担当 Hide Tamura
Excel95? マクロシートがあった頃からExcelVBAを独学で利用しています。現在は、様々な企業様からVBAによる業務効率UPの為のお仕事を頂いております。既にお使いになっているファイルにVBAを利用することで、作業が楽になったり、時間も大幅に短縮されたなど、大変喜ばれています。
■VBA Expert
VBA Expert Standard Crown
ExcelVBA Standard
Access VBA Standard
Excel2002 VBA Standard
■MICROSOFT OFFICE USER SPECIALIST
Microsoft Excel version2002 Expert
Microsoft Excel version2002
経営計画指導 田村博
弊事務所は約50年間、税務・会計業務を継続して参りました。その一方でコンピュータによる経営管理ソフトの開発を行って来ました。 1978年TKC東京会研修所の「利益計画策定システム」の常任講師を担当したのを機に経営計画の研究に入り、日本オリベッティの協力を得て経営計画ソフト「MAPS」を開発し、関東・北陸から西日本一帯に販売しました。 その後、1995年にMS-Excel版の経営計画ソフトを開発し、導入した企業及び会計事務所は北海道から沖縄までに及びましたが、このほど経営計画とは違った先見経営ソフト(MS-Excel版)を開発しました。
Excel 関数 ノート
Excel関数を中心に、Excelの便利機能や、意外としらない使い方など書いていきます。

Excel 関数 ノート
Excel & Access VBAノート
Hide Tamura のVBAノートです。

頻繁に使うVBAコードなどを記録しコピペして使う為に作りました。お役に立てるようでしたら、お使いください。



Excel 経営計画 & 資金計画 書籍


アクセスカウンター