固定費と変動費の見積もりをせずに損益分岐点分析を行うには？

浦和税理士法人
松波 竜太

2006/12/12

　損益分岐点分析を行う場合、勘定科目ごとに費用を変動費・固定費に分けて求める「勘定科目法」が最もポピュラーな方法として使われています。しかし、勘定科目ごとに変動／固定を分ける際に見積もりに頼らざるを得ないため、計算結果に対する信頼性がこの方法だけだといま一つということになります。

　一方で、今回説明する「最小自乗法」は見積もり計算を行わないため、一定条件を満たす場合には、計算結果は最も高い精度で求めることができます。

　最小自乗法は多少の数学的知識を持っていないと計算ができなかったため、従来はこの方法を使って、損益分岐点分析を行うことは少なかったのですが、現在はExcelのグラフ作成機能を使って、簡単に計算ができるようになりました。数期間分のデータを用意して、売上高をx軸に、経常利益をy軸に取って散布図を作成し、近似曲線を引くだけです。関数を使って、算式を求めることも可能ですが、この方法が最も簡単です。

　それでは、具体例を用いて方法を見てみることにしましょう。

月次推移データを取り出す

［ステップ1］ 時系列データを用意する

　まずは、会計ソフトごとに月次推移データの取り出し方を見てみましょう。

（1）弥生会計

1. ［集計表］−［残高推移表］−［年間推移］を選択
2. メニューバーの［Excelへの書き出し］ボタンを押す
3. 「新規Excelファイルに書き出す」の損益計算書をチェックして、保存場所を任意に指定して、ファイル名を指定（例：suii.txt）して［OK］ボタンを押す

（2）勘定奉行

1. ［分析処理］−［推移表］−［科目別推移表］を開く
2. 条件設定画面から［印刷・転送］ボタンを押す
3. 月範囲を指定（当期）して、［転送］ボタンを押す
4. 転送条件は「他ソフト編集」「タブ区切り」を指定して「実行」を押す（保存場所は任意、ファイル名は例えば：suii.txt）

（3）PCA会計

1. ［分析処理］−［月次推移表］を選択
2. ［集計期間］を選択
3. ［印刷］ボタンを押す
4. 「汎用データ」選び、損益計算書をチェックして、保存場所を任意に指定して、ファイル名を指定（保存場所、ファイル名は勘定奉行と同じ）し、［OK］ボタンを押す

［ステップ2］ 売上高と経常利益のみを抽出する

　弥生会計06からのデータを基に説明したいと思います。［ステップ1］の結果、下図のようなExcelファイルが作成されます。

画面1　弥生会計06のデータを取り込んだExcelシート（クリック >> 画像拡大）

　ここで、計算に必要なのは、売上高と経常損益金額だけですので、ほかの行列を削除します。また、12月以降はデータが入っていませんので、これらの列も削除します。

　結果、下のような結果となります。

画面2　必要なデータにまとめる（クリック >> 画像拡大）

［ステップ3］ グラフウィザードを使って散布図を作成する

　次に、グラフウィザードを使って、売上高をx軸・経常損益金額をy軸とした散布図を作成します。

　まずは、セルA9からI10の範囲をドラッグして、グラフ作成のボタンを押します。すると、「グラフウィザード1/4」が表示されますので、［グラフの種類］から散布図を選び、［次へ］ボタンを押します。

画面3　散布図を作成する（クリック >> 画像拡大）

　「グラフウィザード2/4」では系列が「行」になっていることを確認して、［次へ］ボタンを押します。

画面4　散布図のグラフウィザード

　「グラフウィザード3/4」では、グラフタイトルに「限界利益」、x軸に「売上高」、y軸に「経常損益金額」と入力し、［次へ］ボタンを押します。

画面5　散布図のグラフウィザード

　「グラフウィザード4/4」では、オブジェクトを選択して［完了］ボタンを押します。

画面6　散布図のグラフウィザード

　結果、下の図のようなグラフが作成されます。

画面7　作成された散布図

［ステップ4］ 近似曲線を追加する

　プロットされたデータの近似曲線を追加します。まず、グラフ上の任意の一点上で右クリックをして、ショートカットメニューを表示させ、「近似曲線の追加」を選択します。

画面8　グラフ上で右クリック

　すると、「近似曲線の追加」ダイアログボックスが立ち上がりますので、種類タブでは「線形近似」を選択します。

画面9　「近似曲線の追加」ダイアログボックス

　次に、「オプション」タブをクリックして、「グラフに数式を表示する」「グラフにR-2乗値を表示する」のチェックボックスをオンにして、［OK］ボタンを押します。

画面10　「近似曲線の追加」ダイアログボックス

　すると、下のような直線とその数式が表示されます。

画面11　散布図に直線と数式が表示される

［ステップ5］ 数式の表示形式を整える

　ここで、使用したいのは「y=0.3785x−8E＋06」という算式です。ただし、このままでは「8E＋06」部分の数値が読めないので使えません。計算はきちんとされているのですが、表示形式が適切でないために、このような値となってしまうのです。そこで、この算式の表示形式を適切なものに修正します。

　「y=0.3785x−8E＋06」で右クリックしてショートカットメニューを表示させ、「データラベルの書式設定」を選択します。

画面12　数式上で右クリックする

　「データラベルの書式設定」ダイアログボックスが開きますので、「書式設定」タブを選び、下記のように設定し、［OK］ボタンを押します。

　分類：数値
　小数点以下の桁数：3
　桁区切り(,)を使用する：on

画面13　「データラベルの書式設定」ダイアログボックス

　すると、先ほどアルファベット交じりだった部分が数値として表示されます。

画面14　散布図の数式が修正される

［ステップ6］ 求められた数式の各項の意味

　ここで、求められた数式の意味するところは以下のとおりです。

経常利益 ＝ 限界利益率 × 売上高 − 固定費 y ＝ 0.378 × x − 7,964,902

　このように、限界利益率を簡単に求められるわけです。

　さらに、

変動比率＝1−限界利益率 損益分岐点売上高＝固定費÷限界利益率

という算式を使いますと、

変動費率 ＝1−0.378 ＝0.622 損益分岐点売上高 ＝7,964,902÷0.378 ＝21,071,169

が、求められます。

　ちなみに、この損益分岐点売上高はx切片です。

【補足】 勘定科目法と最小自乗法のメリットとデメリット

　冒頭で、「一定条件を満たす場合には、計算結果は最も高い精度」と断ったことに対する説明を、最小自乗法を使った場合のメリット・デメリットからしてみたいと思います。最小自乗法の最大の欠点は、固定費の増減額が変動費として認識されてしまう可能性があるところにあります。つまり、売上とともにたまたま家賃の値上げがあった場合には、その増加分が変動費に含まれて計算されてしまう可能性があるということになります。

　以下に、勘定科目法と最小自乗法のメリット・デメリットをまとめてみました。要するに、勘定科目法と最小自乗法のメリット・デメリットを理解したうえで、双方を組み合わせて補完することにより、より精度の高い計算結果が得られるということがいえましょう。

メリット デメリット 勘定科目法 ・ 1期間分のデータがあれば求められる ・ 科目ごとの見積もりを人為的にするため、思い込みによる計算間違いが起こる可能性がある 最小自乗法 ・ 計算を数学的に行うため、思い込みによる計算間違いが防げる ・ 数期間分のデータがなければ計算できない ・ 固定費の増減が変動費として計算されてしまう可能性がある

「ビジネスソフト ヒント×テクニック」 − INDEXページへ