シナリオマネージャーを使用したWhat-If分析

シナリオマネージャーは、感度分析に3つ以上の変数がある場合に役立ちます。シナリオマネージャーは、検討中の変数の入力値の各セットのシナリオを作成します。シナリオは、以下をサポートする一連の可能な結果を​​調査するのに役立ちます。

  • 最大32の入力セットを変更します。
  • いくつかの異なるワークシートまたはワークブックからのシナリオをマージします。

32を超える入力セットを分析する必要があり、値が1つまたは2つの変数のみを表す場合は、データテーブルを使用できます。1つまたは2つの変数に制限されていますが、データテーブルには必要な数の異なる入力値を含めることができます。このチュートリアルのデータテーブルを使用したWhat-If分析を参照してください。

シナリオ

シナリオは、Excelが保存し、ワークシートで自動的に置き換えることができる値のセットです。さまざまな値のグループを作成してワークシートのシナリオとして保存し、これらのシナリオを切り替えてさまざまな結果を表示できます。

たとえば、考えられるさまざまな収入レベルと費用を比較する、いくつかの異なる予算シナリオを作成できます。また、さまざまな可能な金利とローンの保有期間を比較するさまざまなソースからのさまざまなローンシナリオを持つことができます。

シナリオで使用する情報がさまざまなソースからのものである場合は、情報を別々のワークブックに収集してから、さまざまなワークブックのシナリオを1つにマージできます。

必要なシナリオがすべて揃ったら、シナリオの概要レポートを作成できます-

  • これには、すべてのシナリオからの情報が組み込まれています。
  • これにより、シナリオを並べて比較できます。

シナリオマネージャー

シナリオマネージャーは、ExcelのWhat-if分析ツールの1つです。

シナリオマネージャーで分析レポートを作成するには、次の手順に従う必要があります-

Step 1 −初期値のセットを定義し、変更するセルと呼ばれる、変更する入力セルを特定します。

Step 2 −各シナリオを作成し、シナリオに名前を付け、そのシナリオの変化する各入力セルの値を入力します。

Step 3−追跡する結果セルと呼ばれる出力セルを選択します。これらのセルには、初期値セットに数式が含まれています。数式は、変化する入力セルを使用します。

シナリオマネージャーは、各シナリオの入力値と出力値を含むレポートを作成します。

シナリオの初期値

いくつかの異なるシナリオを作成する前に、シナリオの基礎となる一連の初期値を定義する必要があります。

シナリオの初期値を設定する手順は次のとおりです。

  • 入力値を含むセルを定義します。
  • 入力セルに適切な名前を付けます。
  • 定数値で入力セルを識別します。
  • 定数入力の値を指定します。
  • 値が変化する入力セルを特定します。
  • 変化する入力の初期値を指定します。
  • 結果を含むセルを定義します。結果のセルには数式が含まれています。
  • 結果セルに適切な名前を付けます。
  • 結果セルに数式を配置します。

前のローンの例を考えてみましょう。さて、次のように進めます-

  • ローン金額のセルを定義します。

    • この入力値は、すべてのシナリオで一定です。

    • セルにLoan_Amountという名前を付けます。

    • 値を5,000,000として指定します。

  • 利率、支払い数、およびタイプ(月の初めまたは終わりの支払い)のセルを定義します。

    • これらの入力値は、シナリオ全体で変化します。

    • セルにInterest_Rate、NPER、およびTypeという名前を付けます。

    • これらのセルの分析の初期値をそれぞれ12%、360、0として指定します。

  • EMIのセルを定義します。

    • これが結果値です。

    • セルにEMIという名前を付けます。

    • このセルに数式を-として配置します

      =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

ワークシートは次のようになります-

ご覧のとおり、入力セルと結果セルは列Cにあり、名前は列Dに示されています。

シナリオの作成

シナリオの初期値を設定した後、次のようにシナリオマネージャを使用してシナリオを作成できます。

  • リボンの[データ]タブをクリックします。
  • [データツール]グループの[仮定分析]をクリックします。
  • ドロップダウンリストからScenarioManagerを選択します。

[シナリオマネージャー]ダイアログボックスが表示されます。メッセージが含まれていることがわかります-

“No Scenarios defined. Choose Add to.”

