Excelで作る経営計画

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

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

損益分岐点売上高、目標達成売上高 をユーザー定義関数にして求めることも可能だ。

次のようにユーザー定義関数を作ってみた。

例えば、限界利益率が12%の時、率区分を1にした時は少数で入力し(例:0.12)、率区分を1以外の数値または省略した時は%の値(例:12)を入力できるようにしている。

損益分岐点売上高を求める =損益分岐点売上高(固定費, 限界利益率, 税区分)

Function 損益分岐点売上高(ByVal 固定費 As Long, ByVal 限界利益率 As Double, Optional ByVal 率区分 As Long = 0)
    '率区分=1 だったら少数で入力、1以外だったらパーセントで入力
    
    If 率区分 = 1 Then
        損益分岐点売上高 = Int(固定費 / 限界利益率)
    Else
        損益分岐点売上高 = Int(固定費 / (限界利益率 / 100))
    End If
    
End Function

9

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

損益分岐点売上高を求める =目標達成売上高(目標利益, 固定費, 限界利益率, 税区分)
 
Function 目標達成売上高(ByVal 目標利益 As Long, ByVal 固定費 As Long, ByVal 限界利益率 As Double, Optional ByVal 率区分 As Long = 0)
    
    If 率区分 = 1 Then
        目標達成売上高 = Int((固定費 + 目標利益) / 限界利益率)
    Else
        目標達成売上高 = Int((固定費 + 目標利益) / (限界利益率 / 100))
    End If
    
End Function

10


ユーザー定義関数の引数は、セル参照で指定することも可能だ。

ユーザー定義関数を作ることで簡単に計算できるようになり、また、セルに入力する算式も短くなるメリットがある。これを、アドインファイルとして保存しておけば、いつでも使用できる。


この情報が役に立ったかどうか  ↓ facebook 「いいね!」 で教えてください。



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

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

1

「固定費+変動費」と「売上高」の交点が、損益分岐点ですね。

(注意)
データ表の上部、緑色の売上高は、横軸のメモリを表示する為のものです。
折れ線グラフが、このように表示されない場合は、まず、表の選択範囲を、「目標達成売上高」だけにしてグラフを表示させてから、表の選択エリアを「固定費+変動費」まで広げてみてください。
もし、グラフの横軸のゼロと、縦軸のゼロが一致しない場合の対応方法


この情報が役に立ったかどうか  ↓ facebook 「いいね!」 で教えてください。



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

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

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

3

ストラック図を見ると分かるように、限界利益-固定費 がプラスになれば経常利益が出ます。
目標達成売上高とは、経常利益が目標額(目標経常利益)になった時の売上高です。

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

損益分岐点売上高を求める式は、損益分岐店売上高=固定費/限界利益率 でした。

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


この情報が役に立ったかどうか  ↓ facebook 「いいね!」 で教えてください。



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

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

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

4

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

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


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

限界利益=固定費


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

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


よって、

損益分岐点売上高を求める式は、
損益分岐店売上高=固定費/限界利益率
になります。


以上


この情報が役に立ったかどうか  ↓ facebook 「いいね!」 で教えてください。



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

損益分岐点売上高とは

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

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

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

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

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

1

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


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

2


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

3

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

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

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

4

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

5

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

6


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



この情報が役に立ったかどうか  ↓ facebook 「いいね!」 で教えてください。





記事検索
Excel VBA 担当 Hide Tamura
■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
Excelで作る経営計画 目次


Excel 関数 ノート
Excel関数を中心に、Excelの便利機能や、意外としらない使い方など書いていきます。

Excel 関数 ノート
Excel & Access VBAノート
Hide Tamura の個人的なVBAノートです。

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

ExcelVBAノート

AccessVBAノート
アクセスカウンター

    • ライブドアブログ