モデルテスト（解説編 後半）
こちらの記事で紹介したモデルテストとその解答例について、作成手順に沿って解説していきます。（解説編 前半はこちらへ）
解説編前半ではPL項目、BS項目の作成に関して紹介しましたが、後半ではキャッシュフローとリターン計算について紹介します。
記事の全文は無料ですが、内容に満足頂けましたら最後に投げ銭で応援も頂けますと幸いです。（コーヒー代の足しにさせて頂きます！）
前半（Step 4まで）に作成したファイルはこちら↓
Step 5 CFSの作成
Step 4までで作成したPLとBSを参照し、Net Income、Depreciation、InterestはPLから、A/R、Inventory、A/P、Debt RepaymentはBSからそれぞれ数字を引っ張ってきます。
数字を参照するときは+/-の符号に気を付けましょう。+はキャッシュの増加、- はキャッシュの減少を意味しますので、A/RやInventory等の資産が増えた=現金が資産に変換された、と考え、A/P等の負債が増えた=支払いが将来に猶予され、PL上では支払ったかのように一度減少した現金が手元に返ってきた、と考えれば+/-の向きも間違いにくいのではないかと思います。
DividendとCapital Reductionはこの後作成するので一旦空欄のままで大丈夫です。
一番下まで行ってCash Balanceの計算にNet CFを繋げ、期末残高をBSの現預金の部分に繋げると、見事BSが一致すると思います。
BSが一致したら、PLから一番下のCash Balanceまでの一列を選択し、横にコピペします。BSも全期間で一致していると思います。
この段階では事業から得られたキャッシュを貯めこんでいるだけなので、この後のステップで余剰現預金の分配（配当と減資）のロジックを組み込んでいきます。
Step 6 CFSの作成（配当）
CFSのDividend（配当）とCapital Reduction（減資）の内、まずはDividend（配当）から作成してきます。配当と減資どちらが優先されるかというと、実際に実施する際の手間等から考えても配当が優先してなされるでしょう。
配当の原資となるのは「分配可能現預金」ですが、配当として分配できる金額の上限は「配当考慮前の利益剰余金の残高」となります（正確には少し違いますが、モデル上はこの理解でOKです。）
分配可能現預金について、ローンの利息と約定弁済の支払い後のCF（FCF to Equity）がCF available for Dividendsとなり、そこに期首の現預金残高を加算し、一方で最低限預金として残さねばならない金額を差し引きます。差し引いた残りが＋であればそれが分配可能な現預金（≠CF）となります。
当期の配当については、この分配可能な現預金と配当考慮前の利益剰余金の残高のどちらか小さい方となります。「現預金>利益剰余金残高」の場合は利益剰余金残高が配当可能な金額の上限となり、「現預金<利益剰余金残高」であれば分配可能現預金の額が配当可能な金額の上限となります。
この計算を行うときはIF文を使っても良いですが、Max関数とMin関数だけで計算することができ、
[=+MIN(分配可能現預金,MAX(利益剰余金残高,0))]
という式を使います。
上記の式でMax関数を使用しているのは、利益剰余金がマイナスの場合は0と計算する為です。分配可能現預金の計算の方でも同様の理由でMax関数を使用しているのが、ファイルの中の式を見て頂ければ分かると思います。
これでCFSの配当とBSの利益剰余金残高の計算が出来ましたが、上記の現預金>利益剰余金残高の場合、分配可能なのに配当できない現預金が余ってしまいますので、その場合には「減資」という形で出資者へ還流します。
Step 7 CFSの作成（減資）
減資の考え方のロジックはStep 6の配当とほぼ同じです。
分配可能現預金の計算においてCF available for Capital Reductionを配当考慮後のCFで計算し、配当考慮前の利益剰余金残高の代わりに資本金（Paid-in Capital)の残高を使用して計算するだけです。
CFSのCapital Reductionに減資によるCFを反映すればBSの現預金とも繋がり、これで見事に財務三表完成となります。
Step 8 リターン（IRR）計算
財務三表が出来たら、モデルテストで課題②として出題していた各種IRRについて計算してみましょう。
Project IRRはそのプロジェクト(事業)全体のリターンを計算するもので、どのCFを当初投資額や収益として計算するかは各PJ毎に異なることがありますが、以下の通り計算するケースが多いです。
当初投資額 = 借入+出資
収益 = FCF to F [= CF Available for Debt Service(CFADS)]
添付のモデルでは持分の30%だけで計算してますが、100%でも同じです。
Equity IRRはEquity（出資）に対するリターンとなるので、以下の通り計算します。
当初投資額 = 出資額（又は株式取得価格）
収益 = FCF to Equity [=CF Available for Equity]
Dividend IRRはあまり実務で使用することはないですが、以下の通り計算します。
当初投資額 = 出資額（又は株式取得価格）
収益 = Dividend + Capital Reduction
Equity IRRとの違いは、Equity IRRが実際に払い出せるか否かを考慮しないでリターン計算をしているのに対し、Dividend IRRでは上記のStep 6やStep 7で計算した各期に配当や減資として払い出せる能力を考慮したリターン計算である点です。現預金はあるのに、利益剰余金が足りないケース等では払出のタイミングが遅くなる為、E-IRRよりは低くなることがあります。
P-IRRとE-IRRの比較については、P-IRRをWACC、E-IRRをCost of Equityと考えると分かりやすいかもしれません。期待リターンの低いDebtをレバレッジとして活用することで、E-IRRを高くすることが出来ます。
例えば、P-IRRが10%のPJについて、借入の割合が70%、金利が2%の場合と借入の割合が60%、金利が3%の場合だと以下の通りEquityのCost of Capital（=EIRR）は異なります。（Debtの節税効果は未考慮）
投資ファンドの人たちは少しでも多くのDebtを安く調達し、Equity IRRを高めるために銀行団と日夜ハードな交渉をしているのです。（多分）
おわりに
以上でモデルテスト解説編を終わります。
今回モデルテストを通して紹介したのは、エクセルを使用して財務三表を作る基本的な考え方や手順の一つ（これが唯一の正攻法というわけではない）ですが、この応用でLBOモデルや新規事業計画の財務モデリングなんかもできるようになると思います。
財務モデルを組む上で忘れてはいけないのは、財務モデルはただのツールの一つであり、より正確なモデルや複雑なモデルを組むことがゴールではありません。（例として）投資検討を行う上で経済性の試算を行うことや銀行と借入交渉を行うことが財務モデルを組むことの目的であり、緻密で正確なモデルを組むことが目的ではないので、財務モデルを組む時は「財務モデルを組む目的」を念頭にどのように作ればよいか（計算ロジックはどうすればよいか、より細分化した方がよいのはどこか、省略してもOKなところはどこか等）を考えながら作業に没頭しましょう。
どんなに売上の構成を細分化しても、その変数が与えるインパクトが小さければその項目を変数とする意味がないですし、コントロールできない数字をKPIとして事業計画を作成してもアクションに結びつきません。
計画上の数字と取るべきアクションに整合性があって初めて予実対比が活きてくるので、例えば売上を構成する要素の内Keyとなる要素は何か、その項目を改善するにはどのようなアクションが必要か、そのアクションを行うにはどの程度コストが必要か、といった内容をモデルで分析・予測できるように作り込むことが大事だと考えて、私は財務モデルを組むようにしています。（なので、BDD経験のある戦略コンサルの方が財務モデルを組めるようになると良いモデルができるようになるのだろうなと思います。）
次回はLBOモデルのテストでも紹介しようと思います。
「LBOモデルの解答や解説も読みたい！」という方がいれば、是非この記事にスキ！をお願いします！
ここから先は
¥ 100
この記事が気に入ったらサポートをしてみませんか？