How to perform sensitivity analysis in Excel | Steps to create a table of WACC x Permanent Growth Rate using data table (What-If analysis) / What to do when it is not calculated

Excelで感応度分析表を作るなら、データテーブル（What-If分析）が最も確実です。DCF法の感応度分析表を作る手順は5つです。①変動させる前提（WACC・永久成長率）を1つのセルにまとめ、モデル内のすべての計算がそのセルを参照するように作る。②表の左上に結果セル（株式価値）を参照する数式を置き、横軸・縦軸に候補値を直接入力する。③表の範囲を選択して「データ」タブ→「What-If分析」→「データテーブル」を開く。④「行の代入セル」に横軸の前提が入っているセル、「列の代入セル」に縦軸の前提が入っているセルを指定する。⑤表の中央の値がモデルの結果と一致することを確認する。

うまくいかない原因のほとんどは、前提が数式に直接書き込まれている、軸の値がモデルのセルを参照している、代入セルが別シートにある、計算方法が「データテーブル以外自動」になっている、のいずれかです。

この記事では、Excelで感応度分析表を作るこの手順を、設例のDCFモデルと操作模式図で説明し、よくあるエラーの対処法、株価算定・減損テストでの使い方を整理します。なお、図はExcelの実画面ではなく、操作を再現するための模式図です。

Excelで感応度分析をするなら：データテーブルとは

データテーブルは、Excelの「データ」タブにある「What-If分析」の機能の一つで、モデル内の1つまたは2つの入力セルの値を順に置き換え、そのたびに計算される結果を表にまとめる機能です。つまり、手作業で前提を書き換えて結果をコピーする作業を、Excelが自動で行います。1つの入力を変動させる「1変数データテーブル」と、2つの入力を同時に変動させる「2変数データテーブル」があります。そして、DCF法の感応度分析では、WACC（縦軸）と永久成長率（横軸）を組み合わせた2変数データテーブルが定番です。

Excelで感応度分析を行う設例のDCFモデル

説明に用いる設例は次のとおりです（単位は任意）。

• 予測期間5年のフリー・キャッシュ・フロー（FCF）：100、110、120、130、140
• 永久成長率（セルC4）：0%
• WACC（セルC5）：0%
• ネットデット（セルC6）：200
• 継続価値 ＝ 5年目のFCF ×（1＋永久成長率）÷（WACC － 永久成長率）＝ 140 × 1.01 ÷ 0.09 ＝ 1,571.1
• 事業価値 ＝ 各年のFCFの現在価値の合計7 ＋ 継続価値の現在価値 975.5 ＝ 1,423.2
• 株式価値（セルC17）＝ 事業価値 1,423.2 － ネットデット 200 ＝ 1,223.2
  

  図15-1 設例のDCFモデルと感応度分析表の枠（操作模式図）。継続価値の分子は5年目のFCF×(1＋C4)、分母は(C5－C4)で、いずれも前提セルを参照します。

Excelで感応度分析をする前の準備：モデルの作り方が9割

1. 変動させたい前提が「1つのセル」に入っていること：WACCや永久成長率が複数の数式の中に直接数値として書き込まれていると、データテーブルは機能しません。たとえば、継続価値の計算式は「=I9*(1+1%)/(10%-1%)」のように数値を直接書くのではなく、「=I9*(1+$C$4)/($C$5-$C$4)」のように前提セルを参照して書きます。
2. すべての計算がその前提セルを参照していること：割引係数（=1/(1+$C$5)^年数）、継続価値、継続価値の現在価値のすべてがC5を参照しているかを確認します。なぜなら、1か所でも直接入力があると、データテーブルの結果がずれるからです。
3. 代入セルがデータテーブルと同じシートにあること：データテーブルの「行の代入セル」「列の代入セル」は、データテーブルを置くシートと同じシートのセルでなければなりません。別シートのセルを指定すると「入力セルの参照が正しくありません」というエラーになります。なお、対処は後述します。

