金融モデルのドロップダウンシナリオを構築する方法 - ダミー

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

<！ - 1 - >

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

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

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

これをモデル化した方法では、入力は列Bに並んでいます。入力の1つを変更するだけで感度分析を行うことができます。たとえば、セルB3の顧客1人当たりのオペレータを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行目に、「ベストケース 」、「 ベースケース、、最悪ケース」というタイトルを入力します。
シナリオ分析のためのモデルの設定。
数式は列Bの入力にもリンクしていますが、セルC12を選択してF2ショートカットキーを押すとわかります。
各シナリオの下の入力を編集します。
あなたが考えているものはすべて入れても構いませんが、この例の数値と数字を一致させるには値を入力します。今は列Bを無視してください。
<！ - 3 - >
シナリオ分析の入力。
上部にドロップダウンボックスを追加する必要があります。これにより、シナリオが推進されます。正確にどこにドロップダウンボックスを置くかは重要ではありませんが、通常はページの上部で見つけやすい場所に配置する必要があります。
セルE1に、シナリオ というタイトルを入力します。
セルF1を選択し、このセルが編集可能であることをユーザーが確認できるように書式を入力に変更します。
これを行う最も簡単な方法は、次の手順に従うことです。
1. 入力として既にフォーマットされているセルの1つ、たとえばセルE3をクリックします。
2. 「ホーム」タブの左側にある「クリップボード」セクションの書式ペインタアイコンを押します。カーソルがペイントブラシに変わります。
3. 書式を貼り付けるには、セルF1を選択します。
  書式ペインタは通常1回のみの使用です。セルを選択すると、ペイントブラシがカーソルから消えます。書式ペインタを「スティッキー」にして複数のセルに適用する場合は、「ホーム」タブからアイコンをダブルクリックします。
セルF1で、[データ]タブの[データツール]セクションから[データの検証]を選択します。
[データ検証]ダイアログボックスが表示されます。
「設定」タブで、「許可」ドロップダウンを「リスト」に変更し、マウスを使用して範囲= $ C $ 2：$ E $ 2を選択し、「OK」をクリックします。
データ検証ドロップダウンシナリオの作成。
セルF1の横に表示されるドロップダウンボックスをクリックし、いずれかのシナリオ（Base Caseなど）を選択します。

シナリオに式を適用する

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

セルB3：B8を選択し、[ホーム]タブの[フォント]グループから[塗りつぶしの色]を選択します。
塗りつぶしの色を白い背景に変更します。モデルの式と入力セルを区別することは非常に重要です。この範囲のセルには数式が含まれているため、オーバーライドしないようにモデルを開いたユーザーには明示する必要があります。
ここで、列Bのハードコードされた値を、ドロップダウンボックスの変更に応じて変更される数式に置き換える必要があります。これは、さまざまな機能を使用して実行できます。 HLOOKUP、ネストされたIF文、IFS、およびSUMIFはすべてトリックを行います。次の手順を実行して数式を追加します。

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

= HLOOKUP（$ F $ 1、$ C $ 2：$ E $ 8,2,0）
この解決策では、 2から3までの行インデックス番号など、数式をコピーするときに続きます。代わりに、= HLOOKUP（$ F $ 1、$ C $ 2：$ E $ 8、ROW（A3）-1、0）
- = IF（$ F $ 1 = $）のように3番目のフィールドにROW関数を使用できます。 IFS（$ F $ 1 = $ C $ 2、C3、$ F $ 1 = $ D $ 2、D3、$ F $ 1 = C $ 2、C3、IF（$ F $ 1 = $ D $ 2、D3、E3） $ E $ 2、E3）
  = SUMIF（$ C $ 2：$ E $ 2、$ F $ 1、C3：E3）
- いつものように、いくつかの選択肢があります。理解するのが最も簡単で簡単です。これらの関数のいずれも、まったく同じ結果を生成しますが、HLOOKUPの行インデックス番号を変更する必要はありません。また、ROWを追加するとユーザーが混乱する可能性があります。入れ子にされたIF文は、構築するのが難しく、新しいIFS関数はネストされたIF関数をより簡単にするように設計されていますが、扱いにくいです。 SUMIFは構築とフォローが非常に簡単で、将来的に追加のシナリオを追加する必要がある場合は簡単に拡張できます。
- IFSは、Office 365およびExcel 2016以降でのみ使用可能な新しい機能です。この関数を使用していて、以前のバージョンのExcelでこのモデルを開くと、そのモデルを表示できますが、編集することはできません。
- セルのB3の数式を列の下にコピーします。
  完成したシナリオ分析。
  
  通常のコピー&ペーストを使用すると、すべての書式が失われます。どの入力がドル値、パーセンテージ、または顧客番号であるかを一目で見ることができるように、モデルの書式を保持することが重要です。式の貼り付けを使用して書式を保持します。セルをクリップボードにコピーし、目的の範囲を強調表示し、右クリックし、[数式を貼り付け]アイコンを選択して式のみを貼り付け、書式設定はそのままにして、アクセスできます。
今楽しみの部分！モデルのシナリオ機能をテストする時間です。
セルF1をクリックし、ドロップダウンボックスを変更し、さまざまなシナリオを切り替えるとモデル出力が変化するのを確認します。