MBAで教えるエクセルを使った感度分析 テーブル編
MBAのビジネス講座 > エクセル > テーブル
エクセルテーブルとは感応度分析をする為のツールです。エクセルを使って将来の予想モデルを構築する場合、絶対的な解を特定できないことが多いです。そういう時はエクセルのテーブル機能を使って感応度分析を行い、複数の解から一番良さそうな解を探し出します。
例えば、持ち家と賃貸の経済性を比較する場合、将来の金利、インフレ率でどちらが優位か答えが変わってしまいます。将来の金利が2%である場合、3%である場合、4%である場合というようにいくつもシナリオを用意して、それぞれの場合における解を計算し、比較することを感応度分析と呼びます。
エクセルテーブルの使い方 (エクセル2010)
感応度分析を実行するにはエクセルのテーブル機能を使いましょう。
-
テーブルの準備をします。上図のA5～A10の列に金利のシナリオを並べます。そしてB4に金利と支払い額を変数にした現在価値の数式を入力します。ここでは一定の金利の下、定額の家賃を未来永劫支払い続けるという前提で、B１とB2セルを参照しながら＝年間家賃/金利（＝B2/B1）という式を入力しています。（※支払い額を金利で割ると未来永劫支払い続けた場合の現在価値がでます。これをMBAではPerpetuityとならいます）そして、上図のように打ち込んだセルを全て選択しておきます。
-
次にデータ→What IF分析→データテーブルを選択、上図のデータテーブル入力画面を呼び出します。上の例では縦にシナリオを入れているので、「列の代入セル」の欄に、結果を表現しているセルB4の数式の中で、どの参照変数にシナリオを代入するか指定します。ここでは、B4には＝B2/B1という式が入っており、金利がB１になりますので、B1をクリックして、選択します。
-
次にOKをクリックすると、下図のように空欄だったB5～B10にそれぞれの金利下での支払家賃の現在価値が計算されます。金利が2％であれば10,000の価値ですが、金利が7％になると2,857万円の価値しかないことになります。
スポンサーリンク
下の例では振らせる変数を一つだけ指定してますが、下図のように同時に２つの変数を振らせることが可能です。（下図ではB4～E4に年間家賃のシナリオを入れ、行の代入セルにB2を入力しています）
結果は以下のように出てきます。
