モデルの循環参照に効果覿面なExcel術 【Circuit Breaker】
複雑なモデルを作る上で、避けて通れない問題が循環参照。エクセルがうまく回っている時はいいが、循環参照込みのエクセルは脆い。ひょんなことからエクセル全体が#REF!エラーでバグり、何十分かけて修復する・・ということは珍しくない。実はこの問題【Circuit Breaker】で簡単に解決できる。
循環参照とは？
かなり簡易的なモデルを使って、まず循環参照のコンセプトについて説明する。2022A列の青字は実績値、それ以降の列の青字は予想値。なお、このNoteはモデルの組み方講座ではないので、キャッシュフローは今回の説明に無関係な項目は全て省いて、簡略化している。
Cash Flow：利息支払い前の利益から、後ほど計算するDebt Interest（支払利息）を引いたものが毎期のCash Profit（現金ベースの利益）
Cash Schedule：現金残高の流れを示した表。Beg Balance（期首残高）に先ほどのCash Profitを足し、Minimum Cash（ビジネスに必要最低限の現金、ここでは「5」と仮定。一旦除外するが後で足し戻す）を引いた金額（枠で囲んだ合計額）がDebt Paydown（借入金返済）に充てられる余剰現金。End Balance（期末残高）を計算するには、上記にMinimum Cashを足し戻し、最後に後ほど計算するDebt Paydownを引く
Debt Schedule：借入金残高の流れを示した表。Beg Balanceから返済するDebtを控除してEnd Balanceを計算。Debt Paydown（返済する借入金）は最低でも枠で囲んだ合計額、最大でも期首時点の残高
Debt Interest：期首と期末の借入金残高の平均値に利率をかけて、毎期の利息を計算。図1では、2023EのDebt Interestは期首35と期末18の平均値に10％をかけて算出。実務では月次でモデルを組むことはあまりないため、このように期首と期末の平均値を使い利息を計算することが多い
図1の矢印の流れでも分かるように、この計算はDebt Interestのせいで循環している。簡単に説明すると、
毎期のCash ProfitはDebt Interestに影響される→
Debt Interestは期末の借入金残高に影響される→
期末の借入金残高はDebt Paydownに影響される→
Debt PaydownはCash Profitに影響される→
❶に戻る
Circuit Breakerの役割
冒頭で触れたように循環参照が入ったモデルを組んだことがある人は経験があると思うが、図2のようにエクセルが何かの拍子にいきなりバグることはよくあり、エラーの根源が不明な場合は修復でタイムロスが生じる。
これを秒速で直す方法がCircuit Breaker。エクセルのどこかにオン・オフできるスイッチを作り、循環参照の根源にIF式を使って組み込む。今回の例ではDebt Interestがエラーの根源に当たるので、図3のような形で該当するセル（D24とE24）にスイッチをリンクさせる。
Circuit Breakerの使い方は簡単でエクセルがエラーになった時、スイッチをオン（黄色セルに1を入力）にし、一旦Debt Interestを0にする。
エラーが全て消えたことを確認したら、今度はスイッチをオフ（黄色セルに0を入力）。すると、エクセルは元通りになる。Circuit Breakerを使って循環参照を一時的にリセットしてあげたことで、エクセルが修復された。
参考までに図6が各セルの数式を表示したエクセル。
今回の例のような簡単なモデルの場合は単純にDebt Interestのセルを一時的に空白にしても同じ効果が得られる。但し、実務のモデルはもっと複雑になるので、Circuit Breakerを用いるとエラー修復時にかなりの時間短縮になる。循環参照が必要なエクセルには是非Circuit Breakerを組み込んでほしい。
余談: 上記のような利息の計算以外でも循環参照が必要な場面は多々ある。よくある例がオプションを発行してる会社の一株あたりのバリュエーションを算出する時。これについては、また別途まとめる予定。
この記事が気に入ったらサポートをしてみませんか？