Excelで感度分析を行う方法：ステップ・バイ・ステップ
Excelで感度分析を行うには、モデルの各入力を個別のセルに入力します。次に、それらの入力を変更し、結果がどのように動くかを記録します。1つの入力には単一変数データテーブル、2つの入力には2変数データテーブル、そしてケース全体を比較するにはシナリオマネージャーを使用します。最後に、影響度順に入力をランク付けするトルネードチャートで仕上げます。
このガイドでは、感度分析とは何か、モデルのセットアップ方法、および各Excelツールの使用方法について説明します。実際の数式を使用した実例、AIを活用したより迅速な方法、および避けるべき間違いも含まれています。
感度分析とは何か
感度分析は、1つの仮定が変わったときに結果がどれだけ変わるかを示すものです。「価格が10%下がったら利益はどうなるか？」といった疑問に答えます。
ほとんどのビジネスモデルは推測に基づいています。販売量、価格、コスト、成長率はすべて見積もりです。感度分析は、それらの推測のうちどれが最も重要かをテストします。
Excelでは、これらのツールを「予測」グループの「What-If分析」にまとめています。Microsoftは、これを数式の結果がどのように変化するかを確認するために「セルの値を変更するプロセス」と定義しています。
Microsoftは、3つの組み込みツールとして「シナリオ、ゴールシーク、データテーブル」を挙げています。それぞれ異なる役割があります。
- データテーブルは、1つまたは2つの入力の多くの値にわたる1つの結果を表示します。
- シナリオマネージャーは、ワーストケースやベストケースなど、入力の完全なセットを保存します。
- ゴールシークは逆方向に機能します。目標とする結果に到達するために必要な入力を特定します。
感度分析では通常、最初の2つを使用します。ゴールシークは、損益分岐点価格などの関連する問いに役立ちます。
開始前にモデルをセットアップする
Excelのツールは、モデルがクリーンに構築されている場合にのみ機能します。まず以下の点を確認してください。
すべての入力を個別のセルに入力する。 数量、価格、コスト、率はそれぞれセルが必要です。数式の中に数値を直接入力しないでください。
結果を入力にリンクさせる。 利益などの結果セルは、入力セルを参照する数式である必要があります。そうでない場合、Excelが新しい値を入れ替えても何も変わりません。
入力をまとめておく。 シートの上部の1つのブロックにグループ化します。それぞれにラベルを付け、入力セルに色を付けて目立たせます。
テストする結果を1つ選ぶ。 感度分析は、利益、正味現在価値、年度末の現金など、1つの出力を追跡すると読みやすくなります。
ベースケースをメモする。 開始時の値を書き留めておきます。分析のすべての結果は、このベースと比較されます。
以下は、参考にできるシンプルな利益モデルです。架空の自転車店、Northgate Cyclesの例です。
| セル | ラベル | 値 |
|---|---|---|
| B1 | 販売数量 | 2,400 |
| B2 | 単価 | $850 |
| B3 | 単位あたり変動費 | $510 |
| B4 | 固定費 | $520,000 |
| B5 | 利益 | =B1*(B2-B3)-B4 |
これらのベース値では、利益は$296,000です。
データテーブルで単一変数感度分析を行う方法
単一変数データテーブルは、1つの入力について一定範囲の値をテストします。Microsoftは、これが「1つ以上の数式に含まれる1つの変数の値を変えた場合に、数式の結果がどのように変化するか」を示すとしています。
例のモデルで価格をテストする方法は次のとおりです。
- D2からD6に、テストする価格を入力します：$800、$825、$850、$875、$900。
- E1（最初の価格の1行上で1列右）に、=B5 と入力します。
- D1:E6を選択します。
- 「データ」タブで、「What-If分析」 > 「データテーブル」を選択します。
- 「列の代入セル」に、価格のセルであるB2を入力します。「行の代入セル」は空欄のままにします。
- 「OK」をクリックします。
ExcelはE2:E6に各価格の利益を記入します。数式の配置が重要です。Microsoftは、それを「値の列の1行上で1セル右」に入力するように指示しています。
Northgate Cyclesの結果：
| 価格 | 利益 |
|---|---|
| $800 | $176,000 |
| $825 | $236,000 |
| $850 | $296,000 |
| $875 | $356,000 |
| $900 | $416,000 |
価格が$25変わるごとに、利益は$60,000動きます。これが価格に対する利益の感度です。
2変数感度分析を行う方法
2変数データテーブルは、2つの入力を同時にテストします。Microsoftは、これが「1つの数式に含まれる2つの変数の値を変えた場合に、その数式の結果がどのように変化するか」を示すとしています。
価格と数量を一緒にテストする方法は次のとおりです。
- D10などの空白のセルに、=B5 と入力します。
- その下のD11からD13に、販売数量を入力します：2,000、2,400、2,800。
- その右側のE10からG10に、価格を入力します：$800、$850、$900。
- D10:G13を選択します。
- 「What-If分析」 > 「データテーブル」を選択します。
- 「行の代入セル」にB2を入力し、「列の代入セル」にB1を入力します。
- 「OK」をクリックします。
結果は利益のグリッドになります。
| 数量 \ 価格 | $800 | $850 | $900 |
|---|---|---|---|
| 2,000 | $60,000 | $160,000 | $260,000 |
| 2,400 | $176,000 | $296,000 | $416,000 |
| 2,800 | $292,000 | $432,000 | $572,000 |
このグリッドは、テストされたすべての組み合わせでショップが黒字を維持することを示しています。最も弱い角である「$800で2,000台」でも、$60,000の利益が出ます。
1つ厳しい制限があります。Microsoftは「データテーブルには2つ以上の変数を収容できない」としています。より多くの入力については、シナリオマネージャーを使用してください。
また、データテーブルはシートが再計算されるたびに再計算されます。大規模なワークブックでは、Excelの動作が遅くなる可能性があります。Microsoftは、F9キーを押したときにのみデータテーブルが再計算されるように計算設定を行うことを推奨しています。
シナリオマネージャーでフルケースを比較する方法
シナリオマネージャーは、入力の完全なセットを保存し、それらを入れ替えます。Microsoftはシナリオを「Excelが保存し、ワークシート上で自動的に置き換えることができる値のセット」と説明しています。
3つのケースをセットアップする方法は次のとおりです。
- 「データ」タブで、「What-If分析」 > 「シナリオマネージャー」を選択します。
- 「追加」をクリックし、シナリオ名を「ベース」とし、変化させるセルとしてB1:B4を入力します。現在の値を保持します。
- 「ワーストケース」を追加します：数量2,000、価格$825、変動費$530、固定費$540,000。
- 「ベストケース」を追加します：数量2,800、価格$875、変動費$495、固定費$510,000。
- シナリオを選択し、「表示」をクリックしてその値を読み込みます。
- 「情報」をクリックし、結果セルとしてB5を選択してレポートを作成します。
Northgate Cyclesの場合、要約には3つの利益が表示されます。ワーストケースは$50,000、ベースケースは$296,000、ベストケースは$554,000の利益となります。
Microsoftはいくつかの制限を指摘しています。各シナリオは最大32個の変化させる値を保持できます。要約レポートは自動ではありません。「シナリオレポートは自動的に再計算されません」。シナリオを変更した場合は、新しい要約を作成してください。
ゴールシークで損益分岐点を見つける方法
感度分析は結果がどこまで動くかを示します。ゴールシークは逆の問いに答えます：目標とする結果を得るための入力値は何ですか？
Microsoftは、ゴールシークは「結果を受け取り、その結果を生み出す可能性のある入力値を決定する」としています。その例として、ローン支払いに必要な金利を見つけることを挙げています。
Northgate Cyclesの損益分岐点価格を見つける方法は次のとおりです。
- 「データ」タブで、「What-If分析」 > 「ゴールシーク」を選択します。
- 「数式入力セル」に、利益セルであるB5を入力します。
- 「目標値」に0を入力します。
- 「変化させるセル」に、価格セルであるB2を入力します。
- 「OK」をクリックします。
Excelは利益がゼロになるまで価格を変更します。2,400台でベースコストの場合、損益分岐点価格は約$726.67です。これは現在の$850の価格より約15%低いです。
ゴールシークには1つの制限があります。Microsoftは、これが「1つの変数入力値のみで動作する」としています。複数の入力については、Microsoftはソルバーアドインを案内しています。
トルネードチャートの作成方法
トルネードチャートは、結果をどれだけ動かすかによって入力をランク付けします。これは感度分析を提示する最も明確な方法です。
作成方法は次のとおりです。
- 各入力を10%低く、10%高くするなど、テスト範囲を決めます。
- 一度に1つの入力を変更し、低い結果と高い結果を記録します。他の入力はベースのままにします。
- 結果を入力ごとに1行のテーブルにまとめます。
- 範囲が最も広い入力が一番上に来るように行を並べ替えます。
- 低い結果と高い結果の棒グラフを挿入します。
Northgate Cyclesで、各入力を10%上下に動かした場合：
| 入力 | −10%時の利益 | +10%時の利益 | 範囲 |
|---|---|---|---|
| 価格 | $92,000 | $500,000 | $408,000 |
| 変動費 | $418,400 | $173,600 | $244,800 |
| 販売数量 | $214,400 | $377,600 | $163,200 |
| 固定費 | $348,000 | $244,000 | $104,000 |
価格が圧倒的に重要です。10%の価格下落により、利益は$296,000から$92,000に減少します。固定費の影響が最も小さいです。これにより、オーナーはどの仮定のチェックに時間を費やすべきかがわかります。
Excelの棒グラフは左から右に棒を描きます。トルネードの形にするには、生の数値ではなく、ベース利益からの各入力の変化をプロットします。そうすると、低いケースの棒は左を向き、高いケースの棒は右を向きます。
AIで感度分析を行う方法
大規模なモデルに対してデータテーブル、シナリオ、トルネードチャートを作成するには時間がかかります。AIツールを使用すれば、ワークブックから同じテストを実行できます。
ここでは、Powerdrill Bloomを使用した3つのステップの方法を紹介します。
ステップ1：モデルをアップロードする
モデルが含まれているスプレッドシートから始めます。AI Sensitivity Analysis Generatorは、「DCF、ビジネスケース、運用計画、またはあらゆるスプレッドシートモデル」をアップロードするように求めます。
入力と結果のセルに明確なラベルが付いていることを確認してください。ツールは、どの数値が仮定であるかを知る必要があります。
ステップ2：自然言語で感度ビューを選択する
実行したいテストを説明します。例：「各入力を10%上下に動かした利益のトルネードチャートを表示してください。価格と販売数量の2元表も追加してください。」
ツールページには、「トルネード、2元、モンテカルロ、または損益分岐点」の4つのビューがリストされています。トルネード出力は「答えを最も動かす入力をランク付けする」チャートとして説明されています。また、「重要な2つの仮定にわたる感度テーブル」も提供されます。
ステップ3：結果を確認して分析をエクスポートする
共有する前に、1つか2つの結果を自分のデータテーブルと比較してください。ベースケースはモデルと正確に一致する必要があります。
その後、エクスポートします。ツールページには「Excelワークブック、ブランド化されたPDF、またはキャンバスリンク」がリストされています。プレゼン資料用には、Excel to PPTツールが同じファイルをスライドに変換します。AI Financial Modelingページでは、モデル自体の構築について説明しています。
結果の読み方と提示方法
感度分析は、人々がそれに基づいて行動して初めて役に立ちます。以下の習慣が役立ちます。
ランキングから始める。 トルネードチャートから始めましょう。どの仮定が結果を左右するかを1つの図で示せます。
範囲を意思決定に変換する。 「価格が10%下がると利益は$92,000に落ちる」というのは事実です。「リースに署名する前に価格をテストすべきだ」というのが意思決定です。
損益分岐点を示す。 分析と、結果がゼロになる値をペアにします。Northgate Cyclesの場合、損益分岐点はベースの価格とコストで約1,530台です。当社のExcelでの損益分岐点分析ガイドでその計算方法を説明しています。
現実的な範囲を使用する。 10%の変動は安定した入力に適しています。新製品の数量には、より広い範囲が必要になる場合があります。
何を一定に保ったかを明記する。 一度に1つずつのテストでは、他の入力はベースのままです。スライドや脚注でその旨を伝えてください。そうすれば、読者は10%の価格変更が同じ数量、コスト、固定費を前提としていることを理解できます。この一行があるだけで、手法に関するほとんどの追加質問を防げます。
結果が出た後に再確認する。 実際の数値が出たら、それを自分の範囲と比較してください。予算対実績レポートは自然なフォローアップになります。
よくある感度分析の間違い
これらの間違いはよくありますが、簡単に避けられます。
数式内のハードコードされた数値。 数式にB2ではなく850が含まれている場合、データテーブルはそれを変更できません。すべての入力をセルに保持してください。
一度に多すぎる入力をテストする。 データテーブルは1つか2つを扱います。それ以上の場合は、シナリオマネージャーまたはトルネードチャートに移行してください。
すべての入力に同じ範囲を使用する。 価格と数量が同じ不確実性を持つことは稀です。実際に知っていることを反映した範囲を選択してください。
入力間のリンクを無視する。 価格を下げると数量が増える可能性があります。一度に1つのテストではそれを見逃すため、両方を動かすシナリオを追加してください。
古いシナリオの要約。 シナリオレポートは自動的に更新されません。変更後は再作成してください。
ベースケースを忘れる。 すべてのテーブルにベースの結果を表示し、読者が各変化の大きさを確認できるようにすべきです。
次のモデルをストレスリサーチする準備ができたら、計画に使用しているワークブックでPowerdrill Bloomを試してみてください。
よくある質問
Excelでの感度分析とは何ですか？
入力が変わったときに結果がどのように変わるかをテストする方法です。Excelでは、What-If分析ツール（主にデータテーブルとシナリオマネージャー）を使用して、新しい入力値を入れ替えて結果を記録します。
Excelで単一変数感度分析を行うにはどうすればよいですか？
入力値を列にリストします。結果を参照する数式を1行上で1列右に配置します。範囲を選択し、「What-If分析」 > 「データテーブル」を選択して、入力セルを「列の代入セル」として入力します。
データテーブルとシナリオマネージャーの違いは何ですか？
データテーブルは、多くの値にわたる1つまたは2つの変数をテストし、すべての結果を1つのグリッドに表示します。シナリオマネージャーは、それぞれ最大32個の変化させる値を含む入力の完全なセットを保存し、一度に1つずつ入れ替えます。
Excelでトルネードチャートを作るにはどうすればよいですか？
各入力を一度に1つずつ同じ量だけ上下に動かし、結果を記録します。範囲の大きさで入力を並べ替え、ベース結果からの各入力の変化を横棒グラフとして表示します。
AIは感度分析ができますか？
はい。AIツールはスプレッドシートモデルを読み取り、トルネードテストや2元テストを実行し、結果をワークブックやレポートとしてエクスポートできます。それでも、ベースケースを確認し、現実的な範囲を選択する必要があります。
出典： Microsoft Support, Introduction to What-If Analysis · Microsoft Support, Calculate multiple results by using a data table · Microsoft Support, Switch between various sets of values by using scenarios.