無料VBA講座

目標利益に数値を入れると、必要売上高が表示される。
セル内関数
必要売上高 = IF ( I21 = "","" , ( M10 + I21) / L11)
必要売上高 = IF ( 目標利益 = "","", ( 固定費 + 目標利益 ) ÷ 限界利益 )

※数値表内は、各セルの値を関数によって取得しているので、グラフも変化する。

●シミュレーション画面を見てみよう

上記表は、Sheet1の基本数値を基に記入されたものである。
商品単価、単価に対する変動費、固定費、単価に対する限界利益である。
尚、金額の単位は千円である。

現在、売上個数が36個の場合、利益は70,000円、売上高は1,800万円である。
売上個数が35個なら50,000円の赤字になる。
目標利益を100万円にすると、必要な売上高は2,187万5千円になる。
その場合、売上個数が44個で売上高2,200万円、利益が103万円になる。

※44個の売上が見込めない場合、何をどうすれば良いのかを、固定費の削減、変動費の削減、
　販売単価などの数値を変更して、損益分岐点売上を下げる方策を考える必要がある。

●各計算式

限界利益とは、売上から変動費を差し引いたもので、固定費に利益を足したものと
考えられる。つまり、限界利益によって、固定費を回収した残りが利益になります。
限界利益率は、売上高に対する限界利益の割合で表し、限界利益率が高いほど収益性が
良好と言える。

固定費とは、売上高の増減に関係なく発生する固定的な費用である。
変動費とは、売上高の増減に応じて変動する費用である。
変動費率は、売上高に対する変動費の割合である。

目標利益達成に必要な売上高は、限界利益率を高くすれば、目標利益達成に必要な
売上高が相対的に低くなります。

[系列値]で表の分岐点 O17から O23を選択し[OK]を押すと、グラフに横線と凡例に分岐点が
追加される。

分岐点の線を選択して、右クリックし、出たダイアログの[データ系列の書式設定]を
クリックする。

[色]と[実線/点線]の中から選択して選ぶ。

次に、売上高と売上個数の軸ラベルを追加してみましょう。
まず、グラフを選択して、

[グラフのデザイン][グラフの要素の追加]を押し、出たリストから[軸ラベル][第一横軸]を
選択する。

[軸ラベル]がグラフに追加された。[軸ラベル]で[第一縦線]を選択すれば、売上金額の
ラベルを作成できる。
尚、[軸ラベル]の文字にカーソルを入れて[売上個数]に変更し、ドラグして好きな位置に
配置すればよい。

●それではシミュレーションするSheet2を見てみましょう。

上記が全体である。見えにくいので、左右に分割してみよう。
左側にグラフがあり、右下にデータ表がある。

これが、損益分岐点のグラフである。
固定費は、売上高が変化しても変わらないので横一線になる。
総経費は、売上数量によって固定費の上に変動費が加算されるので、固定費の線から
右上に伸びてゆく。
売上個数が増えると変動費も増加するので、総経費が増加する。
売上高は、0から始まり売上個数と共に右上に伸びてゆく。
売上高と総経費が交差した点が損益分岐点である。
下の、損益分岐点売上高の枠内にその金額が表示される。

ここで、分岐点の横線の追加方法を説明しよう。

グラフの[総経費]の線を選択して、右クリックし、[データ選択]をクリックする。

出たダイアログの[追加]を押す。

系列の編集が出るので、[系列名]の右の矢印を押し、表内の[分岐点]を選択すると
位置が挿入される。。]

表示されたグラフの、販売数量の数値1,2,3を10,20,30に修正しましょう。
グラフ全体を選択して、右クリックしその中の、[データ選択]をクリックする。

売上を選択して、右の編集をクリックします。

[軸ラベル]のダイアログが出る。[軸ラベルの範囲]の右矢印を押して、販売シミュレーションの
数値の部分を選択すると、[軸ラベルの範囲]に範囲が入力される。[OK]を押して戻り
[データソースの選択]を[OK]を押して終了する。

販売数が変更された。
次に売上高の上限が35,000になっているから、30,000に変更しよう。
グラフの売上額の数値全体を選択して、右クリックする。

まず、B8からB15までを選択して、その後キーボードの[Ctrl]キーを押しながら、D8からD15まで、
E8からE15までを選択します。[Ctrl]キーを放しても選択されたままになります。
[挿入]をクリックし、[おすすめグラフ]を押し、[すべてのグラフ]を選択する。

[折れ線]の中に、[損益分岐点グラフ]が存在するので選択する。
※ここで重要なことは、データの選択で、まず B8から B15 までを選択して、その後に
　[Ctrl]キーを押しながら、D8 から D15 まで、E8 から E15 までを選択することです。
　最初から、[Ctrl]キーを押して三列を選択すると、損益分岐点グラフは出てきません。
　これは、Excel2007とExcel2016で発生します。Excel2019ではこの現象は発生しないので、
　初めから[Ctrl]キーを押して選択しても、損益分岐点グラフは出来ます。

尚、上部には損益分岐点グラフのグラフスタイルが表示されます。

又、グラフには2種類あって、普通のグラフとスパークライングラフがありますが、
普通のグラフを選択してください。スパークラインはセルにグラフを表示する時に
使用します。

●上記がシミュレーション画面である。
基本になるデータ表と各数値に増減するボタンが付いている。
どの数値のボタンを増減しても、関連する項目の数値が変化し、それがグラフと関係していれば
グラフの分岐点の位置も変化する。
実は、今表示されている画面は、Sheet2である。基本になる表は、Sheet1に書かれている。

(Sheet1の表とグラフ)

下記が基本となる数値表である。
シミュレーションをSheet2で行う為に、基本の数値に戻すことができるように、
商品単価、固定費の詳細、変動費の詳細が書かれている。
変動費と固定費に設置している[取得]ボタンでSheet1の基本数値を取得することができる。
基本の数値では、損益分岐点売上高は17,70万8千円になっている。

上記は、損益分岐点の分析をするためのExcelファイルのシミュレーション画面である。
構成は、売上高、固定費、変動費の3つの数値である。各数値を増減させることにより
グラフが変化して、分岐点を示す。
売上額は、どれだけの価格の商品を、何個売ったかでその額が表される。
固定費は、人件費や経費の合計値である。
変動費は、商品の仕入価格や材料費、外注加工費である。

※健全な会社は、利益の上がっている会社である。利益が上がらないまま会社を運営し続けると
　赤字が増大し、いずれは経営破綻を起こす。そこで、現在の売上高と固定費、変動費を把握し
　どのように各数値を改善すれば、業績がどう変化するのかを知ることは重要である。
　損益分岐点とは、売上と費用が同じで利益がゼロの状態をいう。つまり、売上高が損益分岐点
　売上高より下回るときに赤字になり、上回れば利益が出ることになる。

上記が損益分岐点グラフである。固定費線は一定位置にあり、売上線と総費用線が交わった
位置が損益分岐点である。その損益分岐点売上高が表示されている。