シナリオマネージャーで、変化する値のセットごとにシナリオを作成する必要があります。最初のシナリオを初期値で定義しておくと、さまざまなシナリオを表示しながら、いつでも初期値に戻すことができるので便利です。

次のように初期値を使用して最初のシナリオを作成します-

  • クリック Add [シナリオマネージャー]ダイアログボックスのボタン。

ザ・ Add Scenario ダイアログボックスが表示されます。

  • [シナリオ名]に「シナリオ1」と入力します。
  • [セルの変更]で、Ctrlキーを押しながら、セルの参照(C3、C4、C5)を入力します。

ダイアログボックスの名前が[シナリオの編集]に変わります。

  • のテキストを編集します Comment as – Initial Values ボックス。

  • [保護]で[変更を防止する]オプションを選択し、[OK]をクリックします。

ザ・ Scenario Valuesダイアログボックスが表示されます。定義した初期値は、変化する各セルボックスに表示されます。

Scenario 1 初期値で作成されます。

次のように、変化するセルの値を変化させて、さらに3つのシナリオを作成します-

  • クリック Add [シナリオ値]ダイアログボックスのボタン。

[シナリオの追加]ダイアログボックスが表示されます。C3、C4、C5が[セルの変更]ボックスに表示されることに注意してください。

  • [シナリオ名]ボックスに、「シナリオ2」と入力します。

  • のテキストを編集します Comment として–異なる金利。

  • [保護]で[変更を防止する]を選択し、[OK]をクリックします。

ザ・ Scenario Valuesダイアログボックスが表示されます。初期値は変化するセルに表示されます。の値を変更しますInterest_Rate0.13 をクリックします Add

ザ・ Add Scenarioダイアログボックスが表示されます。C3、C4、C5は、セルの変更の下のボックスに表示されることに注意してください。

  • [シナリオ名]ボックスに、「シナリオ3」と入力します。

  • のテキストを編集します Commentボックスとして–​​異なる番号 支払いの。

  • [保護]で[変更を防止する]を選択し、[OK]をクリックします。

[シナリオ値]ダイアログボックスが表示されます。初期値は変化するセルに表示されます。NPERの値を300に変更し、をクリックしますAdd

ザ・ Add Scenarioダイアログボックスが表示されます。C3、C4、C5が[セルの変更]ボックスに表示されることに注意してください。

  • [シナリオ名]ボックスに、「シナリオ4」と入力します。

  • のテキストを編集します Comment ボックスとして–​​異なるタイプの支払い。

  • [保護]で[変更を防止する]を選択し、[OK]をクリックします。

ザ・ Scenario Valuesダイアログボックスが表示されます。初期値は変化するセルに表示されます。Typeの値を1に変更します。追加するすべてのシナリオを追加したら、[OK]をクリックします。

ザ・ Scenario Managerダイアログボックスが表示されます。[シナリオ]の下のボックスに、作成したすべてのシナリオの名前が表示されます。

  • シナリオ1をクリックします。ご存知のとおり、シナリオ1には初期値が含まれています。
  • 次に、をクリックします Summary。[シナリオの概要]ダイアログボックスが表示されます。

シナリオ要約レポート

Excelは、2種類のシナリオ概要レポートを提供します-

  • シナリオの要約。
  • シナリオピボットテーブルレポート。

[シナリオの概要]ダイアログボックスには、これら2つのレポートタイプがあります。

[レポートタイプ]で[シナリオの概要]を選択します。

シナリオの概要

の中に Result cells ボックスで、セルを選択します C6 (ここに、 PMT関数)。[OK]をクリックします。

シナリオの概要レポートが新しいワークシートに表示されます。ワークシートには、シナリオの概要という名前が付けられています。

シナリオ概要レポートで次のことを確認できます-

  • Changing Cells−変更セルとして使用されるすべてのセルを参加させます。セルにInterest_Rate、NPER、およびTypeという名前を付けたので、これらはレポートを意味のあるものにしているように見えます。それ以外の場合は、セル参照のみが一覧表示されます。

  • Result Cells −指定された結果セル(EMI)を表示します。

  • Current Values −これは最初の列であり、要約レポートを作成する前に[シナリオマネージャー]ダイアログボックスで選択されたシナリオの値を一覧表示します。

  • 作成したすべてのシナリオで、変化するセルが灰色で強調表示されます。

  • EMI行には、各シナリオの結果値が表示されます。

