財務モデルでドロップダウンシナリオを構築する方法

シナリオを作成するために最も一般的に使用される方法は、数式とドロップダウンボックスの組み合わせを使用することです。財務モデルでは、可能なシナリオとその入力のテーブルを作成し、シナリオ名を入力セルのドロップダウンボックスにリンクします。モデルの入力はシナリオテーブルにリンクされています。すべての入力が出力に流れるようにモデルが適切に構築されている場合、ユーザーがドロップダウンボックスからさまざまなオプションを選択すると、モデルの結果が変化します。

データ検証ドロップダウンボックスは、シナリオ分析など、財務モデリングのさまざまな目的で使用されます。

データ検証を使用して収益性シナリオをモデル化する

ファイル0801.xlsxをダウンロードします。それを開き、8-1-startというラベルの付いたタブを選択します。

これがモデル化された方法では、入力は列Bに並んでいます。入力の1つを変更するだけで感度分析を実行できます。たとえば、セルB3の通話オペレーターごとの顧客を40から45に変更すると、次のようになります。すべての従属番号の変更を参照してください。変更する変数は1つだけなので、これは感度分析になります。代わりに、この完全なシナリオ分析の演習では、一度に複数の変数を変更するため、手動でいくつかの数値を微調整する以上のことを行う必要があります。

データ検証ドロップダウンボックスを使用してシナリオ分析を実行するには、次の手順に従います。

ダウンロードしたモデルを取得し、列Cから列Fに説明をカットアンドペーストします。これを行うには、セルC6：C8を強調表示し、Ctrl + Xを押し、セルF6を選択して、Enterキーを押します。

セルB3からB8の入力は、モデルを駆動するアクティブ範囲であり、その状態を維持します。ただし、作成するドロップダウンボックスに応じて変化する数式になる必要があります。

列Bの範囲を列C、D、およびEにコピーします。

これを行うには、B3：B8を強調表示し、Ctrl + Cを押し、セルC3：E3を選択して、Enterキーを押します。これらの金額は、変更するまで各シナリオで同じになります。

行2に、「ベストケース」、「ベースケース」、および「ワーストケース」というタイトルを入力します。

シナリオ分析のためのモデルの設定。

セルC12を選択してF2ショートカットキーを押すとわかるように、数式は引き続き列Bの入力にリンクしていることに注意してください。

各シナリオの下の入力を編集します。

可能性が高いと思われるものは何でも入力できますが、この例の数値と一致させるために、値を入力してください。今のところ列Bは無視してください。

シナリオ分析のための入力。

次に、シナリオを推進するドロップダウンボックスを上部に追加する必要があります。ドロップダウンボックスを正確にどこに置くかは重要ではありませんが、通常はページの上部など、見つけやすい場所に配置する必要があります。

セルE1に、タイトル「シナリオ」を入力します。

セルF1を選択し、フォーマットを入力に変更して、このセルが編集可能であることをユーザーが確認できるようにします。

これを行う最も簡単な方法は、次の手順に従うことです。

セルE3など、入力として既にフォーマットされているセルの1つをクリックします。

[ホーム]タブの左側にある[クリップボード]セクションの[フォーマットペインタ]アイコンを押します。カーソルが絵筆に変わります。

セルF1を選択して、書式を貼り付けます。

Format Painterは通常、使い捨てです。セルを選択すると、ペイントブラシがカーソルから消えます。フォーマットペインタを「スティッキー」にして複数のセルに適用する場合は、[ホーム]タブからアイコンを選択するときにアイコンをダブルクリックします。

次に、セルF1で、[データ]タブの[データツール]セクションから[データ検証]を選択します。

[データ検証]ダイアログボックスが表示されます。

[設定]タブで、[許可]ドロップダウンを[リスト]に変更し、マウスを使用して範囲= $ C $ 2：$ E $ 2を選択し、[OK]をクリックします。

データ検証ドロップダウンシナリオの作成。

セルF1の横に表示されるドロップダウンボックスをクリックし、シナリオの1つ（たとえば、ベースケース）を選択します。

シナリオへの数式の適用

列Bのセルはまだモデルを駆動しているため、これらを数式に置き換える必要があります。ただし、数式を追加する前に、範囲内のセルの書式を変更して、ハードコードされた数値ではなく数式が含まれていることを示す必要があります。次の手順を実行します：

セルB3：B8を選択し、[ホーム]タブの[フォント]グループから[塗りつぶしの色]を選択します。

塗りつぶしの色を白い背景に変更します。

モデル内の数式と入力セルを区別することは非常に重要です。モデルを開くすべてのユーザーに対して、この範囲のセルには数式が含まれており、オーバーライドしてはならないことを明確にする必要があります。

次に、列Bのハードコードされた値を、ドロップダウンボックスの変更に応じて変更される数式に置き換える必要があります。これは、さまざまな機能を使用して実行できます。HLOOKUP、ネストされたIFステートメント、IFS、およびSUMIFがすべてこのトリックを実行します。次の手順に従って数式を追加します。

セルB3を選択し、セルF1の内容に応じて値を変更する数式を追加します。

さまざまなオプションでの数式は次のとおりです。

• = HLOOKUP（$ F $ 1、$ C $ 2：$ E $ 8,2,0）

  このソリューションでは、数式をコピーするときに、行のインデックス番号を2から3などに変更する必要があることに注意してください。代わりに、次のように3番目のフィールドでROW関数を使用できます。= HLOOKUP（$ F $ 1、$ C $ 2：$ E $ 8、ROW（A3）-1,0）

• = IF（$ F $ 1 = $ C $ 2、C3、IF（$ F $ 1 = $ D $ 2、D3、E3））
• = IFS（$ F $ 1 = $ C $ 2、C3、$ F $ 1 = $ D $ 2、D3、$ F $ 1 = $ E $ 2、E3）
• = SUMIF（$ C $ 2：$ E $ 2、$ F $ 1、C3：E3）

  いつものように、選択できるいくつかの異なるオプションがあり、最良の解決策は、最も単純で理解しやすいものです。これらの関数はどれもまったく同じ結果を生成しますが、HLOOKUPの行インデックス番号を変更する必要があることは堅牢ではなく、ROWを追加するとユーザーが混乱する可能性があります。ネストされたIFステートメントは、作成して従うのが難しいです。新しいIFS関数は、ネストされたIF関数を単純にするように設計されていますが、それでも扱いにくいです。SUMIFは構築とフォローが非常に簡単で、将来シナリオを追加する必要がある場合は簡単に拡張できます。

  IFSは、Office365およびExcel2016以降がインストールされている場合にのみ使用できる新機能であることに注意してください。この関数を使用していて、誰かが以前のバージョンのExcelでこのモデルを開いた場合、その人は数式を表示できますが、編集することはできません。

セルB3の数式を列の下にコピーします。

完了したシナリオ分析。

通常のコピーアンドペーストを使用すると、すべてのフォーマットが失われます。どの入力がドル値、パーセンテージ、または顧客数であるかが一目でわかるように、モデルのフォーマットを保持することが重要です。数式の貼り付けを使用して、書式を保持します。セルをクリップボードにコピーし、宛先範囲を強調表示して右クリックし、[数式の貼り付け]アイコンを選択して数式のみを貼り付け、書式をそのままにしておくと、アクセスできます。

さあ、楽しい部分です！モデルのシナリオ機能をテストする時が来ました。

セルF1をクリックし、ドロップダウンボックスを変更して、さまざまなシナリオを切り替えるときにモデルの出力が変化するのを確認します。