Excel の What-If 分析をマスター:ビジネス シナリオを予測およびモデル化するための 3 つの実績のあるテクニック

 

What-if 分析は、重要な決定を下す前に不確実性を調査し、さまざまなシナリオをテストするための強力なアプローチです。 「価格が上昇したらどうなるか?」など、ビジネスや研究の成果を達成するのに役立ちます。または「コストが下がったらどれくらいの利益が得られるでしょうか?」 Excel の What-If 分析ツールを使用すると、数式を書き直さずにさまざまな可能性を探ることができます。

このチュートリアルでは、Excel でシナリオを予測およびモデル化するための 3 つの What-If 分析手法を示します。

次の 3 つの重要な What-If 分析手法を検討します。

• ゴールシーク: 逆算して特定の結果に必要な入力を見つける
• データテーブル: 1 つまたは 2 つの変数が結果にどのような影響を与えるかを分析する
• シナリオ マネージャー: 複数の仮定セットを保存して比較する

1.ゴール シーク:望ましい結果のための入力を見つける

ゴール シークは、リバース エンジニアリング モデルに最適です。単一の入力値を調整して特定の出力を実現し、「望ましい結果を達成するにはどのような入力値が必要ですか?」などの質問に答えるのに役立ちます。

ゴール シークでは、式に依存するセル (出力) が目的の値に達するまで、反復を使用して 1 つのセル (入力) を変更します。単純な最適化には最適ですが、変数は 1 つに制限されます。

ゴール シークを使用する場合:

• 利益目標を達成するために必要な販売量を決定する
• ローン支払いの制約を満たすために必要な金利を計算する
• 製品またはサービスの損益分岐点を見つける
• 特定の成績を達成するために最終試験で必要なスコアを特定する

調べてみましょう:「20,000 ドルの利益を得るには何個売らなければなりませんか?」

手順:

• Excel でモデルを設定します（たとえば、製品の詳細や利益計算式を入力します）
• 利益を計算するために次の式を挿入します

• データに移動します。 タブ>> What-If 分析を選択します>> ゴール シークを選択します

• ダイアログボックス内:
  • セルを設定: 出力セル (例:E2、利益計算式) を選択します
  • 価値観: 目標出力を入力します (例:目標利益 20000)
  • セルを変更する場合: 調整する入力セルを選択します (例:B2) 、販売単位）
• [OK] をクリックします。

• Excel は、目標利益が達成されるまで、自動的に売上高の調整を繰り返します

洞察: 既知の目標 (損益分岐点や売上目標など) から計算式を逆算する場合は、ゴール シークを使用します。

• 長所: 単一変数の問題を迅速かつ簡単に解決
• 短所: 複数の変数を処理しません。モデルが複雑な場合は収束しない可能性があります

2.データテーブル:1 つまたは 2 つの変数が結果にどのような影響を与えるかを確認します

データ テーブルを使用すると、1 つまたは 2 つの入力変数の変更が結果にどのような影響を与えるかを確認でき、すべての可能性を 1 つのテーブルに表示できます。これにより、潜在的な結果の範囲を示す感度分析が作成されます。

• 1 変数テーブル: 1 つの入力を行または列にわたって変化させます。単一の入力に対してさまざまな値をテストする
• 2 変数テーブル: 2 つの入力 (1 行、1 列) を変化させて行列を作成します。組み合わせごとにモデルを再計算し、傾向を視覚化するのに役立ちます

1 変数データ テーブル

単価の違いが利益にどのような影響を与えるかを見てみましょう。単価が 40 ドルから 70 ドルに増加すると、利益はどのように変化しますか?

手順:

• モデルを設定する
  • これらの価格を 1 列に縦方向に入力します
  • 最初の価格の隣のセル (例:B5) で、主要な収益結果セル (例:E2) を参照します。

• ヘッダーを含むテーブル範囲全体を選択します (例:A5:B12)
• データに移動します。 タブ>> What-If 分析を選択します>> データテーブルを選択します

• 行入力セルを残します 空白
• 列入力セル内 、元の単価セル (A2) を選択します
• [OK] をクリックします。

• Excel は各価格の利益を自動的に入力します

解釈:

• 結果の列には、価格に対する利益の感応度が表示されます
• 通常、価格が上昇すると、需要が低下するまで利益が増加します（モデル化されている場合）

