エクセルのデータテーブルを使った感応度分析

このブログでは、エクセルのデータテーブル機能を使用したDCF法における感応度分析の方法を説明します。

DCF法を含めた企業価値評価の実務では、パラメータ1つで価値が大きく変わることもあるため、パラメータを変化させた場合に価値がどのように変動するのかを何通りもシュミレーションします。
このシュミレーションのことを感応度分析や感度分析と呼びます。

例えば、割引率を1%上げたら価値はいくらになるのか？、利益率を1%上げたら価値はいくらになるのか？などです。

パラメータを変動させた結果を把握する際に、エクセルのパラメータをいちいち修正するのは煩雑ですし、一覧性もないため、あらかじめ想定されるパラメータのレンジ内における価値を計算しておくことがベターです。

エクセルにはデータテーブルという機能があり、この機能を使うことで簡単に感応度分析を行うことができます。
今回のブログでは、DCF法を題材にして、エクセルのデータテーブル機能を使った感応度分析を実演してみたいと思います。

DCF法におけるデータテーブルを使った感応度分析の実例

データテーブルを使った感応度分析では、2つのパラメータを変動させた場合の結果をシュミレーションすることができます。

DCF法を前提とした場合、どのパラメータを変動させるかの正解はありません。
パラメータの水準を決めきれない、ブレる可能性があるパラメータを選定する必要があります。

筆者のような外部の企業価値評価の業者であれば、自分たちが選定するWACC（割引率）や永久成長率を変動させた感応度分析を行うことが多いですが、事業計画作成者の立場であれば、利益率や設備投資の水準等を採用することもあると思います。

今回は、WACCと永久成長率を変動させた場合の株式価値をシュミレーションしてみたいと思います。

DCF法の計算シートが以下のような形だったとします。

感応度分析を行う際は、感応（変動）させたい部分をすべて式で参照することが重要です。
例えば継続期間の営業利益を「I5*(1+1%)」と数値を直接入力するのではなく、「I5*(1+E4)」とする必要があります。
K列に継続期間の計算で間違えやすい個所の算式を記載しています。

今回、感応度分析を行うレンジとして、WACCは10%±1%、永久成長率は1%±1%としました。
この前提の場合の感応度分析を行う際の表は以下の通り作成します。

N10のセルは、最終的に知りたい値（今回の場合は株式価値）のD18セルを参照します。
永久成長率と割引率の行列は逆でも構いません。

この表の永久成長率や、割引率の数値はDCF法のセルから参照しないようにしましょう。
データテーブルは、DCF法の数値を変動させた場合の変化を分析する機能ですので、DCF法のセルを参照してしまうと、DCF法のセルの数値の変動に合わせて、データテーブルの表の数値も変動してしまうからです。
Inputシートを別に作り、DCF法もデータテーブルもInputシートから数値を参照することをお勧めします。

ここまで来たら下準備は完了です。
データテーブルの作成は、下記画面の通り、N10～S15のセルの範囲を選択したうえで、エクセルの「データ」タブ⇒「What-If分析」⇒「データテーブル」を押下。

下記画面の通り、行の代入セルはDCF法の永久成長率のE4セルを、列の代入セルはDCF法のWACCのE13のセルを参照し、OKをクリックします。

これで感応度分析は終了ですが、N10のセルに「15,737」と表示されているままだと見た目が良くないので、書式を変更します。
何でもよいのですが、筆者はいつも表示形式として「””」(ダブルクォーテーション2つ)としています。
フォントの色を変更するなどでもよいと思います。

これで感応度分析の表の完成です！

念のため、DCF法の永久成長率やWACCを変動させて、データテーブルの結果通りに計算されるかを確認しましょう。

エクエルが重くなる場合の対処法

データテーブルは非常に便利な機能ですがエクセルが非常に重くなるという欠点があります。

初期設定の場合、自動でデータテーブルの計算を行うことになっているため、データテーブルの計算のみ手動に変更することをお勧めします。
手動への変更方法は以下の通りです。

エクセルの「数式」タブ⇒「計算方法の設定」⇒「データテーブル以外自動」です。

この設定を行うことで、データテーブルの更新は手動で行うことになります。
データテーブルを更新する際は「数式」タブ⇒「再計算実行」ボタンを押下します。

データテーブルを手動更新することによって、データテーブルを更新し忘れるリスクがありますので、エクセルを修正した場合は、データテーブルの更新を忘れないようにしましょう。

DCF法等を含めた財務モデリングにおいては、以下の書籍がおススメです。
値段が高いので、なかなかい買いづらいのですが。。。