内容
金融分野のプロセスは常に相互に関連しています。XNUMX つの要因が別の要因に依存し、それとともに変化します。 これらの変更を追跡し、おそらく Excel の関数やスプレッドシートの方法を使用して、将来何が予想されるかを理解してください。
データ テーブルを使用して複数の結果を取得する
データシート機能は、what-if 分析の要素であり、多くの場合、Microsoft Excel で実行されます。 これは感度分析の別名です。
概要
データ テーブルは、一部のセルの値を変更することで問題を解決するために使用できるセルの範囲の一種です。 数式のコンポーネントの変更を追跡し、これらの変更に従って結果の更新を受け取る必要がある場合に作成されます。 研究でのデータ テーブルの使用方法と、その種類を見てみましょう。
データ テーブルの基本
データ テーブルには XNUMX つのタイプがあり、コンポーネントの数が異なります。 確認する必要がある値の数に焦点を当ててテーブルをコンパイルする必要があります。
統計学者は、結果を変更できる変数が 1 つ以上の式に XNUMX つしかない場合に、単一の変数テーブルを使用します。 たとえば、PMT 機能と組み合わせて使用されることがよくあります。 この式は、定期的な支払いの金額を計算するように設計されており、契約で指定された利率が考慮されます。 このような計算では、変数が XNUMX つの列に書き込まれ、計算結果が別の列に書き込まれます。 変数が XNUMX つのデータ プレートの例:
次に、2 つの変数を持つプレートを考えます。 それらは、2 つの要因が指標の変化に影響を与える場合に使用されます。 XNUMX つの変数は、ローンに関連付けられた別のテーブルに格納される可能性があります。このテーブルを使用して、最適な返済期間と毎月の支払い額を決定できます。 この計算では、PMT 関数も使用する必要があります。 XNUMX つの変数を持つテーブルの例:
変数が XNUMX つのデータ テーブルの作成
在庫が 100 冊しかない小さな書店を例に、分析方法を考えてみましょう。 それらの一部はより高く販売でき ($50)、残りは購入者の費用が安くなります ($20)。 すべての商品の販売による総収入が計算されます。所有者は、書籍の 60% を高値で販売することを決定しました。 大量の商品の価格を 70% などに引き上げると、収益がどのように増加するかを調べる必要があります。
注意を払う！ 総収益は数式を使用して計算する必要があります。そうしないと、データ テーブルをコンパイルできません。
- シートの端から離れた空いているセルを選択し、数式を書き込みます。 =総収益のセル。 例えばC14セルに収入を記入する場合（任意指定あり）、以下のように記入する必要があります。 ＝Ｓ１４．
- このセルの左側の列に商品の量のパーセンテージを書き込みます。セルの下ではなく、これは非常に重要です。
- パーセンテージ列と総収入へのリンクが配置されているセルの範囲を選択します。
- 「データ」タブで「What if分析」という項目を見つけてクリックします。開いたメニューで、「データテーブル」オプションを選択します。
- 小さなウィンドウが開き、最初に高値で販売された書籍の割合を含むセルを指定する必要がある場所で、[… の行ごとに値を置換] 列に表示されます。 このステップは、増加率を考慮して総収益を再計算するために行われます。
表を作成するためのデータを入力したウィンドウで「OK」ボタンをクリックすると、計算結果が行に表示されます。
式を単一の変数データ テーブルに追加する
変数が XNUMX つだけのアクションを計算するのに役立つテーブルから、式を追加することで高度な分析ツールを作成できます。 既存の数式の隣に入力する必要があります。たとえば、テーブルが行指向の場合、既存のセルの右側にあるセルに数式を入力します。 列の向きが設定されたら、古い式の下に新しい式を書き込みます。 次に、アルゴリズムに従います。
- セルの範囲をもう一度選択しますが、新しい数式が含まれているはずです。
- 「what if」分析メニューを開き、「データシート」を選択します。
- プレートの向きに応じて、行または列の対応するフィールドに新しい式を追加します。
XNUMX つの変数を含むデータ テーブルを作成する
このようなテーブルの開始は少し異なります。パーセンテージ値の上に総収益へのリンクを配置する必要があります。 次に、次の手順を実行します。
- 収入へのリンクを含む価格オプションを XNUMX 行に書き込みます。価格ごとに XNUMX つのセルです。
- セルの範囲を選択します。
- ツールバーの「データ」タブから、変数が XNUMX つのテーブルをコンパイルする場合と同様に、データ テーブル ウィンドウを開きます。
- 「…の列ごとに値を代入」列に初期高価格のセルを代入します。
- 「…の行ごとに値を代入する」列に、高価な本の販売の初期パーセンテージを含むセルを追加し、「OK」をクリックします。
その結果、テーブル全体が、商品の販売条件が異なる可能性のある収入の金額で満たされます。
データ テーブルを含むワークシートの計算を高速化する
ワークブック全体の再計算をトリガーしないデータ テーブルでの迅速な計算が必要な場合は、プロセスを高速化するためにできることがいくつかあります。
- オプションウィンドウを開き、右側のメニューで「式」項目を選択します。
- [ブック内の計算] セクションで [データ テーブルを除いて自動] を選択します。
- 表の結果を手動で再計算してみましょう。 これを行うには、数式を選択して F キーを押します。
感度分析を実行するためのその他のツール
プログラムには、感度分析を実行するのに役立つ他のツールがあります。 手動で行わなければならないいくつかのアクションを自動化します。
- 「パラメータ選択」機能は、目的の結果が既知であり、そのような結果を得るために変数の入力値を知る必要がある場合に適しています。.
- 「解決策を探す」は、問題を解決するためのアドオンです。 制限を設定してそれらを指す必要があります。その後、システムが答えを見つけます。 解は、値を変更することによって決定されます。
- 感度分析は、シナリオ マネージャーを使用して実行できます。 このツールは、[データ] タブの what-if 分析メニューにあります。 いくつかのセルの値を代入します。その数は 32 に達することがあります。ディスパッチャーはこれらの値を比較するため、ユーザーが手動で値を変更する必要はありません。 スクリプト マネージャーの使用例:
Excel での投資プロジェクトの感度分析
What-If 分析は、投資などの予測が必要な状況で特に役立ちます。 アナリストは、この方法を使用して、いくつかの要因の変化の結果として、会社の株式の価値がどのように変化するかを調べます。
投資感度分析方法
「what if」を分析するときは、手動または自動の列挙を使用します。 値の範囲は既知であり、式に XNUMX つずつ代入されます。 結果は一連の値です。 その中から適切な数を選びなさい。 金融分野で感度分析が行われるXNUMXつの指標を考えてみましょう。
- 正味現在価値 – 収入額から投資額を差し引いて計算されます。
- 内部収益率 / 利益 – 投資から XNUMX 年にどれだけの利益を得る必要があるかを示します。
- 回収率は、初期投資に対するすべての利益の比率です。
- 割引利益指数 – 投資の有効性を示します。
式
埋め込み感度は、次の式を使用して計算できます。 出力パラメーターの変化 (%) / 入力パラメーターの変化 (%)。
出力パラメータと入力パラメータは、前述の値にすることができます。
- 標準条件下での結果を知る必要があります。
- 変数の XNUMX つを置き換えて、結果の変化を監視します。
- 確立された条件に対する両方のパラメーターの変化率を計算します。
- 得られたパーセンテージを式に挿入し、感度を決定します。
Excel での投資プロジェクトの感度分析の例
分析方法論をよりよく理解するには、例が必要です。 次の既知のデータを使用してプロジェクトを分析してみましょう。
- プロジェクトを分析するためにテーブルに入力します。
- OFFSET関数を使用してキャッシュフローを計算します。 初期段階では、フローは投資と同じです。 次に、式を適用します。 =IF(OFFSET(数値,1;)=2;SUM(流入1:流出1); SUM(流入1:流出1)+$B$ 5）数式内のセルの指定は、テーブルのレイアウトによって異なる場合があります。 最後に、初期データからの値、つまり残存価額が追加されます。
- プロジェクトが完済する期間を決定します。 初期期間については、次の式を使用します。 =まとめ(G7：G17;»<0″). セル範囲はキャッシュ フロー列です。 それ以降の期間については、次の式を適用します。 =初期+IF(最初のe.stream>0;最初のe.stream;0)。 プロジェクトは 4 年で損益分岐点にある。
- プロジェクトが成果を上げた期間の数の列を作成します。
- 投資収益率を計算します。 ある期間の利益を初期投資で割った式を作る必要があります。
- 次の式を使用して割引係数を決定します。 =1/(1+ディスク%) ^番号.
- 乗算を使用して現在価値を計算します。キャッシュ フローに割引係数を掛けます。
- PI（収益性指数）を計算してみましょう。 経時的な現在価値は、プロジェクト開始時の投資で割ります。
- IRR 関数を使用して内部収益率を定義しましょう。 =IRR(キャッシュフローの範囲)。
データシートを使用した投資感度分析
投資分野のプロジェクトの分析には、データ テーブル以外の方法が適しています。 多くのユーザーは、数式をコンパイルするときに混乱を経験します。 ある要因が他の要因の変化にどのように依存しているかを調べるには、計算を入力したりデータを読み取ったりするための正しいセルを選択する必要があります。
計算の自動化による Excel での因子分析と分散分析
感度分析のもう XNUMX つの類型は、因子分析と分散分析です。 最初のタイプは数値間の関係を定義し、XNUMX 番目のタイプはある変数の他の変数への依存を明らかにします。
Excel での分散分析
このような分析の目的は、値の変動性を次の XNUMX つの要素に分割することです。
- 他の値の影響による変動性。
- それに影響する値の関係による変化。
- ランダムな変化。
Excelアドイン「データ分析」で分散分析を行ってみましょう。 有効になっていない場合は、設定で有効にすることができます。
最初のテーブルは XNUMX つの規則に従う必要があります。値ごとに XNUMX つの列があり、その中のデータは昇順または降順で配置されます。 紛争時の行動に対する教育レベルの影響を確認する必要があります。
- [データ] タブでデータ分析ツールを見つけて、そのウィンドウを開きます。 リストで、一元配置分散分析を選択する必要があります。
- ダイアログ ボックスの行を入力します。 入力間隔は、ヘッダーと数値を除くすべてのセルです。 列ごとにグループ化します。 結果を新しいシートに表示します。
黄色のセルの値が XNUMX より大きいため、この仮定は正しくないと見なすことができます。教育と衝突時の行動には関係がありません。
Excel での因子分析: 例
販売の分野でデータの関係を分析してみましょう。人気のある商品と人気のない商品を特定する必要があります。 初期情報:
- XNUMX か月目にどの商品の需要が最も増加したかを調べる必要があります。 需要の増加と減少を判断するために、新しい表を作成しています。 成長は、次の式を使用して計算されます。 =IF((需要 2 - 需要 1)>0; 需要 2 - 需要 1;0)。 数式を減らす: =IF(成長=0; 需要 1 - 需要 2;0)。
- 商品の需要の伸びをパーセンテージで計算します。 =IF(成長/結果 2 =0; 減少/結果 2; 成長/結果 2)。
- わかりやすくするためにグラフを作成しましょう。セルの範囲を選択し、[挿入] タブでヒストグラムを作成します。 設定では、塗りつぶしを削除する必要があります。これは、データ系列の書式設定ツールを使用して行うことができます。
Excel での双方向分散分析
分散分析は、いくつかの変数を使用して実行されます。 これを例に考えてみましょう。異なる音量の音への反応が男性と女性でどれだけ速く現れるかを調べる必要があります。
- 「データ分析」を開きます。リストで、繰り返しのない双方向分散分析を見つける必要があります。
- 入力間隔 – データを含むセル (ヘッダーなし)。 結果を新しいシートに表示し、[OK] をクリックします。
F 値は F-critical よりも大きく、床が音に対する反応速度に影響を与えることを意味します。
まとめ
この記事では、各ユーザーがアプリケーションの方法を理解できるように、Excel スプレッドシートでの感度分析について詳しく説明しました。