投資銀行エクセル術:1分で感応度分析を行う方法(データテーブル)

エクセル実践

DCFなどの財務モデリングでは、割引率や売上成長率など様々な変数に一定の前提を置いて企業価値や株式価値を算出します。ある変数を変化させた場合に、企業価値等がどう変化するかを一覧できる感応度分析表を素早く作る方法を解説します。

データテーブルを使った感応度分析の作り方

このページの説明と合わせて、下のエクセルをダウンロードすると、実際にショートカットの使い方を練習できますので、ぜひ使ってください。

なお、ノートPCを使っている方は、Page Up/DownやHome、EndはFunctionキー(Fnと書かれているキー)と同時に押す必要がある場合がありますので、自分のPCの使用をご確認ください。

単入力テーブル(変数1つ)の作り方

私のエクセルの設例では、元本100を金利5%で5年間複利で運用した時のリターンを計算しています。

感応度分析-前提

ちょっとしたエクセルのテクニックですが、エクセルで運用期間の「5年」と書かれているセルを見てみると、実際には「5」としか入力されていません。これは、下の図のようにセルの書式設定で入力した数字の後に「年」を付け加えるように設定しているためです。こうすることで、見た目をわかりやすくしつつエクセルの計算も可能になります。

なお、セルのフォーマットはCtrl + 1のショートカットで表示させることができます。

感応度分析-セルフォーマット

では、単入力テーブルの作り方に入ります。

ここでは、運用期間を変動させるとリターンがどのように変わるかを分析してみます。

まずは、表の左側に変動させる運用期間を並べます。

感応度分析-ステップ2

次に、表の右上セルに前提の想定リターンをリンクさせます。

感応度分析-ステップ3

その後、表全体を選択した状態で、エクセルツールバーの「データ」→「What-If分析」→「データテーブル」を選択します(ショートカットの場合はAlt→A→W→T)。

すると、以下の図にあるテータテーブルダイアログボックスが表示されますので、「列の代入セル」に、変動させたい運用期間の前提値である「5年」が入力されているセルを選択します。

感応度分析-ステップ4

すると、以下のように一瞬で感応度分析ができあがります。

感応度分析-ステップ5

しかし、このままだと右上のセルが元々の想定リターンの値となっているので、表の見栄えをよくするために、このセルを「想定リターン」というタイトルに変更します。そのためには、先ほど説明したセルのフォーマットで表示形式を「ユーザー定義」にし、「想定リターン」と表示されるように変更します。

感応度分析-ステップ5‐2

こうすると、以下のように見栄えが良くなります。

感応度分析-ステップ5-3

複入力テーブル(変数2つ)の作り方

ここでは、運用期間と金利が変化すると想定リターンがどうなるかを分析します。

まず、縦軸に運用期間、横軸に金利を並べます。

感応度分析-ステップ6

次に、左上のセルに前提の想定リターンをリンクさせます。

感応度分析-ステップ7

その後、先ほどと同様にエクセルのデータテーブルを選択して、行の代入セルに前提の金利、列の代入セルに運用期間をリンクさせます。

感応度分析-ステップ8

すると、以下のとおり一瞬で感応度分析ができあがります。

感応度分析-ステップ9

ただし、このままだと左上のセルに前提の想定リターンが表示されたままなので、このセルの文字の色を白にすることで、見栄えをよくします。

感応度分析-ステップ10

これで、感応度分析が完成です。

SNSでもご購読できます。

コメントを残す

*