Excelで感応度分析表を作成する手順（2変数データテーブル）

具体的には、WACCを10%±1%、永久成長率を1%±1%の範囲で変動させ、株式価値の変化を表にします。

手順1：表の枠を作る

• 左上の角のセル（B20）：結果として知りたいセルを数式で参照します（「=C17」）。
• 角のセルの右側（横軸、C20〜G20）：永久成長率の候補値0%、0.5%、1.0%、1.5%、2.0% を直接入力します。
• 角のセルの下側（縦軸、B21〜B25）：WACCの候補値0%、9.5%、10.0%、10.5%、11.0% を直接入力します。

なお、横軸・縦軸の候補値は、数値を直接入力してください。なぜなら、モデル内のC4やC5を参照して「=C5+1%」のように作ると、データテーブルが代入セルを置き換えるたびに軸の値まで動いてしまい、結果が正しくならないからです。

手順2：範囲を選択してデータテーブルを起動する

角のセルB20から、横軸・縦軸の候補値と結果が入る空白セルを含む長方形の範囲B20:G25を選択し、「データ」タブ→「What-If分析」→「データテーブル」をクリックします。また、キーボードの場合は、Altキーを押した後にA、W、Tの順に押します。

手順3：代入セルを指定する

ダイアログの「行の代入セル」には、表の横方向（行）に並べた候補値を代入する先、すなわち永久成長率の前提セルC4を指定します。「列の代入セル」には、縦方向（列）に並べた候補値を代入する先、すなわちWACCの前提セルC5を指定して、OKを押します。

ただし、ここで行と列を逆に指定すると、永久成長率の候補値（0〜2%）がWACCのセルに、WACCの候補値（9〜11%）が永久成長率のセルに代入されます。表が転置されるのではなく、モデルの前提そのものが入れ替わった誤った結果になります。そのため、表の見出しとダイアログの対応を必ず確認してください。

図15-2 データテーブルのダイアログと代入セルの対応（操作模式図）。横に並べた値は「行の代入セル」へ、縦に並べた値は「列の代入セル」へ代入されます。

手順4：結果を確認する

表の中に、各組み合わせにおける株式価値が一度に表示されます。モデルのWACCと永久成長率が10%・1%であれば、表の中央（WACC10.0%×永久成長率1.0%）の値がモデルの株式価値1,223と一致しているはずです。また、念のため、モデルのC4・C5を手で変えてみて、表の該当セルと同じ値になるかを確認してください。

図15-3 完成した感応度分析表（設例の計算結果、単位は任意）。数値は設例のモデルから計算したものです。

手順5：見た目を整える

左上の角のセルB20には株式価値の数値がそのまま表示されるため、そのままでは見栄えがよくありません。そこで、セルの表示形式をユーザー定義で「;;;」（セミコロン3つ）にすると、数式を残したまま非表示にできます。ベースケース（モデルの前提と同じ組み合わせ）のセルに罫線や太字を付けておくと、読み手が基準を把握しやすくなります。さらに、条件付き書式で一定の価格（たとえば買収価格）を上回るセルに網掛けをすると、取締役会資料でも使いやすくなります。

入力セルが別シートにある場合

前提を入力シートにまとめ、モデルを別シートに置いている場合、データテーブルの代入セルに入力シートのセルを指定することはできません。そこで、次のいずれかで対処します。

4. データテーブルを入力シートに置く：まず、代入セル（入力シートのC4・C5）と同じシートにデータテーブルを作ります。角のセルにはモデルシートの結果セルを参照する数式（「=DCF!C17」）を置きます。
5. モデルシートに中継セルを作り、モデルの計算がその中継セルを参照するようにする：たとえばモデルシートのC4に「=入力!C4」、C5に「=入力!C5」という中継セルを作ります。そのうえで、割引係数や継続価値などモデル内のすべての数式が入力シートではなくこの中継セルを参照するように接続し直します。最後に、データテーブルの代入セルに中継セルC4・C5を指定します。なお、中継セルを作っただけで、モデルの数式が入力シートを直接参照したままでは、データテーブルが中継セルを置き換えてもモデルの結果は動かず、表がすべて同じ値になります。

