Excelで作る経営計画

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

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

※完成ファイルは販売ご提供予定。お問合せ http://www.tamcon.co.jp/vba/index.php

Excelで利益計画表(計画)を作ります。下の表は例です。Excelで簡単に作れると思います。

「合計」や「利益」は、数式を入れて計算させましょう。


■合計を求める
例)=SUM(C3:C6)
セル範囲の合計を求めることが可能

月別利益計画表(計画)  ↓下図クリックで拡大
rk-k1
rk-k2


上の表では、一番左に「前期実績」の入力列あります。ここに前期実績値の年計を入れます。
これは、今期の計画を立てる為の参考値にする為です。

その右隣には、「今期目標」を入力する列があります。ここに今期目標値の年計を入れます。
算式を利用して、前期実績値に増減率を掛けて算出しても良いでしょう。
これは、今期の各月の計画を立てる参考値になります。

その右隣からは、月別の計画値を入力する列があります。
算式を利用すれば、今期目標値を各月に配分することもできます。
配分方法は、前期の売上比で配分したり、均等に配分したり、Excelの数式を使えば簡単です。

■前期の売上高で配分
例)=今期目標額 × 前期の各月の売上高 / 前期の売上高の年計

■均等配分
例)=今期目標額 / 12か月

------------------------------------------------------

bizvba_160月別利益計画表(計画)ができましたら、別シートに同じフォーマットで月別利益計画表(実績・予測)を作ります。こちらには、計画値でなく、実績値と予測値を入力します。

経過月には実績値を入力します。
未来月には実績値から予測される値(計画値の修正)を入力しますが、計画通りであれば計画値のままで問題ありません。

下の表は例です。Excelで簡単に作れると思います。
計や利益は、数式を入れて計算させましょう。

月別利益計画表(実績・予測)
rk-g1

上の表では、一番左に「前期実績」の入力列あります。ここに前期実績値の年計を入れますが、既に、月別利益計画表(計画)で入力していますので、数式でイコールにすれば表示できます。

その右隣には、「今期目標」を入力する列があります。ここに今期目標値の年計を入れますが、ここも既に、月別利益計画表(計画)で入力していますので、数式でイコールにすれば表示できます。

その右隣からは、月別の実績値・予測値を入力する列があります。ここに実績値と予測値を入力します。経過月の上には「実績」、未来月の上には「予測」と表示させ、どこまでが実績月か分かるようにします。


bizvba_160ここまでで、月別利益計画表(計画)と、月別利益計画表(実績・予測)ができました。
次に、この二つの表から、月別利益計画表(比較)を作ります。「計画」と「実績・予測」の比較です。
月ごとに、「計画」と「実績・予測」を隣り合わせて比較できるようにします。

下の表は例です。既に、月別利益計画表(計画)と月別利益計画表(実績・予測)でデータを入力していますので、数式でイコールにすれば表示できます。入力の必要はありません。

月別利益計画表(比較)
rk-hk


つづく・・・



 
    このエントリーをはてなブックマークに追加 mixiチェック

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で、その他いろいろな関数を作ってみてください。





    このエントリーをはてなブックマークに追加 mixiチェック

bizvba_160

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

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

1

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

sonekibunki


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

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


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





    このエントリーをはてなブックマークに追加 mixiチェック

bizvba_160

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

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

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

3

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

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

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

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

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


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





    このエントリーをはてなブックマークに追加 mixiチェック

bizvba_160

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

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


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

4

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

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


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

限界利益=固定費


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

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

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

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

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

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

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





    このエントリーをはてなブックマークに追加 mixiチェック

このページのトップヘ