シナリオの作成中に追加したコメントを表示することで、レポートをより意味のあるものにすることができます。

  • シナリオ名を含む行の左側にある[+]ボタンをクリックします。シナリオのコメントは、シナリオ名の下の行に表示されます。

さまざまなソースからのシナリオ

3つの異なるソースからシナリオを取得し、マスターワークブックでシナリオの概要レポートを準備する必要があるとします。これを行うには、さまざまなワークブックのシナリオをマスターワークブックにマージします。以下の手順に従ってください-

  • シナリオがワークブックBank1_Scenarios、Bank2_Scenarios、およびBank3_Scenariosにあると想定します。3つのワークブックを開きます。

  • 初期値が記載されているマスターワークブックを開きます。

  • マスターワークブックで[データ]> [仮定の分析]> [シナリオマネージャー]をクリックします。

ザ・ Scenario Manager ダイアログボックスが表示されます。

ご覧のとおり、まだ追加していないため、シナリオはありません。クリックMerge

[シナリオのマージ]ダイアログボックスが表示されます。

ご覧のとおり、からのマージシナリオでは、2つのボックスがあります-

  • Book
  • Sheet

結果に追加するシナリオを含む特定のワークブックから特定のワークシートを選択できます。のドロップダウン矢印をクリックしますBook ワークブックを表示します。

Note −このリストに表示するには、対応するワークブックが開いている必要があります。

本を選択– Bank1_Scenarios

Bank1シートが表示されます。ダイアログボックスの下部に、ソースシートで見つかったシナリオの数が表示されます。[OK]をクリックします。

[シナリオマネージャー]ダイアログボックスが表示されます。マスターワークブックにマージされた2つのシナリオは、シナリオの下に一覧表示されます。

クリック Mergeボタン。ザ・Merge Scenariosダイアログボックスが表示されます。ここで、Bank2_Scenarios [ブック]ボックスのドロップダウンリストから。

Bank2シートが表示されます。ダイアログボックスの下部に、ソースシートで見つかったシナリオの数が表示されます。[OK]をクリックします。

ザ・ Scenario Managerダイアログボックスが表示されます。マスターワークブックにマージされた4つのシナリオは、シナリオの下に一覧表示されます。

クリック Mergeボタン。ザ・Merge Scenariosダイアログボックスが表示されます。ここで、Bank3_Scenarios [ブック]ボックスのドロップダウンリストから。

Bank3シートが表示されます。ダイアログボックスの下部に、ソースシートで見つかったシナリオの数が表示されます。[OK]をクリックします。

[シナリオマネージャー]ダイアログボックスが表示されます。マスターワークブックにマージされた5つのシナリオは、シナリオの下に一覧表示されます。

これで、シナリオ要約レポートを作成するために必要なすべてのシナリオができました。

[概要]ボタンをクリックします。ザ・Scenario Summary ダイアログボックスが表示されます。

  • シナリオの概要を選択します。
  • [結果セル]ボックスにC6と入力し、[OK]をクリックします。

シナリオの概要レポートは、マスターワークブックの新しいワークシートに表示されます。

シナリオの表示

シナリオを提示していて、あるシナリオから別のシナリオに動的に切り替えて、対応するシナリオの入力値と結果値のセットを表示したいとします。

  • [データツール]グループから[データ]> [仮定の分析]> [シナリオマネージャー]をクリックします。[シナリオマネージャー]ダイアログボックスが表示されます。シナリオのリストが表示されます。

  • 表示するシナリオを選択します。クリックShow

ワークシートの値は、選択したシナリオの値に更新されます。結果の値が再計算されます。

シナリオピボットテーブルレポート

シナリオレポートは、ピボットテーブルの形式でも表示できます。

  • の[概要]ボタンをクリックします Scenario Managerダイアログボックス。[シナリオの概要]ダイアログボックスが表示されます。

  • を選択 Scenario PivotTable report レポートタイプの下。

  • にC6と入力します Result cells ボックス。

シナリオピボットテーブルレポートが新しいワークシートに表示されます。