Excelで感応度分析がうまくいかないときのよくあるエラーと対処

「データテーブル以外自動」にしたときの注意

「データテーブル以外自動」に設定した場合、モデルを修正した後に表の更新を忘れると、古い結果のまま資料に貼り付けてしまうリスクがあります。そのため、資料化する前には必ずF9で再計算し、中央のセルがモデルの結果と一致していることを確認する習慣をつけてください。

株価算定・減損テストでの使い方

評価人の立場では、自ら設定するWACCと永久成長率を変動させた表を示すのが一般的です。一方、事業計画を作成する側の立場であれば、売上成長率と営業利益率、あるいは営業利益率と設備投資水準のように、事業計画の前提を軸にした表も有用です。いずれにしても、次の点に留意してください。

• 変動幅は「合理的に起こり得る範囲」にする：WACCであれば±1〜2%、永久成長率であれば0〜2%程度が一般的です。逆に、極端な幅を取ると、表の意味が薄れます。
• 永久成長率がWACCに近づく組み合わせは表示しない：なぜなら、継続価値の計算式の分母（WACC－成長率）が小さくなり、価値が異常に大きくなるからです。
• 減損テストでは「減損に至る前提」を示す：回収可能価額が帳簿価額を下回る組み合わせに網掛けをすると、ヘッドルーム（余裕）が一目で分かります。IFRSでは、合理的に起こり得る前提の変化で減損が生じる場合に感度の注記が必要になるため、この表がそのまま注記の基礎になります。

1変数データテーブルの活用

• 1変数データテーブルの活用：売上成長率だけ、営業利益率だけといった1つの前提を変動させ、株式価値・EV/EBITDA倍率・IRRなど複数の結果を横に並べる使い方もできます。具体的には、角のセルの右側に複数の結果セルを参照する数式を並べ、「列の代入セル」だけを指定します。

なお、Excelで感応度分析表を作っても、「入力したWACCと永久成長率が合理的であること」が前提であることに変わりはありません。

summary

Excelで感応度分析表を作る方法をまとめると、データテーブルは、DCF法の感応度分析表を一度に作成できるExcelの標準機能です。うまく使うための鍵は4つです。変動させる前提を1つのセルにまとめ、モデル内のすべての計算がそのセルを参照するように作っておくこと。軸の候補値を直接入力にすること。代入セルとデータテーブルを同じシートに置くこと（別シートの場合は中継セルにモデルを接続し直すこと）。行の代入セルには横軸、列の代入セルには縦軸の前提を指定することです。なお、Excelが重くなる場合は計算方法を「データテーブル以外自動」にし、資料化の前に必ず再計算して中央の値を確認してください。

Things you can discuss with Keystone Advisory

Keystone Advisoryでは、株価算定・PPA・減損テストの計算モデルを、感応度分析を含めた形で構築し、計算モデル（Excel）ごと納品しています。自社で作成した評価モデルのレビュー（前提セルの参照関係、継続価値の計算、割引率の根拠）、監査法人に提出する感応度分析資料の作成、入力するWACCの算定など、Excelで感応度分析を行う際の実務についてもご相談ください。Contact formPlease contact us from.

References

Microsoftサポート「データ テーブルを使用して複数の結果を計算する」

Writer

Kensuke Tanabe (CPA)

Keystone Advisory

Engaged in valuation/PPA work, statutory audits, and IPO support at Tohmatsu Audit Corporation and PwC Advisory. He assumed his current position after working as a director at a PE fund and an issuer at an operating company.

※本記事の操作手順はMicrosoft 365版Excel（Windows）を前提としています。バージョンによってメニューの名称や位置が異なる場合があります。図は操作模式図であり、Excelの実画面ではありません。設例の数値は理解のために簡略化しています。