2 変数データ テーブル

見てみましょう:「単価と販売数の両方が異なる場合はどうなるでしょうか?」

手順:

• 価格を縦方向 (左の列) に、販売数量を横方向 (上の行) に入力します
  • 表の左上のセル (40 より上、800 の左) で、メインの収益結果セルを参照します (たとえば、「=E2」と入力します) D5)

• ヘッダーを含むテーブル全体を選択します (例:D5:H9)
• データに移動します。 タブ>> What-If 分析を選択します>> データテーブルを選択します
• 行入力セル内 、販売単位 (B2) を選択します
• 列入力セル内 、単価（A2）を選択します
• [OK] をクリックします。

• Excel は、価格と売上のあらゆる組み合わせにおける利益を示す完全なマトリックスを作成します

注: データ テーブルの場合、入力セルはデータ テーブルと同じシート上にある必要があります。テーブルを埋めるために使用される式は、参照により別のシートに置くことができます。

洞察: データ テーブルは、価格弾力性や数量利益分析などの迅速な感度テストに最適です。

• 長所: 範囲を視覚化します。最大 2 つの変数を効率的に処理します
• 短所: 大きなテーブルでは計算負荷が高くなる可能性があります。結果は更新されるまで静的です

3.シナリオ マネージャー:複数の「What-If」モデルを比較する

シナリオ マネージャーを使用すると、式を上書きせずに、さまざまな入力値のセット (シナリオ) を保存して比較できます。それらをすばやく切り替えたり、すべてのシナリオを並べて比較する概要レポートを作成したりできます。最良/最悪の予測など、複数の変数を含む複雑なシナリオに適しています。

いつ使用するか:

• 最良のケース、最悪のケース、最も可能性の高いビジネス予測を比較する
• 変化する複数の前提条件を使用して、さまざまな戦略的オプションを評価する
• 複数のシナリオを明確で整理された形式で関係者に提示する
• モデルのさまざまなバージョンを長期にわたって追跡する

最良のケース、基本ケース、最悪のケースの 3 つのシナリオを使用してビジネス予測を作成しましょう。

手順:

• データに移動します。 タブ>> What-If 分析を選択します>> [シナリオ マネージャー] を選択します。

• [追加] をクリックします。
  • シナリオ名: 最初のシナリオに名前を付けます (例:Best Case)
  • セルの変更中 、単価 (A2)、販売数 (B2)、固定費 (C2) を選択します。 、および変動費 (D2)
  • [OK] をクリックします。

• ベストケースの値を入力します。 シナリオ
  • 単価: 65
  • 販売単位: 1600
  • 固定費: 8500
  • 変動費: 25
• [OK] をクリックします。

• [追加] をクリックします。 もう一度「基本ケース」に対して繰り返します

• [追加] をクリックします。 「最悪の場合」についても繰り返します

• 任意のシナリオを選択>> [表示] をクリックします 値を即座に適用する
• [概要] をクリックします。 すべてのケースを表示する

• シナリオの概要を選択します
• 「結果セル」として利益セルを選択します (例:E2)
• [OK] をクリックします。

• Excel は、すべてのシナリオの結果を比較する新しいシナリオ概要シートを作成します

洞察: Scenario Manager What-If 分析を使用して、Excel でシナリオを予測およびモデル化できます。これは、複数の前提条件を保存して一緒に検討する必要がある、最良の意思決定ツール (投資、予算編成、リスク評価など) の 1 つです。

• 長所: 複数の変数を管理します。レポートと簡単に比較
• 短所: 手動セットアップ。シナリオごとに変更できるセルは 32 個に制限

練習ワークブックをダウンロード

結論

このチュートリアルでは、3 つの What-If 分析手法を使用して、Excel でシナリオを予測およびモデル化する方法を説明します。 What-If 分析は、ゴール シーク、データ テーブル、シナリオ マネージャーを使用してさまざまな可能性を探ることにより、不確実性を障壁から機会に変えます。ビジネス予算の計画、投資オプションの評価、プロジェクトの結果のモデル化のいずれの場合でも、これらの Excel ツールは、前提条件の変更が結果にどのような影響を与えるかを理解するのに役立ちます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!