Excelで感度分析を行う方法
Excelで1変数と2変数のデータテーブルを作り、どの前提条件が財務上の判断を左右するかを確認する手順を解説します。
- 1変数の表で数量、2変数の表で価格と数量の組み合わせを検証します。
- 中央と四隅のうち1か所の値をモデルと照合します。
- 例では単価100ドルの損益分岐点は875個です。
営業利益5,000ドルの予算で、どの程度の値下げや販売目標の未達に耐えられるでしょうか。感度分析は同じモデルを異なる前提で再計算します。このガイドではExcelで1変数と2変数の表を作り、基準値の照合と損益分岐点の確認を行います。
感度分析とは?
感度分析は、指定した入力を変えたときにモデルの出力がどれくらい変わるかを測ります。ベースケースから始め、検証したい入力だけを動かし、その他の前提は固定します。出力の差が、検証した変更に対する感度です。
例えば販売単価100ドル、販売数量1,000個の予算では、価格と数量が入力、営業利益が出力です。1変数の検証では単価100ドルで数量だけを変え、2変数の検証では価格と数量の組み合わせを比較します。
同じ方法を資金繰り予測や企業価値評価にも使えます。売掛金の回収が遅れたときの月末現金残高や、割引率が変わったときの正味現在価値を確認できます。入力範囲は検討している意思決定に合わせます。
感度分析とシナリオ分析の違い
感度分析では、価格や販売数量が指定範囲内で変動したときの出力を調べます。シナリオ分析では、同時に起こり得る前提をまとめて評価します。販促シナリオなら値下げ、販売増加の見込み、追加の販促費を組み込みます。感度分析の表で個別の影響を見て、シナリオで計画全体を評価します。
四半期終了後は、予実差異分析で実績利益と予算の差を調べられます。目標値が決まっている場合は、Excelのゴールシークで入力を1つ逆算できます。価格を固定して営業利益が0ドルになる販売数量を求める場合などです。
感度分析の入力と範囲の決め方
まず、検討している決定と、その判断を左右する出力を把握します。値引きの承認なら営業利益と損益分岐点の販売数量、資金繰りなら最低現金残高が重要です。
基準値、検証範囲の上下限、その根拠、確認担当者を記録します。価格は最近の見積もりや承認済みの値引き方針、数量は比較可能な期間の実績や受注、販売予測を参考にします。対象期間と単位をそろえ、率の変化は相対変化かポイント差かを明記します。10%に2ポイントを足すと12%、10%を2%増やすと10.2%になります。
例では単価95〜105ドル、四半期販売数量800〜1,200個を検証します。費用は基準の想定で固定します。実際の予算では、数量が生産能力の上限や仕入先の価格帯をまたいで費用が変わらないか確認します。
Excelで感度分析を実行する方法
デスクトップ版Excelの同じワークシートに、以下の前提と営業利益の数式を入力します。
| セル | モデル項目 | 値または数式 |
|---|---|---|
B2 | 販売単価 | 100 |
B3 | 四半期販売数量 | 1000 |
B4 | 単位当たり変動費 | 60 |
B5 | 四半期固定費 | 35000 |
B6 | 四半期営業利益 | =(B2-B4)*B3-B5 |
B6 のベースケースは 5,000ドル です。両方の表でこのセルを出力として参照します。
1変数のデータテーブルを作る
単価を100ドルに固定して、販売数量から検証します。
=$B$6にE3を入力します。800に1000、1200、D4:D6を1行ずつ入力します。D3:E6を選択し、データ → What-If 分析 → データ テーブル を開きます。- 列の代入セル に
$B$3を指定し、行の代入セル は空欄のまま OK を選びます。
Excelは E4:E6 に −3,000ドル、5,000ドル、13,000ドル を表示します。1,000個の値は基準利益5,000ドルと一致します。この単価では1個増えるごとに固定費の回収と利益に回せる金額が40ドル増えます。
2変数のデータテーブルを作る
価格を上の行に、数量を左の列に並べます。
=$B$6にH3を入力します。95に100、105、I3:K3、800に1000、1200、H4:H6を入力します。H3:K6を選択し、データ → What-If 分析 → データ テーブル を開きます。- 上の価格に対応する 行の代入セル に
$B$2、左の数量に対応する 列の代入セル に$B$3を指定し、OK を選びます。
価格と販売数量別の四半期営業利益.
| 四半期販売数量 | 単価95ドル | 単価100ドル | 単価105ドル |
|---|---|---|---|
| 800 | −$7,000 | −$3,000 | $1,000 |
| 1,000 | $0 | $5,000 | $10,000 |
| 1,200 | $7,000 | $13,000 | $19,000 |
中央の値を B6 と照合し、隅の値を手計算します。単価95ドルで800個なら1個当たりの限界利益は35ドルです。固定費控除前は28,000ドル、控除後は −7,000ドル です。最後に100ドルの列を1変数の表と照合します。こうすると出力参照の誤り、入力の逆転、再計算漏れを見つけやすくなります。
感度分析表の読み方
1,000個のまま単価を100ドルから95ドルに下げると、基準利益5,000ドルがなくなります。単価100ドルのままなら 875個 で利益がゼロです。固定費35,000ドルを単位当たり限界利益40ドルで割って求められます。単価95ドルでは限界利益が35ドルになり、損益分岐点は 1,000個 に上がります。
表は指定した組み合わせごとの結果です。値下げで数量が増える想定なら、見込みの根拠と追加販売に対応できるかを確認します。現在の費用条件で単価を95ドルに下げ、利益5,000ドルを維持するには 少なくとも1,143個 が必要です。追加費用や過去の販促実績に基づく見込みをシナリオに加えられます。
経営向けの資料には次のように記載できます。
単価95ドル、販売数量1,000個のとき、四半期利益は5,000ドルから0ドルに下がる。現行の費用条件で5,000ドルを維持するには1,143個以上が必要。値引き承認前に過去の販促実績と初期受注で見込みを確認する。
ブックの版と検証範囲の根拠を保管します。確認済みの表、前提メモ、根拠資料から、Kuseで感度分析表を意思決定資料にまとめられます。ベースケース、判断が変わる境目、今後必要な根拠を記載します。モデル担当者が数字と記述をExcelと照合し、次回も同じ書式を使います。
よくある質問
Excelの感度分析表がすべて同じ値になるのはなぜですか?
入力を1つ手動で変えて結果が動くか確認し、基準値に戻します。出力が変わるのに表が変わらないなら、出力と入力セルの参照先を見直します。自動再計算の対象外であれば、表の数式を選択して再計算します。
感度分析表のベースケースがモデルと異なるのはなぜですか?
表の見出しが基準値と厳密に一致するか確認します。丸め表示が同じでもExcelに渡す入力は異なることがあります。出力参照を照合して再計算し、検証対象外の入力も確認します。
既存のデータテーブルの入力セルはどこで確認できますか?
計算セルを選択して数式バーを見ます。デスクトップ版Excelでは数式に行と列の代入セル参照がこの順に表示されることがあります。参照先の前提が出力につながっているか確認します。
2変数のデータテーブルとゴールシークはどう使い分けますか?
価格と数量ごとの利益など、2つの前提の組み合わせを比較するときは2変数の表を使います。目標の出力から1つの入力を求めるときはゴールシークを使います。例えば固定価格で利益が0になる数量を求められます。
感度分析に適した入力はどう選びますか?
現行または承認済みのベースケースから始め、過去実績や契約条件、見積もり、予測の前提などで上下限を決めます。意思決定の境目に近い値を含め、検討用の値には印をつけます。
感度分析表で損益分岐点をどう見つけますか?
利益ゼロのセルか、隣り合うセルで符号が変わるところを探します。表の値の間に分岐点があれば、数式かゴールシークで求めます。数量は必要に応じて整数に切り上げ、利益をモデルで確認します。